Sub Workbook_Open and BeforeClose not working

[Dorothy]

I have created the following macros in my workbook:
Sub Workbook_Open()
Application.CommandBars("Tools").Controls("Protection").Enabled = False
End Sub
Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Tools").Controls("Protection").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!

 

[Conan Kelly]

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]

Thanks so much!! Problem solved!!

Dorothy,


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