VBScript: wait for Excel MsgBox, Click OK

Question

I've written a VBScript that automates a load of processing of Excel workbooks.

For some of these workbooks, a MsgBox is triggered when they are opened. This message box is interrupting the automation.

Is there a way to listen for this MsgBox in a VBScript and "click" OK when it pops up?

VBScript or VBA? Message boxes are designed to halt and force the user to interact. If you just want a message you will probably want to create a userform (vba) to allow your script to continue. ignore me, i though you wanted the message box :D — NickSlash
– NickSlash, Commented Feb 21, 2013 at 13:12
There are options, but they tend to be complex, inelegant and/or unreliable for any kind of production use. If you let us know what messagebox you're trying to get around then we might be able to offer more useful advice. — James Snell
– James Snell, Commented Feb 21, 2013 at 13:14
@JamesSnell The messagebox is called from the VBA code within the sheet. — Griffin
– Griffin, Commented Feb 21, 2013 at 13:33
Well, so it's your code, if you don't want the msgbox then the most elegant solution is to get rid of it in the other sheets as getting around it is going to be a world of dirty hacks. MsgBox should be banned IMHO. — James Snell
– James Snell, Commented Feb 21, 2013 at 14:04
@JamesSnell, I think the whole thing is a dirty hack, unfortunately I'm not in a position to do much about it. I can't get rid of the MsgBox, but I could temporarily disable it if the VBA somehow 'knew' that it was my script that opened the workbook, and not a user. — Griffin
– Griffin, Commented Feb 21, 2013 at 14:53

Peter Albert · Accepted Answer · 2013-02-21 20:42:03Z

3

Try to use this wrapper:

Application.DisplayAlerts = False
Application.Workbooks.Open ([your code])
Application.DisplayAlerts = True

This will suppress any warnings on opening, e.g. compatibility issues, etc.

UPDATE:

In case MsgBox is generated from workbook code, try to use this:

Application.AutomationSecurity = msoAutomationSecurityForceDisable

In case Late Binding is used, this will work:

Application.AutomationSecurity = 3

Peter Albert

17.6k6 gold badges68 silver badges89 bronze badges

answered Feb 21, 2013 at 13:12

Maks Gordeev

3513 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Thanks for your answer, I had no luck with it

@Griffin could you please provide relevant piece of code? Or simply in your books there are macros that run on opening and trigger msgboxes?

good find, but unfortunately this only applies to Office for the Mac.

@Griffin I don't think so: msdn.microsoft.com/en-us/library/office/… - link was just a sample. Anyway, it's a matter of seconds to try it out.

I tried before posting, I got Variable is underfined: 'msoAutomationSecurityForceDisable', Code:800A01F4

|