VBA to Obtain Chart Axes Major and Minor Units

[bisel]

Hello All,

I am trying to use VBA to obtain the value of the major and minor units for a chart's vertical axis and then assign that value to range on the worksheet. Here is the code ...

:
:
' Set major and minor units
    Sheet7.Range("chart1_major").Value = Sheet7.ChartObjects("startyear_rfb_chart").Chart.Axes(x1Value).MajorUnit
    Sheet7.Range("chart1_minor").Value = Sheet7.ChartObjects("startyear_rfb_chart").Chart.Axes(x1Value).MinorUnit
:
:

When I execute the code, I get this error message ...

Trying to debug this and running into dead end. Any help appreciated.

Thanks,

Steve

 

[NdNoviceHlp]

Hi Steve. Your question isn't clear. U stated that you're trying to obtain the value of the major unit but your code says that you're trying to set it. Anyways, I'm not sure what part of your code is wrong but I set up a trial wb with a chart on sheet 1 and a named range "chart1_major" and all of the code below works. HTH. Dave

'display major unit value
MsgBox Sheets("Sheet1").ChartObjects(1).Chart.Axes(xlValue).MajorUnit
'1)set named range "chart1_major" to major unit value
Sheets("Sheet1").Range("chart1_major").Value = _
        Sheets("Sheet1").ChartObjects(1).Chart.Axes(xlValue).MajorUnit
'2)set major unit value to named range "chart1__major"
Sheets("Sheet1").ChartObjects(1).Chart.Axes(xlValue).MajorUnit = _
        Sheets("Sheet1").Range("chart1_major").Value

ps. use either 1 or 2 above

 

[bisel]

Hi Steve. Your question isn't clear. U stated that you're trying to obtain the value of the major unit but your code says that you're trying to set it. Anyways, I'm not sure what part of your code is wrong but I set up a trial wb with a chart on sheet 1 and a named range "chart1_major" and all of the code below works. HTH. Dave

'display major unit value
MsgBox Sheets("Sheet1").ChartObjects(1).Chart.Axes(xlValue).MajorUnit
'1)set named range "chart1_major" to major unit value
Sheets("Sheet1").Range("chart1_major").Value = _
        Sheets("Sheet1").ChartObjects(1).Chart.Axes(xlValue).MajorUnit
'2)set major unit value to named range "chart1__major"
Sheets("Sheet1").ChartObjects(1).Chart.Axes(xlValue).MajorUnit = _
        Sheets("Sheet1").Range("chart1_major").Value

ps. use either 1 or 2 above

What the code is stating is that a named range is set to the Major or Minor Units of the charts. And I resolved this by re-writing the code. It is exactly the same, but now it works. Here is my code ...

Sub charts_update()

Dim maxpct As Double
Dim axesmajor As Double
Dim axesminor As Double
:
:
' Set major and minor units
    Sheet7.Range("chart1_major").Value = Sheet7.ChartObjects("startyear_rfb_chart").Chart.Axes(xlValue).MajorUnit
    Sheet7.Range("chart2_major").Value = Sheet7.ChartObjects("eoy_reserve_chart").Chart.Axes(xlValue).MajorUnit
    Sheet7.Range("chart3_major").Value = Sheet7.ChartObjects("startyear_reserve_expenses_chart").Chart.Axes(xlValue).MajorUnit
    Sheet7.Range("chart1_minor").Value = Sheet7.ChartObjects("startyear_rfb_chart").Chart.Axes(xlValue).MinorUnit
    Sheet7.Range("chart2_minor").Value = Sheet7.ChartObjects("eoy_reserve_chart").Chart.Axes(xlValue).MinorUnit
    Sheet7.Range("chart3_minor").Value = Sheet7.ChartObjects("startyear_reserve_expenses_chart").Chart.Axes(xlValue).MinorUnit
:
' On the worksheet, each of the name ranges, above, is examined and then two named ranges (axesmajor and axesminor) are then set to
'    the minimum values of the ranges above and then used to set the major and minor units of three charts so all three charts look similar
'    with the same vertical axes major and minor units.
:
    Sheet7.ChartObjects("startyear_rfb_chart").Chart.Axes(xlValue).MajorUnit = Range("axesmajor").Value
    Sheet7.ChartObjects("eoy_reserve_chart").Chart.Axes(xlValue).MajorUnit = Range("axesmajor").Value
    Sheet7.ChartObjects("startyear_reserve_expenses_chart").Chart.Axes(xlValue).MajorUnit = Range("axesmajor").Value
    Sheet7.ChartObjects("startyear_rfb_chart").Chart.Axes(xlValue).MinorUnit = Range("axesminor").Value
    Sheet7.ChartObjects("eoy_reserve_chart").Chart.Axes(xlValue).MinorUnit = Range("axesminor").Value
    Sheet7.ChartObjects("startyear_rfb_chart").Chart.Axes(xlValue).MinorUnit = Range("axesminor").Value
:
:
End Sub

Regards,

Steve