1

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

braX
  • 11,506
  • 5
  • 20
  • 33
a.lay
  • 161
  • 2
  • 8
  • The code needs to go into the sheet module e.g. `Sheet1` which in VBE is the name **not** in parentheses. Also, you need to deactivate the worksheet (e.g. select another sheet), and then activate it again. It's not enough to just click on it (when it is already active). – VBasic2008 Nov 26 '21 at 17:38
  • Try changing `Private` to `Public` – Brett Nov 26 '21 at 17:40
  • i've tried all you are proposing but it's not working. I think it's related to my sheet type. i'm editing my question to be more explicit – a.lay Nov 26 '21 at 18:04

1 Answers1

2

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.

Toddleson
  • 4,321
  • 1
  • 6
  • 26
  • I used `Private Sub Chart_Activate()` in the code module for the chart. and `mymacro` is public. i got this error "invalid use of property". but when i wrote the macro code directly inside the [Private Sub Chart_Activate()] it's working fine. Thanks, I can use it like this also if you don't have a solution to fix this problem – a.lay Nov 26 '21 at 18:49
  • 1
    Try adding the module for `mymacro` like `Call Module1.mymacro`. If `mymacro` is in a seperate VB Project, you will need to add a project reference and also include the project name like `MyAddon.Module1.mymacro`. – Toddleson Nov 26 '21 at 18:52