VBA event activate worksheet compile error

[bmaas93]

Hi,

I'am a beginner to VBA and trying to get a code running I got from a tutorial. The only thing I wanted to change is the trigger event. Change it from change cell to active worksheet.

For some reason I keep getting the compile error: a variable is not defined (or something like that, I have my excel in Dutch).

This is the code:

Option Explicit

Private Sub Worksheet_Activate()
  With ActiveWorkbook.ChartObjects("Grafiek 14").Chart
    Select Case Target.Address
      Case "$B$14"
        .Axes(xlCategory).MaximumScale = Target.Value
      Case "$B$15"
        .Axes(xlCategory).MinimumScale = Target.Value
      Case "$B$16"
        .Axes(xlCategory).MajorUnit = Target.Value
      Case "$T$3"
        .Axes(xlValue).MaximumScale = Target.Value
      Case "$T$4"
        .Axes(xlValue).MinimumScale = Target.Value
      Case "$T$16"
        .Axes(xlValue).MajorUnit = Target.Value
    End Select
  End With
End Sub

I hope someone can help me out.

Thanks.

 

[Sektor]

There're no variables in your presented code. Hence your missing variable declaration is out there.
Always include Option Explicit directive on top of all your modules. You can even ask VBA to do it automatically - Tools=>Options=>Require Variable Declaration.
Put it there and execute Debug=>Compile VBAProject. If there're some missing declarations, VBE will highlight the row.

 

[bmaas93]

Hi Sektor,

Thank you for your response. Option Explicit is included.

Where should the missing variable be inserted? I really have no clue..

 

[Sektor]

When error message appears, press Debug button - it will lead you to the row with error.

 

[bmaas93]

When error message appears, press Debug button - it will lead you to the row with error.

Yes, it leads me to: Private Sub Worksheet_Activate()

 

[Sektor]

Oops, didn't notice.
You refer to ChartObjects of Workbook, but it's in Worksheet object. You have missing sheet reference. Should be something like this:

With ActiveWorkbook[B].Sheets("NAME_OF_SHEET")[/B].ChartObjects("Grafiek 14").Chart