Dynamic "Max" & "Crosses At"

  • Thread starter Thread starter SAMMY
  • Start date Start date
S

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
 
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
 
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?!
 
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
 
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
 
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.
 
Back
Top