Change Chart Axis in Protected Sheet via VBA not working

[Rootero]

Hi all, I am struggling to dynamically change the axis of my chart in a protected worksheet. The code below works fine on an unprotected sheet, but once I protect the sheet the code is not unprotecting the sheet and it stops without error at the line ws.ChartObjects("SUBPlot").Chart.Axes(xlValue).MaximumScale = upper. I am at a loss as to why the unprotect is not working (MsgBox ws.protectionstatus will give "true" before and after unprotect command). Could you please help me figure out what is going wrong here?

Thank you!

Function ChangeChartAxisMaxScale(upper)
    Dim pass As String
    pass = "myPassword"

    Dim wasProtected As Boolean
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.ActiveSheet
        If ws.ProtectionMode Then
            ws.Unprotect Password:=pass
            wasProtected = True
        Else
            wasProtected = False
        End If

    ws.ChartObjects("SUBPlot").Chart.Axes(xlValue).MaximumScale = upper

    If wasProtected Then
            ws.Unprotect Password:=pass, UserInterfaceOnly:=True, AllowFiltering:=True, DrawingObjects:=True
            ws.EnableOutlining = True
    Else
    End If

End Function

 

[NdNoviceHlp]

Maybe this line of code before the stall line?

ws.ChartObjects("SUBPlot").Activate

HTH. Dave

 

[Rootero]

I would have never figured that out - thank you! It works now as intended. Below the complete code in case someone needs it in the future. One could also make relatively easily turn it into a general function that hands the ChartObject as a variable to the function.

Function ChangeChartAxisMaxScale(upper)
    Dim pass As String
    pass = "myPassword"

    Dim wasProtected As Boolean
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
        If ws.ProtectionMode Then
            wasProtected = True
            ws.Unprotect Password:=pass
        Else
            wasProtected = False
        End If

    ws.ChartObjects("SUBPlot").Activate
    ws.ChartObjects("SUBPlot").Chart.Axes(xlValue).MaximumScale = upper


    If wasProtected Then ws.Protect Password:=pass, Contents:=True, UserInterfaceOnly:=True, AllowFiltering:=True, DrawingObjects:=True
    If wasProtected Then ws.EnableOutlining = True


End Function

 

[Rootero]

Actually, it doesn't work anymore after restarting the file. When removing the code after the ws.Unprotect Password:=pass loop, then the worksheet remains protected (even if I feed the routine the actual worksheet name). The code does however continue after the unprotect command and then stops as soon as I try to address the Chart in any way without error

It will stop without generating an error after
ws.ChartObjects("SUBPlot").Activate

The next line (ws.ChartObjects("SUBPlot").Chart.Axes(xlValue).MaximumScale = upper) is never reached. If I remove ws.ChartObjects("SUBPlot").Activate, then the line ws.ChartObjects("SUBPlot").Chart.Axes(xlValue).MaximumScale = upper is reached, but not executed.

I am absolutely at a loss what is happening here.

Just in case this is relevant here, the file is protected using upon save:

Worksheets("Dashboard SUB").Protect Password:=pass, UserInterfaceOnly:=True, AllowFiltering:=True