Full chart view?
in [Charts]
|
Prev: Chart Area
Next: Pie chart
From: ck13 on 27 May 2010 15:54 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!
From: ck13 on 27 May 2010 16:13 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!
From: Doug on 27 May 2010 17:26 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!
From: Doug on 27 May 2010 17:47 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!
From: Doug on 27 May 2010 18:31
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! |