Creating a Macro that will Lock everything besides data-entry cells

[evxret]

Hi all, I had help creating a VB macro that is attached to buttons that will lock or unlock a workbook based on a macro-attached button press. I got the buttons to function the way they need to, the problem lies now that when I press "unprotect", the entire sheet is left vulnerable to formula & other structural changes when users need to add their data entry before pressing the "Protect" button. I want them to be able to save and lock their numbers, and they cannot be changed unless they ask me first. I hope im making sense. Basically I need help editing the lines in the attached code so instead of fully unprotecting the sheet, it unprotects only cells that I want to be able to be edited. Im not sure if this is possible, but I appreciate any and all help with this.

Here is the attached code:

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 > 5 Then
      MsgBox "Password may only be entered 5 times. Application will now close."
        Application.DisplayAlerts = False
        ThisWorkbook.Saved = True
        Application.Visible = False
        Application.Quit
      Exit Sub
    End If
    
    PassWord = InputBox("Enter Password (Accessable by admin only)")
    
Loop Until PassWord = "abc"
    
    If PassWord = "abc" Then
     Sheet1.Unprotect PassWord:="abc"
    End If
    
End Sub

I basically need the part that says "Sheet1.Unprotect PassWord" to be changed to something that will instead keep protection on everything besides data entry cells.

 

[Micron]

the problem lies now that when I press "unprotect", the entire sheet is left vulnerable

So you didn't set any ranges that should be unlocked when the sheet protected and a valid password is entered? AFAIK, anything outside of that range(s) remains protected. It's been a long time since I bothered with protected sheets/ranges, so sorry if that doesn't help. If you think it might, see Protect group on Review tab of ribbon.

 

[evxret]

So you didn't set any ranges that should be unlocked when the sheet protected and a valid password is entered? AFAIK, anything outside of that range(s) remains protected. It's been a long time since I bothered with protected sheets/ranges, so sorry if that doesn't help. If you think it might, see Protect group on Review tab of ribbon.

You actually 100% answered my question!! I didn't realize that if I just set edit ranges, they would remain even when the "Protect" button is pressed, I thought this would all have to be done with VBA because I assumed edit ranges would be overwritten by the "Sheet.Protect" function. But they did not, cheers!

 

[evxret]

So you didn't set any ranges that should be unlocked when the sheet protected and a valid password is entered? AFAIK, anything outside of that range(s) remains protected. It's been a long time since I bothered with protected sheets/ranges, so sorry if that doesn't help. If you think it might, see Protect group on Review tab of ribbon.

OK; I retract my statement I just identified the issue I was expecting. I want my edit ranges to be active when the "Unprotect" status is set, but I don't want anyone to be able to edit ANYTHING when i select the protect button and run the "ProtectSheet" macro. Is there a way to write into my code the ability to set edit ranges when "Unprotect" is selected, but not when "Protect" is?

 

[Micron]

Sorry, I know I can be too analytical most of the time and this isn't my area of expertise, but I still can't figure out if
- you want to lock the whole sheet when protected and unlock the whole sheet when not, or
- you want one range to be unprotected upon valid password entry and another range to still be locked

Does this help?

Excel-VBA : Range - Lock/Unlock Specific Cells

There are two different cases you might deal with, Lock only few specific cells and rest of the cells in the work sheet are not locked(means you can edit them)Lock the Enitre worksheet except few cells Lock only few specific …

excel-macro.tutorialhorizon.com

 

[evxret]

Sorry, I know I can be too analytical most of the time and this isn't my area of expertise, but I still can't figure out if
- you want to lock the whole sheet when protected and unlock the whole sheet when not, or
- you want one range to be unprotected upon valid password entry and another range to still be locked

Does this help?

Excel-VBA : Range - Lock/Unlock Specific Cells

There are two different cases you might deal with, Lock only few specific cells and rest of the cells in the work sheet are not locked(means you can edit them)Lock the Enitre worksheet except few cells Lock only few specific …

excel-macro.tutorialhorizon.com

That link is along the lines of what i need. Ill try and break it down to make it more understandable.

I have a sheet which contains a form that must be filled in by a certain team and passed on to another team.
When the first team finishes their data entry, I don't want anyone to be able to edit these numbers anymore, therefore the "Lock-All" button.
While the first team has access to the editable form, I still want to be able to protect formulas and sheet structure changes.
I currently have a solution to lock and unlock the sheet with a button, but when I unlock the sheet (using original code posted), every cell is available for editing.

Basically, in the original code, in the part that unlocks the sheet, (bottom part of code) I need that to be changed to still protected, but with edit ranges. Does this make sense?
I tried what I'm trying to accomplish below, but it's returning a syntax error, but I think I'm close.

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.Protect PassWord:="abc"
     Sheet1.Protection.AllowEditRanges.Add _
     Title:="EditableRange", _
     Range:=Range("A1:A5"), _

    End If
    
End Sub

 

[Micron]

As a matter of course, you should always state what line causes the error. Usually I also state the error number and message (can't go wrong with all of that) but in this case I think the only thing missing is the line that causes it. Without that it's often too ambiguous. That error might be all about scope of a variable/sheet/range but not really possible to say without knowing what the offending line is. Once that error is dealt with, that code can probably be condensed.

 

[evxret]

As a matter of course, you should always state what line causes the error. Usually I also state the error number and message (can't go wrong with all of that) but in this case I think the only thing missing is the line that causes it. Without that it's often too ambiguous. That error might be all about scope of a variable/sheet/range but not really possible to say without knowing what the offending line is. Once that error is dealt with, that code can probably be condensed.

It is giving "Run-Time Error '1004': Application-defined or object-defined error."
Here is a screenshot of my code:

 

[Micron]

This doesn't error for me the first time so I'm guessing you've run it at least once already:
Sheet1.Protection.AllowEditRanges.Add Title:="EditableRange", Range:=Range("A1:A5"), Password:=""
Next time through, it does. Assuming that's because the range already exists and you can't add again, try

With Sheet1
  .Select
  .Sheet1.Protection.AllowEditRanges("EditableRange").Delete
  .Protection.AllowEditRanges.Add Title:="EditableRange", Range:=Range("A1:A5"), Password:=""
End With

or you can test if it exists and do something other than delete and recreate:

If Not Sheet1.Protection.AllowEditRanges("EditableRange") Is Nothing Then MsgBox "exists"

 

[evxret]

This doesn't error for me the first time so I'm guessing you've run it at least once already:
Sheet1.Protection.AllowEditRanges.Add Title:="EditableRange", Range:=Range("A1:A5"), Password:=""
Next time through, it does. Assuming that's because the range already exists and you can't add again, try

With Sheet1
  .Select
  .Sheet1.Protection.AllowEditRanges("EditableRange").Delete
  .Protection.AllowEditRanges.Add Title:="EditableRange", Range:=Range("A1:A5"), Password:=""
End With

or you can test if it exists and do something other than delete and recreate:

If Not Sheet1.Protection.AllowEditRanges("EditableRange") Is Nothing Then MsgBox "exists"

Attempted adding your lines of code into my project;

With Sheet1
  .Select
  .Sheet1.Protection.AllowEditRanges("EditableRange").Delete
  .Protection.AllowEditRanges.Add Title:="EditableRange", Range:=Range("A1:A5"), Password:=""
End With

These lines here^
I was thrown "Compile Error: Method or Data Member Not Found"
With This line highlighted:

.Select
  .Sheet1.Protection.AllowEditRanges("EditableRange").Delete
  .Protection.AllowEditRanges.Add Title:="EditableRange", Range:=Range("A1:A5"), PassWord:=""

I think it has to do something with the way "Sheet1" is referred to in the code. I'm a VBA amateur, so any help is extremely appreciated.

Did I input your code into the sheet correctly? I will paste how I went about that below.

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

Do
    i = i + 1
    If i > 3 Then
      MsgBox "Only 3 Password Tries Allowed. Application will now save & close."
        Application.DisplayAlerts = False
        ThisWorkbook.Saved = True
        Application.Visible = False
        Application.Quit
      Exit Sub
    End If
   
    PassWord = InputBox("Enter Password (Accessable by admin only)")
   
Loop Until PassWord = "abc"
   
    If PassWord = "abc" Then
     With Sheet1
  .Select
  .Sheet1.Protection.AllowEditRanges("EditableRange").Delete
  .Protection.AllowEditRanges.Add Title:="EditableRange", Range:=Range("A1:A5"), PassWord:=""
End With
    End If

End Sub