VBA to set y-axis min and max via values in worskheet cells?

Topic

New Reply

[INDENT] Sorry, I’ve tried unsuccessfully adapting code posted elsewhere for this…I would like to use VBA to be able to have three named cells determine the min, max and major unit on the y-axis of each of 3 line charts on a worksheet.

In the simple example attached, I have data for three charts, and the charts themselves. The y-axis for each is currently set to auto-scaling.

There are also three named cells, Axis_min, Axis_max, and Major_unit, in which the user would enter the desired values. The user would then click a button and have each of the three charts use the specified y-axis min, max and major unit.

Before posting this I found an explanation of how to do this on John Peltier’s site

http://peltiertech.com/Excel/Charts/…nkToSheet.html

which I have tried to adapt, without success.

The adapted code — which if I can ever get to work — will be triggered by a button on the worksheet, and is as follows:

——————————————
Option explicit

Sub ChangeAxisScales()
With ActiveSheet.ChartObjects(“Chart 1”).Chart

‘ Value (Y) Axis
With .Axes(xlValue)
.MaximumScale = ActiveSheet.Range(“Axis_max”).Value
.MinimumScale = ActiveSheet.Range(“Axis_min”).Value
.MajorUnit = ActiveSheet.Range(“Unit”).Value
End With

End With
End Sub
——————————————–

When I run the macro, however, I get the error message, “Unable to get the ChartObjects property of the worksheet class”.

I’ve no idea what this means. I guess this is what happens when you make a good faith attempt to adapt code when you don’t really understand VBA

If anyone could help me make this work I would be super grateful. [/INDENT]

Reply | Quote

Replies

Your charts are numbered from 2-4 hence the problem (no Chart1). Try this:

Code:

Sub ChangeAxisScales()
   Dim objCht As ChartObject
   For Each objCht In ActiveSheet.ChartObjects
      With objCht.Chart
         ' Value (Y) Axis
         With .Axes(xlValue)
            .MaximumScale = ActiveSheet.Range("Axis_max").Value
            .MinimumScale = ActiveSheet.Range("Axis_min").Value
            .MajorUnit = ActiveSheet.Range("Unit").Value
         End With
      End With
   Next objCht
End Sub

Reply | Quote

Works like a charm. Awesome. Thank you very much, Rory!

Reply | Quote

Hi all,

Sorry to bump linyai’s thread, but it describes exactly what I’ve been trying to do for ages. I’ve replicated rory’s final solution, but – for my purposes – made it a Worksheet Change event, as I’d need this macro to run without the need for activating it by pressing a button. So, I’ve got that to work and it’s an excellent piece of code.

Where I’m struggling though – and where the code falls over – is that, in my workbook, I’d need to take the imbedded Chart object, and move it to its own tab. Once you do that, using this code, the x/y axis variables no longer update

I’d be most grateful if anyone could advise on how to change the code, so that the Chart tab would, for instance, know that it had to look at Sheet 1, to locate the variables. I’ve tried altering the code myself, but unfortunately not had much luck. I’m guessing it can’t be that difficult, but alas my VBA knowledge is very rusty (last dabbled in 2004)

I really would be grateful if someone could give me a steer on this,

Best.

Reply | Quote

Hi

Welcome to the Lounge!

..so, instead of:
.MaximumScale = ActiveSheet.Range(“Axis_max”).Value
.MinimumScale = ActiveSheet.Range(“Axis_min”).Value
.MajorUnit = ActiveSheet.Range(“Unit”).Value

..you could try this:
.MaximumScale = Sheets(“zzz”).Range(“Axis_max”).Value
.MinimumScale = Sheets(“zzz”).Range(“Axis_min”).Value
.MajorUnit = Sheets(“zzz”).Range(“Unit”).Value

..where you change “zzz” to your sheet name, e.g “Sheet1”, “Fred” etc etc.
OR, perhaps you could just use this form:
.MaximumScale = [Axis_max]
.MinimumScale = [Axis_min]
.MajorUnit = [Unit]
..where the square brackets refer directly to named cells

zeddy

Reply | Quote

Hi Zeddy – thank you for the welcome! I’ve been looking through over threads, and the coding is bringing back memories

Thank you for the suggestion – I’ve tired that variation, but it doesn’t seem to work either? I’ve amended it to:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim objCht As ChartObject
   For Each objCht In ActiveSheet.ChartObjects
      With objCht.Chart
         ‘ Value (Y) Axis
         With .Axes(xlValue)
            .MaximumScale = Sheets(“Sheet1”).Range(“Axis_max”).Value
            .MinimumScale = Sheets(“Sheet1”).Range(“Axis_min”).Value
            .MajorUnit = Sheets(“Sheet1”).Range(“Unit”).Value
         End With
      End With
   Next objCht

End Sub

I go back to Sheet1, amend cells E15(Axis_min) or E16(Axis_max) and, while the two charts I’ve left embedded on Sheet1 change accordingly, the one I’ve moved to create the Chart1 tab still doesn’t

Any ideas?

Reply | Quote

Attaching sample file, to illustrate what I’m trying to do/where I’m going wrong!

34646-y-axis-scaling-problem-for-forum_v1.1

Reply | Quote

Thedamo,

Here is a revised version that updates the chart using the Worksheet_Activate event and calls the ChangeAxisScales subroutine in module 1.

HTH,
Maud

Reply | Quote

Hi

Maudibe provides a solution to update the chart sheet “Sheet2”.
But the button on “Sheet1” now won’t update the charts on “Sheet1”.

My attached version updates both.
And uses the shorter vba coding I suggested earlier.

zeddy

Reply | Quote

Thedamo,

In your file, there were 3 instances of the same routine with the same name Sub ChangeAxisScales. In the file that I posted, I purposely removed 2 of them to demonstrate the Worksheet_Activate event

Reply | Quote

Hi Maudibe

..you were correct to purposely remove 2 of them from the sheet codemodules.

..but in [Module1], you changed
For Each objCht In ActiveSheet.ChartObjects
to..
For Each objCht In Worksheets(“Sheet2”).ChartObjects
..whereas if you left it as is, the button would still work

zeddy

Reply | Quote

As Thedamo states

…in my workbook, I’d need to take the imbedded Chart object, and move it to its own tab. Once you do that, using this code, the x/y axis variables no longer update

Reply | Quote

..whereas if you left it as is, the button would still work
..and the Chart on Sheet2 would update as well

zeddy

Reply | Quote

…whereas,

Once you do that, using this code, the x/y axis variables no longer update….

on the chart that was just moved. Unless I am reading it wrong, the OP wants to move the chart to another workbook and not use the chart on the original sheet.

Reply | Quote

Hi Maudibe

..maybe I’m reading it wrong.
..the chart object was already moved to its own tab (no mention of another workbook).

I go back to Sheet1, amend cells E15(Axis_min) or E16(Axis_max) and, while the two charts I’ve left embedded on Sheet1 change accordingly, the one I’ve moved to create the Chart1 tab still doesn’t
[/quote]

In your file, when you change the values on [Sheet1] and then switch to [Sheet2], the Chart updates automatically, which is what thedamo wanted. Top marks for fixing that. I was just pointing out that clicking the button on [Sheet1] no longer updated the two charts on [Sheet1].

zeddy

Reply | Quote

Hi

The attached version doesn’t require the button on [Sheet1]
Updates to the Min, Max, or Major unit values will be shown immediately in the two charts on [Sheet1], and will be automatically shown when you switch to the sheet named [Chart1]

zeddy

Reply | Quote

Sorry, meant worksheet in #14

Reply | Quote

Sorry, meant worksheet

Reply | Quote

Morning guys,

THANK YOU! Both of those solutions work perfectly, and – although I wouldn’t need the charts on Sheet1 to dynamically update (in practice my workbook probably won’t have any charts on the sheet where I calculate the axis variables) it could be that – like myself – someone finds this thread, and would need that.

I’ll be transposing the code into my workbook later, and hopefully the code will update the two charts (which exist as two separate tabs) as necessary. The charts themselves illustrate two different time windows: 2 month, and 6 month (x axis) and some test results (in 000th’s of seconds) running up my y axis.

The dynamism is needed, because my analysis takes place within around a 1 or 1.5sec period – but that period can change, depending on the samples, ie: one test might be acrss 24 – 25.5secs; whereas another might be 34.5 – 35.5. I then use some simple averaging (with +/- 0.5 I think) formula to calculate my upper and lower limits, which become my Min and Max on the Y axis. This works really well, but – as you can imagine – rather slow when doing it manually!

Many thanks – really do appreciate it

Reply | Quote

Evening all,

Unfortunately I still cannot seem to get this code to successfully transpose over into the Workbook which I’m hoping to use it on When I debug, I get a ‘Run-time error ‘9’: Subscript out of range – with a highlighted error line as being:

Code:

For Each objCht In Worksheets(“Sheet2”).ChartObjects

I’m attaching my operational Workbook here, so you can see what I’m hoping to use this for. I’ve named cells C16 & C17 as “Axis_min”, and “Axis_max”, respectively – and it is those cells that I’d wish to use to control my Y axis for the chart on Sheet2.

I’ve inputted the code to Sheet1, and the call procedure to Sheet2 on Activate – but, alas, it doesn’t seem to fire at all

This is seriously baffling me! 34665-Y-Axis-Scaling-Problem-v1.2

Thanks for all the help so far; is it a line of code I’m missing here???

Reply | Quote

Hi

See attached file.
I don’t like Chartsheets.
So I copied your chart to a new Worksheet.
I commented out the vba line
.MajorUnit = [Unit]
as you don’t have this named cell anymore.
(you could always define it later).
Oh, and because I don’t like lots of decimals in my axes, I used ROUND to set it to 2 decimals only

let me know if you are happy with this.

zeddy

Reply | Quote

Zeddy – excellent!!!!

That looks like it’ll do the job. Yes I meant to do some tidying around of formulas, and agree with you about the rounding too Many many thanks – really have appreciated the help. Great forum this – am learning so much (and dusting off many a cobweb!) by having a browse…

Reply | Quote

Evening all

Okay so I’m a bit stuck – again. I’ve been trying to get Zeddy’s code to work for both asis, ie: ‘Y’ (as discussed before) and also ‘X’. I thought that I could just copy the ‘Y’ code, and replace it with the syntax to reference the ‘X’ asis, and also replace the named ranges with those where I would source the values from, specifically: “Axis_date_max” and “Axis_date_min” – creating this code:

Code:

Sub ChangeAxisScales()
Dim objCht As ChartObject

For Each objCht In ActiveSheet.ChartObjects     ‘loop through all charts on sheet

   With objCht.Chart.Axes(xlValue)              ‘define shortcut
    .MaximumScale = [Axis_max]                  ‘set value from named cell
    .MinimumScale = [Axis_min]                  ‘set value from named cell
   ‘ .MajorUnit = [Unit]                         ‘set value from named cell
   End With                                     ‘end of shortcut
   
   With objCht.Chart.Axes(xlCategory)
   [COLOR=”#FF0000″] .MaximumScale = [Axis_date_max][/COLOR]
    .MinimumScale = [Axis_date_min]
    ‘ .MajorUnit = [Unit]                         ‘set value from named cell
    End With

Next objCht                                     ‘process next chart
   
End Sub

Alas, it bugs on red line of red code – returning a “Run-time error ’13’: Type mismatch”

I’m attaching the workbook again, and it’s essentially cells C19 and C20 that I’m trying to use as my min and max ‘X’ axis values. In my master workbook, those are now named as ‘Axis_date_min’ and ‘Axis_date_max’, respecitvely.

I’m sure it’s an easy fix, and I’ve tried cracking this myself but I keep bugging out

Thanks again guys,

Damo34725-Y-Axis-Scaling-Problem-v1.2

Reply | Quote

Hi

see my attached revised file.
When I change the value in cell [C19] on [Sheet1], this is reflected in the chart on sheet[Chart1]

zeddy

Reply | Quote