Dynamic Chart Scale

[BUDDY]

I'm trying to embed an event procedure that changes the
point an axis crosses, based on a cell reference. Below
is the code. Below that is the error message I get.

Help?! TIA


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

Case "$Q$3"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes
(xlCategory).CrossesAt = Target.Value
Case Else
End Select


End Sub




"Unable to get ChartObjects property of the Worksheet
class"

 

[Frank Kabel]

Hi
have a look at
http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

for sample code

 

[BUDDY]

Thanks for the response. This is great for scaling, but I
would really like to have a dynamic "Crosses At" for each
axis... Any ideas?

 

[Kevin Beckham]

Try this fragment

With ActiveSheet.ChartObjects("Chart 1") _
.Chart.Axes(xlCategory)
.Crosses = xlCustom
.CrossesAt = Target.Value
End With


Kevin Beckham

 

[Jon Peltier]

Buddy -

The error message you posted means the code couldn't find the chart on
the worksheet. Probably you don't have a chart named "Chart 1". The rest
of the code works fine, if I have a chart with the right name.

To check or change the name of a chart, select a cell, then hold shift
while selecting the chart. This selects the chart object, not one of the
chart elements. It has little white handles instead of the black ones,
and there's a name in the name box (above the header for column A). Type
a name there that you want, and press Enter.

- Jon