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!
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.
