Graphs in Excel97

schurkenwoud · Apr 26th, 2002, 05:41 AM

Hi,

I want a graph to size its minimum axes automaticly but not to start at zero, as is the standard functionality of Excel97.

Can you help me?

Kind regards,

Erik Schakenbos

Big Blue · Apr 26th, 2002, 07:48 AM

can't think of how to do it automatically (ie without human intervention)but if you want to know how to format axis to something other than zero, when chart is made,
-right click on one of gridlines
-click on format gridlines
-click on 'scale'
-change Minimum from zero to whatever value you want(the check mark next to 'automatic' will disappear)
-click 'ok'

hth

schurkenwoud · Apr 26th, 2002, 08:00 AM

In msaccess it is possible to do this automaticly, using the following code:

Private Sub Rapportvoettekst_Format(Cancel As Integer, FormatCount As Integer)

Dim MINPROD As Double
Dim MINZIEKTE As Double

MINPROD = Int((0.95 * Me!MINPROD) / 100) * 100
Me!Grafiek204.Object.Application.Chart.Axes(2).MinimumScale = MINPROD

MINZIEKTE = Int((Me!MINZIEKTE) * 100) / 100
Me!Grafiek205.Object.Application.Chart.Axes(2).MinimumScale = MINZIEKTE

End Sub

So this must be possible in Excel also. Can anybody help?

[ This Message was edited by: schurkenwoud on 2002-04-26 07:02 ]

prida · Apr 26th, 2002, 08:09 AM

Once you've made the axis scale adjustments mentioned above, you can add it as a user-defined custom chart type (Chart menu, Chart Type dialog box, Custom Types tab) and set it as your default chart if you like.

schurkenwoud · Apr 27th, 2002, 12:26 AM

But the essence of the question is that i want excel to determine the most correct minimum value. Because the content of the graph changes all the time because it is based on a pivot table. So everytime the graph is openend it should determine the most correct minimum value automaticly

Hope anyone can help.

Tommy Bak · Apr 27th, 2002, 04:53 AM

Hi
I don't kknow if your graph has it's own sheet or is an object in another sheet so i cannot be more exact than this.
You can put this into a Sheets_activate og Charts_Activate event.

Sub Makro2()
ActiveSheet.ChartObjects(1).Activate
With ActiveChart.Axes(xlValue)
MINPROD = Application.WorksheetFunction.Min(ActiveChart.SeriesCollection(1).Values)
.MinimumScale = MINPROD
End With
End Sub

regards Tommy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 26th, 2002, 05:41 AM	#1
schurkenwoud New Member Join Date: Apr 2002 Posts: 3	Hi, I want a graph to size its minimum axes automaticly but not to start at zero, as is the standard functionality of Excel97. Can you help me? Kind regards, Erik Schakenbos

Apr 26th, 2002, 07:48 AM	#2
Big Blue Board Regular Join Date: Mar 2002 Location: Ontario Posts: 52	can't think of how to do it automatically (ie without human intervention)but if you want to know how to format axis to something other than zero, when chart is made, -right click on one of gridlines -click on format gridlines -click on 'scale' -change Minimum from zero to whatever value you want(the check mark next to 'automatic' will disappear) -click 'ok' hth

Apr 26th, 2002, 08:00 AM	#3
schurkenwoud New Member Join Date: Apr 2002 Posts: 3	In msaccess it is possible to do this automaticly, using the following code: Private Sub Rapportvoettekst_Format(Cancel As Integer, FormatCount As Integer) Dim MINPROD As Double Dim MINZIEKTE As Double MINPROD = Int((0.95 * Me!MINPROD) / 100) * 100 Me!Grafiek204.Object.Application.Chart.Axes(2).MinimumScale = MINPROD MINZIEKTE = Int((Me!MINZIEKTE) * 100) / 100 Me!Grafiek205.Object.Application.Chart.Axes(2).MinimumScale = MINZIEKTE End Sub So this must be possible in Excel also. Can anybody help? [ This Message was edited by: schurkenwoud on 2002-04-26 07:02 ]

Apr 26th, 2002, 08:09 AM	#4
prida New Member Join Date: Apr 2002 Posts: 8	Once you've made the axis scale adjustments mentioned above, you can add it as a user-defined custom chart type (Chart menu, Chart Type dialog box, Custom Types tab) and set it as your default chart if you like.

Apr 27th, 2002, 12:26 AM	#5
schurkenwoud New Member Join Date: Apr 2002 Posts: 3	But the essence of the question is that i want excel to determine the most correct minimum value. Because the content of the graph changes all the time because it is based on a pivot table. So everytime the graph is openend it should determine the most correct minimum value automaticly Hope anyone can help.

Apr 27th, 2002, 04:53 AM	#6
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	Hi I don't kknow if your graph has it's own sheet or is an object in another sheet so i cannot be more exact than this. You can put this into a Sheets_activate og Charts_Activate event. Sub Makro2() ActiveSheet.ChartObjects(1).Activate With ActiveChart.Axes(xlValue) MINPROD = Application.WorksheetFunction.Min(ActiveChart.SeriesCollection(1).Values) .MinimumScale = MINPROD End With End Sub regards Tommy