VBA to protect sheet in excel

Closed
issa - Mar 30, 2011 at 01:40 PM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 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.

3 replies

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
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
0
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,
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
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
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
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
0
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.
0
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
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
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
0
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.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
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
0