Sub Workbook_Open and BeforeClose not working
 

I have created the following macros in my workbook:
Sub Workbook_Open()
Application.CommandBars("Tools").Controls("Protect ion").Enabled = False
End Sub
Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Tools").Controls("Protect ion").Enabled = True
End Sub

Neither of these macros is working. When I open the file, the Tools
Protection is still enabled. If I run a separate macro to disable the
protection, then save the file and exit, the protection is still disabled.

I am running Excel 2003. The macros are in Module 1 of the worksheet project.

Any help would be very much appreciated!!!!

Thanks!

Sub Workbook_Open and BeforeClose not working
 

Dorothy,

I am running Excel 2003. The macros are in Module 1 of the worksheet
project.

You are trying to run code when workbook events occur. Regular/standar
modules will not detect workbook/worksheet events, so your code will not
work in a regular/standard module. You need to move it to the
"ThisWorkbook" module. If your Project Explorer has modules grouped
together in separate folders, then the "ThisWorkbook" module will be in the
"MS Excel Objects" folder.

HTH,

Conan




"Dorothy" wrote in message
...
I have created the following macros in my workbook:
Sub Workbook_Open()
Application.CommandBars("Tools").Controls("Protect ion").Enabled = False
End Sub
Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Tools").Controls("Protect ion").Enabled = True
End Sub

Neither of these macros is working. When I open the file, the Tools
Protection is still enabled. If I run a separate macro to disable the
protection, then save the file and exit, the protection is still disabled.

I am running Excel 2003. The macros are in Module 1 of the worksheet
project.

Any help would be very much appreciated!!!!

Thanks!

Sub Workbook_Open and BeforeClose not working
 

Thanks so much!! Problem solved!!

"Conan Kelly" wrote in message
...
Dorothy,

I am running Excel 2003. The macros are in Module 1 of the worksheet
project.

You are trying to run code when workbook events occur. Regular/standar
modules will not detect workbook/worksheet events, so your code will not
work in a regular/standard module. You need to move it to the
"ThisWorkbook" module. If your Project Explorer has modules grouped
together in separate folders, then the "ThisWorkbook" module will be in
the "MS Excel Objects" folder.

HTH,

Conan




"Dorothy" wrote in message
...
I have created the following macros in my workbook:
Sub Workbook_Open()
Application.CommandBars("Tools").Controls("Protect ion").Enabled = False
End Sub
Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Tools").Controls("Protect ion").Enabled = True
End Sub

Neither of these macros is working. When I open the file, the Tools
Protection is still enabled. If I run a separate macro to disable the
protection, then save the file and exit, the protection is still
disabled.

I am running Excel 2003. The macros are in Module 1 of the worksheet
project.

Any help would be very much appreciated!!!!

Thanks!