Automatically selecting specific buttons from a message box

lockbox used Ask the Experts™
A lot of the procedures that I write in excel result in message boxes popping up that give the user the opportunity to select how the procedure will run (e.g. Message boxes with 'Yes' and 'No' buttons or 'OK' and 'Cancel' buttons).  Somtimes however I want to call these procedures and have the program automatically select the button (e.g. a scenario where  a procedure that results in a message box where I want the program to automatically select the 'YES' button when the message box is called).  How do I do this?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

If u want the first button selected then MsgBox "Test", vbYesNoCancel + vbDefaultButton1
For Second Button as default: MsgBox "Test", vbYesNoCancel + vbDefaultButton2
For Third Button as default: MsgBox "Test", vbYesNoCancel + vbDefaultButton3


Specifically what I need to do is have a procedure that calls a second procedure, which steps through the Message Box in the second procedure w/o any user interface.  (e.g.  have a procedure that calls a second procedure.  Within the second procedure, when the first message box is called, have the code automatically select the 'YES' button).

Assuming that you are creating your Excel object in the name objExcel,
Dim objExcel as Excel.Application
set objExcel = new Excel. Application

If you add this line,
objExcel.Application.DisplayAlerts = False
This would disable all message boxes that pop up from Excel application. You wouldn't have any user interface at all. This property is set to True by default and hence shows all message boxes.

However, the idea what you are suggesting is not possible. When a messagebox is displayed from an application, it is a modal form and does not hand over control to any other procedure or event in your application. Even timers are disabled when message boxes are shown.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.


With regards to disabling the "DisplayAlerts", are you sure this is the case?  I use this feature often but still have message boxes pop up (ones that I specifically call) where as boxes like the save warning (where the message box prompts you that you are saving over a file that already exists) will not pop up when display alerts is set to "FALSE".

May be tried with

objExcel.Application.Interactive = false
objExcel.Application.DisplayAlerts = False


objExcel.Application.DisplayAlerts = true
objExcel.Application.Interactive = true
The ones u call would obviously pop up. But the ones from Excel will be suppressed.

However, i will iterate again that if u make a "MsgBox" call, no other part of ur application will get control and will require user intervention. There is no way of u calling a msgbox from one function and trying to automatically select some default key from another procedure.

Alternatively, you can work out a different solution.
1. Create a new form "MyMessageBox"
2. Create Command Buttons "Ok", "Cancel", "Yes", "No" and whatever u want.
3. Create a boolean flag for each button, so setting it to true would show it and false would make it's visible to false.
4. Dynamically adjust its left positions as the top can be fixed.
5. Remove the form's controlbox and clipcontrols.
5. Use SendKeys and manipulate the button u want pressed.


Whatever the reason is why sometimes you want to show msgbox and sometimes not, you could add a new argument to the function or sub.
So when the function it's called it will show the messagebox or not (in the second case you'll make the function execute the code skipping the msgbox and setting the flag as if the user pressed "Yes" or "No"), depending on the way you called the function itself.

i.e.: Public Sub MySub (byVal Arg1, ..... , byVal ShowMsg as boolean)

Hope this help!

MsgBox "Content",3, "Title"

The third parameter is the group of buttons, icons, and so on.

To give focus to a specific button, just add 256 to this parameter, if the message has 3 buttons, and you want to give focus to the therd one, add 256*2 (becase add 0 to give focus to the first button).

You will have to evaluate the result of the MsgBox function, so you can do something like
dim i as long
i = MsgBox("Content",3, "Title")
if (i = vbYes) then

Bets regards

If lockbox wanted to have a "visually pressed" button in a msgbox then vinnyd79 is right.

To me it's still not clear if lockbox wants the msgbox to appear to the user for an amount of time and then go ahead with the function, or if he would like to skip it completely... :)

any updates?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial