Hello,
I am posting a code that I found online that has worked wonderfully for me for a couple of years, allowing the chart axis to be dynamic. I don't have to manually adjust the y-axis...
However, I cannot protect my sheet. I am the only one using my workbooks, but there are many cells with formulas, etc. I have to color code my sheets but just want to make it easier since I use them for many hours throughout the day. I have seen some other codes on this forum but they don't seem to work.
I have even tried protecting only one cell on the sheet. When I protect the sheet, and enter the data to update the chart, it remains with the old chart parameters. I unlocked the sheet, then locked one cell, and protected sheet, and checked the Edit Objects box, without any luck.
Is there a way to add a line of code to allow the charts to update, with the sheet protected. If so, where would the code go in the following?"
Thank you.
Erick
I am posting a code that I found online that has worked wonderfully for me for a couple of years, allowing the chart axis to be dynamic. I don't have to manually adjust the y-axis...
However, I cannot protect my sheet. I am the only one using my workbooks, but there are many cells with formulas, etc. I have to color code my sheets but just want to make it easier since I use them for many hours throughout the day. I have seen some other codes on this forum but they don't seem to work.
I have even tried protecting only one cell on the sheet. When I protect the sheet, and enter the data to update the chart, it remains with the old chart parameters. I unlocked the sheet, then locked one cell, and protected sheet, and checked the Edit Objects box, without any luck.
Is there a way to add a line of code to allow the charts to update, with the sheet protected. If so, where would the code go in the following?"
Thank you.
Erick
VBA Code:
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
Else
If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
If MinOrMax = "Min" Then .MinimumScaleIsAuto = 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
Else
If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
If MinOrMax = "Min" Then .MinimumScaleIsAuto = 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
Else
If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
If MinOrMax = "Min" Then .MinimumScaleIsAuto = 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
Else
If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
If MinOrMax = "Min" Then .MinimumScaleIsAuto = 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
Last edited by a moderator: