Auto-Range and Scaling of Chart Axes

[PaulAsaran]

I have two charts taking data from large ranges. Because there's no way for me to know what each user will face in their individual circumstance, I can't reliably set the Y-axis minimum or maximum to a fixed value. I want the charts to automatically determine the Y-axis as the users input their information. I know this is possible because I've done it before, but the source where I got the original information is gone and I'm having trouble reproducing it.

In my research, I came across some code, which I adapted for my purposes:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim targ1 As Range
Dim MinScale1 As Double, MaxScale1 As Double
Dim targ2 As Range
Dim MinScale2 As Double, MaxScale2 As Double
 
Set targ1 = Me.Range("X25:X483")
If Not Intersect(targ1, Target) Is Nothing Then
    Application.ScreenUpdating = False
    MinScale1 = Application.Min(targ1.Value)
    MaxScale1 = Application.Max(targ1.Value)
    TweakChartAxis Me.ChartObjects("Chart 18"), xlValue, MinScale1, MaxScale1
End If

Set targ2 = Me.Range("W25:W483")
If Not Intersect(targ2, Target) Is Nothing Then
    Application.ScreenUpdating = False
    MinScale2 = Application.Min(targ2.Value)
    MaxScale2 = Application.Max(targ2.Value)
    TweakChartAxis Me.ChartObjects("Chart 14"), xlValue, MinScale2, MaxScale2
End If

End Sub

The original code was only for one chart, but I adapted it for two. I am, unfortunately, getting a Compile Error claiming "Sub or Function not defined". I get what it wants, but not what I specifically need to do to fix it as my knowledge of VBA is strictly limited. Any assistance would be most appreciated.

 

[daverunt]

Hi,

no idea about your error but for chart questions I usually search Jon Peltiers site.

https://peltiertech.com/?s=dynamic+auto+axis

 

[Swayzy]

Is the code residing in a standard module? The worksheetchange event indicates that it should be inside a worksheet object

Also the me. Sheets reference dosent seem to be declared nor set. If you delete all instances of me the range reference will refer to the Active sheet

 

[PaulAsaran]

That had the answers I was looking for, thanks!

Go figure, it's also where I originally got the solution months ago, but for some reason I couldn't find it again. So double thanks and bookmarked!

 

[PaulAsaran]

It was in the worksheet object, as needed. I didn't realize the .me was a problem, and getting rid of it did stop the error from popping up. Alas, the code still wasn't doing what I wanted. Fortunately, daverunt's answer above gave me what I needed to make it work.

Still, thanks for the assist. Now I get why the error was popping up and can hopefully avoid it in the future.