PDA

View Full Version : encrypted macros



philfer
08-28-2010, 06:26 AM
Hello,

When I am writing VBA and save the file as an xlsm file I get the "enable content" warning when I re-open the file (unless I set the folder up as a trusted location) which is what I expect

However sometimes after I have written some further code in an xlsm file I get the "this workbook contains encrypted macros" warning which doesnt let me enable the content.

I am the only one writing macros in the workbook and it often happens after I write a macro in the ThisWorkbook object

Does this happen to anyone else and does anyone know why it is happening and what I can do to deal with it. I delete the code I had just written but it still wont work and it is almost like the file is corrupted and I end up having to start from scratch and makes me look very slow and even more incompetant than I already do!!!!

Any advice?

Thanks
Phil

Bob Phillips
08-28-2010, 08:33 AM
Can't say I have noticed it. Can you post an example of this?

Paul_Hossler
08-29-2010, 12:39 PM
I've gotten strange results after a lot of editing also

Running Ron Bovey's CodeCleaner

http://www.appspro.com/Utilities/CodeCleaner.htm

almost always fixes the Excel-wierdness, leaving me to deal with just my own code problems

Paul

Jan Karel Pieterse
08-29-2010, 11:45 PM
If I recall correctly, this is a bug in Excel2007 which affects workbooks with macro's which have been protected. I forgot which password it is, VBA project or workbook open password.

philfer
08-31-2010, 10:45 AM
Hi,

Sods law I cant get the problem to happen again so cant post some examples.

The macro/VBA project wasnt password protected (yet) and it happened when I changed some code and resaved it.

It happens now and then so I have taken to saving my code as a different version regularly just to make sure I dont have to go back too far in oder to recreate my work

As you can imagine this is quite impractical.

Do you know if this bug has been fixed in Excel 2010?

Cheers guys
Phil

Jan Karel Pieterse
08-31-2010, 11:53 PM
Not sure if this has been fixed in 2010. If I recall correctly this may also be related to anti virus software settings. One thing you may benefit from is my autosave tool that saves copies of your VBE project elements, see:
www.jkp-ads.com/download.asp (http://www.jkp-ads.com/download.asp#autosafeVBE) and look for a file called autosafeVBE.

philfer
09-01-2010, 10:03 AM
Hello

I have narrowed down one occasion when it happens.

If I try to protect the struste and windows of a workbook using the Review ribbon and "Protect Workbook" and then save and close the workbook when I re-open it it says that macros are encrypted.

I often protect the structure of a workbook to stop people adding or removing sheets or changing the names of sheets as I often use them in code.

Perhaps if other people try the same they might see what I am talking about

Thanks for the link Jan - is the source code available for the downloads?

All the best
Phil

Jan Karel Pieterse
09-01-2010, 10:56 PM
Ah, indeed that was it. It is a bug in 2007.
NB: There is no need to protect a workbook against sheet name changes if you use the codenames of the sheets in your code instead of the tab names.
EMail me for the source code. (address at bottom of my web pages)