Simple one I think - Worksheet calculate event

[VBA_Newbie]

Hi folks,

I have the following code which changes the max scale in a chart on the active worksheet when ever a calculate is made on that worksheet. Does anyone know how I can modify this so that if I'm on a different worksheet and make a change that affects the worksheet with the chart, I don't get an error code.

It think I have to change the line "ActiveSheet.ChartObjects("Chart 9").Activate" but am not quite sure how.

Many thanks!

Private Sub Worksheet_Calculate()
      
    ActiveSheet.ChartObjects("Chart 9").Activate
    With ActiveChart.Axes(xlValue)
       .MaximumScale = ActiveSheet.Range("W26").Value
    End With

End Sub

 

[macleanb]

Something like:

Me.ChartObjects("Chart 9").Axes(xlValue).MaximumScale = Me.Range("W26").Value
End With

 

[macleanb]

Woops - left in the "With" by accident - please remove....

 

[VBA_Newbie]

What does the "Me" mean in

Me.ChartObjects("Chart 9").Axes(xlValue).MaximumScale = Me.Range("W26").Value

?

I tried it an got an error that said the object doesn't support this property or method. So I tried the following which resulted in the same error:

Worksheets("Gabor-Granger Graph").ChartObjects("Chart 9").Axes(xlValue).MaximumScale = _
Worksheets("Gabor-Granger Graph").Range("W26").Value

Any other thoughts?

 

[macleanb]

I was assuming you were running this code from within the Worksheet code - the "Me" refers to the workshet itself (where the code is run from) THe problem of the code (sorry I didnt check it from your original) was a missing "Chart":

Me.ChartObjects("Chart 1").Chart.Axes(xlValue).MaximumScale = Me.Range("W26").Value

 

[vangogh228]

I tried this...

and I simply cannot get this to work.

I continue to get a rotation of errors:
"Improper use of Me word"
"1004"
"398"
"Function not supported"
etc etc etc

every time I make an edit.

My workbook is called TMHGantt
My worksheet is called Gantt
My min value is in cell B27
My max value is in cell B28
I'm not sure what my chart name is, but when I select chart window, it's titled Gantt Chart 2
The chart is on the sheet called Gantt

Any help would be GREATLY appreciated.

 

[VBA_Newbie]

What exactly are you trying to do? I did eventually get this working and might be able to help.

 

[vangogh228]

Hi Mike. Thanks for the help.

I have a combination chart (a Gantt chart that I made following the MS instructions, with a few minor changes. I want to have a macro that picks up the min and max values from the tasks dates to set the chart Y scale min and max. (It's essentially a horizontal bar chart, so the Y axis is across the top.)

It sounds to me like this is similar to what you were trying to do. I've tried the code that was posted... but I keep getting hung up on the ChartObject and ChartObjects lines.

I'd be glad to share the workbook if that would help. Thanks again!

Tom