Does Protect still occur if sheet is already protected?

I can't find an answer to this. Every source I find talks about userinterfaceonly, but they don't answer my question.

Will this code set userinterfaceonly:=True regardless of the status of ProtectContents?

Please Login or Register  to view this content.

Or do I need to do this?

Please Login or Register  to view this content.

I have tried testing it, but I don't trust my results for some reason. My tests seem to indicate that I don't need to UnProtect first, but because I can't test userinterfaceonly's value, I feel nervous about trusting my results.

Is there any advantage to UnProtecting the protected sheet first?
Is there any disadvantage to Not UnProtecting the protected sheet first?

Hmmm....?

I hadn't considered this option before.
I always use something on these lines

Please Login or Register  to view this content.

This ensures that the sheet in always protected except for running the relevant macro.

I found this on OzGrid

http://www.ozgrid.com/VBA/excel-macr...cted-sheet.htm

UserInterFaceOnly

The UserInterFaceOnly is an optional argument of the Protect Method that we can set to True, the default is False. By setting this argument to True Excel will allow all Excel VBA macros to run on the Worksheet that protected with or without a password.

You need to be fully aware that, that if you use the Protect method with the UserInterfaceOnly argument set to True on a Worksheet, then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To set the user UserInterfaceOnly back to True after the workbook is opened, you must again apply the Protect method with UserInterfaceOnly set to True.

The solution to this is quite easy and requires us to make use of the Workbook_Open Event (fired as soon as the Workbook is opened) As this is an Event of the Workbook Object (ThisWorkbook) we must place the code as shown below in the Private Module of ThisWorkbook. To get here easily, right click on the Excel icon, top left next to "File" and select "View Code"

Please Login or Register  to view this content.

The above code is good if each Worksheet you need to have your macros operate on have different Passwords or your do not want to Protect all Worksheets. We can set the UserInterfaceOnly to True without having to un-protect first.

If you want to set the UserInterfaceOnly to True on all Worksheets and they have the same password you can use this code which must be placed in the same place as the above code.

Please Login or Register  to view this content.

Now, each time you open the Workbook, the code will run and set the UserInterfaceOnly to True allowing your macros to operate while still prevent any changes from users.

Hope this helps

P.S.
Not sure if it is okay to post a link to this site/forum.

If not I apologise

Regards
Alistair

P.P.S
I assumed you had searched the forum.
Try this thread answered by Leith Ross
http://www.excelforum.com/excel-prog...ng-for-me.html

Hi Marcol;

Thanks. I don't know why I couldn't find the link you found. I guess I have to rethink my search techniques.

Not sure if it is okay to post a link to this site/forum.

Of course you can, Alistair. We don't have a monopoly on useful information.