Unprotecting a Worksheet

F

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
 
G

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.


Frank Kabel said:
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
 
F

Frank Kabel

Hi
post the code from your form

--
Regards
Frank Kabel
Frankfurt, Germany

BillCPA said:
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.
 
G

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top