Run a macro when a worksheet is unprotected

[shawthingz]

Hi All,

If possible, I'd like to be able to 'catch' the uprotect event when the correct password is entered to unprotect a specific worksheet & run a macro. I currently working on a fairly 'clunky' solution to run some VBA run from a command button (after the worksheet has been unprotected), but if possible I'd like to have a more elegant solution.

I'm already using the worksheet_change() function to run macros when various cells are updated, but I haven't managed to find anything online (so far) related to this specific question.

Does anyone have any thoughts / answers? Is this even possible?

Thanks

shawthingz

 

[Kenneth Hobson]

Is there a reason to unprotect the sheet(s)? If you use this method, your code will not need to unprotect the sheet(s) to make cell changes.

Private Sub Workbook_Open()
  Dim ws As Worksheet
  For Each ws In Worksheets
    ws.Protect "Ken", UserInterfaceOnly:=True
  Next ws
End Sub

 

[Case_Germany]

Hi,

Is this even possible?

Yes that works, but only with API - and this is a difficult terrain. If you do not necessarily respond directly to protect/unprotect the worksheet, then use the suggestion of Kenneth. Otherwise, I can give you an example.

 

[shawthingz]

We're running under ISO27001 so unfortunately (for me )everything needs to be as 'locked down' as is reasonable possible...

FYI, the workbook moves between various departments as part of this process and only one team within the business is allowed to update this particular worksheet (before passing it on to another), so being able to 'intercept' the "Unprotect" event would be brilliant!

However, if that not possible I'll post the code I eventually come up with that runs from the Command Button (after the sheet is unlocked) in case it helps anyone else out there

shawthingz

 

[shawthingz]

I think my posting "crossed" with Post_Germany's so here's some additional details of the situation I'm in (in case it helps)

The process we have involves a 3rd party & 2 internal departments. Each have a worksheet that only they are allowed to update, so each has its own separate password (which only they know).

The 3rd party updates "sheet1" & emails it to internal dept1.
Dept1 unprotects "sheet2", makes various approvals (where appropriate) then emails it to internal dept2.
Dept2 unlocks "sheet3", makes their approvals & then uploads their approved changes into another system.

I've managed to get absolutely everything sorted out on sheets 1 & 3 (thanks to the great support of this forum & others), but sometimes sheet2 is filled in incorrectly because someone in dept1 decides to make changes to various cells in the "sheet2" that they shouldn't (because *all* cells become unprotected when they enter the unlock password).

Therefore, I'd like to be able to run a macro on the "Unprotect" event so that I can automatically re-lock / re-protect those cells they're *not* supposed to change to help improve their process & help make it more repeatable / reliable.

This may sound like a bit of a 'mad' process (& IMPO it's certainly not ideal), but it's what we currently have, it (usually) works & people appear to be happy using it.

Hope this helps everyone's understanding of what I'm trying to achieve with this.

Thx

shawthingz

 

[mikerickson]

Perhaps this will suit your need.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static HasRun As Boolean

    If Target.Parent.ProtectContents Then
        HasRun = False
    Else
        If Not HasRun Then

            Rem your code
            
            HasRun = True
        End If
    End With
End Sub

 

[Case_Germany]

Hi,

if you want to monitor the protection of a worksheet without making an entry or to change a cell that is only possible via API.

Here is an example:
API - Timer - Protection - Unprotection

Set the worksheet protection, and remove it again - and you see the result.


BUT background knowledge is important when working with API - so first try the other suggestions.

Here is the code from the sample:

In "ThisWorkbook":

Option Explicit
Private Sub Workbook_Open()
    StartT
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopT
End Sub

In a "Module":

Option Explicit
Option Private Module
Private Declare Function KillTimer Lib "user32.dll" _
    (ByVal hWnd As Long, ByVal nIDEvent As Long) As Long
Private Declare Function SetTimer Lib "user32.dll" _
    (ByVal hWnd As Long, ByVal nIDEvent As Long, _
    ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public lngTimer As Long
Sub ProtectCheck(ByVal hWnd As Long, ByVal lngMsg As Long, _
    ByVal lngEla As Long, ByVal lngFunc As Long)
    If lngMsg = &H113 Then
        With Sheet1
            .CommandButton1.BackColor = IIf(Not .ProtectContents, &HFF&, &HC000&)
            .CommandButton1.Caption = IIf(Not .ProtectContents, "Unprotect", "Protect")
        End With
    End If
End Sub
Sub StartT()
    lngTimer = SetTimer(0, 0, 300, AddressOf ProtectCheck)
End Sub
Sub StopT()
    KillTimer 0, lngTimer
End Sub

Does not run in Excel 97.

 

[shawthingz]

Thanks for all the hints & tips folks,

However, given that I'm just a self-taught "hacker", I don't feel confident enough to go down the API route (just yet )

Given that the "Unprotect" event isn't readily available, I've gone with the following "work-around" solution (which seems to work):

To start with, "Sheet2" is password protected & columns A:L (containing sensitive info) are hidden

The User unprotects the sheet (with the appropriate password) then clicks a command button which runs the following code to unprotect the relevant cells:

(Cell F3 contains the number of users which can be 0 - 100)
(Column K (11) is the approval field (e.g. "Y" / "N")


Sub Show_Data()


Sheets("Sheet2").Select

If ActiveSheet.ProtectContents = True And Cells(8, 11).Locked = True Then
answer = MsgBox("This button is disabled as this worksheet is password protected.", _
vbOKOnly + vbCritical, "INVALID ACTION")

ElseIf ActiveSheet.ProtectContents = True And Cells(8, 11).Locked = False Then
answer = MsgBox("This button is currently disabled." & Chr(13) & _
"All User Info is already being displayed.", _
vbOKOnly + vbExclamation)

Else

Columns("E:L").Select
Selection.EntireColumn.Hidden = False

'If Number of Users set to 0 or blank then lock all appropriate cells
If (IsEmpty("f3") Or Cells(3, 6) = 0) Then

'Do nothing - keep all cells locked

'If Number of Users set to 100 then unlock all appropriate cells
ElseIf (Cells(3, 6) = 100) Then

Range("D8:F107").Select
Selection.FormulaHidden = False
Selection.Locked = False

Else
Cell_Range_To_Unprotect = "G8:G" & Cells(3, 6) + 8 - 1
Range(Cell_Range_To_Unprotect).Select
Selection.Locked = False
Selection.FormulaHidden = False

Cell_Range_To_Unprotect = "K8:K" & Cells(3, 6) + 8 - 1
Range(Cell_Range_To_Unprotect).Select
Selection.Locked = False
Selection.FormulaHidden = False


Cell_Range_To_Protect = "E" & Cells(3, 6) + 8 & ":K107"
Range(Cell_Range_To_Protect).Select
Selection.Locked = True
Selection.FormulaHidden = True

End If

'Set the Cursor back to the cell containing the first Approval cell (K*)
Range("K8").Select

'ActiveSheet.Protect Password:="A.Password"

End If

End Sub


Hope this posting makes sense & helps someone out in the future.

shawthingz