Trying to find Excel VBA code for "round dot" gridline.
April 18, 2010 6:14 PM   Subscribe

Excel VBA question: help me insert a "round dot" gridline in my chart.

The following line of code inserts a dotted gridline in my chart:

ActiveChart.Axes(xlValue).MajorGridlines.Border.LineStyle = xlDot

But if you change the gridline manually, you have two dotted line options: a faint "round dot" line and a more prominent "square dot" line. The "xlDot" choice in the code above returns the "square dot" style. I want the round dot, but none of the xlLineStyle options correspond with the round dot style. How do I get that line style?
posted by mullacc to Computers & Internet (8 answers total)
The linestyle options are just labels for integers, right?

Once you've manually set the style you want, I'd try:

MsgBox (ActiveChart.Axes(xlValue).MajorGridlines.Border.LineStyle)

to find out the value, and then set it straight to the integer, rather than the label.
posted by pompomtom at 6:59 PM on April 18, 2010

Strangely, it returns "-4118" which is the integer for xlDot (otherwise known as the "square dot" line).
posted by mullacc at 7:14 PM on April 18, 2010

What version of excel are you using? (guessing 2003, which I don't have handy...)

I don't suppose you want to send me an example? I'm intrigued. I suspect perhaps there's another property to the border which wants setting, but the (xl2007) test workbook I have here isn't really helping.
posted by pompomtom at 9:31 PM on April 18, 2010

I am using Excel 2007. I uploaded the example file to divshare.
posted by mullacc at 10:09 PM on April 18, 2010

Ouch. I'm stumped.

I've forwarded this to an excel list I recommend (see: Perhaps the experts there can tell us...
posted by pompomtom at 10:56 PM on April 18, 2010

Cool. Thanks.
posted by mullacc at 11:06 PM on April 18, 2010

Courtest of that list, the answer seems to be:

ActiveChart.Axes(xlValue).MajorGridlines.Format.Line.DashStyle = msoLineSysDot

posted by pompomtom at 4:44 PM on April 19, 2010

Winner winner.
posted by mullacc at 5:42 PM on April 19, 2010

« Older Help me find Ancient Britain on film   |   No-frills OS X Widget for sending e-mails? Newer »
This thread is closed to new comments.