Anzeige
Archiv - Navigation
1204to1208
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
UserForm in anderer Tabelle anzeigen
René
Hallo liebe Excelgemeinde,
kann mir eventuell zu diesem Code hier einen Tip geben, was ich ändern muss damit ich die UserForm in Tabelle1 angezeigt bekomme - und dabei die Tabelle2 von der die Daten ausgelesen werden ausgeblendet bleibt oder nicht sichtbar ist? Würde mich sehr freuen. Betrifft vor allem die UserForm Initialize an der wahrscheinlich eine Zeile geändert werden muss. Schönes WE René
Option Explicit
Private Sub ComboBox1_Change()
Dim SC_SL  As Object
Dim vTemp  As Variant
Dim iIndx  As Integer
Set SC_SL = CreateObject("System.Collections.SortedList")
vTemp = Range("B4:C" & Cells(Rows.Count, 2).End(xlUp).Row)
For iIndx = 1 To UBound(vTemp)
If vTemp(iIndx, 1) = ComboBox1.Value Then
If vTemp(iIndx, 2)  "" Then SC_SL(vTemp(iIndx, 2)) = ""
End If
Next iIndx
ComboBox2.Clear
For iIndx = 0 To SC_SL.Count - 1
ComboBox2.AddItem SC_SL.GetKey(iIndx)
Next iIndx
End Sub

Private Sub ComboBox2_Change()
Dim SC_SL  As Object
Dim vTemp  As Variant
Dim iIndx  As Integer
Set SC_SL = CreateObject("System.Collections.SortedList")
vTemp = Range("B4:D" & Cells(Rows.Count, 2).End(xlUp).Row)
For iIndx = 1 To UBound(vTemp)
If vTemp(iIndx, 1) = ComboBox1.Value And _
vTemp(iIndx, 2) = ComboBox2.Value Then
If vTemp(iIndx, 3)  "" Then SC_SL(vTemp(iIndx, 3)) = ""
End If
Next iIndx
ComboBox3.Clear
For iIndx = 0 To SC_SL.Count - 1
ComboBox3.AddItem SC_SL.GetKey(iIndx)
Next iIndx
End Sub

Private Sub ComboBox3_Change()
Dim WkSh          As Worksheet
Dim rZelle        As Range
Dim sFundst       As String
Dim sSuchbegriff  As String
sSuchbegriff = ComboBox3.Value
Set WkSh = ThisWorkbook.Worksheets("Tabelle2")
TextBox1.Value = ""
With WkSh.Columns(4)
Set rZelle = .Find(What:=sSuchbegriff, LookAt:=xlWhole, LookIn:=xlValues)
If Not rZelle Is Nothing Then
sFundst = rZelle.Address
Do
If ComboBox1.Value = WkSh.Range("B" & rZelle.Row) And _
ComboBox2.Value = WkSh.Range("C" & rZelle.Row) Then
If TextBox1.Value = "" Then
TextBox1.Value = WkSh.Range("A" & rZelle.Row).Value
Else
TextBox1.Value = TextBox1.Value & ", " & _
WkSh.Range("A" & rZelle.Row).Value
End If
End If
Set rZelle = .FindNext(rZelle)
Loop While Not rZelle Is Nothing And rZelle.Address  sFundst
End If
End With
End Sub
Private Sub UserForm_Initialize()
Dim iIndx  As Integer
Dim iTop   As Integer
Dim vText  As Variant
Dim SC_SL  As Object
Dim vTemp  As Variant
Worksheets("Tabelle2").Activate
vText = Array(" ", "Nachname", "Vorname", "Straße", "Nummer")
iTop = 16
For iIndx = 1 To 3
With Controls("ComboBox" & iIndx)
.Height = 20
.Left = 90
.Top = iTop
.Width = 148
.Font.Size = 12
.Style = 2
End With
iTop = iTop + 30
Next iIndx
With TextBox1
.Height = 20
.Left = 90
.Top = iTop
.Width = 148
.Font.Size = 12
End With
iTop = 20
For iIndx = 1 To 4
With Controls("Label" & iIndx)
.Height = 18
.Left = 12
.Top = iTop
.Width = 72
.Font.Size = 10
.Caption = vText(iIndx)
.TextAlign = 2
End With
iTop = iTop + 30
Next iIndx
Set SC_SL = CreateObject("System.Collections.SortedList")
vTemp = Range("B4:B" & Cells(Rows.Count, 2).End(xlUp).Row)
For iIndx = 1 To UBound(vTemp)
If vTemp(iIndx, 1)  "" Then SC_SL(vTemp(iIndx, 1)) = ""
Next iIndx
ComboBox1.Clear
For iIndx = 0 To SC_SL.Count - 1
ComboBox1.AddItem SC_SL.GetKey(iIndx)
Next iIndx
End Sub

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: UserForm in anderer Tabelle anzeigen
11.03.2011 16:48:54
René
Hallo liebe Excelgemeinde,
habe den Code wie folgt umgeschrieben, aber jetzt flackert der Bildschirm. Wie kann ich das in Office 2010 ausschalten? Kann mir bitte jemand einen Tip geben?
Danke René
Option Explicit
Private Sub ComboBox1_Change()
Dim SC_SL  As Object
Dim vTemp  As Variant
Dim iIndx  As Integer
Worksheets("Tabelle2").Activate
Set SC_SL = CreateObject("System.Collections.SortedList")
vTemp = Range("B4:C" & Cells(Rows.Count, 2).End(xlUp).Row)
For iIndx = 1 To UBound(vTemp)
If vTemp(iIndx, 1) = ComboBox1.Value Then
If vTemp(iIndx, 2)  "" Then SC_SL(vTemp(iIndx, 2)) = ""
End If
Next iIndx
ComboBox2.Clear
For iIndx = 0 To SC_SL.Count - 1
ComboBox2.AddItem SC_SL.GetKey(iIndx)
Next iIndx
Worksheets("A_Beanstandung").Activate
End Sub

Private Sub ComboBox2_Change()
Dim SC_SL  As Object
Dim vTemp  As Variant
Dim iIndx  As Integer
Worksheets("Tabelle2").Activate
Set SC_SL = CreateObject("System.Collections.SortedList")
vTemp = Range("B4:D" & Cells(Rows.Count, 2).End(xlUp).Row)
For iIndx = 1 To UBound(vTemp)
If vTemp(iIndx, 1) = ComboBox1.Value And _
vTemp(iIndx, 2) = ComboBox2.Value Then
If vTemp(iIndx, 3)  "" Then SC_SL(vTemp(iIndx, 3)) = ""
End If
Next iIndx
ComboBox3.Clear
For iIndx = 0 To SC_SL.Count - 1
ComboBox3.AddItem SC_SL.GetKey(iIndx)
Next iIndx
Worksheets("A_Beanstandung").Activate
End Sub

Private Sub ComboBox3_Change()
Dim WkSh          As Worksheet
Dim rZelle        As Range
Dim sFundst       As String
Dim sSuchbegriff  As String
Worksheets("Tabelle2").Activate
sSuchbegriff = ComboBox3.Value
Set WkSh = ThisWorkbook.Worksheets("Tabelle2")
TextBox1.Value = ""
With WkSh.Columns(4)
Set rZelle = .Find(What:=sSuchbegriff, LookAt:=xlWhole, LookIn:=xlValues)
If Not rZelle Is Nothing Then
sFundst = rZelle.Address
Do
If ComboBox1.Value = WkSh.Range("B" & rZelle.Row) And _
ComboBox2.Value = WkSh.Range("C" & rZelle.Row) Then
If TextBox1.Value = "" Then
TextBox1.Value = WkSh.Range("A" & rZelle.Row).Value
Else
TextBox1.Value = TextBox1.Value & ", " & _
WkSh.Range("A" & rZelle.Row).Value
End If
End If
Set rZelle = .FindNext(rZelle)
Loop While Not rZelle Is Nothing And rZelle.Address  sFundst
End If
End With
Worksheets("A_Beanstandung").Activate
End Sub

Private Sub UserForm_Initialize()
Dim iIndx  As Integer
Dim iTop   As Integer
Dim vText  As Variant
Dim SC_SL  As Object
Dim vTemp  As Variant
Worksheets("Tabelle2").Activate
vText = Array(" ", "Nachname", "Vorname", "Straße", "Nummer")
iTop = 16
For iIndx = 1 To 3
With Controls("ComboBox" & iIndx)
.Height = 20
.Left = 90
.Top = iTop
.Width = 148
.Font.Size = 12
.Style = 2
End With
iTop = iTop + 30
Next iIndx
With TextBox1
.Height = 20
.Left = 90
.Top = iTop
.Width = 148
.Font.Size = 12
End With
iTop = 20
For iIndx = 1 To 4
With Controls("Label" & iIndx)
.Height = 18
.Left = 12
.Top = iTop
.Width = 72
.Font.Size = 10
.Caption = vText(iIndx)
.TextAlign = 2
End With
iTop = iTop + 30
Next iIndx
Set SC_SL = CreateObject("System.Collections.SortedList")
vTemp = Range("B4:B" & Cells(Rows.Count, 2).End(xlUp).Row)
For iIndx = 1 To UBound(vTemp)
If vTemp(iIndx, 1)  "" Then SC_SL(vTemp(iIndx, 1)) = ""
Next iIndx
ComboBox1.Clear
For iIndx = 0 To SC_SL.Count - 1
ComboBox1.AddItem SC_SL.GetKey(iIndx)
Next iIndx
Worksheets("A_Beanstandung").Activate
End Sub

Anzeige
AW: UserForm in anderer Tabelle anzeigen
11.03.2011 16:48:58
René
Hallo liebe Excelgemeinde,
habe den Code wie folgt umgeschrieben, aber jetzt flackert der Bildschirm. Wie kann ich das in Office 2010 ausschalten? Kann mir bitte jemand einen Tip geben?
Danke René
Option Explicit
Private Sub ComboBox1_Change()
Dim SC_SL  As Object
Dim vTemp  As Variant
Dim iIndx  As Integer
Worksheets("Tabelle2").Activate
Set SC_SL = CreateObject("System.Collections.SortedList")
vTemp = Range("B4:C" & Cells(Rows.Count, 2).End(xlUp).Row)
For iIndx = 1 To UBound(vTemp)
If vTemp(iIndx, 1) = ComboBox1.Value Then
If vTemp(iIndx, 2)  "" Then SC_SL(vTemp(iIndx, 2)) = ""
End If
Next iIndx
ComboBox2.Clear
For iIndx = 0 To SC_SL.Count - 1
ComboBox2.AddItem SC_SL.GetKey(iIndx)
Next iIndx
Worksheets("A_Beanstandung").Activate
End Sub

Private Sub ComboBox2_Change()
Dim SC_SL  As Object
Dim vTemp  As Variant
Dim iIndx  As Integer
Worksheets("Tabelle2").Activate
Set SC_SL = CreateObject("System.Collections.SortedList")
vTemp = Range("B4:D" & Cells(Rows.Count, 2).End(xlUp).Row)
For iIndx = 1 To UBound(vTemp)
If vTemp(iIndx, 1) = ComboBox1.Value And _
vTemp(iIndx, 2) = ComboBox2.Value Then
If vTemp(iIndx, 3)  "" Then SC_SL(vTemp(iIndx, 3)) = ""
End If
Next iIndx
ComboBox3.Clear
For iIndx = 0 To SC_SL.Count - 1
ComboBox3.AddItem SC_SL.GetKey(iIndx)
Next iIndx
Worksheets("A_Beanstandung").Activate
End Sub

Private Sub ComboBox3_Change()
Dim WkSh          As Worksheet
Dim rZelle        As Range
Dim sFundst       As String
Dim sSuchbegriff  As String
Worksheets("Tabelle2").Activate
sSuchbegriff = ComboBox3.Value
Set WkSh = ThisWorkbook.Worksheets("Tabelle2")
TextBox1.Value = ""
With WkSh.Columns(4)
Set rZelle = .Find(What:=sSuchbegriff, LookAt:=xlWhole, LookIn:=xlValues)
If Not rZelle Is Nothing Then
sFundst = rZelle.Address
Do
If ComboBox1.Value = WkSh.Range("B" & rZelle.Row) And _
ComboBox2.Value = WkSh.Range("C" & rZelle.Row) Then
If TextBox1.Value = "" Then
TextBox1.Value = WkSh.Range("A" & rZelle.Row).Value
Else
TextBox1.Value = TextBox1.Value & ", " & _
WkSh.Range("A" & rZelle.Row).Value
End If
End If
Set rZelle = .FindNext(rZelle)
Loop While Not rZelle Is Nothing And rZelle.Address  sFundst
End If
End With
Worksheets("A_Beanstandung").Activate
End Sub

Private Sub UserForm_Initialize()
Dim iIndx  As Integer
Dim iTop   As Integer
Dim vText  As Variant
Dim SC_SL  As Object
Dim vTemp  As Variant
Worksheets("Tabelle2").Activate
vText = Array(" ", "Nachname", "Vorname", "Straße", "Nummer")
iTop = 16
For iIndx = 1 To 3
With Controls("ComboBox" & iIndx)
.Height = 20
.Left = 90
.Top = iTop
.Width = 148
.Font.Size = 12
.Style = 2
End With
iTop = iTop + 30
Next iIndx
With TextBox1
.Height = 20
.Left = 90
.Top = iTop
.Width = 148
.Font.Size = 12
End With
iTop = 20
For iIndx = 1 To 4
With Controls("Label" & iIndx)
.Height = 18
.Left = 12
.Top = iTop
.Width = 72
.Font.Size = 10
.Caption = vText(iIndx)
.TextAlign = 2
End With
iTop = iTop + 30
Next iIndx
Set SC_SL = CreateObject("System.Collections.SortedList")
vTemp = Range("B4:B" & Cells(Rows.Count, 2).End(xlUp).Row)
For iIndx = 1 To UBound(vTemp)
If vTemp(iIndx, 1)  "" Then SC_SL(vTemp(iIndx, 1)) = ""
Next iIndx
ComboBox1.Clear
For iIndx = 0 To SC_SL.Count - 1
ComboBox1.AddItem SC_SL.GetKey(iIndx)
Next iIndx
Worksheets("A_Beanstandung").Activate
End Sub

Anzeige
AW: UserForm in anderer Tabelle anzeigen
11.03.2011 16:52:45
René
Danke hat sich geklärt
ActiveWorkbook.PrecisionAsDisplayed = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
SChönes We René

307 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige