applying select case function to selectable options in listbox

[Kwasimitsu]

I have a listbox with multiple options to choose from on sheet1. On sheet2 I have the actual options that come up when selecting from the list box. the list box works in conjunction with the combobox to generate graphs. Certain options in the combobox only relate to particular options in the listbox. How do I write a select case function that will basically say, if you choose this one from the combobox, then only these options are available in the list box, and if you choose this other one in the combobox then only these other options are available in the listbox, and so on?

Here is the code for the combobox in question:

Please Login or Register  to view this content.

And here is the code for the listbox in question:

Please Login or Register  to view this content.

[xlbiznes]

Basically, based on the selection of the value in the combobox , you need to populate the listbox with the options.

[Kwasimitsu]

Ok, that makes sense. How would the select case structure look. More specifically, how do I write the line that tells excel to populate the listbox based on whats selected in the combobox?

Please Login or Register  to view this content.

Something like that?

[xlbiznes]

Here is a file to see how it will work and the code is in sheet1.

[Kwasimitsu]

Awesome. Thanks so much, this is really helpful!

[xlbiznes]

you are welcome.

[Kwasimitsu]

@ xlbiznes

I applied the code you showed me to my situation, and when I do it gives me an error message:

run time error 424
object required

When I debug, it highlights the line of code that says:

ListBox1.Clear


Here is the code you showed me retooled to my needs. Please let me know what you think might be causeing this error, as it's basically the exact same thing you showed me and your version ran perfect.

Please Login or Register  to view this content.

I am using excel 2007, not 2010.

[Everstrivin]

Try

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

thanks to http://www.mrexcel.com/forum/excel-q...box-items.html

[Kwasimitsu]

@Everstrivin

Thanks a bunch for your recommendation. One question though. You suggested "listBox1.Items.Clear();". Is the ";" part of the line of code as well?

Also, thanks for suggesting how I can check code!

[Kwasimitsu]

OK, I tried it with and without, and still getting the same error of "object required". So frustrating. I dont understand what it is about "ListBox1.clear" thats tripping up the program, or what "object" that it requires. Anything helps. Thanks

[Everstrivin]

Yeah, that first suggestion was just a result from a search (not even excel vba), sorry about that one.

For some reason the code can't find ListBox1. It might be that you need to put Sheets("Sheet1"). or userform1. before the statement.

[xlbiznes]

Hi,

As @Everstrivin, has suggested try adding the form name/sheet in front of the listbox.

In my file both the controls , were on the sheet.