ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Charts and Charting in Excel (https://www.excelbanter.com/charts-charting-excel/)
-   -   Make a chart axis visible/invisible with VBA (https://www.excelbanter.com/charts-charting-excel/135865-make-chart-axis-visible-invisible-vba.html)

Jeff Reese

Make a chart axis visible/invisible with VBA
 
(I posted this in the Programming newsgroup but realized it was probably more
appropriate to post here...)

Hello.

I'm using Excel 2003. I have a chart on a page and I want to set the scale
on the Y axis programatically with VBA.

I have written code that does this successfully.

The problem: The code will not work if the Y axis is hidden (which I want)
- it throws an error saying it can't set the Maximum value.

Where I need help: I'm having trouble writing the code to make the Y axis
visible, then invisible again. The idea being that I'll display it, set the
scale, then hide it again.

I've looked around in the newsground and internet and I haven't been able to
find what I'm looking for.

Any help is appreciated.

Thanks.

--
Jeff

ExcelBanter AI

Answer: Make a chart axis visible/invisible with VBA
 
Hi Jeff,

To make the Y axis visible/invisible with VBA, you can use the following code:

To make the Y axis visible:
Formula:

[list=1][*]ActiveSheet.ChartObjects("Chart 1").Activate 'Replace "Chart 1" with the name of your chart[*]ActiveChart.Axes(xlValue).Select[*]Selection.TickLabels.Orientation = xlUpward 'This line is optionalit rotates the tick labels[*]Selection.Format.Line.Visible msoTrue[/list] 

To make the Y axis invisible:
Formula:

[list=1][*]ActiveSheet.ChartObjects("Chart 1").Activate 'Replace "Chart 1" with the name of your chart[*]ActiveChart.Axes(xlValue).Select[*]Selection.Format.Line.Visible = msoFalse[/list] 

You can use these code snippets in your existing code to make the Y axis visible, set the scale, and then hide it again.

Hope this helps!

Jon Peltier

Make a chart axis visible/invisible with VBA
 
Jeff -

Don't make the axis invisible by setting HasAxis = False; this actually
deletes the axis. Keep HasAxis equal to True, but set the axis patterns to
none, like this:

With ActiveChart.Axes(xlValue)
With .Border
.Weight = xlHairline
.LineStyle = xlNone
End With
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlNone
End With

Now the axis is there, just not visible. You can access any of its scale
properties without making it visible.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Jeff Reese" wrote in message
...
(I posted this in the Programming newsgroup but realized it was probably
more
appropriate to post here...)

Hello.

I'm using Excel 2003. I have a chart on a page and I want to set the
scale
on the Y axis programatically with VBA.

I have written code that does this successfully.

The problem: The code will not work if the Y axis is hidden (which I
want)
- it throws an error saying it can't set the Maximum value.

Where I need help: I'm having trouble writing the code to make the Y axis
visible, then invisible again. The idea being that I'll display it, set
the
scale, then hide it again.

I've looked around in the newsground and internet and I haven't been able
to
find what I'm looking for.

Any help is appreciated.

Thanks.

--
Jeff




Jeff Reese

Make a chart axis visible/invisible with VBA
 
Thanks, Jon. This works perfectly.

--
Jeff


"Jon Peltier" wrote:

Jeff -

Don't make the axis invisible by setting HasAxis = False; this actually
deletes the axis. Keep HasAxis equal to True, but set the axis patterns to
none, like this:

With ActiveChart.Axes(xlValue)
With .Border
.Weight = xlHairline
.LineStyle = xlNone
End With
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlNone
End With

Now the axis is there, just not visible. You can access any of its scale
properties without making it visible.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Jeff Reese" wrote in message
...
(I posted this in the Programming newsgroup but realized it was probably
more
appropriate to post here...)

Hello.

I'm using Excel 2003. I have a chart on a page and I want to set the
scale
on the Y axis programatically with VBA.

I have written code that does this successfully.

The problem: The code will not work if the Y axis is hidden (which I
want)
- it throws an error saying it can't set the Maximum value.

Where I need help: I'm having trouble writing the code to make the Y axis
visible, then invisible again. The idea being that I'll display it, set
the
scale, then hide it again.

I've looked around in the newsground and internet and I haven't been able
to
find what I'm looking for.

Any help is appreciated.

Thanks.

--
Jeff






All times are GMT +1. The time now is 04:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com