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?
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?
Response by poster: 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
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 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
Response by poster: I am using Excel 2007. I uploaded the example file to divshare.
posted by mullacc at 10:09 PM on April 18, 2010
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: peach.ease.lsoft.com). Perhaps the experts there can tell us...
posted by pompomtom at 10:56 PM on April 18, 2010
I've forwarded this to an excel list I recommend (see: peach.ease.lsoft.com). Perhaps the experts there can tell us...
posted by pompomtom at 10:56 PM on April 18, 2010
Best answer: Courtest of that list, the answer seems to be:
posted by pompomtom at 4:44 PM on April 19, 2010
ActiveChart.Axes(xlValue).MajorGridlines.Format.Line.DashStyle = msoLineSysDot
posted by pompomtom at 4:44 PM on April 19, 2010
This thread is closed to new comments.
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