User Defined Function (UDF) results in #VALUE! if sheet is protected

[strasbdj]

Hello,
I have a spreadsheet that contains some some charts and I found a UDF online that allows me to have the axes set automatically based on the values of certain cells. This works great and gives me very aesthetically pleasing charts. However, when I protect the sheet that contains the charts, the UDFs all results in a #VALUE! error. I need to enforce protection on these charts so end users have the ability to see them but not modify them. Is there a way to make the UDF work when the sheet is protected? Below is the module I used to create the UDF.

Thank you,
Dusty

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

 

[GWteB]

Would it be an option for you to unprotect your sheet before calling this SetChartAxis function and protect your sheet afterwards?

 

[strasbdj]

I could unprotect the sheet, calculate the function, then reprotect it but that negates ever setting the protection. I don't want the end user to be able to unprotect it but I want the function to still update in real time.

 

[GWteB]

... but that negates ever setting the protection.

I do not understand this part. You can protect your sheet with VBA using a password. Whenever you protect your VBA project with a password also, real time updates are possible without exposing the sheet protection password.

 

[strasbdj]

Figured out a solution to my issue so thought I'd share. I created a module and assigned it to a button. The module will unprotect my sheet, run the calculations, and then reinforce sheet protection. This way end users don't need the password, they just click a button to refresh things. Below is the code for others to use. "Password" is your sheet's password.

Sub
ActiveSheet.Unprotect "password"
Calculate
ActiveSheet.Protect "password"
End Sub

 

[GWteB]

Glad you worked it out. Thanks for letting me know.