Automatically Selecting "Yes" in an Externally Called Message Box

[IComeInTheWest]

Hello,

I am writing a macro that opens a workbook (say, MyWorkbook) and calls
a macro (say, MyMacro) from MyWorkbook. MyMacro displays a vbYesNo
message box in which I'd like to select "Yes" automatically.

One idea I was given (with a caveat of method-unreliability) was to
use the SendKeys Method as follows:

Application.SendKeys "{RETURN}"
Application.Run "MyWorkbook!MyMacro"

I tried this and it worked the first few times but has not worked the
previous few.

Why is the SendKeys Method so quirky?

Is there another, better way to automatically select "Yes" in a
message box which was called from another workbook?

Note: Editing the code in MyMacro is not an option.

Regards,

Calvin

 

[Jon Peltier]

SendKeys is unreliable because it writes to the active window. If your
dialog pops up behind the active window, or if there's a delay in its
appearance, you will be sending keystrokes to the wrong window.

- Jon

 

[IComeInTheWest]

SendKeys is unreliable because it writes to the active window. If your
dialog pops up behind the active window, or if there's a delay in its
appearance, you will be sending keystrokes to the wrong window.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______













- Show quoted text -

Is there a logical way to determine in which window a dialog box will
appear?

 

[Tom Ogilvy]

It would be better to suppress the display of the msgbox.

If it is produced by event code in the other workbook

Application.EnableEvents = False
set bk = Workbooks.Open("C:\Myfolder\Myboooks.xls")
' process the book
bk.Close SaveChanges:=False
Application.EnableEvents = True

 

[IComeInTheWest]

It would be better to suppress the display of the msgbox.

If it is produced by event code in the other workbook

Application.EnableEvents = False
set bk = Workbooks.Open("C:\Myfolder\Myboooks.xls")
' process the book
bk.Close SaveChanges:=False
Application.EnableEvents = True

--
Regards,
Tom Ogilvy






- Show quoted text -

Tom,

Thanks for the idea. I'm not sure if that will work, however, because
the dialog box in question has "Yes" and "No" buttons which route the
procedure to different places. I'd like to automatically select "Yes".

Regards,

Calvin