Unprotecting a Worksheet

[Frank Kabel]

Hi
try specifying the objects:

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

-----Original Message-----
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

 

[Guest]

I can't seem to get that to make it work either.

If I remove the 'ActiveWorkbook.Close' statement from the code under the
Close button, then close the workbook normally, it works. But if I initiate
the 'Close' from the form, it doesn't.

Hi
try specifying the objects:

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

-----Original Message-----
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

 

[Frank Kabel]

Hi
post the code from your form

--
Regards
Frank Kabel
Frankfurt, Germany

I can't seem to get that to make it work either.

If I remove the 'ActiveWorkbook.Close' statement from the code under the
Close button, then close the workbook normally, it works. But if I initiate
the 'Close' from the form, it doesn't.

 

[Guest]

If you just need the code under the 'Close' button on the form, what is shown
below is all there is.

If you need all of the code under the form, it is over 7 pages long. I have
read messages that talk about 'posting' code, and it sounds like there is
somewhere to do it other than inside these messages. But I have never done
it, so if that is the case, please let me know how to do it.