Run macro when combo box changes

[Thomas]

Heya everyone... can't seem to get this one:

I have a worksheet that I've added a combobox to that allows users the ability to change the data that is shown. I need to run a macro when the user selects a different value in the combobox,, but cannot seem to get it to fire off.

I've named the combobox 'RepComboBox' and tried adding the code

Private Sub RepComboBox_Change()
       RunMyMacro
End Sub

but nothing happens. Any thoughts?


PS... if it matters, I'm on Excel 2007.

 

[dave3009]

Try

Private Sub RepComboBox_Change()
Run MyMacro
End Sub

Or

Private Sub RepComboBox_Change()
Call MyMacro
End Sub

 

[Thomas]

Hmm... unfortunately, these doesn't seem to trigger the code.

If I manually run:

Private Sub RepComboBox_Change()
Run MyMacro
End Sub

I get an error for 'expected function or variable'

If I manually run:

Private Sub RepComboBox_Change()
Call MyMacro
End Sub

It calls the function like it should, but the RepComboBox doesn't trigger it.

I've even tried using a Worksheet_Change() event looking at the linked cell for the ComboBox, but still no dice.

 

[dave3009]

Well I can't see why, what is MyMacro please

 

[Thomas]

Here's what I have:

In module for the worksheet "Dashboard":

Private Sub RepComboBox_Change()
     Call ChartLimits
End Sub

In its own module (truncated version):

Sub ChartLimits()
    Sheets("Dashboard").Activate
    
    If ThisWorkbook.Names("RepChoice").RefersToRange = "Summary - All" Then
        ActiveSheet.ChartObjects("Chart 4").Select
        ActiveChart.Axes(xlValue).MaximumScale = 25000
        ActiveChart.Axes(xlValue).MajorUnit = 12500
        
        ActiveSheet.ChartObjects("Chart 5").Select
        ActiveChart.Axes(xlValue).MaximumScale = 25000
        ActiveChart.Axes(xlValue).MajorUnit = 12500
        ActiveChart.SetElement (msoElementPrimaryValueAxisNone)
    Else
        ActiveSheet.ChartObjects("Chart 4").Select
        ActiveChart.Axes(xlValue).MaximumScale = 5000
        ActiveChart.Axes(xlValue).MajorUnit = 2500
        
        ActiveSheet.ChartObjects("Chart 5").Select
        ActiveChart.Axes(xlValue).MaximumScale = 5000
        ActiveChart.Axes(xlValue).MajorUnit = 2500
        ActiveChart.SetElement (msoElementPrimaryValueAxisNone)
    End If

    Sheets("Dashboard").Range("A1").Select
End Sub

To explain: the named range in the book 'RepChoice' is an offset of the list populating the ComboBox, and its value is equal to that of the user's choice in the box (as opposed to the numerical value output by the ComboBox itself); when "Summary - All" is selected, the chart limits are raised; otherwise they're lowered.

So far, I haven't been able to get the event to trigger.

 

[Thomas]

***SOLVED***

...not exactly what I was expecting to do, but I happened to right-click the control and noticed an 'Assign Macro' option; did that and it works a treat!

Never could get an event to fire; still don't understand why, but I guess it doesn't matter if it works.

Thanks anyway for the help!

 

[dave3009]

Right there are two comboboxes in Excel, one comes from the Forms controls and the other from the controls (or ActiveX) controls. The fact that you needed to assign a macro means you chose the one from the Forms controls. If you use the other one and right click you will not see 'assign macro' but 'view code' instead.

Point to note for future reference

 

[ardykav]

This thread is very old but I have found it extremely helpful. Thanks