Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 19
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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!
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 19
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
change invisible bars back to visible hf Charts and Charting in Excel 1 July 28th 06 02:29 PM
Check Box to switch make comments visible or invisible DanHegarty Excel Discussion (Misc queries) 1 June 10th 06 04:08 PM
Can't make secondary axis in bar chart Angivan Charts and Charting in Excel 2 October 6th 05 09:20 AM
How do you make a combination chart with 2 axis. smw Charts and Charting in Excel 1 October 6th 05 12:28 AM
How do I make the Y axis in an excel chart the category axis? megnadoodle Charts and Charting in Excel 2 March 29th 05 09:27 AM


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

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"