Dynamic Chart Value

[N8theGreat]

I have a script in a module which defines the data in the chart as well as the X and Y axis.
In the module:

Sheets(she).Cells(14, 20) = Int(tn - 10)

In the sheet I have the following script:
Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address
    
    Case "$T$12"
        ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory).MaximumScale = Target.Value
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory).MaximumScale = Target.Value
        ActiveSheet.ChartObjects("Chart 3").Chart.Axes(xlCategory).MaximumScale = Target.Value
End Select
End Sub

When the module script runs and changes the value of "T20", the worksheet script runs and errors with Runtime error '-2147024809 (80070057)' "The item with the specified name wasn't found."
Why am I getting this error?

 

[N8theGreat]

Is there a method of ensuring the script in the worksheet will not run until the module script has completed?

I realize Cells(14,20) and T12 are not the same location. There are many updates happening on the page, I chose two that did not match location.

 

[N8theGreat]

Is there a wait until function that could be used to ensure the module script completes before the worksheet case & target.value script tries to update?

 

[N8theGreat]

Am I going about this in a manner that is not efficient? Any ideas on how to improve the process?

 

[Jon Peltier]

So you're getting an error, good to know. Also good to know would be which line is highlighted in the debugger when the error message pops up.

 

[N8theGreat]

It errors on line:

ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory).MaximumScale = Target.Value

The code in the module writes the value I want as Min. and Max. x-axis values, and the worksheet code (case .... target) immediately runs and errors on the line above. The module code is not complete when the case tries to update the chart value.

 

[Jon Peltier]

So what items in that line could possible not be found?

ActiveSheet... Probably not

ChartObjects("Chart 2")... maybe, but I'd expect a "Subscript out of Range" error instead, if there's no chart object named "Chart 2". But check the chart object names.

Axes(xlCategory)... same as previous, and make sure the axis actually exists.

MaximumScale... What kind of chart is it? If it isn't an XY Scatter chart, then the category axis has no MaximumScale property.

 

[N8theGreat]

Yup ... feeling pretty dumb right now.