VBA - Closing or clicking OK in MsgBox from another workbook,

Question

Hi I the following code in Excel VBA,

Sub A ()
    Workbooks.open ("A.xls")
    ActiveWorkbook.Worksheets("1").Select
    ActiveSheet.CommandButton1.value = true
End Sub

I'm opening another workbook (the code inside is protected so I can't modify the workbook "B") and clicking a run button on the worksheet, and it returns a MsgBox with OK button.

I want to know how I can use VBA to close this MsgBox or clicking "OK" ...I tried to use,

`application.DisplayAlert = false` 

before opening the workbook "B" but this does not work..

Thanks for your help!

Solution

There might be an approach using SendKeys -- but SendKeys is notoriously temperamental. Here is a more reliable approach:

1) If you don't already have it organized like this, have the click-event handler of the button a 1-line sub that looks like:

Private Sub CommandButton1_Click()
    Process
End Sub

Where Process is the sub which actually does the heavy lifting. In general, I think it a good idea to have event handlers mostly functioning as dispatchers to subs.

2) Change the code for Process (or whatever you choose to name it) in the following way:

a) Modify the first line to look like

Sub Process(Optional Verbose As Boolean = True)

b) Where Process has something like

MsgBox "Processed!"

replace it by

If Verbose Then MsgBox "Processed!"

3) In the code you gave above, replace the line

ActiveSheet.CommandButton1.value = true

by the line

Application.Run "A.xls!Process", False

This approach will bypass the button entirely and run the code which the button normally triggers, but run it in silent mode.

On Edit: To use SendKeys you could do the following. Put the line

Application.SendKeys "~" 

before the line

ActiveSheet.CommandButton1.value = True

~ is the character shortcut for Enter. SendKeys doesn't itself send the keystroke, instead it puts something on the Windows Message Queue. VBA doesn't have any direct control about exactly when this message will be processed. In this case the lack of control is a benefit. The VBA interpreter moves onto the next line, which triggers the MsgBox. By the time the SendKeys message is processed the default Okay button on the message box has the focus hence receives the enter key. This can even happen before the box is painted, making it seem that the MsgBox was never there -- but that is better to think of it as being destroyed before you have time to see it.

The reason why it is necessary to have the SendKeys line before the line which clicks the button is that once the message box appears it will cause the VBA interpreter to wait until it is closed -- hence the calling code will suspend its execution until after the message box is closed, hence the SendKeys wouldn't be processed until it is no longer needed.

I don't really trust SendKeys. I suspect that sometimes when you run the code what will happen is that A1 in the newly activated sheet will receive the enter key (shifting the selection from A1 to A2) before the message box appears. I'm not sure if this can happen, but if it does a workaround might be to move the SendKeys to a VBScript program. Launch this program (with window minimized and not waiting for return) before the button is clicked. The VBScript program can have say an 0.5 second pause before it uses SendKeys. The script will be running in a different thread so it won't be blocked by the message box.

Comments

[lukieleetronic]

Hey John, I the "B" file where the MsgBox is generated is like a black box and I have no control over it, I'm only calling a small function from that file as the output will be used in my main excel for some other functions. its just that when the "B" file calling is finished, it gives me an annoying MsgBox pop up telling me that the process is finished.. I want to automatically click "ok" to move on...

[lukieleetronic]

wow, I found a way, if I send application.sendkeys "~" before calling the VBA function the MsgBox never appears... but WHY?

[John Coleman]

@lukieleetronic I added what I think is the explanation of what is happening. SendKeys solutions are seldom completely satisfactory but sometimes they are unavoidable.