Dynamic "Max" & "Crosses At"

[SAMMY]

I'm having trouble using Jon Peltier's code (bastardized
below). I simply want to have both axes cross at points
based on a cell reference. Same thing for the maximum
value of the x-axis.

Ideally these cell references would be linked to another
worksheet, but I couldn't even get it to trigger a change
by manually changing the target cells....

TIA!


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address

'X-AXIS = UNITS ******************
Case "$I$3"
ActiveSheet.ChartObjects("Chart 1025").Chart.Axes
(xlCategory) _
.CrossesAt = Target.Value

'Y-AXIS = GM% ******************
Case "$G$3"
ActiveSheet.ChartObjects("Chart 1025").Chart.Axes(xlValue)
_
.CrossesAt = Target.Value

'X-AXIS MAX VALUE ******************
Case "$H$3"
ActiveSheet.ChartObjects("Chart 1025").Chart.Axes(xlValue)
_
..MaximumScale = Target.Value

Case Else
End Select
End Sub

 

[Jon Peltier]

Sammy -

The code is working for me.

Is it an XY Scatter chart?
Is the chart really named "Chart 1025"?
Are any sheet events occurring?

- Jon

 

[Sammy]

Hi Jon,

Yes, it is an XY Scatter. For the chart name I right-
clicked the chart and chose "Chart Window" and it
displayed the worksheet name and "Chart 1025". There are
no sheet events occuring.

Help?!

 

[Jon Peltier]

Sammy -

That isn't the "official" name of the chart. Select a cell, then hold
Shift while you select the chart. Now what's it say in the name box
(above the top left cell)?

- Jon

 

[Sammy]

I'm afraid it is Chart 1025.... Thanks for your patience.

 

[Andy Pope]

Hi Sammy,

The code works as described for me.
The only 2 scenarios I can think of that will cause it to fail are,

The chart is not really called 'Chart 1025'. To confirm select chart
then goto the immediate window in VBE (ALT+F11)(CTRL+G) and enter
?activechart.Parent.name

Or the code is in the wrong place. Maybe in a standard module rather
than the worksheets code area.

Try adding this line of code before the select case statement,

MsgBox Target.Address

A messagebox should popup when any cell on the sheet is changed.

Cheers
Andy

 

[Sammy]

Hi Andy,

Thanks for posting. Cool trick to confirm the object, but
alas it is indeed "chart 1025". Moreover, the code is in
the worksheet area and did get a message to popup when I
changed a cell.... I'm thinking I need to start from
scratch.