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

evxret

New Member
Joined
Apr 8, 2022
Messages
38
Office Version
  1. 365
Platform
  1. Windows
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:
VBA 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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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?
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.

VBA 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 > 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
 
Upvote 0
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.
 
Upvote 0
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:
1649457782910.png
 
Upvote 0
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

VBA Code:
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"
 
Upvote 0
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

VBA Code:
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;
VBA Code:
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:
VBA Code:
.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.
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top