VBA to set Chart MajorUnit to specific cell

[kmbryant09]

I have a Chart ("Chart 3") that has some basic VBA code attached to it to set the Y-Axis Min & Max values based on specific cells:

ActiveSheet.ChartObjects("Chart 3").Chart.Axes(xlValue).MaximumScale = Range("CB1").Value
ActiveSheet.ChartObjects("Chart 3").Chart.Axes(xlValue).MinimumScale = Range("CB2").Value

I'm trying to also add VBA to set the Y-Axis Major Unit to cell CB3. I've tried the following code, but get an error when running the Macro:

ActiveSheet.ChartObjects("Chart 3").Chart.Axes(xlValue).MajorUnit = Range("CB3").Value

Any help?

Thanks!

 

[Santoli3]

Did you get an answer? I am trying to do exactly the same thing.

 

[Santoli3]

I setteled on this. works for me
Function setChartAxis(sheetName As String, chartName As String, MinOrMax As String, _
ValueOrCategory As String, PrimaryOrSecondary As String, Value As Variant)

'Create variables
Dim cht As Chart
Dim valueAsText As String

'Set the chart to be controlled by the function
Set cht = Application.Caller.Parent.Parent.Sheets(sheetName) _
.ChartObjects(chartName).Chart

'Set Value of Primary axis
If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _
And PrimaryOrSecondary = "Primary" Then

With cht.Axes(xlValue, xlPrimary)
If IsNumeric(Value) = True Then
If MinOrMax = "Max" Then .MaximumScale = Value
If MinOrMax = "Min" Then .MinimumScale = Value
If MinOrMax = "Major" Then .MajorUnit = Value
Else
If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
If MinOrMax = "Major" Then .MajorUnitIsAuto = True
End If
End With
End If

'Set Category of Primary axis
If (ValueOrCategory = "Category" Or ValueOrCategory = "X") _
And PrimaryOrSecondary = "Primary" Then

With cht.Axes(xlCategory, xlPrimary)
If IsNumeric(Value) = True Then
If MinOrMax = "Max" Then .MaximumScale = Value
If MinOrMax = "Min" Then .MinimumScale = Value
If MinOrMax = "Major" Then .MajorUnit = Value
Else
If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
If MinOrMax = "Major" Then .MajorUnitIsAuto = True
End If
End With
End If

'Set value of secondary axis
If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _
And PrimaryOrSecondary = "Secondary" Then

With cht.Axes(xlValue, xlSecondary)
If IsNumeric(Value) = True Then
If MinOrMax = "Max" Then .MaximumScale = Value
If MinOrMax = "Min" Then .MinimumScale = Value
If MinOrMax = "Major" Then .MajorUnit = Value
Else
If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
If MinOrMax = "Major" Then .MajorUnitIsAuto = True
End If
End With
End If

'Set category of secondary axis
If (ValueOrCategory = "Category" Or ValueOrCategory = "X") _
And PrimaryOrSecondary = "Secondary" Then
With cht.Axes(xlCategory, xlSecondary)
If IsNumeric(Value) = True Then
If MinOrMax = "Max" Then .MaximumScale = Value
If MinOrMax = "Min" Then .MinimumScale = Value
If MinOrMax = "Major" Then .MajorUnit = Value
Else
If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
If MinOrMax = "Major" Then .MajorUnitIsAuto = True
End If
End With
End If

'If is text always display "Auto"
If IsNumeric(Value) Then valueAsText = Value Else valueAsText = "Auto"

'Output a text string to indicate the value
setChartAxis = ValueOrCategory & " " & PrimaryOrSecondary & " " _
& MinOrMax & ": " & valueAsText

End Function

 

[Santoli3]

Oops, wrong image. It looks like the image here. The Right column cell formula for X axis Major unit is: =setChartAxis("EXCH vs LQA","Chart 3","Major","X","Primary",VALUE(AA99)) where AA is the middle coulumn.