Running a macro on combo box selection

[Nashy19]

Hi, I'm looking to change a lot of formulas on the page by just using a combo box selection.

How can I assign a macro to each selection?

Idiots guide appreciated.

Thanks,

[Kenneth Hobson]

Depends on the control. If you use the Control Toolbox toolbar's Combobox, doubleclick it and type in the commands or macro to play. Click the design button to enable the combobox.

If you use the Form toolbar's Combobox, right click it and Assign your macro.

[Nashy19]

I'm on Excel 2007 so I have a choice of tool's or ActiveX combo boxes.

I want to run a different macro based on the combo selection.

[Kenneth Hobson]

You can use the value or the selected index. I used A1:A4 for the ListFillRange with values of 1-4. Use one of these 2 methods. Replace 1 or 2 at the end of the sub's Change with nothing to use it as the change event.

Obviously, you need to replace MsgBox with the name of your sub to run based on the value or index selected.

Private Sub ComboBox1_Change1()
  Select Case ComboBox1.Value
    Case 0: MsgBox "0"
    Case 1: MsgBox "1"
    Case 2: MsgBox "2"
    Case 3: MsgBox "3"
    Case Else: MsgBox "NA"
  End Select
End Sub

Private Sub ComboBox2_Change()
  Select Case ComboBox1.ListIndex 'Index 0 based
    Case 0: MsgBox "1"
    Case 1: MsgBox "2"
    Case 2: MsgBox "3"
    Case Else: MsgBox "NA"
  End Select
End Sub

[Nashy19]

How do I allow a macro to change tabs, at the moment I could record the macro for a certain tab but it will only run on the one I triggered it from.

[shg]

If you post a macro that runs on a given sheet, I or someone else will show you how to change it to operate on a different sheet.