UserForm with combobox and listbox depending on this

[chris1983]

Hi everyone!

I created a userform with a combobox and a listbox. The idea is to choose from the combobox, and depending on the choice, to choose from a specific list in the listbox and use that value later on.

So what i have done so far, is the following event with Combobox change:

Private Sub ComboBox1_Change()

Dim i, n As Integer
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

n = Application.WorksheetFunction.Match(Me.ComboBox1.Value, sh.range("1:1"), 0)
Me.ListBox1.Clear
For i = 2 To Application.WorksheetFunction.CountA(sh.Cells(1, n).EntireColumn)
    Me.ListBox1.AddItem sh.Cells(i, n).Value
 Next i

End Sub

After making a choice in combox ,the listbox shows all the items correctly. I added a command button to run the following.

Private Sub CommandButton_Click()

dim s as varient
If Me.ListBox1.ListIndex > -1 Then
s = Me.ListBox1.Value
End If

End Sub

And here is the problem. I want to use the value of the selected item in listbox but i get an error. (Run-time error '94', Invalid use of Null). The listindex however is correct.

Any ideas? Thanks in advance

 

[Fluff]

You're welcome & thanks for the feedback