Solved

Excel 2003 VBA - changing axis scale upon worksheet change

Posted on 2013-02-04

Microsoft Excel

1 solution

427 Views

Last Modified: 2013-02-11

Worksheet with combo boxes. User selects combos linked to a cell and the chart changes according to selections. Need the axis to also adjust to my named ranges. I would like this to be upon a worksheet_change vs manually calling the macro/code if possible.

I've tried many different ways of doing this and have nothing workable yet. I need this asap and have spent many many hours researching this from examples.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ch As ChartObject
Set ch = Worksheets(Summary).ChartObjects("Chart 16").Select

With ch.Axes(xlValue, xlSecondary)
.MinimumScale = ActiveSheet.Name("MinScale_LP")
.MaximumScale = ActiveSheet.Name("MaxScale_LP")
End With

With ch.Axes(xlValue, xlSecondary)
.MinimumScale = Summary.Name("MinScale_GFWC")
.MaximumScale = Summary.Name("MaxScale_GFWC")
End With
End Sub

This did not work either:
Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.ChartObjects("Chart 16").Chart.Axes(xlValue).MaximumScale = MaxScale_LP
ActiveSheet.ChartObjects("Chart 16").Chart.Axes(xlValue).MinimumScale = MinScale_LP
ActiveSheet.ChartObjects("Chart 16").Chart.Axes(xlValue, xlSecondary).MaximumScale = MaxScale_GFWC
ActiveSheet.ChartObjects("Chart 16").Chart.Axes(xlValue, xlSecondary).MinimumScale = MinScale_GFWC

End Sub

Comment

Question by:culpees

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

3 Comments

Accepted Solution

by:culpees

culpees earned 0 total points

ID: 388535232013-02-04

Nevermind. I figured it out.
I recorded a macro to see what Excel wrote for the code and came up with this:
Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.ChartObjects("Chart 16").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("$Q$32")
.MaximumScale = Range("$r$32")
.MajorUnitIsAuto = True
.MajorUnit = Range("$S$32")
.MinorUnitIsAuto = Range("$T$32")
.MinorUnit = Range("$u$32")
End With
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = Range("$Q$33")
.MaximumScale = Range("$r$33")
.MajorUnitIsAuto = True
.MajorUnit = Range("$S$33")
.MinorUnitIsAuto = Range("$T$33")
.MinorUnit = Range("$u$33")
End With
End Sub

It wasn't working with defined names, so I had to use ranges.

LVL 85

Expert Comment

by:Rory Archibald

ID: 388585602013-02-06

FYI, it should work with named ranges too if you use
Range("MaxScale_LP")
syntax.

Author Closing Comment

by:culpees

ID: 388753982013-02-11

I resolved it before anyone else bothered to answer.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title	# Comments	Views	Activity
EXCEL file checking.	11	45	2017-05-08
look up for 2 numbers of 3	2	33	2017-05-08
need count any combinaton of 4 numbers	7	19	1d
Problem to macro	5	16	7h

Freeze Panes feature

Article by: Rob

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.

Microsoft Excel

MS Excel - using the Hyperlink formula

Article by: Thomas

Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!