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

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
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!


VBA Code:
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,095
Office Version
  1. 365
Platform
  1. Windows
It needs to be
VBA Code:
If Worksheets("Change Record").ProtectContents = True Then
your code is actually protecting the sheet
 
Solution

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
It needs to be
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,095
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,116
Messages
5,768,211
Members
425,459
Latest member
Danniey

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