|
Automatically Selecting "Yes" in an Externally Called Message Box
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 |
Automatically Selecting "Yes" in an Externally Called Message Box
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 _______ wrote in message oups.com... 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 |
Automatically Selecting "Yes" in an Externally Called Message Box
On Aug 13, 4:34 pm, "Jon Peltier"
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 _______ wrote in message oups.com... 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? |
Automatically Selecting "Yes" in an Externally Called Message
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 " wrote: On Aug 13, 4:34 pm, "Jon Peltier" 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 _______ wrote in message oups.com... 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? |
Automatically Selecting "Yes" in an Externally Called Message
On Aug 13, 5:40 pm, Tom Ogilvy
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 " wrote: On Aug 13, 4:34 pm, "Jon Peltier" 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 _______ wrote in message roups.com... 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 |
| All times are GMT +1. The time now is 03:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com