Hi all,

I'm pretty knew to Excel VBA and charting. I have a simple histogram
chart on a worksheet and the data ranges are 1 to 100. So Excel
automatically puts the Y-axis tick marks at 0, 20, 40, 60, 80, 100. But
I only want three tick marks to show on the Y-axis (0, 50, 100). How
can I do that?

Also is there a way to put a horizontal line at the 50 y-axis tick
mark that will show on top of my chart? My users want to be able to
quickly see the 50 line (i.e. which histogram bars extend above it, and
which ones don't reach it).

It would be great if I could programmatically do this in VBA code.

Thanks!
Brad

Hi,

Double click the Y axis and on the Scale tab set the Major Unit value to 50.
If you want code use the macro recorder whilst you do this and then you
should get something like,

ActiveChart.Axes(xlValue).MajorUnit = 50

As for adding a datum line see here for examples.
http://www.andypope.info/charts/averageline.htm

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Brad Stone" wrote in message
...
Hi all,

I'm pretty knew to Excel VBA and charting. I have a simple histogram
chart on a worksheet and the data ranges are 1 to 100. So Excel
automatically puts the Y-axis tick marks at 0, 20, 40, 60, 80, 100. But
I only want three tick marks to show on the Y-axis (0, 50, 100). How
can I do that?

Also is there a way to put a horizontal line at the 50 y-axis tick
mark that will show on top of my chart? My users want to be able to
quickly see the 50 line (i.e. which histogram bars extend above it, and
which ones don't reach it).

It would be great if I could programmatically do this in VBA code.

Thanks!
Brad

Thanks Andy! I have it working perfectly now thanks to your suggestions.

~Brad


Andy Pope wrote:

Hi,

Double click the Y axis and on the Scale tab set the Major Unit value to 50.
If you want code use the macro recorder whilst you do this and then you
should get something like,

ActiveChart.Axes(xlValue).MajorUnit = 50

As for adding a datum line see here for examples.
http://www.andypope.info/charts/averageline.htm

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Brad Stone" wrote in message
...
Hi all,

I'm pretty knew to Excel VBA and charting. I have a simple histogram
chart on a worksheet and the data ranges are 1 to 100. So Excel
automatically puts the Y-axis tick marks at 0, 20, 40, 60, 80, 100. But
I only want three tick marks to show on the Y-axis (0, 50, 100). How
can I do that?

Also is there a way to put a horizontal line at the 50 y-axis tick
mark that will show on top of my chart? My users want to be able to
quickly see the 50 line (i.e. which histogram bars extend above it, and
which ones don't reach it).

It would be great if I could programmatically do this in VBA code.

Thanks!
Brad