VBA to protect sheet in excel

Closed

issa - Mar 30, 2011 at 01:40 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 5, 2012 at 09:01 AM

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.

VBA to protect sheet in excel
Mark sheet in excel - Guide
Number to words in excel formula without vba - Guide
How to open excel sheet in notepad++ - Guide
How to screenshot excel sheet - Guide
Google sheet right to left - Guide

3 responses

Answer 1 / 3

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Mar 31, 2011 at 10:25 AM

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

issa
Apr 1, 2011 at 01:27 AM

Dear Trowa,

First of all thank you to your reply and assistance.
Unfortunately this is not what I want. I already gave the end-user the sheet to fill in some specified cells with already protected sheet, I mean I used the option Allow to Edit range" under (Tools/Protection/Allow Edit Range) and then I protected the sheet from tools ...etc, so what I need now is a Command button when clicked to Protect all the sheet including that specified range to be edited which means to protect also the range of cells that already were unprotected.
Therefore in order to do that, I think we need VBA in that command button to deactivate the first option "Allow to Edit range" under (Tools/Protection/Allow Edit Range) and then to protected the whole sheet.

Thanks in advance for any assistance.

BR,

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 1, 2011 at 09:36 AM

If you know the name of th protection rule, then one way is this

ActiveSheet.Protection.AllowEditRanges("LiveLongAndProsper").Delete

other way is to use index
ActiveSheet.Protection.AllowEditRanges(1).Delete

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 1, 2011 at 09:39 AM

I hate these auto fitlers. OK now attempt #2
with index,
ActiveSheet.Protection.AllowEditRanges(1).Delete

with named rules,

ActiveSheet.Protection.AllowEditRanges("namehere").Delete

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 1, 2011 at 09:51 AM

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

issa
Apr 4, 2011 at 08:07 AM

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.

Answer 2 / 3

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Feb 21, 2012 at 01:41 AM

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

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Feb 23, 2012 at 10:04 AM

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

Answer 3 / 3

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Feb 29, 2012 at 12:52 AM

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.

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Mar 5, 2012 at 09:01 AM

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

Similar discussions

Facebook says I have been trying too often

Similar discussions

Subscribe To Our Newsletter!