3

I am using Application.run to call a macro in another workbook written by my colleague that has something like this in the middle,

resp = MsgBox("Message goes here...", vbOKCancel)

How do I press OK automatically, basically suppressing the message box? I used Application.DisplayAlerts = False at the top of my sub and also put Application.SendKeys ("~") right below Application.run, neither works.

Nicholas
  • 2,560
  • 2
  • 31
  • 58
  • 2
    I dont think your code continues to the next line until your colleagues code is complete. Maybe edit your colleague's code (or ask them to do it...) – Tim Williams Jun 25 '17 at 03:18
  • You can possibly try [this technique](https://stackoverflow.com/a/41826510/4926357) – A.S.H Jun 25 '17 at 04:10
  • 1
    If you can access the code...why not just comment out that line? – BruceWayne Jun 25 '17 at 04:24
  • 3
    What's the point of a message box if you then go and code to press OK automatically? Take out the code for the message box and set the variable `resp` to the desired value. – teylyn Jun 25 '17 at 04:29
  • @A.S.H unfortunately, doesn't your comment on the other thread mean that technique is invalid for this use case ? – Robin Mackenzie Jun 25 '17 at 11:55
  • @RobinMackenzie There are lot of comments there, which one? – A.S.H Jun 25 '17 at 12:04
  • 1
    *@wooly In Windows everything is a Window. In my test it worked for a messagebox as well, provided you feed the function the correct Title of the box. The only problem I could fear is if that messagebox and the corresponding code run in the same context as your VBE project, in which case you cannot run your macro while that code is running. – A.S.H Jan 24 at 23:19* Did you mean that the code you presented will still be waiting behind the `MsgBox` issue because the `Application` object is the same for the calling and called macro? – Robin Mackenzie Jun 25 '17 at 12:07
  • @RobinMackenzie I see, but no actually, because that message was prior to the last (working) solution. It was when we tried to send the windows message from within Excel/VBA (hoping that the involved Add-In method was written in a different language and running its own thread). But then we switched to the final solution of launching a special Word Doc which will run a "watch-dog" macro to kill Excel's message-box. So in summary that comment does not apply to the final solution. – A.S.H Jun 25 '17 at 12:13
  • All comments above suppose that the user actually has access to the "other code". However, there *are* situations where the user has no access to the utility code (s)he's using (Protected workbook, Add-In, etc.) So the question seems valid to me. – A.S.H Jun 25 '17 at 12:35

1 Answers1

1

If you need to press the OK button of message box automatically that means you want to execute the whole program regardless of output of message-box. In this case you can just remove the command of message-box display and use the variable in the rest of program. So that neither you will get message-box nor you will be needed to press OK button.

Ashish Baboo
  • 154
  • 1
  • 1
  • 13