Select Case in ComboBox

B

Brian

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,
 
D

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
 
B

Brian

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
 
B

Brian

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


Brian said:
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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top