inserting a vertical line in a specific point on the horizontal axis

[gocolonel77]

Hi,

I am trying to figue out how to put a vertical line on just certain tick marks on the horizontal axis.

See attachment.

Each number represents a week.

I want to be able to have a vertical line all the way up every fourth week which would represent a period.


Any suggestions.

Thanks

[NickyC]

There are at least a couple of ways to do this. You could use a macro to insert a line at every fourth point, or you could add a column series that shows a value only if the week is divisible by four, something like this (asuming your week numbers data for the x axis start with value "1" in cell A2)

=IF(A2/4=ROUND(A2/4,0),1.2,NA())

then set the chart type to column and apply the maximum gap width

[gocolonel77]

The latter of ur suggestion would not apply for it does not start in A2. This vertical line(s) would be duplicated over many similar charts. Would a macro be more efficent and if so I am not very knowledgeable about creating one. I know how to run one. Any website suggestions on how to learn to make one or any assistance from you would be greatly appreciated.

Thanks

[NickyC]

Hi
if you change the A2 in the formula to the address of the cell with your first label, then copy the formula, it should work.
Another simple way to do this would be to add major gridlines to the x axis, and set the increment between them to 4.
If for some reason this doesn't work for you, and you want to use a macro instead, here is one which should insert a vertical line at every fourth point in your chart:

Please Login or Register  to view this content.

if you want to change the increment between lines, change the XInc = 4 to the increment you want
If you want to change the colour or line width, adjust the RGB or weight
Because this inserts shapes into your graph, the lines will not adjust if you do something that changes the chart's dimensions (resizing, adding a title etc). If you do this, you will have to delete the lines and run the macro again.

To delete the shapes you could use this macro - but beware, it will delete all shapes including textboxes etc:

Please Login or Register  to view this content.

to use the macros, copy them from here and in excel press Alt+F11 to enter the visual basic area, select insert > module if you don't already have modules in the workbook, then paste the code from here

[gocolonel77]

Your Macro worked great (thank you). I attached the entire file for a better perspective, which I should have done in first place.

Two things:

is their a way to run the macro across all charts without have to insert it into each one and

Simple is good and you suggestion about major gridline on X axis on increments of 4 does work the easiest; however, since the X axis starts at 1 instead of 0 my lines are on Weeks 5, 9, 13, etc. They needs to be on 4, 8, 12, etc like the macro does. Any suggestion on how to make axis start at 0 so gridlines match the week i want. Or would you initial formula you suggested work the best.

Thanks for your efforts.

Jason

[NickyC]

Hi
if you want to use major gridlines, you could add an extra point at the start of the chart with no text label or data - it will mean your series lines don't start quite on the y axis, but given the scale, this will hardly be noticeable.

To use the macro to all charts in the workbook, assuming all charts are on chart sheets not embeded in worksheets, try this macro:

Please Login or Register  to view this content.

this assumes the earlier macro chart_add_vertical_lines is in your workbook