Rowsource aus Tabellenblatt referenzieren
08.10.2004 08:47:06
Jens_Pu
ich bräuchte mal wieder Eure Hilfe.
Geht das auch irgendwie ohne die beiden Activate Zeilen?
Public AnzCombobox As Integer
Private Sub ComboBoxen_einlesen()
'Liest dei gespeicherten Daten vom Tabellenblatt in die ComboBoxen ein
Dim wks As Worksheet
Dim i As Integer
Dim objname As String 'Objektname
Dim RSspalte As Integer 'Spalte der Rowsource
Dim lRSzeile As Integer 'letzte Zeile der Rowsource
Dim RSrange As Range 'Bereich Rowsource
Set wks = Worksheets("ComboRows")
wks.Activate
For i = 1 To AnzCombobox
objname = "Combobox" & i
RSspalte = WorksheetFunction.Match(objname, wks.Rows(2), 0)
lRSzeile = wks.Cells(65536, RSspalte).End(xlUp).Row
Set RSrange = wks.Range(wks.Cells(3, RSspalte), wks.Cells(lRSzeile, RSspalte))
With Controls(objname)
.RowSource = RSrange.Address
End With
Next i
Worksheets("Eingabeformular").Activate
Set wks = Worksheets("ComboForm")
For i = 1 To AnzCombobox
objname = "Combobox" & i
With Controls(objname)
.Value = wks.Cells(i + 1, 3)
.ListIndex = wks.Cells(i + 1, 4)
.Enabled = wks.Cells(i + 1, 5)
.Locked = wks.Cells(i + 1, 6)
.Visible = wks.Cells(i + 1, 7)
.MatchRequired = wks.Cells(i + 1, 8)
.TabStop = wks.Cells(i + 1, 9)
.TabIndex = wks.Cells(i + 1, 10)
.ListRows = wks.Cells(i + 1, 11)
.ControlTipText = wks.Cells(i + 1, 13)
.Tag = wks.Cells(i + 1, 14)
.text = wks.Cells(i + 1, 15)
.Left = wks.Cells(i + 1, 16)
.Top = wks.Cells(i + 1, 17)
.Height = wks.Cells(i + 1, 18)
.Width = wks.Cells(i + 1, 19)
.TextAlign = wks.Cells(i + 1, 20)
.BackColor = wks.Cells(i + 1, 21)
.ForeColor = wks.Cells(i + 1, 22)
End With
Next i
End Sub
Gruß Jens