Unprotecting a Worksheet

B

BillCPA

I have the following code in the 'BeforeClose' routine of my spreadsheet:

Sheets("Lists").Unprotect
Range("Lists!I1").Value = ""
Sheets("Lists").Protect DrawingObjects:=True, . . . . .

The code has worked for months with no problem as I have executed various
macros from ActiveX buttons on the different worksheets.

I have now created a form as a menu. When showing the form, I minimize the
spreadsheet window. On the form is a Close button with the following code:

UserForm4.Hide
Workbooks("Main.xls").Activate
ActiveWindow.WindowState = xlMaximized
Sheets("Menu").Select
ActiveWorkbook.Close

which obviously activates the above 'BeforeClose' routine. It executes the
'Unprotect' statement without any message, but when it tries to set the I1
cell to empty, I get a debug message saying the worksheet is protected. And,
as you might expect, if I unprotect the worksheet so that it can set the cell
value, then the 'Protect' statement has no effect on the worksheet.

Any ideas on what I have done to cause the 'Unprotect/Protect' statements to
quit working?
 
Top