VBA to protect sheet in excel [Closed]

Report
-
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
-
Hello,

I need any help if some one can help me.
I make an overtime sheet in excel that allow the employee to fill in the date and time only and it automatically make the calculations by formulas, but I fear that some employee may delete or manipulate with the formulas, so I used the option "Allow to Edit range" under (Tools/Protection/Alow Edit Range) and protected the sheet, but at the end of the month the employee should submitted that sheet and to be protected and cannot be able to make any changes retroactively, so I created command button in order to let the employee to click to protect all the sheet and to print, but it didn't work perfectly, I expected to protect all the sheet but it makes the same job as protect except that rang ("Allow to Edit range").
So I taught if there is a code / VBA that by clicking the command button will remove or deactivate the option ("Allow to Edit range") and then to protect the whole sheet.

Is there some body can tell me the code that can deactivate the option ("Allow to Edit range") and then to protect the whole sheet.
Thanks in advance.

3 replies

Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
461
Hi Issa,

Select a cell and press Ctrl+1 (or right-click properties) goto the right most tab called protection. Here you have to options: Block and hide. Both do nothing untill the sheet is protected.

Make up your sheet so that the cells which should not be altered are blocked and if you don't want others to see your formula or other content use hide as well.
As default all cells are blocked, so select the cells others will put content in and unblock them.

Then use these codes to either protect or unprotect your sheet:
Sheets("Sheet1").Protect password:="YourPassword"

Sheets("Sheet1").Unprotect password:="YourPassword"

If you don't want to use passwords, then leave that part out.

Can you work with this?

Best regards,
Trowa
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
I hate these auto fitlers. OK now attempt #2
with index,
ActiveSheet.Protection.AllowEditRanges(1).Delete

with named rules,

ActiveSheet.Protection.AllowEditRanges("namehere").Delete
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
461
OK Issa,

So you have a protected sheet with a specified range (Allow to edit range) for users to enter/alter data.
Now you want to hit the button so that the entire sheet is protected and users can not enter/alter anything.
Is this correct?

Use this code:
Sub RemoveEditRange()
Sheets("Sheet1").Unprotect
ActiveSheet.Protection.AllowEditRanges(1).Delete
Sheets("Sheet1").Protect
End Sub

Now for users to use the specified range again, use this code:
Sub ActivateEditRange()
Sheets("Sheet1").Unprotect
ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Range("A1:A10")
Sheets("Sheet1").Protect
End Sub

Is this better?

Best regards,
Trowa
Dear Trowad,
Thanks a lot, but I don't know how to add it to the following code that I did:

Private Sub CommandButton2_Click()
Const PW As String = "Secret" 'to change the PW
With Me
If .CommandButton2.Caption = "Protect Sheet" Then
ActiveSheet.Protect PW
.CommandButton2.Caption = "Unprotect Sheet"
Else: .CommandButton2.Caption = "Protect Sheet"
ActiveSheet.Unprotect

End If
End With
End Sub

So this code make the protection but keep those ranges unprotected, so I need to add some code to remove first the allowEditrange then to protect the sheet within the same command button.

Thank you very much in advance.
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
461
Let's see if I get now Issa,

For example you have a range A1:A10 you want users to input data and nowhere else.
On the same sheet you have a button reading: Protect sheet.
When hitting that button all cells will be blocked so no data can be altered.
The button's name will be changed to Unprotect sheet.
When hitting the button again a password will be asked.
If password is correct then range A1:A10 will be unblocked.
Users can now alter the data in A1:A10 again but nowhere else.
The button's will also be renamed to Protect sheet.
etc...

To do the above the following code works for me:
Private Sub CommandButton2_Click()
Const PW As String = "Secret" 'to change the PW
If CommandButton2.Caption = "Protect Sheet" Then
ActiveSheet.Unprotect PW
ActiveSheet.Protection.AllowEditRanges(1).Delete
ActiveSheet.Protect PW
CommandButton2.Caption = "Unprotect Sheet"
Else: CommandButton2.Caption = "Unprotect Sheet"
ActiveSheet.Unprotect
CommandButton2.Caption = "Protect Sheet"
ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Range("A1:A10")
ActiveSheet.Protect PW
End If
End Sub

Best regards,
Trowa
Dear Trowa,

Yes it worked, thank you very much, this is what I need exactly.
Thanks again.
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
118
Hi TrowaD,

I want to user edit a range in the protected worksheet and for this I used the code
ActiveSheet.Unprotect Password:=PW

ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Range("A1:E500")
ActiveSheet.Protect


but it shows run time error '1004':

Apllication defined or object defined error.

Please help me out.

Regards,
Rahul
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
461
Hi Rahul,

Besides the error message everything seems to be working.
Allow edit ranges is applied and the sheet in protected.

Adding the line:
On Error Resume Next
will bypas the error.

If you don't like the thought of bypassing on error consider this code:
Sub test()
ActiveSheet.Unprotect
ActiveSheet.Range("A1:E500").Locked = False
ActiveSheet.Protect
End Sub

Do you consider this to be a solution?

Best regards,
Trowa
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
118
Hi Trowa D,

Thanks for replying

I already tried this and this is working fine.

Now I am facing a another problem and the problem is if I send this sheet to someone else and macros are disable in his system then he is able to change the protected cells also.

Hope you help me out.
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
461
Hi Rahul,

I have been wondering about that myself. I created a file which is protected by password. When password is false then Excel will close, but when macro's are disabled the password prompt doesn't appear.

When searching the web I found a site which has a "clunky but effective workaround". It's not very clear to me (I can't open the provided zip file), but maybe you have some benefit from it:
https://www.ozgrid.com/forum/index.php?thread/16599-sorting-selection/

Maybe a new topic will have some others take a look at it.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!