Keystroke to get out of workbook_open macro?

[chinster99]

Hi,

I have a number of reports that are scheduled to run at various parts of the week/month. At the moment I have written a macro that resides in workbook_open. The macro saves the master documnent, makes a copy in another directory and creates a log of the process before shutting down. The purpose of the code was so that it would be an automated process when it came to scheduling.

However to do this I have had to set the macro security level to low so it would run automatically. Is there a way to code it so it works automatically with the security level set to medium

Alternativly if the security level remains at low, is there something I can put in that effectivly stops the macro at startup by pressing a key, as at the moment it loops till application is shut down.

Thanks

Leo

 

[combo]

An alternative, since office 2000, is to sign the VBA project. The digital signature can be created by SELFCERT.EXE (option to install in office package).
After creating the signature with selfcert, the signature can be selected in VB Editor (tools>digital signature).
When you run office document with macros and signed vba project, with protection set to 'medium', you can mark 'trust' option. If so, you will have in the future no warning screen for vba macros signed with trusted source.
Moreover, you as the owner of signature - can modify such projects without rejecting the signature in document.

combo

 

[chinster99]

Thanks for that, it helps with security level problem.

Is there a way to stop the macro by a keystroke? because i still have the problem of the report looping to a close. (ie. if someone wants to modify the master document without the macro running)

 

[combo]

Holding SHIFT key while opening a document disables its macros.

combo

 

[xlbo]

Easiest way to do this surely would be to have

cont = inputbox("Do you want to run the report&quot
if cont = "no" then
exit sub
else
end if

as your 1st few lines

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[chinster99]

The only problem with the input box method is that the reports are run on a scheduler and needs to be an automatic process.

The shift key method seems to work on my own computer however when I try to do it on the document via PC anywhere, it doesn't work. I don't know if its not working due to PC anywhere.

Anyway thanks all for your help

 

[xlbo]

If it's run on a schedular - does that mean that is is opened under a different userID ??
You could use

mUser = Environ("userid&quot
to return the userId of the person opening the workbook and use an IF statement (as per my last post) to decide whether to run the sub or not

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[chinster99]

Thanks Geoff, I have already got similar if statement for the path of the document (so it only works in the master directory) but your idea is good because it means that the macro only works on my computer and only I need to know how to break the macro.

Good job!!!