Select Case in ComboBox

[Guest]

I have a combobox from which, when the user selects an item, I want it to
find the data within a range, and use it to populate a ListBox. For the life
of me, I can't seem to figure it out. Any thoughts would be appreciated as
this is my first attempt at using Excel VBA. The code I have so far is:
(there is more than one case statement, but one should suffice for pointers.)

Private Sub ComboBox1_click()
Dim refrange As Range
Select Case ComboBox1.Value
Case "GSOP_286"
Set refrange = Sheets("Data").Range("A3:A20").Value.Select
For Each c In refrange
ListBox1.AddItem c.Value
Next
End Select
End Sub

Thanks,

 

[Dave Peterson]

Maybe...

Private Sub ComboBox1_click()
Dim refrange As Range
dim c as range
Select Case ComboBox1.Value
Case "GSOP_286"
'no .value and no .select
Set refrange = Sheets("Data").Range("A3:A20")
For Each c In refrange
ListBox1.AddItem c.Value
Next c
End Select
End Sub

 

[Guest]

Thanks for the quick response.

Tried but failed. Am I right in assuming that the ListBox.AddItem will
result in the creation of the ListBox? The ComoBox was created in the code;

Private Sub UserForm_Initialize()
Dim myrange As Range

Set myrange = Sheets("GSOPs").Range("A4:A40")

For Each c In myrange
ComboBox1.AddItem c.Value
Next
End Sub
Private Sub ComboBox1_click()
Dim refrange As Range
Dim c As Range
Select Case ComboBox1.Value
Case "GSOP_286"
'no .value and no .select
Set refrange = Sheets("Data").Range("A3:A20")
For Each c In refrange
ListBox1.AddItem c.Value
Next c
End Select
End Sub

 

[Guest]

Dave,
Ignore previous post, sorted it out.
Thanks for your help. The final problem was down to (my) finger trouble.
--
Brian McCaffery

Thanks for the quick response.

Tried but failed. Am I right in assuming that the ListBox.AddItem will
result in the creation of the ListBox? The ComoBox was created in the code;

Private Sub UserForm_Initialize()
Dim myrange As Range

Set myrange = Sheets("GSOPs").Range("A4:A40")

For Each c In myrange
ComboBox1.AddItem c.Value
Next
End Sub
Private Sub ComboBox1_click()
Dim refrange As Range
Dim c As Range
Select Case ComboBox1.Value
Case "GSOP_286"
'no .value and no .select
Set refrange = Sheets("Data").Range("A3:A20")
For Each c In refrange
ListBox1.AddItem c.Value
Next c
End Select
End Sub

 

[Dave Peterson]

Nope. .additem adds more items to an existing listbox.

You can add the listbox in your code (too much work though).

Or you can just design your form with the listbox already there. If you don't
want it to show when you start, you can can start with:

me.listbox1.visible = false

And decide to show it later with

me.listbox1.visible = true

The code you shared didn't create the combobox. It just added items to the
existing combobox.

======
Could it be that the value in the combobox isn't GSOP_286.

maybe...

Select Case lcase(trim(ComboBox1.Value))
Case lcase("GSOP_286")

to guard against leading/trailing spaces and lower case letters?????

Thanks for the quick response.

Tried but failed. Am I right in assuming that the ListBox.AddItem will
result in the creation of the ListBox? The ComoBox was created in the code;

Private Sub UserForm_Initialize()
Dim myrange As Range

Set myrange = Sheets("GSOPs").Range("A4:A40")

For Each c In myrange
ComboBox1.AddItem c.Value
Next
End Sub
Private Sub ComboBox1_click()
Dim refrange As Range
Dim c As Range
Select Case ComboBox1.Value
Case "GSOP_286"
'no .value and no .select
Set refrange = Sheets("Data").Range("A3:A20")
For Each c In refrange
ListBox1.AddItem c.Value
Next c
End Select
End Sub

 

[Dave Peterson]

I didn't see this followup. Ignore my other reply <vbg>.