Cannot disable macros after digital signing (Excel 2002 (xp) SP2)

Topic

New Reply

Oops!

I have created a workbook with a macro that automatically fires upon opening the workbook. The macro does it’s thing, then quits Excel. Excel security is set to medium, which would always produce the macro security dialog box, where I could disable macros if I didn’t want it to fire. Well, I created a digital certificate (using selfcert) and signed the macro.

Now when I open the workbook, the macro runs and closes Excel automatically (like I wanted it to). However, the old “hold shift key while opening” trick doesn’t do it’s, huh, trick. It doesn’t keep the macro from firing. I do not seem to have access to make any changes to this workbook. This would be bad.

Someone please tell me that there’s a simple thing I’ve overlooked

On a related note, is there a command line argument that will allow me to run a macro at startup (besides the Workbook_Open procedure)? Access has such a feature, but couldn’t find anything similar in Excel.

Reply | Quote

Replies

the old “hold shift key while opening” trick should work for you. I just tested.

However if you continue to have problems, you could switch off event processing with Application.EnableEvents = False, then open your workbook, and set event processing back on with True in place of False.

If you name a sub Auto_Open and place it in a general module, it should run automatically when the workbook is opened.

Andrew C

Reply | Quote

Some clarification:
Subs named Auto_open are not run if the file is opened via code: you have to use the RunAutoOpen method to run them after the workbook is opened (or before the workbook is closed via code if you have an Auto_Close macro.

To run auto open:

Workbooks.Open "c:MyFile.xls"
ActiveWorkbook.RunAutoMacros xlAutoOpen

To run autoclose

With ActiveWorkbook
	.RunAutoMacros xlAutoClose
	.Close
End With

The Workbook open and close events will always fire (unless you have disabled events) whether the file is opened manually or via a macro.

Steve

Reply | Quote

I had assumed that the workbook was being opened manually, as was being employed.

I am aware that Auto_open only fires automatically when the workbook is manually opened.

Andrew C

Reply | Quote

Sorry, I did not mean to imply that you didn’t know. I just wanted to clarify for the other readers who might not know since there is confusion on this occasionally.

Steve

Reply | Quote

Sorry, I did not mean to imply that you didn’t know. I just wanted to clarify for the other readers who might not know since there is confusion on this occasionally.

Steve

Reply | Quote

I had assumed that the workbook was being opened manually, as was being employed.

I am aware that Auto_open only fires automatically when the workbook is manually opened.

Andrew C

Reply | Quote

Some clarification:
Subs named Auto_open are not run if the file is opened via code: you have to use the RunAutoOpen method to run them after the workbook is opened (or before the workbook is closed via code if you have an Auto_Close macro.

To run auto open:

Workbooks.Open "c:MyFile.xls"
ActiveWorkbook.RunAutoMacros xlAutoOpen

To run autoclose

With ActiveWorkbook
	.RunAutoMacros xlAutoClose
	.Close
End With

The Workbook open and close events will always fire (unless you have disabled events) whether the file is opened manually or via a macro.

Steve

Reply | Quote

the old “hold shift key while opening” trick should work for you. I just tested.

However if you continue to have problems, you could switch off event processing with Application.EnableEvents = False, then open your workbook, and set event processing back on with True in place of False.

If you name a sub Auto_Open and place it in a general module, it should run automatically when the workbook is opened.

Andrew C

Reply | Quote

AFAIK Excel does not have any startup switch that allows you to run macro code at startup, you have to use the Workbook Open event. For info on available startup command line arguments, see this MSKB article:

XL2000: Startup Switches for Microsoft Excel 2000

If you look at the available options, the one that may be most useful is the /s switch to open Excel in “Safe Mode.” If you use shortcut with this option, you still get the “Disable Macros/Enable Macros” warning, even if the file’s VBA project has been signed using a self-signed certificate. You can then elect to enable or disable macros. Only drawback, after responding to the warning msg, you have to respond to an additional msgbox asking you to enter your Office User Name & Initials. See MSKB article for more info on this option.

Another option I tested was the /p switch that allows you to specify the active path to use instead of the default path. You can create shortcut with this option, specifying path other than default (typically the current user’s My Documents folder), then have the Workbook Open event test to see if the current folder is not the default, using the Windows GetCurrentDirectory API function to get directory path for the current process, and the Excel Application DefaultFilePath property to get the default path. This worked OK, as long as Excel not already open. Otherwise not reliable, because the current path could change for any number of reasons, such as browsing for a file to open or save.

Recommend try using /s switch as simplest approach. Also the shift key bypass worked when opening workbook from Open File dialog in Excel; did not work when double-clicking a shortcut to open file.

HTH

Reply | Quote

Thanks, everyone, for your responses to my question. Digitally signing a project has been tricky to figure out, having not done so to this point. This was just another piece to the puzzle.

I have been double-clicking a shortcut to open the file. Opening it through the Open dialog box while holding down shift worked as designed. I will probably add a simple IF Statement and MsgBox asking if I want to run the macro, which I can comment out once all my testing is done.

Thanks again for everyone’s help.

Reply | Quote

I usually have a public constant, like:

Public bTest as Boolean=True

WHich I use to execute or bypass code that is only there for testing. One example I use sometimes is:

Activesheet.Printout Preview:=bTest

So that whilst I’m testing, I just get a preview, not a print.

Reply | Quote

What a great tip. Thanks Jan!

Reply | Quote

What a great tip. Thanks Jan!

Reply | Quote

I usually have a public constant, like:

Public bTest as Boolean=True

WHich I use to execute or bypass code that is only there for testing. One example I use sometimes is:

Activesheet.Printout Preview:=bTest

So that whilst I’m testing, I just get a preview, not a print.

Reply | Quote

Thanks, everyone, for your responses to my question. Digitally signing a project has been tricky to figure out, having not done so to this point. This was just another piece to the puzzle.

I have been double-clicking a shortcut to open the file. Opening it through the Open dialog box while holding down shift worked as designed. I will probably add a simple IF Statement and MsgBox asking if I want to run the macro, which I can comment out once all my testing is done.

Thanks again for everyone’s help.

Reply | Quote

AFAIK Excel does not have any startup switch that allows you to run macro code at startup, you have to use the Workbook Open event. For info on available startup command line arguments, see this MSKB article:

XL2000: Startup Switches for Microsoft Excel 2000

If you look at the available options, the one that may be most useful is the /s switch to open Excel in “Safe Mode.” If you use shortcut with this option, you still get the “Disable Macros/Enable Macros” warning, even if the file’s VBA project has been signed using a self-signed certificate. You can then elect to enable or disable macros. Only drawback, after responding to the warning msg, you have to respond to an additional msgbox asking you to enter your Office User Name & Initials. See MSKB article for more info on this option.

Another option I tested was the /p switch that allows you to specify the active path to use instead of the default path. You can create shortcut with this option, specifying path other than default (typically the current user’s My Documents folder), then have the Workbook Open event test to see if the current folder is not the default, using the Windows GetCurrentDirectory API function to get directory path for the current process, and the Excel Application DefaultFilePath property to get the default path. This worked OK, as long as Excel not already open. Otherwise not reliable, because the current path could change for any number of reasons, such as browsing for a file to open or save.

Recommend try using /s switch as simplest approach. Also the shift key bypass worked when opening workbook from Open File dialog in Excel; did not work when double-clicking a shortcut to open file.

HTH

Reply | Quote