Col2Combo

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

Combo As ComboBox, StartCellName, Optional WSh = "", Optional Wkb = "This"

Views 1792 Downloads 874


ANmarAmdeen
612
Components VBA-Excel
Revisions

v1.0