When a password protected workbook is opened in Excel 2007 macros are automatically disabled,
presumably because the macros cannot be scanned due to the file being encrypted.
You have this problem when you use a File Open password or if use the normal workbook protection.
This is independent of the usual macro security setting which controls whether macros are enabled,
not scanned. Note: It is possible that you not have this problem.

***This problem is Fixed in Office 2007 SP2***

Jim Rech posted this solution if you want to use the code in your protected workbooks:

Before you try Jim's solution you may want to make a back up of the registry before you start.

1: Click on the Windows start button
2: Click on Run
3: Type  regedit
4: OK




Then we use File Export in the Registry Editor




Enter a name for the backup file and be sure that All under the "Export Range"
option is selected and click on Save.





Now we follow Jim's steps to add a DWORD registry key.

You can also download this PassWord.reg file to do the same, easy if you want to do it on more then one computer. Double click the file to open it and it will add the DWORD in the register for you.


Follow the steps below if you want to do it manual.

Click before HKEY_CURRENT_USER in the editor to expand it.


Click on each of these in order:

\Software
    \Microsoft
        \Office
            \12.0
                \Excel
                    \Security


1:  Right click on Security
2:  Choose New>DWORD
3:  Copy/Paste this in the Name box ExcelBypassEncryptedMacroScan
4:  Press Enter
5:  Double click on the name
6:  Change the 0 to 1
7:  OK
8:  File>Exit to close the Registry Editor


If you follow the steps correct you can use the code now in your protected workbooks.

See also this Microsoft article
http://support.microsoft.com/kb/927150