Cannot unprotect Sheet from Module

[Sorata]

Hello!

I have a strange issue.
On my worksheet, I initiate macros with added right-click buttons.
The macro first unlocks the sheet, changes the cells' (carried via parameter) content (clears), and locks it again.

During the subroutine's step-by-step debug everything works fine.
However, when initiated with a right-click, the sheet remains protected, and no error message at Unprotect (only on content change).

Code on Sheet1:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim MySubMenu As CommandBarControl
    Dim cmdButton As CommandBarButton
    Dim ContextMenu As CommandBar
    Dim ctrl As CommandBarControl

    'Set ContextMenu to the Cell menu
    Set ContextMenu = Application.CommandBars("Cell")

    'Delete custom controls
    For Each ctrl In ContextMenu.Controls
        ctrl.Visible = False
        If ctrl.ID = 22 Or ctrl.ID = 21437 Then
            ctrl.Delete
        ElseIf ctrl.Tag = "CustomScheduleMenu" Then
            ctrl.Delete
        End If
        Err.Clear
    Next ctrl

    If (ActiveSheet.Name = "Sheet1") And (Not (Intersect(Target, Range("c5:i62")) Is Nothing)) Then
        'Clear all button
        Set cmdButton = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, before:=1, Temporary:=True)

        With cmdButton
                .OnAction = "=ClearAll(""" & Target.Address & """)"
                .Caption = "Clear cell content"
                .Style = msoButtonCaption
                .Tag = "CustomScheduleMenu"
        End With
    End If
End Sub

Code in Module1:

Public Sub ClearAll(ByVal cTarget As String)
    Dim Target As Range
    
    For Each Target In Sheets("Sheet1").Range(cTarget)
        If (Target.Column >= 3 And Target.Column <= 9) And (Target.Row >= 5 And Target.Row <= 62) Then
            Sheets("Sheet1").Unprotect Password:="whatever"

            Target.Value = ""

            Sheets("Sheet1").Protect Password:="whatever"
        End If
    Next
End Sub

My only guess is that I cannot initiate sheet protect-unprotect from Module1.

 

[EXCEL MAX]

Hello Sorrata,
This works for me..

'this goes to the workbook Open event
Private Sub Workbook_Open()

    Call InitiateVariable

End Sub

'this goes to the "Sheet1" module
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    Dim MySubMenu As CommandBarControl
    Dim cmdButton As CommandBarButton
    Dim ContextMenu As CommandBar
    Dim ctrl As CommandBarControl
   
    cTarget = Target.Address
'Set ContextMenu to the Cell menu
    Set ContextMenu = Application.CommandBars("Cell")

'Delete custom controls
    For Each ctrl In ContextMenu.Controls
        ctrl.Visible = False
        If ctrl.ID = 22 Or ctrl.ID = 21437 Then
            ctrl.Delete
        ElseIf ctrl.Tag = "CustomScheduleMenu" Then
            ctrl.Delete
        End If
        Err.Clear
    Next ctrl

    If (ActiveSheet.name = "Sheet1") And (Not (Intersect(Target, Range("c5:i62")) Is Nothing)) Then
'Clear all button
        Set cmdButton = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, before:=1, Temporary:=True)
        With cmdButton
                .OnAction = "ClearAll"
                .Caption = "Clear cell content"
                .Style = msoButtonCaption
                .Tag = "CustomScheduleMenu"
        End With
    End If
   
End Sub




'this goes to the module

Public cTarget
Sub InitiateVariable()

End Sub


Sub ClearAll()

    Dim Target1 As Range
    For Each Target1 In Sheets("Sheet1").Range(cTarget)
        If (Target1.Column >= 3 And Target1.Column <= 9) And (Target1.Row >= 5 And Target1.Row <= 62) Then
            Application.ActiveWorkbook.Sheets("Sheet1").Unprotect "whatever"
            MsgBox "Inside Protection"
            Target1.Value = "BLA_BLA"
            Sheets("Sheet1").Protect Password:="whatever"
        End If
    Next

End Sub

 

[Sorata]

Hello Sorrata,
This works for me..

'this goes to the workbook Open event
Private Sub Workbook_Open()

    Call InitiateVariable

End Sub

'this goes to the "Sheet1" module
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    Dim MySubMenu As CommandBarControl
    Dim cmdButton As CommandBarButton
    Dim ContextMenu As CommandBar
    Dim ctrl As CommandBarControl
  
    cTarget = Target.Address
'Set ContextMenu to the Cell menu
    Set ContextMenu = Application.CommandBars("Cell")

'Delete custom controls
    For Each ctrl In ContextMenu.Controls
        ctrl.Visible = False
        If ctrl.ID = 22 Or ctrl.ID = 21437 Then
            ctrl.Delete
        ElseIf ctrl.Tag = "CustomScheduleMenu" Then
            ctrl.Delete
        End If
        Err.Clear
    Next ctrl

    If (ActiveSheet.name = "Sheet1") And (Not (Intersect(Target, Range("c5:i62")) Is Nothing)) Then
'Clear all button
        Set cmdButton = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, before:=1, Temporary:=True)
        With cmdButton
                .OnAction = "ClearAll"
                .Caption = "Clear cell content"
                .Style = msoButtonCaption
                .Tag = "CustomScheduleMenu"
        End With
    End If
  
End Sub




'this goes to the module

Public cTarget
Sub InitiateVariable()

End Sub


Sub ClearAll()

    Dim Target1 As Range
    For Each Target1 In Sheets("Sheet1").Range(cTarget)
        If (Target1.Column >= 3 And Target1.Column <= 9) And (Target1.Row >= 5 And Target1.Row <= 62) Then
            Application.ActiveWorkbook.Sheets("Sheet1").Unprotect "whatever"
            MsgBox "Inside Protection"
            Target1.Value = "BLA_BLA"
            Sheets("Sheet1").Protect Password:="whatever"
        End If
    Next

End Sub

This really works.
But my initial issue return as soon as I add a parameter to the subroutine.

 

[Sorata]

Oh, dear f...

Found the problem.

I used the subroutine call as:

.OnAction = "=ClearAll(" & param1 & ", " & param2 & ")"

and not as:

.OnAction = "'ClearAll " & param1 & ", " & param2 & "'"

After switching, everything worked as I wanted.

?‍

 

[EXCEL MAX]

OK.
Little change makes big things.

 

[6StringJazzer]

During the subroutine's step-by-step debug everything works fine.
However, when initiated with a right-click[....]

The lesson here is that when debugging a macro, start in the earliest entry point. It sounds like you went right to ClearAll to step through the code, because you assumed what the problem was before doing any debugging.

 

[Sorata]

The lesson here is that when debugging a macro, start in the earliest entry point. It sounds like you went right to ClearAll to step through the code, because you assumed what the problem was before doing any debugging.

You are right.

I assumed the issue was at ClearAll because previously I successfully used the code in the "=SubRoutine(..)" form.
Although then there were no sheet protection.