AW: Exceldaten Formular übertragen
17.06.2011 13:42:57
Rudi
Hallo,
als Ansatz:
Combobox in 'Umzugsdaten' einfügen.
Code in der Tabelle:
Private Sub ComboBox1_Change()
Dim myNames
myNames = Application.Transpose(arrNames(ComboBox1))
Range(Cells(7, 3), Cells(7, 3).End(xlDown)).ClearContents
Cells(7, 3).Resize(UBound(myNames)) = myNames
End Sub
Private Sub Worksheet_Activate()
Application.EnableEvents = False
With ComboBox1
.Clear
.List = arrAbt
End With
Application.EnableEvents = True
End Sub
In ein Modul:
Function arrAbt()
Dim oAbt As Object, rngC As Range
Set oAbt = CreateObject("Scripting.Dictionary")
With Sheets(1)
For Each rngC In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
oAbt(rngC.Value) = 0
Next
End With
arrAbt = oAbt.keys
End Function
Function arrNames(ByVal strAbt As String)
Dim oNames As Object, rngC As Range
Set oNames = CreateObject("Scripting.Dictionary")
With Sheets(1)
For Each rngC In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
If rngC = strAbt Then
oNames(rngC.Offset(, 1).Value & ", " & rngC.Offset(, 2).Value) = 0
End If
Next
End With
arrNames = oNames.keys
End Function
Tab1 aktivieren und wieder zurück zu Umzugsdaten. Dann ist die CBx gefüllt.
Gruß
Rudi