different lists in a combobox

Hi

On a worksheet i have 4 optionbuttons and a combobox (from the control toolbox bar) how would you (if poss) get the list to change in the combobox each time a different optionbutton is selected. The differnet lists i have for the combobox are in cell column on the worksheets hidden away

Many thanks

Raw

Hello Raw,

Here is macro that will get you going. You will have to modify this to run in your workbook. You will need to enter the names of the Drop Down, the Option Buttons, and the Fill Ranges for the Drop Down. This example has only 2 Option Buttons and the Fill Ranges are on the same sheet as the Option Buttons and Drop Down.

Sub ChangeFillRange()

 'Name of the Drop Down (ComboBox)
  DropDwn = "Drop Down 3"
  
 'Get the name of the Option Button that was Clicked
  Btn = Application.Caller
  
   'Choose the Drop Down's Fill Range based on the Option Button
    Select Case Btn
      Case "Option Button 1"
        FillRng = "$A$1:$A$5"
      Case "Option Button 2"
        FillRng = "$B$1:$B$5"
      Case Else
        Exit Sub
    End Select
    
   'Complete the ListFillRange String
    FillRng = "'" & ActiveSheet.name & "'!" & FillRng
    
   'Reset the Drop Down's Fill Range
    ActiveSheet.Shapes(DropDwn).ControlFormat.ListFillRange = FillRng
 
End Sub

If you have any questions, let me know.

Sincerely,
Leith Ross

Hi
Thanks for the code, the only a couple of things

Where do you put (attach) the code to the combobox or in the sheet or to the optionbuttons?

Name of the Drop Down (ComboBox)
DropDwn = "Drop Down 3"

The dropdown box is called combobox1 should i insert this where you have put "drop down 3" as the name?

Many thanks for your help

RAW

Hello Raw,

I discovered that I coded the macro incorrectly. I coded it for a Forms Drop Down Box instead of a ControlToolBox ComboBox. So, please use the code in this post. Place the macro code in a Standard VBA Module. Here is how to do that...

1. Copy the macro code by selecting it and type CTRL+C
2. Open your Excel workbook
3. Type ALT+F11 (Press the ALT key down and press F11). This opens the VB Editor.
4. Type ALT+I (This activates the Insert menu).
5. Press M (This inserts a Module into your workbook).
6. Type CTRL+V to Paste the code.
7. Type ALT+Q to return to Excel.

Sub ChangeFillRange()

 'Name of the ComboBox
  CboName = "ComboBox1"
  Set Cbo = ActiveSheet.OLEObjects(CboName).Object
  
 'Get the name of the Option Button that was Clicked
  Btn = Application.Caller
  
   'Choose the Drop Down's Fill Range based on the Option Button
    Select Case Btn
      Case "Option Button 1"
        FillRng = "$A$1:$A$5"
      Case "Option Button 2"
        FillRng = "$B$4:$B$7"
      Case Else
        Exit Sub
    End Select
    
   'Load the ComboBox with the Fill Range Data
    Cbo.Clear
      With ActiveSheet
        For Each Cell In .Range(FillRng)
          Cbo.AddItem Cell.Value
        Next Cell
      End With
 
End Sub

To attach the Macro to the Option Buttons:
1. Right Click the Option Button
2. Click "Assign Macro..." on the popup menu.
3. Find "ChangeFillRange" in the macro list and Click it.
4. Click the "OK" button.
5. Repeat steps 1 to 4 for each Option Button

I put "ComboBox1" in the code already. All you need to change are the areas in red. The Option Button names and the Fill Ranges. Also add 2 more case statements for the 2 other Option Buttons. If you run into problems let me know.

Sincerely,
Leith Ross

Hi Leith Ross

Thanks for all your help

I have place the code in a module and the optionbuttons on the sheet are also from the controltoolbox panel so i place code to run the "changefillrange" code but when i click the optionbuttons i get a "Run-time error 13, Type mismatch"

I see in your code below you have the "Activesheet.OLEObject" code. So before this code i put the "sheets("sheet3").select" to see if this helped but i still got the same error and the Debug (when VB highlights the problem in yellow) was the "case "optionbutton1" part of the code

'Name of the ComboBox
CboName = "ComboBox1"
Set Cbo = ActiveSheet.OLEObjects(CboName).Object

Hope this make sense

again thanks for the help

RAW