linking two optionbuttons with single combo box for different rowsource

Question

I have three sheets "display", "whole_list", "loose_list". I just draws one combo box and 2 optionbutton in display sheet.

I have 200 item list in whole_list and loose_list. I wish to do that when I click on the optionbutton1 combo box will show me the list from whole_list. If I click on the optionbutton2 it will show me the list from loose_list.

Can someone guide me to create that what I mentioned above?

Answer 1

So i have time to spare and thought i would show you a way with Forms controls.

This assumes display tab has the Form combobox and the two Form option buttons.

In whole_list set up a named range called whole_list covering your list of 200 items e.g. A1:A200 and do the same thing for loose_list.

In a standard module enter the following:

Option Explicit
Dim myList As Variant

Private Sub OptionButton1_Click()

   mylist = ThisWorkbook.Worksheets("whole_list").Range("whole_list").Value

   PopulateComboBox myList

End Sub


Private Sub OptionButton2_Click()

   mylist = ThisWorkbook.Worksheets("loose_list").Range("loose_list").Value

   PopulateComboBox myList

End Sub

Private Sub PopulateComboBox(myList As Variant)

    Dim wb As Workbook
    Dim ws as Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.worksheets("display")

    Dim myDropDown As DropDown

    Set myDropDown = ws.Shapes("Drop Down 1").OLEFormat.Object
    myDropDown.List = myList

End Sub 

Make sure the object and sheet names match up. You can check the combobox and option button names by highlighting them and then checking the Name box at the top left of the sheet.

Right click each Option Button and choose assign macro and associate each button with the correct OptionButton click sub.

If your objects are ActiveX:

In the worksheet code window containing your ActiveX objects e.g. display , if you right click on the tab and select view code, it will bring up the code window and enter the following (Assuming you have the two named ranges as before)

Option Explicit
Public myList As Variant

Private Sub OptionButton1_Click()

   mylist = ThisWorkbook.Worksheets("whole_list").Range("whole_list").Value

   Me.ComboBox1.List = myList

End Sub


Private Sub OptionButton2_Click()

   mylist = ThisWorkbook.Worksheets("loose_list").Range("loose_list").Value

   Me.ComboBox1.List = myList

End Sub

No additional procedure needed and no right click and assigning of macros. The Me part says take the reference of the sheet the code is in. If you have Option Explicit, at the top of the worksheet code, then you should receive a prompt which includes the name of your combobox, amongst the options available, but you can also get it from the Name Box in the sheet. Here, my combobox is called Combobox1.