Reads everything in a column (starting from certain cell until blank cell is found) into a combobox and select the default value
Default value is the cell above the StartCell
Code need to be in same form as combobox
' Reads everything in a column (until blank cell is found) into a combobox and select the default value
' Default value is the cell above the StartCell
' Code need to be in same form as combobox
Sub Col2Combo(Combo As ComboBox, StartCellName, Optional WSh = "", Optional Wkb = "This")
Dim R_List As Range
If Wkb = "This" Then Wkb = ThisWorkbook.Name
If WSh = "" Then WSh = Workbooks(Wkb).Worksheets(1).Name
Combo.Clear
Set R_List = Workbooks(Wkb).Worksheets(WSh).Range(StartCellName)
X1 = 1
St1 = R_List.Offset(X1).Value
Do Until St1 = ""
Combo.AddItem St1
X1 = X1 + 1
St1 = R_List.Offset(X1).Value
Loop
If Combo.ListCount > 0 Then Combo.ListIndex = Val(R_List.Offset(-1).Value) - 1
Set R_List = Nothing
End Sub
' Default value is the cell above the StartCell
' Code need to be in same form as combobox
Sub Col2Combo(Combo As ComboBox, StartCellName, Optional WSh = "", Optional Wkb = "This")
Dim R_List As Range
If Wkb = "This" Then Wkb = ThisWorkbook.Name
If WSh = "" Then WSh = Workbooks(Wkb).Worksheets(1).Name
Combo.Clear
Set R_List = Workbooks(Wkb).Worksheets(WSh).Range(StartCellName)
X1 = 1
St1 = R_List.Offset(X1).Value
Do Until St1 = ""
Combo.AddItem St1
X1 = X1 + 1
St1 = R_List.Offset(X1).Value
Loop
If Combo.ListCount > 0 Then Combo.ListIndex = Val(R_List.Offset(-1).Value) - 1
Set R_List = Nothing
End Sub
Combo As ComboBox, StartCellName, Optional WSh = "", Optional Wkb = "This"
Views 3,053
Downloads 1,395
CodeID
DB ID