allow macro to get behind cell protection

[Billm]

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

[Ricky Morris]

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

will protect and

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

will unprotect

[Billm]

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 ]

[Billm]

or is it possible to assign control priveleges to the macro to overcome this protection ?

Thanks
Bill

[Tom Schreiner]

ActiveSheet.Unprotect Password:="YourPassword"
'code here
ActiveSheet.Protect Password:="YourPassword"

[Billm]

[Billm]

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

[Tom Schreiner]

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

[Billm]

great .. works a treat ..

Many thanks for your assistance
Bill

[Billm]

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