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

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
_______


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

On Aug 13, 4:34 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> 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
> _______
>
> <IComeInTheW...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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

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


"(E-Mail Removed)" wrote:

> On Aug 13, 4:34 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
> > 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
> > _______
> >
> > <IComeInTheW...@gmail.com> wrote in message
> >
> > news:(E-Mail Removed)...
> >
> >
> >
> > > 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- Hide quoted text -
> >
> > - Show quoted text -
>
> Is there a logical way to determine in which window a dialog box will
> appear?
>
>

On Aug 13, 5:40 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:
> 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
>
>
>
> "IComeInTheW...@gmail.com" wrote:
> > On Aug 13, 4:34 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> > wrote:
> > > 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
> > > _______
>
> > > <IComeInTheW...@gmail.com> wrote in message
>
> > >news:(E-Mail Removed)...
>
> > > > 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- Hide quoted text -
>
> > > - Show quoted text -
>
> > Is there a logical way to determine in which window a dialog box will
> > appear?- Hide quoted text -
>
> - 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