Dynamic Axis Scaling

[John Baltimore]

Is there a way to change the X-axis scaling (min, max, major unit,
minor unit) without going into the Format Axis dialog box on the
chart?

Thanks in advance for any assistance,

John Baltimore

[Jon Peltier]

John -

I suspect you want something that will easily apply some value to the chart
axes. Here's an oldie that I just dusted off:

Use a worksheet change event procedure to update your chart's axis scale
parameters.

In this example, I put these values into cells D1:F4. When any value in this
range is changed ("Target" in the code), the event procedure uses the new
value for the appropriate axis parameter.

Axes X Y
Max 6 6
Min 0 0
Tick 1 1

Right click on the worksheet tab, select View Code, and paste this into the
code module that pops up in the VB editor.

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\ START CODE \\\\\\\\\\\\\\\\\\\\\\\\\\\
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Select Case Target.Address
Case "$E$2"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.MaximumScale = Target.Value
Case "$E$3"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.MinimumScale = Target.Value
Case "$E$4"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.MajorUnit = Target.Value
Case "$F$2"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
.MaximumScale = Target.Value
Case "$F$3"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue). _
MinimumScale = Target.Value
Case "$F$4"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
.MajorUnit = Target.Value
Case Else
End Select
End Sub
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ END CODE \\\\\\\\\\\\\\\\\\\\\\\\\\\\

Based on which of the values was updated, the code changes the
appropriate axis scaling value by the amount in the cell which was
changed (Target).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <mmga5vc2f5mmtfqaa...@4ax.com>, John Baltimore said
...

[John Baltimore]

Well, this is making me nuts. :-) It didn't work, possibly because I
changed some things incorrectly. My chart is 'Chart3(Today1)' on the
list ont he left, so I tried both "Chart3" and "Today1" for "Chart 1"
in your example lines. I'll keep banging on it. :-)

On Thu, 20 Feb 2003 21:42:46 -0800, jonpe...@yahoo.com (Jon Peltier)
wrote:

John Baltimore

[Tushar Mehta]

You may want to look at the AutoChart Manager add-in on my web site.
It automates how chart min./max. values are set.

--
Regards,

Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel

In article <l0nc5vshu34t27mj0...@4ax.com>,
th...@comcastNoSpam.net says...

[John Baltimore]

On Fri, 21 Feb 2003 13:44:17 -0500, Tushar Mehta
<ng_p...@bigfoot.com> wrote:

>You may want to look at the AutoChart Manager add-in on my web site.
>It automates how chart min./max. values are set.

Thank you. I had hoped to do it without something I had to transport
from machine to machine, but...

Question: Once the Add-In is specified, can the unzipped file be
deleted? If not, is it bound to the specified directory, that is, if
in the directory with the .xls file, will it always look in the .xls
file's directory or will I have to duplicate the target directory
struture on another machine to which the .xls file is transferred?

Thanks again,

John Baltimore

[Tushar Mehta]

Since this has to be done programmatically, there is no simple
solution. Use Jon's approach and every time you open the workbook
you'll get the 'This file contains macros' warning. Use my add-in and
you trade the warning for the requirement that the add-in be present on
every machine where you open the workbook.

The way the add-in, or, for that matter, any add-in that is not self-
installed, works is as follows. Download the zipped file. Unzip the
file into the folder of your choice. There will be a .xla file, and
other optional files, including maybe a .hlp or a .chm help file.

Open XL. Select Tools | Add-Ins... Locate the .xla file you saved
during the unzip operation. Make sure the check box next to the add-in
is selected. Close the add-in dialog box.

[The add-in file is *not* opened through File | Open... It is only
loaded as described above.]

Once done, you can dispose off the downloaded (.zip) file. However,
the add-in file (.xla) and any supporting files (.hlp, .chm, .dll,
etc.) must remain on the machine.

--
Regards,

Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel

In article <mc4d5v83bvisuab0d...@4ax.com>,
th...@comcastNoSpam.net says...

[John Baltimore]

I was able to get your add-in working just fine. Being able to focus
on specific time frames (the X-Axis) really makes the data meaningful.

Thank you for the explanation of carrying the add-in file(s) to other
machines. I'm also going to have to master Jon's code so I can send
this spreadsheet out.

While I'm at it, and this is long overdue, thanks to you (and Jon) for
monitoring this newsgroup and giving hope to those of us who struggle!
:-)

John Baltimore

[John Baltimore]

Again thanks for your post and also your website. You and Tushar
Mehta are appreciated more than you know.

I'm still unable to get the code working. I made a Sheet1 with the
columns you specified. I put this in this in D7:F15 and charted it:

A 1 9
B 2 8
C 3 7
D 4 6
E 5 5
F 6 4
G 7 3
H 8 2
I 9 1

...and then ceated Chart1 on the tab (which I did not rename). I
inserted your code, and nothing happened, neither right away nor when
closing and re-opening the file, nor when changing values D1:F4. I
changed "Chart 1" to "Chart1" int he code and still nothing. I'm sure
I'm doing something wrong that's simple to you, but I'm pulling out my
hair over it. Please help?

I don't know if this makes a difference, but I'm using Excel 2000.

When I apply this to the original chart, chart type is a scatter chart
and the X-Axis is time values. Will that make a difference in the
code?

Thanks in advance again,

John Baltimore

On Thu, 20 Feb 2003 21:42:46 -0800, jonpe...@yahoo.com (Jon Peltier)
wrote:

>John -

[Tushar Mehta]

In article <jc8f5v80ovfm02jf2...@4ax.com>,
th...@comcastNoSpam.net says...

> While I'm at it, and this is long overdue, thanks to you (and Jon) for
> monitoring this newsgroup and giving hope to those of us who struggle!
> :-)

You are welcome.

--
Regards,

Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel

In article <jc8f5v80ovfm02jf2...@4ax.com>,
th...@comcastNoSpam.net says...

[Jon Peltier]

John -

My code as presented works to adjust the first chart in the active worksheet.
Did you mean that your chart was listed as 'Chart3(Today1)' in the project
explorer pane of the VB Editor? The chart is on its own chart sheet, and
you've put the name 'Today1' in the tab of the sheet. So you would change
this phrase:

ActiveSheet.ChartObjects("Chart 1").Chart.Whatever

to this:

ActiveWorkbook.Charts("Today1").Whatever

The trick in programming Excel is to keep straight all the different objects
in the vast hierarchy of its object model. The top phrase is for a chart
object embedded in a worksheet, the bottom for a chart sheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <l0nc5vshu34t27mj0...@4ax.com>, John Baltimore said

[Jon Peltier]

John -

See the response I just made to your other post. A chart embedded in a
worksheet is referenced differently by VBA code than a chart sheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <nr8f5vkbv881iptlh...@4ax.com>, John Baltimore said

[John Baltimore]

Hmmm... Do you know whether Tushar Mehta's AutoChart add-in
conflicts with your code?

John Herold

On Sun, 23 Feb 2003 20:06:27 -0800, jonpe...@yahoo.com (Jon Peltier)
wrote:

John Baltimore

[John Baltimore]

I'm sorry to keep bugging you about this, but...

The situation is as you devined. I made the changes but it still
doens't work. I suspect it doesn't know which worksheet ("Test" on
the tab) to look at for a change in the target cells ($E$2, etc.).
Could that be the problem, and how would I specify that?

Again, thanks in advance for your help.

John Baltimore

On Sun, 23 Feb 2003 20:06:27 -0800, jonpe...@yahoo.com (Jon Peltier)

[Jon Peltier]

John -

I would think that my lines of code and Tushar's add-in would wreak havoc with
your chart, if applied together using the same input cells and the same chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <455l5v8t8t5qr23s8...@4ax.com>,
th...@comcastNoSpam.net says...

[Jon Peltier]

John -

If you put the macro I provided on the code class module of the sheet that
contains the changing cells, the code will notice those changes. A few
messages back, I described how. It looks like:

>>>Right click on the worksheet tab, select View Code, and paste this into the
>>>code module that pops up in the VB editor.

- Jon

-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <jjtm5vc5m9meuhvf7...@4ax.com>,
th...@comcastNoSpam.net says...

[John Baltimore]

Perhaps you know the phrase, "mai culpa"? Well, in this case it's
"mia stupido". :-) I was clicking on the tab for the chart and
putting the code there, not on the underlying worksheet (duh). Your
last note gave me my error.

With that fixed, I was able to apply to the other three charts in the
workbook as well as the primary chart, including the secondary axes in
two of the other three charts (the VB Help finally made some sense to
me).

Again, thanks, not only for the help but also for the patient
persistance... very much appreciated.

John Baltimore

On Tue, 25 Feb 2003 09:26:08 -0800, jonpe...@yahoo.com (Jon Peltier)

[Jon Peltier]

Hey John -

If it weren't for patient persistence, I wouldn't be here.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <qehn5voocvf6dbp86...@4ax.com>, John Baltimore said

[John Baltimore]

I'm afraid that's a very good thing, because I have more on this. :-(

The code worked perfectly as long as I manually changed the cells
referenced in the Case statements. However, those cells are changed
as a result of (re)calculations that are triggered by refreshing
external data, and I've been unable to fashion the proper SUB
statement. Again, any assistance would be greatly appreciated.

John Baltimore

On Tue, 25 Feb 2003 20:50:44 -0800, jonpe...@yahoo.com (Jon Peltier)

[Jon Peltier]

John -

Does Worksheet_Calculate help? Sometimes externally updated data doesn't
trigger events in Excel. I don't know the particulars, but you culd try
searching the group:
http://groups.google.com

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <meap5vghp533vih0u...@4ax.com>,

[John Baltimore]

I tried these three lines (one at a time), with no effect:

'Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Private Sub Worksheet_SheetCalculate(ByVal Target As Excel.Range)

I'm not sure I did the middle one correctly (not sure if I was
supposed to replace "Sh" with something). I think I also tried a
ReCalculate form, too.

John Baltimore

On Wed, 26 Feb 2003 09:53:01 -0800, jonpe...@yahoo.com (Jon Peltier)

[Jon Peltier]

John -

I suspect the problem is with the externally updated data. It's beyond my
ken, but this search of google groups looks promising. Let us know if it was
fruitful:

http://groups.google.com/groups?as_q=external%20update%20change%20event&safe=
images&ie=UTF-8&oe=UTF-8&as_ugroup=*excel*&lr=&num=30&hl=en
(URL is all on one line)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <1h4q5vsdf4i3mtbt9...@4ax.com>,
th...@comcastNoSpam.net says...