vba excel sort combobox items by cell value and auto select the corresponding one

Question

Been trying to find a way to populate a combobox by reading a range and only choosing the cells that have some value. I have some code that creates a button every row at column S to open a user form with a combobox. in column "H" from row 5 down, I have cells filled with colors (text) My objective is that the itemlist of the combobox shows by default not the 1st item from the range (starting at H5) but the corresponding item from each cell

Here's my piece of code for populating the combobox1:

Sub testingcombo()  

Dim c As Range  
Dim index As Integer

ComboBox1.Clear  
index = ComboBox1.ListIndex

With Worksheets("sheet1")

For Each c In .Range(.Range("H5"), .Range("H" & .Rows.Count).End(xlUp))

If c.Value <> vbNullString Then ComboBox1.AddItem c.Value

Next c

End With

Me.ComboBox1.ListIndex = 0 '(this only chooses by default the 1st entry of the range)

Thks,

Edgar

CMArg CMArg · Accepted Answer · 2017-01-24T11:58:27

In the code associated with the buttons, write this (not my code. Look here):

Public rs As Integer

Sub MyButton()
    Dim b As Object
    Dim cs As Integer
    Dim ss, ssv As String
    Set b = ActiveSheet.Buttons(Application.Caller)
    With b.TopLeftCell
        rs = .Row
        cs = .Column
    End With
    ss = Left(Cells(1, cs).Address(False, False), 1 - (ColNumber > 26)) & rs
    ssv = Range(ss).Value
    'MsgBox "Row Number " & rs & "    Column Number " & cs & vbNewLine & _
    '"Cell " & ss & "   Content " & ssv

    UserForm1.Show

End Sub

And then in the UserForm this:

Public Sub UserForm_Initialize()

    Dim c As Range
    Dim index As Integer

    ComboBox1.Clear
    index = ComboBox1.ListIndex

    With Worksheets("sheet1")

        For Each c In .Range(.Range("H5"), .Range("H" & .Rows.Count).End(xlUp))

            If c.Value <> vbNullString Then ComboBox1.AddItem c.Value

        Next c

    End With

    Me.ComboBox1.ListIndex = rs - 5
End Sub

vba excel sort combobox items by cell value and auto select the corresponding one

1 Answers