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