Excel Chart from Access VBA

[aldi07]

Hi,
I am creating a chart in excel 2007 from access VBA 2007. It works very well. The only point I could not program, is changing the Axis Title color. I tried:

With appExcel
.ActiveChart.Axes(xlCategory).AxisTitle.Font.Color = vbCyan
End With

and I get "Object doesn't support this property or method"

Any clues anyone? thank you in advance.

 

[dhookom]

Have you tried to create the code in Excel by recording a macro? You can typically review the Excel code and apply it in the Access code.


Duane
Hook'D on Access
MS Access MVP

 

[aldi07]

Hi dhookom,
Actually yes, I did. It just ignores the action, and does not reproduce it into VBA code.
For example if I select the y axis title, and changes it's font to red, this is what the recorded macro shows in excel:
Sub Macro2()
'
' Macro2 Macro
'

'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveSheet.ChartObjects("Chart 1").Activate
End Sub

 

[dhookom]

This worked for me:

Sub SetYAxisColor()
    Dim myChart As Chart
    Dim ax As Axis
    Set myChart = ThisWorkbook.Worksheets(2).ChartObjects(1).Chart
    Set ax = myChart.Axes(xlCategory)
    With ax
        .AxisTitle.Font.Color = vbCyan
    End With
End Sub

Duane
Hook'D on Access
MS Access MVP