Trouble disabling macros

Hello everyone,

I'm using a workbook (.xlt) that someone else created. When I open the workbook for the first time, I get an input form that I fill in and then the workbook is saved as a normal .xls workbook.

The problem is that I then need to disable the macros so that the next time the workbook (.xls) is opened, the input form does not come up.

I disable the macros by changing the security setting to Very High. I then save the file and close it. The idea being that the next time the file is opened, the security setting is Very High, and the macros do not run. This works as long as I keep the Excel program open and only close the workbook. But if I close Excel completely and try opening the workbook again, then the macros run!

Is there another way of disabling macros? I take it that the security setting only applies while Excel is still open and the next time Excel is started, the security setting reverts back to the default setting?

Any help with how to disable macros would be much appreciated!

/Helen (using MS Excel 2003)

I've just found a thread asking a similar question
http://www.excelforum.com/excel-misc...e-a-macro.html

Would it be possible to change the code that is no longer needed to comments using VB? Or does this always need to be done manually?

It would be great if I could get a "disable macros" button that goes in and changes the relevant code to comments. Is that possible?

Thanks, Helen

Hi, thanks for the reply. I suspected that was probably what was going on.

A way for me to work around it then, would be to open the Excel application before I open the workbook. Make sure that the security setting is Very High in the Excel application and then open the workbook. That way the workbook will open without the macros running.

If I want to avoid that and be able to open the workbook without thinking, and have it open with macros disabled - is there a way of automatically disabling macros? It feels like there should be a menu option somewhere for "disable macros" or "remove macros". Any ideas?

Thanks again, Helen

Yeah, I had read about Medium setting being best, so I thought the default setting of low on my pc was wrong. I phoned up our company helpdesk to check if it was wrong and they said "no", it should be set to low. When I pointed out the security risk, they said that we just have to think twice before we open any Excel file!

So unfortunately I don't have any way of keeping the security setting as Medium or higher on the pc I'm using.

Thanks anyway for the tip. Maybe you should email it to my company's helpdesk!!

/Helen

Originally Posted by HelenStrand

Hello everyone,

I'm using a workbook (.xlt) that someone else created. When I open the workbook for the first time, I get an input form that I fill in and then the workbook is saved as a normal .xls workbook.

The problem is that I then need to disable the macros so that the next time the workbook (.xls) is opened, the input form does not come up.

Just for clarity's sake, .xlt is not a Workbook (per se). It's a Template. The big difference between a Workbook (.xls) and a Template (.xlt) is that the default action for a Workbook is "Open" and the default action for a Template is "New". Open does what you'd think, while New makes a copy and opens the copy (with a temporary name).

Anyway... I can't provide the code, but I think I have a solution to your problem...

You need to have a flag internal to the file, probably a CONST (my admittedly limited experience outside of recorded and tweaked macros indicates CONSTs are saved when a file is closed). When the file opens, it checks the CONST. If it's set to a particular value (say "1"), it displays the form.

You then need to have code (I think it would go in "Workbook_BeforeClose") that sets the variable based on the file name. If you're saving the file as a Template (.xlt), it sets the CONST to 1 so the form will present the next time it's opened. If you're saving the file as a Workbook (.xls), it sets the CONST to some other value (0 or 2 would be fine).

The next time you open your Workbook, it checks the CONST (which is now some value other than 1) and doesn't display the form. You don't need to mess with macro levels or disabling macros. Just let them run. You'll just have a macro that does nothing most of the time.