Full chart view?

Doug · External Since: Mar 12, 2006 Posts: 159

When I have the verticle axis set to automatic on the Min & Max range it
usually shows a huge distance on the chart because it generally defaults to
0. If the data is 55-100, the chart will start at 0 at the bottom and the
55-100 will be toward the top scrunching the data. Seems like it should size
up the data to best fit everything when on the auto setting. I don't want to
use a fixed setting because if the data changes then I run into a similar
problem. Is there a way to either change the default settings so the charts
will automatically size themselves, or something else I can do to make this
work for me?
--
Thank you!

Doug · External Since: Mar 12, 2006 Posts: 159

Were do I need to put the macro? I tried placing it in the workbook and in
the sheet view code, but doesn't do anything. What am I doing wrong?
--
Thank you!

"ck13" wrote:

> Hi,
>
> I faced this problem and found a solution but you need to use macro.
>
> Sub AutoScaleYAxes()
> Dim ValuesArray(), SeriesValues As Variant
> Dim Ctr As Integer, TotCtr As Integer
> With ActiveChart
> For Each X In .SeriesCollection
> SeriesValues = X.Values
> ReDim Preserve ValuesArray(1 To TotCtr + UBound(SeriesValues))
> For Ctr = 1 To UBound(SeriesValues)
> ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
> Next
> TotCtr = TotCtr + UBound(SeriesValues)
> Next
> .Axes(xlValue).MinimumScaleIsAuto = True
> .Axes(xlValue).MaximumScaleIsAuto = True
> .Axes(xlValue).MinimumScale = Application.Min(ValuesArray)
> .Axes(xlValue).MaximumScale = Application.Max(ValuesArray)
> End With
> End Sub
>
>
>
> Another way you can do is try this method by Jon Peltier
> http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html
>
> I have not tried his method but it should work fine as I have very good
> experience using his other solutions to my charting problems.
>
> "Doug" wrote:
>
> > When I have the verticle axis set to automatic on the Min & Max range it
> > usually shows a huge distance on the chart because it generally defaults to
> > 0. If the data is 55-100, the chart will start at 0 at the bottom and the
> > 55-100 will be toward the top scrunching the data. Seems like it should size
> > up the data to best fit everything when on the auto setting. I don't want to
> > use a fixed setting because if the data changes then I run into a similar
> > problem. Is there a way to either change the default settings so the charts
> > will automatically size themselves, or something else I can do to make this
> > work for me?
> > --
> > Thank you!

Doug · External Since: Mar 12, 2006 Posts: 159

I have a volume-Open-High-Low-Close chart that I am using this on and it made
the volume extend to the top of the chart, but the price pattern didn't move?
Any more helpful suggestions?
--

"ck13" wrote:

> Hi, tried psoting my reply but seems to have some problems. Press "Alt"+"F11"
> to open the visual basic. Then at the left you will see VBAProject (Name of
> your workbook"). Right click at that and insert a new module. Copy the code
> into the new module and close VBA. Click on the chart that you want to change
> and press "Alt"+"F8" to open the macros selection. Click on the appropriate
> macros and run it.
>
>
> "Doug" wrote:
>
> > Were do I need to put the macro? I tried placing it in the workbook and in
> > the sheet view code, but doesn't do anything. What am I doing wrong?
> > --
> > Thank you!
> >
> >
> > "ck13" wrote:
> >
> > > Hi,
> > >
> > > I faced this problem and found a solution but you need to use macro.
> > >
> > > Sub AutoScaleYAxes()
> > > Dim ValuesArray(), SeriesValues As Variant
> > > Dim Ctr As Integer, TotCtr As Integer
> > > With ActiveChart
> > > For Each X In .SeriesCollection
> > > SeriesValues = X.Values
> > > ReDim Preserve ValuesArray(1 To TotCtr + UBound(SeriesValues))
> > > For Ctr = 1 To UBound(SeriesValues)
> > > ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
> > > Next
> > > TotCtr = TotCtr + UBound(SeriesValues)
> > > Next
> > > .Axes(xlValue).MinimumScaleIsAuto = True
> > > .Axes(xlValue).MaximumScaleIsAuto = True
> > > .Axes(xlValue).MinimumScale = Application.Min(ValuesArray)
> > > .Axes(xlValue).MaximumScale = Application.Max(ValuesArray)
> > > End With
> > > End Sub
> > >
> > >
> > >
> > > Another way you can do is try this method by Jon Peltier
> > > http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html
> > >
> > > I have not tried his method but it should work fine as I have very good
> > > experience using his other solutions to my charting problems.
> > >
> > > "Doug" wrote:
> > >
> > > > When I have the verticle axis set to automatic on the Min & Max range it
> > > > usually shows a huge distance on the chart because it generally defaults to
> > > > 0. If the data is 55-100, the chart will start at 0 at the bottom and the
> > > > 55-100 will be toward the top scrunching the data. Seems like it should size
> > > > up the data to best fit everything when on the auto setting. I don't want to
> > > > use a fixed setting because if the data changes then I run into a similar
> > > > problem. Is there a way to either change the default settings so the charts
> > > > will automatically size themselves, or something else I can do to make this
> > > > work for me?
> > > > --
> > > > Thank you!

Doug · External Since: Mar 12, 2006 Posts: 159

I tried this and works fine accept it changes the primary values. Is it
possible to have this change the secondary values instead?

"ck13" wrote:

> Hi, tried psoting my reply but seems to have some problems. Press "Alt"+"F11"
> to open the visual basic. Then at the left you will see VBAProject (Name of
> your workbook"). Right click at that and insert a new module. Copy the code
> into the new module and close VBA. Click on the chart that you want to change
> and press "Alt"+"F8" to open the macros selection. Click on the appropriate
> macros and run it.
>
>
> "Doug" wrote:
>
> > Were do I need to put the macro? I tried placing it in the workbook and in
> > the sheet view code, but doesn't do anything. What am I doing wrong?
> > --
> > Thank you!
> >
> >
> > "ck13" wrote:
> >
> > > Hi,
> > >
> > > I faced this problem and found a solution but you need to use macro.
> > >
> > > Sub AutoScaleYAxes()
> > > Dim ValuesArray(), SeriesValues As Variant
> > > Dim Ctr As Integer, TotCtr As Integer
> > > With ActiveChart
> > > For Each X In .SeriesCollection
> > > SeriesValues = X.Values
> > > ReDim Preserve ValuesArray(1 To TotCtr + UBound(SeriesValues))
> > > For Ctr = 1 To UBound(SeriesValues)
> > > ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
> > > Next
> > > TotCtr = TotCtr + UBound(SeriesValues)
> > > Next
> > > .Axes(xlValue).MinimumScaleIsAuto = True
> > > .Axes(xlValue).MaximumScaleIsAuto = True
> > > .Axes(xlValue).MinimumScale = Application.Min(ValuesArray)
> > > .Axes(xlValue).MaximumScale = Application.Max(ValuesArray)
> > > End With
> > > End Sub
> > >
> > >
> > >
> > > Another way you can do is try this method by Jon Peltier
> > > http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html
> > >
> > > I have not tried his method but it should work fine as I have very good
> > > experience using his other solutions to my charting problems.
> > >
> > > "Doug" wrote:
> > >
> > > > When I have the verticle axis set to automatic on the Min & Max range it
> > > > usually shows a huge distance on the chart because it generally defaults to
> > > > 0. If the data is 55-100, the chart will start at 0 at the bottom and the
> > > > 55-100 will be toward the top scrunching the data. Seems like it should size
> > > > up the data to best fit everything when on the auto setting. I don't want to
> > > > use a fixed setting because if the data changes then I run into a similar
> > > > problem. Is there a way to either change the default settings so the charts
> > > > will automatically size themselves, or something else I can do to make this
> > > > work for me?
> > > > --
> > > > Thank you!

ck13 · External Since: Mar 16, 2010 Posts: 8

You will need to amend the VBA. I am not strong in this so you might need to
post your questions. This discussion group is going to close on 1st June. So
i strongly advise you not to post your question here. post it in this forum
instead. http://answers.microsoft.com/en-us/office/default.aspx#tab=1

This is where I managed to get help from others to amend the VBA i gave you
to include secondary axis.

"Doug" wrote:

> I tried this and works fine accept it changes the primary values. Is it
> possible to have this change the secondary values instead?
>
>
>
> "ck13" wrote:
>
> > Hi, tried psoting my reply but seems to have some problems. Press "Alt"+"F11"
> > to open the visual basic. Then at the left you will see VBAProject (Name of
> > your workbook"). Right click at that and insert a new module. Copy the code
> > into the new module and close VBA. Click on the chart that you want to change
> > and press "Alt"+"F8" to open the macros selection. Click on the appropriate
> > macros and run it.
> >
> >
> > "Doug" wrote:
> >
> > > Were do I need to put the macro? I tried placing it in the workbook and in
> > > the sheet view code, but doesn't do anything. What am I doing wrong?
> > > --
> > > Thank you!
> > >
> > >
> > > "ck13" wrote:
> > >
> > > > Hi,
> > > >
> > > > I faced this problem and found a solution but you need to use macro.
> > > >
> > > > Sub AutoScaleYAxes()
> > > > Dim ValuesArray(), SeriesValues As Variant
> > > > Dim Ctr As Integer, TotCtr As Integer
> > > > With ActiveChart
> > > > For Each X In .SeriesCollection
> > > > SeriesValues = X.Values
> > > > ReDim Preserve ValuesArray(1 To TotCtr + UBound(SeriesValues))
> > > > For Ctr = 1 To UBound(SeriesValues)
> > > > ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
> > > > Next
> > > > TotCtr = TotCtr + UBound(SeriesValues)
> > > > Next
> > > > .Axes(xlValue).MinimumScaleIsAuto = True
> > > > .Axes(xlValue).MaximumScaleIsAuto = True
> > > > .Axes(xlValue).MinimumScale = Application.Min(ValuesArray)
> > > > .Axes(xlValue).MaximumScale = Application.Max(ValuesArray)
> > > > End With
> > > > End Sub
> > > >
> > > >
> > > >
> > > > Another way you can do is try this method by Jon Peltier
> > > > http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html
> > > >
> > > > I have not tried his method but it should work fine as I have very good
> > > > experience using his other solutions to my charting problems.
> > > >
> > > > "Doug" wrote:
> > > >
> > > > > When I have the verticle axis set to automatic on the Min & Max range it
> > > > > usually shows a huge distance on the chart because it generally defaults to
> > > > > 0. If the data is 55-100, the chart will start at 0 at the bottom and the
> > > > > 55-100 will be toward the top scrunching the data. Seems like it should size
> > > > > up the data to best fit everything when on the auto setting. I don't want to
> > > > > use a fixed setting because if the data changes then I run into a similar
> > > > > problem. Is there a way to either change the default settings so the charts
> > > > > will automatically size themselves, or something else I can do to make this
> > > > > work for me?
> > > > > --
> > > > > Thank you!

Doug · External Since: Mar 12, 2006 Posts: 159

Thanks for your help

"ck13" wrote:

> You will need to amend the VBA. I am not strong in this so you might need to
> post your questions. This discussion group is going to close on 1st June. So
> i strongly advise you not to post your question here. post it in this forum
> instead. http://answers.microsoft.com/en-us/office/default.aspx#tab=1
>
> This is where I managed to get help from others to amend the VBA i gave you
> to include secondary axis.
>
>
>
> "Doug" wrote:
>
> > I tried this and works fine accept it changes the primary values. Is it
> > possible to have this change the secondary values instead?
> >
> >
> >
> > "ck13" wrote:
> >
> > > Hi, tried psoting my reply but seems to have some problems. Press "Alt"+"F11"
> > > to open the visual basic. Then at the left you will see VBAProject (Name of
> > > your workbook"). Right click at that and insert a new module. Copy the code
> > > into the new module and close VBA. Click on the chart that you want to change
> > > and press "Alt"+"F8" to open the macros selection. Click on the appropriate
> > > macros and run it.
> > >
> > >
> > > "Doug" wrote:
> > >
> > > > Were do I need to put the macro? I tried placing it in the workbook and in
> > > > the sheet view code, but doesn't do anything. What am I doing wrong?
> > > > --
> > > > Thank you!
> > > >
> > > >
> > > > "ck13" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I faced this problem and found a solution but you need to use macro.
> > > > >
> > > > > Sub AutoScaleYAxes()
> > > > > Dim ValuesArray(), SeriesValues As Variant
> > > > > Dim Ctr As Integer, TotCtr As Integer
> > > > > With ActiveChart
> > > > > For Each X In .SeriesCollection
> > > > > SeriesValues = X.Values
> > > > > ReDim Preserve ValuesArray(1 To TotCtr + UBound(SeriesValues))
> > > > > For Ctr = 1 To UBound(SeriesValues)
> > > > > ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
> > > > > Next
> > > > > TotCtr = TotCtr + UBound(SeriesValues)
> > > > > Next
> > > > > .Axes(xlValue).MinimumScaleIsAuto = True
> > > > > .Axes(xlValue).MaximumScaleIsAuto = True
> > > > > .Axes(xlValue).MinimumScale = Application.Min(ValuesArray)
> > > > > .Axes(xlValue).MaximumScale = Application.Max(ValuesArray)
> > > > > End With
> > > > > End Sub
> > > > >
> > > > >
> > > > >
> > > > > Another way you can do is try this method by Jon Peltier
> > > > > http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html
> > > > >
> > > > > I have not tried his method but it should work fine as I have very good
> > > > > experience using his other solutions to my charting problems.
> > > > >
> > > > > "Doug" wrote:
> > > > >
> > > > > > When I have the verticle axis set to automatic on the Min & Max range it
> > > > > > usually shows a huge distance on the chart because it generally defaults to
> > > > > > 0. If the data is 55-100, the chart will start at 0 at the bottom and the
> > > > > > 55-100 will be toward the top scrunching the data. Seems like it should size
> > > > > > up the data to best fit everything when on the auto setting. I don't want to
> > > > > > use a fixed setting because if the data changes then I run into a similar
> > > > > > problem. Is there a way to either change the default settings so the charts
> > > > > > will automatically size themselves, or something else I can do to make this
> > > > > > work for me?
> > > > > > --
> > > > > > Thank you!