Calling a macro when selecting a chart sheet-CodePudding

I'm using the following simple code to run a macro "mymacro" when clicking/selecting a sheet.

Private Sub Worksheet_Activate()
Call mymacro
End sub

When i execute the macro manually it's working very well but when i click on the sheet it is not. Basically i'm using the macro to change a chart colors...so when i apply this to a normal sheet where i have a chart as object it's working but when i tried with a sheet where there is only a chart (created by using "Move Chart" on new sheet option) nothing happens

Thank you for the help

CodePudding user response：

The name needs to be Private Sub Chart_Activate() since it is a Chart and not a Worksheet. As VBasic2008 pointed out, the code needs to be in the code module for the Chart. Press Alt F11 to open the VB Project, CRTL R to open the Project Explorer. Double click on the Chart, eg.Chart1.

Your code module should look like :

Private Sub Chart_Activate()
    Call mymacro
End Sub

Ensure that mymacro is Public or is also in this code module.