Controlling a chart in a macro...

[SteveD]

Hi all,

Hope someone can help with this. In Excel 2007, I'm writing a macro to create several charts (column) that need to match what a designer has already built. I'm having a devil of a time finding the code I need to create parts of the chart. I've searched the web and this forum, but I must just not be asking the right things.

I can see everything I want to do in the format pop-up window when I right click on the charts in Excel, here is the path:

1) Format Minor Gridlines, Line Style, Dash Type, Rounded Dot
2) Format Axis, Axis Options, Major tick mark type, None
3) Format Axis, Axis Options, Position Axis, Between tick marks

Thanks for your help!

Steve

 

[VoG]

Had you been using an earlier version i would have advised recording a macro. Unfortunately that won't work with Excel 2007.

If you can access an earlier version of Excel the macro recorder should give you the correct syntax.

 

[SteveD]

Thanks... I'm stuck with 2007 and zero help from the recorder.

 

[SteveD]

Bumping to see if anyone can help out with these three lines of code.

Thanks

 

[Kaps_mr2]

Steve,

When you say designer - does that mean that the graphs already exist ? If so I had a similar problem a while ago.

In the end I used the macro recorder, clicked on the graph feature that I wanted, changed it to some other option, and then changed it back. I then hacked the code.

I hope this helps. Thanks

kaps

 

[SteveD]

It would... but excel 2007's recorder does not record these changes. It only selects the graph. Way to go MS.

Since two people have said this in this thread... perhaps someone could record it and post the code I can mark it up from there. I just don't know the key words and the object path. ANd a ton of searching on line has not yielded me anything. If you have a good resource that shows the code for these, posting a link would be great!

Thanks
Steve

 

[Kaps_mr2]

if you send me a private message with your e-mail address, I will send the workbook over to you. However, it is not complete - I got sidetracked by another request from the same client.

Kaps

 

[Andrew Poulsom]

See if this helps:

Sub Test()
    With ActiveChart.Axes(xlCategory)
        With .MinorGridlines.Border
            .Weight = xlHairline
            .LineStyle = xlDot
        End With
        .MajorTickMark = xlNone
        .AxisBetweenCategories = False
    End With
End Sub

The Object Browser can also be helpful (if you know what you are looking for).

 

[SteveD]

Thank you.

xlDot is not the same as the small dot.
and .MajorTickMark = xlNone
kicks out the error "Object does not support this property or method. "
'-----------------------------------------------------
'Bottom Axis (x)
'-----------------------------------------------------
With .Axes(1)
.HasMajorGridlines = True
.TickLabels.Font.Bold = True
.TickLabels.Font.Color = RGB(11, 12, 13) 'Black
'.tickmark = xlCross
With .MajorGridlines
.Border.Color = RGB(0, 136, 187) 'Blue
.Border.Weight = xlHairline
.Border.LineStyle = xlDot
.MajorTickMark = xlNone
.AxisBetweenCategories = False
End With
End With

 

[Andrew Poulsom]

Use the Object Browser and Help to find the LineStyle constants.

MajorTickMark is a property of the Axis object (as in my code), not of the GridLines object (as in yours).