Cannot unprotect Sheet from Module

Sorata

New Member
Joined
Dec 28, 2020
Messages
4
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2010
Platform
  1. Windows
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:
VBA Code:
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:
VBA Code:
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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
292
Office Version
  1. 2007
Platform
  1. Windows
Hello Sorrata,
This works for me..
VBA Code:
'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

New Member
Joined
Dec 28, 2020
Messages
4
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2010
Platform
  1. Windows
Hello Sorrata,
This works for me..
VBA Code:
'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

New Member
Joined
Dec 28, 2020
Messages
4
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2010
Platform
  1. Windows
Oh, dear f...:mad:

Found the problem.

I used the subroutine call as:
VBA Code:
.OnAction = "=ClearAll(" & param1 & ", " & param2 & ")"

and not as:
VBA Code:
.OnAction = "'ClearAll " & param1 & ", " & param2 & "'"

After switching, everything worked as I wanted.

🤦‍♂️
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
292
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

OK.
Little change makes big things.
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
714
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 28, 2020
Messages
4
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,237
Messages
5,594,988
Members
413,958
Latest member
nstevox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top