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]

Is your listbox single or multi select?

 

[chris1983]

Is your listbox single or multi select?

Single select

 

[chris1983]

Single select

I am sorry, multi select

 

[Fluff]

In that case do you want to get all the selected values?
If so can there be duplicates?

 

[dmt32]

Unlikely to solve your issue but couple of observations

• need to correct spelling of your variables data type

dim s as varient

to this

Dim s As Variant

Also,

This line

Dim i, n As Integer

Variable i declared this way is a Variant not an Integer.

When declaring variables even on same line you must explicitly declare the data type for each

Dim i As Long, n As Long

dealing with Rows, it is safer to declare as Long data type as rows > 32767

Hope Helpful

Dave

 

[chris1983]

Unlikely to solve your issue but couple of observations

• need to correct spelling of your variables data type

dim s as varient

to this

Dim s As Variant

Also,

This line

Dim i, n As Integer

Variable i declared this way is a Variant not an Integer.

When declaring variables even on same line you must explicitly declare the data type for each

Dim i As Long, n As Long

dealing with Rows, it is safer to declare as Long data type as rows > 32767

Hope Helpful

Dave

Thanks for your comments!

 

[chris1983]

In that case do you want to get all the selected values?
If so can there be duplicates?

I have already checked for duplicates when adding the values for the list, there are no any.
Yes, it would be great to get all the selected values

 

[Fluff]

How about

Private Sub CommandButton2_Click()
   Dim i As Long
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Me.ListBox1
      For i = 0 To .ListCount - 1
         If .Selected(i) Then Dic(.List(i)) = Empty
      Next i
   End With
   MsgBox Join(Dic.keys, Chr(10))
End Sub

 

[chris1983]

How about

Private Sub CommandButton2_Click()
   Dim i As Long
   Dim Dic As Object
  
   Set Dic = CreateObject("scripting.dictionary")
   With Me.ListBox1
      For i = 0 To .ListCount - 1
         If .Selected(i) Then Dic(.List(i)) = Empty
      Next i
   End With
   MsgBox Join(Dic.keys, Chr(10))
End Sub

It seems to work fine! Thank you very much!