Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting Y axis scale of a chart using a scrollbar?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting Y axis scale of a chart using a scrollbar?
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 -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting Y axis scale of a chart using a scrollbar?
Maybe someone in the charting group would be more qualified to answer this. Have you posted in that group? "Brad" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting Y axis scale of a chart using a scrollbar?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting Y axis scale of a chart using a scrollbar?
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 "OssieMac" wrote: Hi Brad, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting Y axis scale of a chart using a scrollbar?
Ignore the below . I dropped my code in the Workbook_SheetCalculate event and heypresto. Thanks Again! "Brad" wrote: 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 "OssieMac" wrote: Hi Brad, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting Y axis scale of a chart using a scrollbar?
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 -- Regards, OssieMac |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting Y axis scale of a chart using a scrollbar?
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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting Y axis scale of a chart using a scrollbar?
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 A1:D21 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(B2:D21)*(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 "OssieMac" wrote: Hi again Brad, You now have me intrigued and I wonder if I fully understand what you are -- Regards, OssieMac |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting Y axis scale of a chart using a scrollbar?
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 "Brad" wrote: 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 A1:D21 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(B2:D21)*(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 "OssieMac" wrote: Hi again Brad, You now have me intrigued and I wonder if I fully understand what you are -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to force chart axis to same scale | Excel Discussion (Misc queries) | |||
Adjusting scale of an adjsuting chart | Excel Programming | |||
How to draw chart: log scale on X axis, natural scale on y axis? | Charts and Charting in Excel | |||
Scale on X & Y Axis of the scatter chart | Charts and Charting in Excel | |||
How do I split the y-axis scale on a chart? | Charts and Charting in Excel |