Sheets.Protect UserInterfaceOnly parm not working for me

I manage about 1,600 shared Excel workbooks each day. In each workbook I use a VBA script with Excel 2003 to access a protected worksheet and add/delete/alter data in unprotected (unlocked) cells. I now find a need to write data into protected cells in each workbook.

I'm trying to figure out how to use the UserInterFaceOnly:=True parameter of the Sheets Protect method so I don't need to sandwich my VBA code between a Sheets.Unprotect and a Sheets.Protect method.

So I took one of my workbooks and striped its worksheet free of protection and then reprotected with the following code.

Please Login or Register  to view this content.

This generates no errors.

So I reopen the workbook with another VBA script and attempt to write to a protected cell.

Please Login or Register  to view this content.

I first tried the write without the ".Protect Password:="pwd1", UserInterfaceOnly:=True" statement. And it failed with the error that the cell to which I'm writing is protected. I read somewhere that the UserInterfaceOnly parameter is lost when the workbook is saved and has to be reapplied when the workbook is next opened. So I added the statement in. It still failed for the same reason.

I'd appreciate any suggestions on how I can get the UserInterFaceOnly parameter to make protected cells available to VBA writing. Thanks in advance.

Hello Weebie,

I am not sure what you are trying to achieve, but it appears there may be some confusion about the use of the UserInterfaceOnly argument. Hopefully, the following explanations will help clear up any questions you have.

If this argument is omitted, protection applies both to macros and to the user interface
when using the Protect method on a Worksheet.

If you apply the Protect method with the UserInterfaceOnly argument set to True to a worksheet and then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To unprotect the worksheet but re-enable user interface protection after the workbook is opened, you must again apply the Protect method with UserInterfaceOnly set to True.

Sincerely,
Leith Ross