Create a Macro button to lock spreadsheet after all data entry is complete

[evxret]

Hi all,

Looking for some assistance here as I've scrolled through google + these forums and have found few answers on very old threads that didn't work.

I created a spreadsheet with 2 different worksheets, they correspond to a team in my company. They will be tracking expense and revenue.
In my case, I want to be able to give this spreadsheet to our sales team, they input "Assumed" profit margin, and then when they pass the sheet along to the next team, they will input actual profit margin, and then there will be an overview to compare the 2. My problem being, I want to create a button on each page to "lock" the sheet down after they complete their edits. I don't want previous teams to be able to go back and change assumed profits, etc.

I hope what Im asking for makes sense, I basically need VBA that can be attached to a button that will pretty much make the sheet unusable after the button is pressed (unless I manually go back and unlock it).

Heres an example of the button I made in the spreadsheet with the description of what it does if my post was confusing.

 

[Logit]

Here is a simple means of protecting / unprotecting a sheet. In this case, the code is referencing Sheet1 :

Option Explicit

Sub ProtectSheet()
    Sheet1.Protect Password:="abc"
    
End Sub

Sub UnprotectSheet()
    Sheet1.Unprotect Password:="abc"
End Sub

 

[evxret]

Here is a simple means of protecting / unprotecting a sheet. In this case, the code is referencing Sheet1 :

Option Explicit

Sub ProtectSheet()
    Sheet1.Protect Password:="abc"
   
End Sub

Sub UnprotectSheet()
    Sheet1.Unprotect Password:="abc"
End Sub

Thank you for the prompt reply! I inputted this code into a module, changed sheet1 to the name of the sheet im using, however when I press the button this macro is assigned to, it gives me "Compile Error: Variable Not Defined" It's highlighting the name of my spreadsheet in the code, so Im not sure what I'm doing wrong?! All I did was change "Sheet1" to the name of my sheet.

Heres what I changed it to, let me know if I did something wrong. Thanks again, it is so much appreciated!

Option Explicit

Sub ProtectSheet()
    Pre_Sales_Form.Protect Password:="TestPW"
    
End Sub

Sub UnprotectSheet()
    Pre_Sales_Form.UnProtect Password:="TestPW"
End Sub

 

[Logit]

Strange ... it works here. Try this variation :

Option Explicit

Sub LockSpecific()

If ActiveSheet.ProtectContents = True Then
    ActiveSheet.Unprotect
    MsgBox "Sheet Unlocked"
Else
    ActiveSheet.Protect
End If

End Sub

 

[evxret]

Strange ... it works here. Try this variation :

Option Explicit

Sub LockSpecific()

If ActiveSheet.ProtectContents = True Then
    ActiveSheet.Unprotect
    MsgBox "Sheet Unlocked"
Else
    ActiveSheet.Protect
End If

End Sub

This variation worked! Thank you SOOO much! It does exactly what I need it to; only question being, is it possible to add a password to this code as well for when you click the button again to unlock? I love the functionality of this but anyone can essentially re-unlock the sheet after the lock is pressed.

 

[Logit]

Let's give it another twist ... give the user three tries then error out if all are wrong. There is a line of code in the ThisWorkbook module that auto protects
sheet1 when the workbook is closed:

Option Explicit


Sub ProtectSheet()
    Sheet1.Protect PassWord:="abc"
End Sub

Sub UnprotectSheet()
Dim PassWord As String, i As Integer
 
  i = 0

Do
    i = i + 1
    If i > 3 Then
      MsgBox "Sorry, Only three tries"
        Application.DisplayAlerts = False
        ThisWorkbook.Saved = True
        Application.Visible = False
        Application.Quit
      Exit Sub
    End If
    
    PassWord = InputBox("Enter Password")
    
Loop Until PassWord = "abc"
    
    If PassWord = "abc" Then
     Sheet1.Unprotect PassWord:="abc"
    End If
    
End Sub

Download workbook : Password Protect Worksheet 3 Tries.xlsm

 

[evxret]

Let's give it another twist ... give the user three tries then error out if all are wrong. There is a line of code in the ThisWorkbook module that auto protects
sheet1 when the workbook is closed:

Option Explicit


Sub ProtectSheet()
    Sheet1.Protect PassWord:="abc"
End Sub

Sub UnprotectSheet()
Dim PassWord As String, i As Integer
 
  i = 0

Do
    i = i + 1
    If i > 3 Then
      MsgBox "Sorry, Only three tries"
        Application.DisplayAlerts = False
        ThisWorkbook.Saved = True
        Application.Visible = False
        Application.Quit
      Exit Sub
    End If
   
    PassWord = InputBox("Enter Password")
   
Loop Until PassWord = "abc"
   
    If PassWord = "abc" Then
     Sheet1.Unprotect PassWord:="abc"
    End If
   
End Sub

Download workbook : Password Protect Worksheet 3 Tries.xlsm

Thank you SOOO much, sending love from Las Vegas! This did exactly what we needed over here. I cannot express how appreciative I am right now.

 

[Logit]

Glad it works for you. Cheers.

 

[evxret]

Glad it works for you. Cheers.

Sorry for the bombardment of questions. If this is too big of a deal to complete, you dont have to reply. Lol. What If I want protections set even when "unprotect" button is pressed? For example, every cell is locked for editing besides A5:A10, when the "Lock" button is pressed, all cells remain locked, including A5:A10 now. Does this make sense? I worry that when It's left unlocked it will leave too much vulnerability to formulas and sheet structure. So it'll almost be one level of protection for "unlocked" and then the entire sheet protected for "locked", does this make sense? I can elaborate if needed.

Attached an imagine trying to explain what im trying to accomplish.