Adjusting Y axis scale of a chart using a scrollbar?

[Brad]

Hi all,

I thought this would be relatively easy, but turns out the solution is
alluding me.

I would basically like to adjust the scale of my budget graph's Y axis using
a scrollbar. My chart is a combination bar & line chart displaying historic
area cost as bars and future forecast costs as a line. The trouble I have is
a number of areas have a large actual / forecast cost while others are
relatively small. I would like to adjust the scale of the y axis effectively
zooming in on these lower cost areas.

Any help provided would be greatly appreciated.

Ta
Brad

 

[OssieMac]

Hi Brad,

I don't know how to insert scroll bars or if it is even possible. However,
you could use a couple of cells on the worksheet containing the chart and set
the Maximum and Minimum values to use for the y axis and place the code in a
worksheet change event so that when you change either value the axis of the
chart will reflect the change something like the following.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect
Set isect = Application.Intersect(Target, Range("D1:E1"))
If Not isect Is Nothing Then
With Sheets("Sheet1").ChartObjects("Chart 1").Chart
With .Axes(xlValue, xlPrimary)
.MaximumScale = Sheets("Sheet1").Range("D1")
.MinimumScale = Sheets("Sheet1").Range("E1")
End With
End With
End If
End Sub

 

[JLGWhiz]

Maybe someone in the charting group would be more qualified to answer this.
Have you posted in that group?

 

[marcus]

Hi Brad

Ossiemac has pointed you in the right direction. You can use his
coding inside a scrollbar. The scrollbar can only change the maximum
or minimum not both at the same time. You could have two scrollbars
one for Max and one for min. Or you could hard code either the
maximum or minimum and use a scroll bar for the other.

Here is a snippet.

Take care

Marcus


'Set the maximum with a scrollbar using example above.
Private Sub ScrollBar1_Change()
With Sheets("Sheet1").ChartObjects("Chart 1").Chart
With .Axes(xlValue, xlPrimary)
.MaximumScale = Sheets("Sheet1").Range("D1")
End With
End With
End Sub

 

[Brad]

Thanks. You've sent me off on a new path, but I haven't quite been able to
crack it yet.

What I have is the slider (scrollbar) updating a value on Sheet1 in cel H1.
I then try to grab this value and use it as my maximum Y axis value. I put
the below code in the Workbook_SheetChange event. I can see it update the
value on the sheet but it doesn't update the graph's y axis value.

However when I go into the sheet and update a cell my graph will change.
What is it that I'm missing?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

MyValue = Sheets("Sheet1").Cells(1, 8).Value
Sheets("Sheet1").ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MaximumScale = MyVal
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.DisplayUnit = xlNone
End With

End Sub

Ta
Brad

 

[Brad]

Ignore the below .

I dropped my code in the Workbook_SheetCalculate event and heypresto.

Thanks Again!

 

[OssieMac]

Hi again Brad,

You now have me intrigued and I wonder if I fully understand what you are
actually doing. Do I understand that you have inserted an ActiveX scroll bar
on the worksheet? If so, then I agree with having a linked cell as a visual
display of the scrollbar value as it is changed but I would use the
ScrollBar1_Change event to alter the y axis value.

Also note the way I have coded the following so that there is no need to
actually select the chart.

Private Sub ScrollBar1_Change()
With Sheets("Sheet1") .ChartObjects("Chart 1").Chart.Axes(xlValue)
.MaximumScale = ScrollBar1.Value
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.DisplayUnit = xlNone
End With
End Sub

Another tip is that you can use the minimum and maximum of the y axis values
to set the min and max of the scrollbar as soon as it has focus. While it may
not affect your project, I can see this as a benefit if charts are set up on
an autofiltered range.

Private Sub ScrollBar1_GotFocus()
ScrollBar1.Min = WorksheetFunction.Min(Range("B1:B20"))
ScrollBar1.Max = WorksheetFunction.Max(Range("B1:B20"))
End Sub

 

[Peter T]

If I follow you will need two scrollbars, to set the first and last values
in the zoomed section. All this can be done with dynamic named ranges to
define the X & Y values, and perhaps a pair of scrollbars to set the lower
and upper bounds. No code required. Post back if interested to try something
like this.

Regards,
Peter T

 

[Brad]

Hi OssieMac,

It's not actually an ActiveX control, rather the scroll bar from the "Forms"
toolbar. Not really sure what that comes under? It seems the ActiveX
scrollbar wont accept insertion into the chart area, so I didn't get past
trying to insert it.

At the moment I have my data in Cells A121 with Column A being the "Areas"
that are ploted on the X axis. The form scrollbar is placed inside the chart
area and linked to cell F1 producing a number between 0 & 100 dependent on
its position. Cell G1 contains the formula "=MAX(B221)*(100-F1)/100" and
this number is grabbed by the macro and used as the maximum Y axis scale.

I'd be happy to send you the workbook if you're still interested. i'm
certain you'd be able to find a more elegant way of achieving what I'm doing.

ta
Brad

Hi again Brad,

You now have me intrigued and I wonder if I fully understand what you are

 

[OssieMac]

Hi again Brad,

It looks like you have it all under control so I suggest that you run with
what works.

I was able to place an ActiveX scrollbar on the chart provided that I
selected the required icon while a cell on the worksheet was selected and
before the chart is selected and then select the chart and create the
control. However, in xl2002, the scroll bar is created but when the chart is
selected I can no longer see the scroll bar. This occured with both Forms
control and ActiveX control. In xl2007 there are no problems and the control
remains visible.


--
Regards,

OssieMac

Hi OssieMac,

It's not actually an ActiveX control, rather the scroll bar from the "Forms"
toolbar. Not really sure what that comes under? It seems the ActiveX
scrollbar wont accept insertion into the chart area, so I didn't get past
trying to insert it.

At the moment I have my data in Cells A121 with Column A being the "Areas"
that are ploted on the X axis. The form scrollbar is placed inside the chart
area and linked to cell F1 producing a number between 0 & 100 dependent on
its position. Cell G1 contains the formula "=MAX(B221)*(100-F1)/100" and
this number is grabbed by the macro and used as the maximum Y axis scale.

I'd be happy to send you the workbook if you're still interested. i'm
certain you'd be able to find a more elegant way of achieving what I'm doing.

ta
Brad

Hi again Brad,

You now have me intrigued and I wonder if I fully understand what you are