Invoking a macro from a cell change

[Planner999]

Hi

I am trying to run a macro that needs to run whenever the user selects
an entry from a combo box on a chart page. The combo box cell link is
returning the value and I have tried to link to that cell using the
worksheet "Private Sub Worksheet_Change(ByVal Target As Range)".
If the value changes due to the combo box value changing nothing
happens, however if I manually change the value the macro runs.

How do I make this work for me please.....

Thanks in advance

John

 

[Dave Peterson]

It's really a Chart sheet--not a worksheet with a chart on it?

If that's true, then it's a dropdown from the Forms toolbar, right?

Why not just assign a macro to that dropdown (rightclick on it and choose Assign
Macro).

This worked ok for me:

Option Explicit
Sub testme()

Dim myDD As DropDown
Set myDD = ActiveSheet.DropDowns(Application.Caller)

If myDD.Value < 1 Then
MsgBox "nothing selected"
Else
With myDD
MsgBox .List(.ListIndex)
End With
End If
End Sub

 

[Jim Thomlinson]

Why not initiate the macro from the change event assocated with the Combo Box
instead of the linked cell? The linked cell will not fire in this case, but
the combo box will work. I would post an example for you but I don't know if
your combo box is from the Forms Toolbar or the Control Toolbox...

 

[Jim Thomlinson]

Come to think of it I don't think you can get controls from the control
toolbox on a chart sheet. That being said your combo box must be from the
forms toolbar. Right click the control and select Assign Macro... Associate
your macro with it and you should be ready to go...

My recomendation would be to take the guts of the change event code (give or
take a few modifications) and place it in it's own sub procedure. Your change
event will invoke that sub. Additionally your combo box will be associated
with that sub...

 

[Planner999]

Come to think of it I don't think you can get controls from the control
toolbox on a chart sheet. That being said your combo box must be from the
forms toolbar. Right click the control and select Assign Macro... Associate
your macro with it and you should be ready to go...

My recomendation would be to take the guts of the change event code (give or
take a few modifications) and place it in it's own sub procedure. Your change
event will invoke that sub. Additionally your combo box will be associated
with that sub...
--
HTH...

Jim Thomlinson







- Show quoted text -

Hi Both

I have invoked the macro from the combo box - so simple in the
end.....
Thanks for the time helping me out...


John