problem with macros and protections

[johkur]

I wonder if anyone else is having this problem. I created some files
from scratch in Excel 2007, added macros and a user-defined function,
and protected all of the worksheets and the workbook. When I went to
use the file, none of the macros worked and the function returned an
error. My trust settings are set to allow macros to run freely. I went
back and removed all of the protections and the macros then worked OK.
Is there some kind of an issue that prevents macros from running when
protections are turned on. I really need both in my files. Is there
something I'm missing here?

 

[Cimjet]

Hi
I could be way off but in case: If your file extension is XLSX, it wont work you
need to save it as XLSM or for a Template: XLTM
I don't have 2007 so I can't test anything.
Good luck
John

 

[johkur]

Files are .xlsm. Macros work when protections are removed. Either
Excel has a bug of some kind that is rendering these two features
mutually exclusive, or there's something I'm not aware of that would
make them work. I've checked with numerous websites and Excel forums
and nobody seems to have an answer. I'm stumped.

 

[Jim Rech]

I think this explains your problem:

http://support.microsoft.com/kb/927150

 

[johkur]

This looks promising. Thanks Jim!

 

[johkur]

I've been testing a few things with this and have found that, just
like the article says, it can be a problem when workbook protections
are on, but it doesn't say anything about workSHEET protections. I
had both turned on, but really don't need the book protected as much
as the sheets. If I only protect the sheets, it seems to be OK. So
once again, thanks!