Changing Chart Parameters

[DCSwearingen]

Is there a way to change chart parameters without actually selecting th
sheet or activating the chart?

I have about 160 individual charts on three worksheets.
I know I can set Application.ScreenUpdating = False as a last resort.

Here is the basic structure for updating one chart's parameters.

Sub SetAxesValues()
Dim cMin, cMax
cMax = Sheets("Electric").Range("A3").Value
cMin = Sheets("Electric").Range("A4").Value

Sheets("Electric").Select
ActiveSheet.ChartObjects("PotElectRoll").Activate

With ActiveChart.Axes(xlValue)
.MinimumScale = cMin
.MaximumScale = cMax
.MinorUnit = (cMax - cMin) / 25
.MajorUnit = (cMax - cMin) / 5
End With

cMin = Sheets("Electric").Range("A5").Value
cMax = Sheets("Electric").Range("A6").Value

With ActiveChart.Axes(xlCategory)
.MinimumScale = cMin
.MaximumScale = cMax
End With
End Sub


Can the two lines selecting the sheet and activating the chart b
replaced by
wrapping the rest of the routine in a With / End With construction?

I just can't seem to find the syntax... The following doesn't work.

With Sheets.ChartObjects("PotElectRoll")
...
End Wit

 

[Gazeta]

U¿ytkownik "DCSwearingen"

Is there a way to change chart parameters without actually selecting the
sheet or activating the chart?

I have about 160 individual charts on three worksheets.
I know I can set Application.ScreenUpdating = False as a last resort.

Here is the basic structure for updating one chart's parameters.

Sub SetAxesValues()
Dim cMin, cMax
cMax = Sheets("Electric").Range("A3").Value
cMin = Sheets("Electric").Range("A4").Value

Sheets("Electric").Select
ActiveSheet.ChartObjects("PotElectRoll").Activate

With ActiveChart.Axes(xlValue)
MinimumScale = cMin
MaximumScale = cMax
MinorUnit = (cMax - cMin) / 25
MajorUnit = (cMax - cMin) / 5
End With

cMin = Sheets("Electric").Range("A5").Value
cMax = Sheets("Electric").Range("A6").Value

With ActiveChart.Axes(xlCategory)
MinimumScale = cMin
MaximumScale = cMax
End With
End Sub


Can the two lines selecting the sheet and activating the chart be
replaced by
wrapping the rest of the routine in a With / End With construction?

I just can't seem to find the syntax... The following doesn't work.

With Sheets.ChartObjects("PotElectRoll")
..
End With

if you want to go thru all the sheets & all charts located in there use

for each sh ns sheets
sh.activate
for each char in activesheets.chartobjects
....your code here
next
next

is that what u meant?
mcg

 

[Guest]

The following changes the scaling of a specified chart without selecting
either the worksheet or chart:

Sub SetAxesValues()
Dim cMin As Long, cMax As Long
Dim cht As Chart

cMax = ws.Range("A3").Value
cMin = ws.Range("A4").Value
Set cht = ws.ChartObjects("PotElectRoll").Chart

With cht.Axes(xlValue)
.MinimumScale = cMin
.MaximumScale = cMax
.MinorUnit = (cMax - cMin) / 25
.MajorUnit = (cMax - cMin) / 5
End With

cMin = ws.Range("A5").Value
cMax = ws.Range("A6").Value

With cht.Axes(xlCategory)
.MinimumScale = cMin
.MaximumScale = cMax
End With
End Sub

Regards,
Greg

 

[Guest]

As implied by Gazeta, the task of updating 160 charts is very cumbersome and
likely needs a mechanized approach such as via a loop and passing ws names
and chart names to the macro as arguments. The cell ranges containing the
scaling values, if not the same for each chart (as I would assume), will also
have to be accounted for.

Greg

 

[DCSwearingen]

Thank you both for replying!!

This helps a lot!!!

I do have the 160 charts on 3 sheets in the same workbook. I started
researching MVP web sites as to how to automate and make them dynamic a
few weeks ago. I am off work for a couple of weeks due to a surgery and
figured this would give me plenty of time to try things. I had a
problem figuring out the syntax without going through the select and
activate business.

My plan was to loop through all of the charts in each individual
worksheet, setting variables (range adresses) according to the name of
the chart in the loop at the time using a select case structure. I
suppose I could even wrap all of this in a loop through the sheets as
well.

I intentionally left cMin and cMax as true variables as my category
axis is formatted as dates and the value axis is numerical in all
cases.

No matter what I end up with, it will still be better than having to
manually select and change each chart without VBA.

 

[Guest]

You may have noticed that I forgot to define the ws variable in my first
post. Should have been something like:

Dim ws As Worksheet
Set ws = ActiveSheet

I had originally wrote something like the code below but decided to keep it
simple and go with the basic comment as per my second post. With my original
code, the ws variable was passed to the SetAxesValues macro as an argument
and therefore didn't require defining within the macro. When I scrapped it I
forgot to make the correction.

With the TestABC macro you can loop through your charts and change the range
references that contain the scaling values specific to each chart. In the
example, they start at A5:A8 and, as the loop iterates through each chart,
the ranges offset by 10 rows respectively:

Sub TestABC()
Dim ws As Worksheet
Dim cht As Chart
Dim i As Integer
Dim y1, y2, x1, x2

Set ws = ActiveSheet
For i = 0 To ws.ChartObjects.Count - 1
Set cht = ws.ChartObjects(i + 1).Chart
y1 = ws.Cells(5 + i * 10, 1)
y2 = ws.Cells(6 + i * 10, 1)
x1 = ws.Cells(7 + i * 10, 1)
x2 = ws.Cells(8 + i * 10, 1)
SetAxesValues ws, cht, y1, y2, x1, x2
Next
End Sub

Sub SetAxesValues(ws As Worksheet, cht As Chart, MinY, MaxY, MinX, MaxX)
With cht.Axes(xlValue)
.MinimumScale = MinY
.MaximumScale = MaxY
.MinorUnit = (MaxY - MinY) / 25
.MajorUnit = (MaxY - MinY) / 5
End With
With cht.Axes(xlCategory)
.MinimumScale = MinX
.MaximumScale = MaxX
End With
End Sub

Regards,
Greg

 

[DCSwearingen]

Yes, I noted ws was not declared, wasn't sure why.

Thanks for the update with the added code. As always any help i
appreciated!!

I am actually changing a lot more than just the x & y axis scaling wit
this now, and seeing the added code gives me even more ideas on handlin
a few more items.

I have most of my named formulas created to automatically adjust char
data series. I added a couple of cells for the end user to be able t
select Fiscal Years (which gives the X-Axis scaling), Latest Reportin
Period (for series control) and a few other things.

I can only type with one hand right now (never was too fast to star
with ) and it takes a while to test & modify, but it is fun..

 

[Walden2]

Here's a tangential question.

In a PivotChart, is it possible in code to access a particular value on
the Y axis?

I'd like to draw an upper quartile line across the columns of a
PivotChart, but can't figure out how to access the upper quartile
value.

Thanks,

Walden