How to hook into sheet protection violation event

[AnnaHansen]

I have a Table with formulae I do not want my users to edit, but I still want the table to be expandable. However, with locking set correctly, sheet protection turned on and insert new rows allowed, the "The cell or chart you are trying to protect is on a protected sheet....." alert still pops up. (Note, the new row is actually inserted despite the alert display!)

I can't deploy this without suppressing that alert. There must be an event being triggered, which I could hook into and write some code to suppress the alert, but I can't find any documentation it (looked into On Error Go To and Err.Number but could not find the value for this alert).

Any one know?

 

[AnnaHansen]

Hi Peter,

ok - So did the code work for you as expected in the end ? or do you, like me, don't experience the popup alert issue the OP is describing so you cannot tell if the code actually works or not ?

Regards.

No, there are many ways to insert a row besides a double click, so that will not solve my problem. I could put something on the selection change event to modify the sheet protection at every selection change based on the active cell, but I do not want something firing that often. Only when needed. I am OP, and if you are not getting the alert that I described then you did not fully understand the question. This is a known problem with sheet protection on sheets with tables when row insertion is allowed in the protection settings.

 

[Peter_SSs]

there are many ways to insert a row besides a double click, so that will not solve my problem

But if you follow the steps I outlined in post 3, your users will not be able to use any of those other methods of row insertion. Double-click would be the only way.

I am OP, and if you are not getting the alert that I described then you did not fully understand the question.

I am getting the alert (as described in post 11) and so is Jaafar (as stated in post 12) so I believe we both do understand your question.

Anyway, if neither suggestion is working for you then we'll have to hope a better suggestion comes along or your users will have to put up with the alert.

 

[AnnaHansen]

But if you follow the steps I outlined in post 3, your users will not be able to use any of those other methods of row insertion. Double-click would be the only way.


I am getting the alert (as described in post 11) and so is Jaafar (as stated in post 12) so I believe we both do understand your question.

Anyway, if neither suggestion is working for you then we'll have to hope a better suggestion comes along or your users will have to put up with the alert.

Yeah, it's being published out to a group of users that I don't have any way of providing direction to, so disabling a bunch of things isn't an option and neither is putting up with an erroneous alert. There's always a way to hook into a message like that you just need to know how the software instantiates it. That is what I'm looking for on this thread, not workarounds. But I appreciate that you tried to help, it's entirely possible one of your suggestions may have worked for different circumstances.

 

[Peter_SSs]

There's always a way to hook into a message like that you just need to know how the software instantiates it.

What went wrong for you when you tried Jaafar's suggestion? Did you get the same problem I described in post 5? Or something else went wrong?

 

[AnnaHansen]

So my suggestion in post 3 is not an accepatble alternative?

No because I don't want to hook into the insert event. I want to hook into the event that generates the alert (when the protection violation occurs)

 

[Peter_SSs]

OK, when you find the solution, hopefully you'll post it back here. Good luck!

 

[Jaafar Tribak]

No because I don't want to hook into the insert event. I want to hook into the event that generates the alert (when the protection violation occurs)

There is no such event I am afraid. Only workarounds ... If you can't use Peter's workaround because you don't have any way of providing directions to users and want the process to happen automatically then I have given you just that in Post#13

EDIT1:
". I could put something on the selection change event to modify the sheet protection at every selection change based on the active cell, but I do not want something firing that often. Only when needed. "

The code in Post#13 doesn't fire unless you right click the row headers when proceeding to insert the rows... so there is hardly any impact on performance.

EDIT2:
@ Peter_SSs
My last code in Post#13 no longer generates the error you experienced in earlier posts.

 

[Peter_SSs]

@ Peter_SSs
My last code in Post#13 no longer generates the error you experienced in earlier posts.

Yes, you are right. That sample workbook no longer errors for me. I note that as downloaded the sample file does not have the sheet protected but when I did protect the sheet the code did allow row insertion.

However, I think it still may be not quite what Anna wants. I think the main point of the protection is to preserve any formulas in the table. When I insert a row into the table on your sheet the formulas in column B do not auto-fill into the new row. Also wondering why the file is in 97-2003 format when you, Anna and I are all using later versions? Are those two things related?

it's being published out to a group of users that I don't have any way of providing direction to

Anna, just thinking on this again. If you have no way to direct users to use, for example, my double-click work-around, how are you going to instruct them that they must enable macros in your workbook for any 'hook' to work?

 

[Jaafar Tribak]

Yes, you are right. That sample workbook no longer errors for me. I note that as downloaded the sample file does not have the sheet protected but when I did protect the sheet the code did allow row insertion.

However, I think it still may be not quite what Anna wants. I think the main point of the protection is to preserve any formulas in the table. When I insert a row into the table on your sheet the formulas in column B do not auto-fill into the new row. Also wondering why the file is in 97-2003 format when you, Anna and I are all using later versions? Are those two things related?

Hi Peter,

You are right. The formulas do not autofill even if the file format is xlsm ... I have noticed that this is due to the sheet being protected. If you remove the sheet protection, the formulas autofill as expected.

The following code update should work . It will suppress the annoying sheet protection alert while still allowing the user to insert rows as well as preserve any formulas in the table.

Workbook demo update


1- In a Standard Module:

Option Explicit
   
#If VBA7 Then
    Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, ByVal ncode As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As LongPtr) As Long
    Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As LongPtr, ByVal hmod As LongPtr, ByVal dwThreadId As Long) As LongPtr
    Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private lhHook As LongPtr
#Else
    Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare Function CallNextHookEx Lib "user32" (ByVal hHook As Long, ByVal ncode As Long, ByVal wParam As Long, lParam As Any) As Long
    Private Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As Long) As Long
    Private Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As Long, ByVal hmod As Long, ByVal dwThreadId As Long) As Long
    Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private lhHook As Long
#End If

Private Const WH_CBT = 5&
Private Const HCBT_CREATE = 3&


Public Property Let SuppressSheetProtectionAlert(ByVal Suppress As Boolean)
    If Suppress Then
        Application.CommandBars("row").Controls(GetInsertMenuIndex).OnAction = "SetHook"
    Else
        Application.CommandBars("Row").Reset
    End If
End Property

Private Sub SetHook()
    Call SetCBTHook
    Application.CommandBars("Row").Reset
    Application.OnTime Now, "ExecuteInsertRow"
End Sub

Private Sub ExecuteInsertRow()
    ActiveSheet.Unprotect
    Application.CommandBars("row").Controls(GetInsertMenuIndex).Execute
    Call RemoveCBTHook
    Application.CommandBars("row").Controls(GetInsertMenuIndex).OnAction = "SetHook"
    ActiveSheet.Protect
End Sub

Private Function GetInsertMenuIndex() As Long
    Dim oCtrl As CommandBarControl
 
    For Each oCtrl In Application.CommandBars("row").Controls
        If oCtrl.Caption = "&Insert" Or oCtrl.Caption = "&Rows" Then
            If oCtrl.Caption = "&Rows" Then oCtrl.Caption = "&Insert"
            GetInsertMenuIndex = oCtrl.Index: Exit Function
        End If
    Next
End Function


Private Sub SetCBTHook()
    UnhookWindowsHookEx lhHook
    lhHook = SetWindowsHookEx(WH_CBT, AddressOf HookProc, 0, GetCurrentThreadId)
End Sub

Private Sub RemoveCBTHook()
    UnhookWindowsHookEx lhHook
End Sub


#If VBA7 Then
    Private Function HookProc(ByVal idHook As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
#Else
    Private Function HookProc(ByVal idHook As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
#End If

    Dim sBuffer As String * 256, lRet As Long

    If idHook = HCBT_CREATE Then
        sBuffer = Space(256)
        lRet = GetClassName(wParam, sBuffer, 256)
        If Left(sBuffer, lRet) = "#32770" Or Left(sBuffer, lRet) = "MsoCommandBarPopup" Then
            Debug.Print "Sheet Protection Alert Aborted !"
            HookProc = -1
            Exit Function
        End If
    End If

    HookProc = CallNextHookEx(lhHook, idHook, ByVal wParam, ByVal lParam)

End Function


Public Sub CheckBox_Click()
    If Sheet1.CheckBoxes(Application.Caller).Value = xlOn Then
        SuppressSheetProtectionAlert = True
    Else
        SuppressSheetProtectionAlert = False
    End If
End Sub

Public Sub TickCheckbox(ByVal Tick As Boolean)
    Sheet1.CheckBoxes(1).Value = IIf(Tick, xlOn, xlOff)
End Sub

2- In the ThisWorkbook Module:

Option Explicit

Private Sub Workbook_Activate()
    If ActiveSheet Is Sheet1 Then  '<== Change target Sheet as required !
        Call TickCheckbox(True)
        SuppressSheetProtectionAlert = True
    End If
End Sub

Private Sub Workbook_Deactivate()
    Call TickCheckbox(False)
    SuppressSheetProtectionAlert = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh Is Sheet1 Then  '<== Change target Sheet as required !
        Call TickCheckbox(True)
        SuppressSheetProtectionAlert = True
    End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Call TickCheckbox(False)
    SuppressSheetProtectionAlert = False
End Sub