allow macro to get behind cell protection

Billm

Board Regular
Joined
Mar 19, 2002
Messages
88
My spreadsheet uses Protect Sheet and Protect Workbook. I have protected the range A1.. A3 in my spreadsheet to stop users from messing things up. Instead they have a macro which they can run which will change the formulas in those cells. But as those cells are protected .. the macro errors out when run.

Is there VB code I can put into the start of the macro that will un-protect those cells, then as it finishes .. puts the protection back on.

Thanks Bill
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

will protect and

ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False

will unprotect
 
Upvote 0
That works fine, thanks .. but I have a line that is "activesheet.name = xxx" in the macro that renames the worksheet and that line gives an error using this solutiob .. are there any additional parameters I can enter .. or .. add a new line of code ?

Also, the fix still prompts me for the unprotect password .. which I dont want to be prompted for ( otherwise the users would go and unprotect the whole sheet ) .. can the password be coded into the macro too .. or alternatively .. picked up from the sheet parameters ?

thanks again
This message was edited by billm on 2002-04-18 11:14
 
Upvote 0
or is it possible to assign control priveleges to the macro to overcome this protection ?

Thanks
Bill
 
Upvote 0
ActiveSheet.Unprotect Password:="YourPassword"
'code here
ActiveSheet.Protect Password:="YourPassword"
 
Upvote 0
As my workbook is protected too .. this line fails unless I manually unprotect the workbook
ActiveSheet.Name = new_symbol

is there another instruction that will unlock the workbook and re-lock it again ?

Also, the following line fails even though the password is the same as I used at the start of the macro to unlock the sheet :
ActiveSheet.Protect Password:="fred"

Am I doing anything wrong ?
Thanks
Bill
 
Upvote 0
Sorry,
Try this as well:

ActiveWorkbook.Unprotect Password:="Fred"
ActiveSheet.Unprotect Password:="Fred"

'your code here

ActiveSheet.Protect Password:="Fred"
ActiveWorkbook.Protect Password:="Fred"

Tom
 
Upvote 0
I have just realised that when the macro protects the workbook with the above command, it must be omitting the "windows" option that I normally check when I protect it manually.

Is there a further option for this command to check both of the options, Structure and Windows ?

Thanks
Bill
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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