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:

Sub Populate_Combobox_Worksheet()




    'This line of code locks the boxes in place and prevents them from resizing
    ThisWorkbook.Worksheets("sheet1").Shapes("CommandButton1").Placement = xlFreeFloating

    'The Excel workbook and worksheets that contain the data, as well as the range placed on that data
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnData As Range

    'Variant to contain the data to be placed in the combo box.
    Dim vaData As Variant

    'Initialize the Excel objects
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Sheet3")

    'Set the range equal to the data, and then (temporarily) copy the unique values of that data to the L column.
    With wsSheet
        Set rnData = wsSheet.Range(wsSheet.Range("A1"), wsSheet.Range("A100").End(xlUp))
        rnData.AdvancedFilter Action:=xlFilterCopy, _
                          CopyToRange:=wsSheet.Range("L1"), _
                          Unique:=True
        'store the unique values in vaData
        vaData = wsSheet.Range(wsSheet.Range("L1"), wsSheet.Range("L100").End(xlUp)).Value
        'clean up the contents of the temporary data storage
        wsSheet.Range(wsSheet.Range("L1"), wsSheet.Range("L100").End(xlUp)).ClearContents
    End With

    'display the unique values in vaData in the combo box already in existence on the worksheet.
    With ThisWorkbook.Worksheets("sheet1").OLEObjects("ComboBox1").Object
        .Clear
        .List = vaData
        .ListIndex = -1
        
   
     
        
    End With

End Sub

And here is the code for the listbox in question:

Sub Populate_ListBox_Worksheet2()

    'This line of code locks the boxes in place and prevents them from resizing
    ThisWorkbook.Worksheets("sheet1").Shapes("CommandButton1").Placement = xlFreeFloating


    'The Excel workbook and worksheets that contain the data, as well as the range placed on that data
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnData As Range

    'Variant to contain the data to be placed in the combo box.
    Dim vaData As Variant

    'Initialize the Excel objects
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Sheet3")

    'Set the range equal to the data, and then (temporarily) copy the unique values of that data to the L column.
    With wsSheet
        Set rnData = .Range(.Range("C1"), .Range("C100").End(xlUp))
        rnData.AdvancedFilter Action:=xlFilterCopy, _
                          CopyToRange:=.Range("L1"), _
                          Unique:=True
        'store the unique values in vaData
        vaData = .Range(.Range("L1"), .Range("L100").End(xlUp)).Value
        'clean up the contents of the temporary data storage
        .Range(.Range("L1"), .Range("L100").End(xlUp)).ClearContents
    End With

    'display the unique values in vaData in the combo box already in existence on the worksheet.
    With ThisWorkbook.Worksheets("sheet1").OLEObjects("ListBox1").Object
        .Clear
        .List = vaData
        .ListIndex = -1
    End With

End Sub

[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?

For thisworkbook

Unitop=combobox1.value
param2=listbox1.value 

select case unitop

case "z"
value.range("C1:C5")=param2

case "b"
value.range("D1:D5")=param2

case "S"
value.range("E1:E5")=param2

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.

Sub CombBox_Change()

Dim LotIDz As String
Dim LotIDb As String
Dim LotIDs As String
Dim my_array() As String


ListBox1.Clear

Select Case ComboBox1.Text

Case "Z"
    my_array = Split(LotIDz, ",")
    
Case "B"
    my_array = Split(LotIDb, ",")
    
Case "S"
    my_array = Split(LotIDs, ",")
    
End Select

For x = 0 To UBound(my_array)
ListBox1.AddItem my_array(x)

Next










End Sub

I am using excel 2007, not 2010.

[Everstrivin]

Try

listBox1.Items.Clear();

or

If it's filled with AddItem:

ListBox1.Clear

If it's filled with RowSource:

ListBox1.RowSource = ""

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

[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

[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!

[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.