VBA to protect sheet in excel

Closed

issa - 30 Mar 2011 à 13:40
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 5 Mar 2012 à 09:01

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.

Activesheet protect
Protect pdf from copying - Guide
Winrar password protect - Guide
How to un write protect a usb - Guide
Four tips to protect personal data and avoid malware - Guide
Open office password protect - Guide

3 responses

Réponse 1 / 3

TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
31 Mar 2011 à 10:25

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
1 Apr 2011 à 01:27

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 28 January 2010 Status Contributor Last seen 5 May 2022 766
1 Apr 2011 à 09:36

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 28 January 2010 Status Contributor Last seen 5 May 2022 766
1 Apr 2011 à 09:39

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 12 September 2010 Status Contributor Last seen 27 December 2022 555
1 Apr 2011 à 09:51

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
4 Apr 2011 à 08:07

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.

Réponse 2 / 3

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

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 12 September 2010 Status Contributor Last seen 27 December 2022 555
23 Feb 2012 à 10:04

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

Réponse 3 / 3

RWomanizer Posts 365 Registration date Monday 7 February 2011 Status Contributor Last seen 30 September 2013 120
29 Feb 2012 à 00:52

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 12 September 2010 Status Contributor Last seen 27 December 2022 555
5 Mar 2012 à 09:01

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

Write protect

How to specify macro for current active sheet

how do i release write protection from my sd memory card?

Confirm account Facebook

Similar discussions

Subscribe To Our Newsletter!