Disable macros when opening excel workbook from VBA

[SunGodly]

Hi All,

I need to open a workbook from VBA without running the 'Workbook_Open' routine that is attached to it. I have seen a suggestion in this forum using a global variable but I can't seem to make it work...Any other suggestions?

 

[robcarr]

all you need to do is hold down the shift key when selecting the file and the macros will be disabled.
Hope it helps.

 

[kylua]

I apologise for the global variable tip. I based it on a bug I had when I ran two spreadsheets side by side and the globals clashed. Now I can't get it to work.

You could use the application.username as a flag tho.

sub opener()
dim namehold as string
namehold = application.username
application.username = "No Macros"
'open the workbook
application.username = namehold
end sub

and in
sub workbook_open()
if application.username = "No Macros" then exit sub

If it crashes on the open event, you have lost the users username tho. There is probably an easier way but in the absence of that, this will work.

Other methods would be to get the opening files to check for the presence of the file that you use for opening and quit macros if it's there.

 

[SunGodly]

Okay, I found a VERY simple method that appears to work...

Application.EnableEvents = False

I just tried it and it appears to do the job very nicely. I do not know if the property needs to be reset to True or if it resets automatically.

Anyway, many thanks to everyone who replied, and I hope this is helpful to you as well.

 

[acron]

I would suggest that you reset Application.EnableEvents as it will not automatically reset, and is application wide in it's effect.

A.C.

 

[rmikesmith]

Following up on Acron's advice, I would also wrap the code to open the other workbook in some error trapping. If an untrapped error occurs, the subsequent

Application.EnableEvents = True

line will not execute. All event handling will then be deactivated.


Regards,
Mike