I've discovered something weird about macros and encryption. I am running
Excel 2007 on 2 computers - a laptop running VISTA and a desktop running XP.
Suppose on the desktop computer running XP I passwordencrypt the Excel
workbook using the Prepare - Encrypt Document. I save the document, close
it and then I open the document and type in the correct password. I then
call up the Visual basic editor and verify that my code is there in the
modules I have created. I try to run a macro and get a message that I can't
do it. This despite the fact that in the Trust Center I have the macro
settings "Enable Macros..." and "Trust Access to VBA Project Object Model"
clicked, and in the ActiveX settings I have "Enable All controls" clicked and
"Safe Mode" unclicked. What I must do is select Prepare - Encrypt and then
give a blank password, which unencrypts the document. Then I save the
document as a macro-enabled document and then close it and then re-load it.
Now I can use the macros. Evidently in XP you cannot use macros in an Excel
2007 worksheet that has been encrypted even after you successfully open the
workbook with the correct password. One interesting thing might provide a
clue as to what is happening. When the file is encrypted and I open it and
then try to run a macro, the macro window displays the macros in the form
[Myworkbook.xlms]!test1 [Myworkbook.xlms]!test2, ..., etc. but when I
unencrypt the workbook, save it, close it and then re-open it the macros are
displayed in the form test1, test2, etc.


The phenomenon I have described does not occur in the laptop running Excel
2007 under VISTA.

I wonder if anyone knows a workaround for this phenomenon.

--
-regards

Hi there,

I've posted exactly the same problem on many forums, but it seems that the
answer is here...http://support.microsoft.com/kb/927150

I'm trying to figure out if there is a workaround.

Regards.

"GeorgeJ" wrote:

I've discovered something weird about macros and encryption. I am running
Excel 2007 on 2 computers - a laptop running VISTA and a desktop running XP.
Suppose on the desktop computer running XP I passwordencrypt the Excel
workbook using the Prepare - Encrypt Document. I save the document, close
it and then I open the document and type in the correct password. I then
call up the Visual basic editor and verify that my code is there in the
modules I have created. I try to run a macro and get a message that I can't
do it. This despite the fact that in the Trust Center I have the macro
settings "Enable Macros..." and "Trust Access to VBA Project Object Model"
clicked, and in the ActiveX settings I have "Enable All controls" clicked and
"Safe Mode" unclicked. What I must do is select Prepare - Encrypt and then
give a blank password, which unencrypts the document. Then I save the
document as a macro-enabled document and then close it and then re-load it.
Now I can use the macros. Evidently in XP you cannot use macros in an Excel
2007 worksheet that has been encrypted even after you successfully open the
workbook with the correct password. One interesting thing might provide a
clue as to what is happening. When the file is encrypted and I open it and
then try to run a macro, the macro window displays the macros in the form
[Myworkbook.xlms]!test1 [Myworkbook.xlms]!test2, ..., etc. but when I
unencrypt the workbook, save it, close it and then re-open it the macros are
displayed in the form test1, test2, etc.


The phenomenon I have described does not occur in the laptop running Excel
2007 under VISTA.

I wonder if anyone knows a workaround for this phenomenon.

--
-regards

Thank you for your very helpful post. This info at the link you sent me to
was very complicated. As I understand it, what is happening is that my virus
protection software is unable to scan the encrypted macros, so it sets a
switch making the macros unexecutable, even though I can access the code.
This seems not to be happening on my laptop, which is running Vista. I guess
the solution would be for the scanning software (I am using Norton/Symantec)
to scan the macros after they file is decrypted. Is Symantec aware of this
situation and working on it? I sure hope so. I would appreciate any
comments from Microsoft folks on this matter.