If worksheet is protected display msgbox and complete an action, else display a different msgbox and exit sub

[rdoulaghsingh]

I wrote the code below for a lock and unlock process. First one (Lock) is supposed to state if the worksheet (Change Record) is already locked then show that it's already protected with a timed message box....else (if false) then lock the worksheet.

The second (Unlock) is doing the opposite. If the worksheet is locked then display a message box for confirmation and if yes is selected then unlock the worksheet....else, if false, meaning the worksheet is unlocked already, then display a msgbox saying it's already unlocked.

I've tested this a few different ways, but the msgbox seems to popup incorrectly whether it's locked or unlocked.

Please help!

Sub CR_Lock()
Dim msg1Counter As Integer

If Worksheets("Change Record").Protect = True Then

msg1Counter = CreateObject("WScript.Shell").PopUp(ActiveSheet.Name & " Worksheet is already protected.", 1, "Info.")

Else

msg1Counter = CreateObject("WScript.Shell").PopUp("Hang tight!" & vbCrLf & vbCrLf & "Initiating protection for " & ActiveSheet.Name & " Worksheet.", 2, "Protection in progress")

Worksheets("Change Record").Protect

    End If

End Sub
Sub CR_Unlock()
Dim msg2Counter As Integer

If Worksheets("Change Record").Protect = True Then

answer = MsgBox("Unlock " & ActiveSheet.Name & " Worksheet?", vbQuestion + vbYesNo + vbDefaultButton2, "Unlock Worksheet confirmation")

    If answer = vbYes Then
    
Application.ScreenUpdating = False

Worksheets("Change Record").Unprotect

    End If

Else

msg2Counter = CreateObject("WScript.Shell").PopUp(ActiveSheet.Name & " protection is currently turned off.", 1, "Info.")
    
    End If

Application.ScreenUpdating = True

End Sub

 

[Fluff]

It needs to be

If Worksheets("Change Record").ProtectContents = True Then

your code is actually protecting the sheet

 

[rdoulaghsingh]

It needs to be

If Worksheets("Change Record").ProtectContents = True Then

your code is actually protecting the sheet

I literally just found it on the internet and tested it and it worked. Thanks for your quick reply.

 

[Fluff]

You're welcome & thanks for the feedback.