AW: ComboBox mit Sheets
29.10.2008 10:44:00
Ingo
So sollte es jetzt gehen:
Dim wsh_name As String
Private Sub UserForm_Initialize()
Dim wsh As Worksheet
Me.ComboBox1.Clear
For Each wsh In ThisWorkbook.Worksheets
Me.ComboBox1.AddItem wsh.Name
Next wsh
End Sub
Private Sub ComboBox1_Click()
Dim l_row As Long
Dim i_row As Integer
wsh_name = Me.ComboBox1
l_row = ThisWorkbook.Sheets(wsh_name).Range("B" & Rows.Count).End(xlUp).Row
Me.cboBezeichnung.Clear
Me.cboArtikelNr.Clear
For i_row = 5 To l_row
Me.cboArtikelNr.AddItem ThisWorkbook.Sheets(wsh_name).Cells(i_row, 2)
Me.cboBezeichnung.AddItem ThisWorkbook.Sheets(wsh_name).Cells(i_row, 3)
Next i_row
End Sub
Private Sub cboArtikelNr_Click()
Dim rng As Range
Me.txtLagerbestand = ""
Set rng = ThisWorkbook.Sheets(wsh_name).Range("B:B").Find(Me.cboArtikelNr, LookIn:=xlValues, _
lookat:=xlWhole)
If Not rng Is Nothing Then
Me.txtLagerbestand = rng.Offset(0, 2)
End If
End Sub
Private Sub cboBezeichnung_Click()
Dim rng As Range
Me.txtLagerbestand = ""
Set rng = ThisWorkbook.Sheets(wsh_name).Range("C:C").Find(Me.cboBezeichnung, LookIn:=xlValues, _
lookat:=xlWhole)
If Not rng Is Nothing Then
Me.txtLagerbestand = rng.Offset(0, 1)
End If
End Sub
Bei Auswahl einer Tabelle werden die Comboboxen artikelnr und Bezeichnung gefüllt und bei Auswahl aus einer dieser Comboboxen das Textfeld Lagerbestände
mfG
Ingo Christiansen