Solved: Automate Answers to vbMsgBoxResults using a macro

[xluser2007]

Hi All,

Firstly, I have posted this here. I am posting here only because I have not received the answer to my question.

My question is succintly put as follows:

"When opening another workbook using VBA and running a macro from it, how do you go about automating the answer to the MsgBox vbYesNoCancel prompts"

More specifically I have the following macro which I run from master.xls. It opens up a targetworkbook and runs 2 macros from it, "PopulateSheetlist".

[vba]Option Explicit

Sub test()

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Dim targetworkbook As Workbook

Dim usersave As VbMsgBoxResult

Set targetworkbook = Workbooks.Open("C:\Documents and Settings\testingworkbook.xls", UpdateLinks:=0)

Calculate

Application.Run targetworkbook.Name & "!PopulateSheetlist"

response = vbYes

targetworkbook.Activate

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

End Sub
[/vba]
The above code runs fine overall. After " Application.Run targetworkbook.Name & "!PopulateSheetlist"" runs "PopulateSheetlist", There is a vbYesNoCancel prompt that comes up. This vbMsgboxresult is called "response" in "PopulateSheetlist".

I have tried to tell the macro that I want response = vbYes for my purposes, but the alert still pops up and I have to click vbYes manually i.e. response defintion is not automated.

How do I modify the above to tell the macro that it should accept vbYes only.

I know it seems simple, but again, I am stumped.

Any help greatly appreciated.

[tstav]

Hi xluser2007,

Msgbox is a vbmodal dialog form, meaning it takes exclusive focus (suspends code execution) until an answer button is clicked by a living user (not an automated response).

I'm afraid you cannot surpass that. When a msgbox window pops up, it's there for good.

PS. I hope someone would prove that wrong.

[Simon Lloyd]

You cannot pre program a message box reply AFAIK as it was only intended as a tool, a way of interupting the code momentarily to supply you with information. better off if you use a userform made to look like a message box!

[xluser2007]

Hi tstav and Simon!

As usual thank you for your helpful replies.

Dang! I was hoping that there was going to be some slick manouvre around this issue without fundamentally aletring the "PopulateSheetlist" macro, unforynuately it is not my personal ownership, so can't change it that way.

I had tried to sneak in an Application.DisplayAlerts = False statement before the vbMsgbox came up, but no effect! (as you both had explained why).

Simon, with regards to your specific suggestion:

better off if you use a userform made to look like a message box!

I don't quite follow exactly. Do you mean actually going into the "PopulateSheetlist" macro and editing the vbMsgBox into a customised Userform? If this is the case, I can;t do it for the above-mentioned reason, but would be great to know for future design purposes. Please let me know.

For the problem at hand, it is just a matter of passing through the "response" = vbYes. Is there no way to pre-define it, and when a vbMsgBox prompt comes up, pass it through as previously defined? Rather than define it after as I've done above.

Again thank you very much for your helpful contributions. I'm convinced that we can sneak a workround, but alas I may be over-ambitious !

regards.

[rory]

What response are you trying to send? Have you tried using SendKeys before your Application.Run statement (if you only have one MsgBox to worry about)?

[xluser2007]

Hi rory,

It's just one Msgbox (at this stage ), and it is simply a vbYes.

That is, from my code in post #1, I have:

[vba]Application.Run targetworkbook.Name & "!PopulateSheetlist" [/vba]
As this macro runs, it comes up with a single vbYesNoCancel msgbox.

The variable for this prompt is called "response" in this macro. I want to set response = vbYes for this single MsgBox.

Also, I am a newb on this forum and have only heard of Sendkeys. I know it's something like sending manual keystrokes, but haven;t a clue how to code them. Could you kindly help and teach me accordingly.

regards.

[rory]

I don't really like SendKeys but it may help you in this instance if you can't change the called macro. Try changing your code to this:
[VBA]SendKeys "%Y"
Application.Run targetworkbook.Name & "!PopulateSheetlist"[/VBA]

and see if that helps.

[xluser2007]

Hi rory,

Unfortunately that didn't work. I tried putting it in as follows as well, and similarly didn't work:

[VBA]
Application.Run targetworkbook.Name & "!PopulateSheetlist"
SendKeys "%Y" [/VBA]

Any ideas on how it may work? Looks promising for this case.

The vbYesNoCancel prompt still keeps flashing as per usual.

[Simon Lloyd]

xluser2007 considering the workbook isn't yours and you want to "sneak" in a response wouldn't it be better to approach the author and ask them about access to their workbook? they can work in an access for your username so that it skips the particular msgbox!

[Bob Phillips]

This can be flaky, but works for some

[vba]

'----------------------------------------------------------------
Sub TimedMsgBox()
'----------------------------------------------------------------
Dim cTime As Long
Dim WSH As Object

Set WSH = CreateObject("WScript.Shell")
cTime = 5 '5 secs
Select Case WSH.Popup("Open an Excel file?!", cTime, "Question", vbOKCancel)
Case vbOK
MsgBox "You clicked OK"
Case vbCancel
MsgBox "You clicked Cancel"
Case -1
MsgBox "Timed out"
Case Else
End Select
End SUb
[/vba]

[Bob Phillips]

Hi rory,

Unfortunately that didn't work. I tried putting it in as follows as well, and similarly didn't work:

[VBA]
Application.Run targetworkbook.Name & "!PopulateSheetlist"
SendKeys "%Y" [/VBA]

Any ideas on how it may work? Looks promising for this case.

The vbYesNoCancel prompt still keeps flashing as per usual.

You might need a macro in targetworkbook that does the Sendkeys, and app.run that as well.

[rory]

It may be a timing issue - currently I can't find a way round it, but I will let you know if I do.

[xluser2007]

xluser2007 considering the workbook isn't yours and you want to "sneak" in a response wouldn't it be better to approach the author and ask them about access to their workbook? they can work in an access for your username so that it skips the particular msgbox!

Simon, I think you have clearly misunderstood my words.

Firstly and foremostly, I meant "sneak" a workaround in my code above, not the other workbook which I am opening up!

I would never ever tamper with anyones work without their permission. If I was that type of person, there is no need for me to go and learn new techniques from experts here.

Also, for your kind information, I co-authored the workbook and would have have the courtesy ask permission of the other authors if I wanted to make a change to it.

In future please clarify before making rather loaded statements like the above!

[xluser2007]

rory, xld,

Thank you very much for your helpful replies.

rory, thanks for your efforts. If you do find another way, please do let me know.

xld...Bob, please pardon my lack of understanding but how do I integrate your macro (below) into my code. Or do I need to put this into the other workbook that I'm opening up i.e. targetworkbook.

[VBA]
'----------------------------------------------------------------
Sub TimedMsgBox()
'----------------------------------------------------------------
Dim cTime As Long
Dim WSH As Object

Set WSH = CreateObject("WScript.Shell")
cTime = 5 '5 secs
Select Case WSH.Popup("Open an Excel file?!", cTime, "Question", vbOKCancel)
Case vbOK
MsgBox "You clicked OK"
Case vbCancel
MsgBox "You clicked Cancel"
Case -1
MsgBox "Timed out"
Case Else
End Select
End Sub [/VBA]

Although I did work on creating the targetworkbook, I really don;t think I can amend it. They are many such books and changing all of them would be a painstaking effort indeed.

Do your suggestions necessarily require the targetworkbook be amended.

Again thanks for your time on this, appreciate it.

[rory]

You could try this:
[VBA]Option Explicit
Public Declare Function SetTimer& Lib "user32" (ByVal hwnd&, _
ByVal nIDEvent&, ByVal uElapse&, ByVal lpTimerFunc&)
Private Declare Function KillTimer& Lib "user32" (ByVal hwnd&, _
ByVal nIDEvent&)
Public Const NV_INPUTBOX As Long = &H5000
Public Sub TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idEvent As Long, ByVal dwTime As Long)
SendKeys "%Y"
KillTimer hwnd, idEvent
End Sub
Sub test()

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Dim targetworkbook As Workbook

Dim usersave As VbMsgBoxResult

Set targetworkbook = Workbooks.Open("C:\sendkeys_test3.xls", UpdateLinks:=0)

Calculate
targetworkbook.Activate
SetTimer 0, NV_INPUTBOX, 1000, AddressOf TimerProc
Application.Run targetworkbook.Name & "!tester"

' response = vbYes

targetworkbook.Activate

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = True
End With

End Sub
[/VBA]

Save your work first just in case!

[xluser2007]

rory, that works beautifully!

Thanks a ton. Tried it on a test version.

Now I jus got to figure out how it works .

You seem to be setting up some kind of time window after the Msgbox pops up for Send Keys to Act, but can't get my head around it. Possibly better to rest on it. If you have the time, coudl you give a brief explanation.

I'm sure there are other keen beans that would like to know as well.

regards

[rory]

That's basically it, actually.
You set a timer (the 1000 is the number of milliseconds delay) and tell it what function to call when the timer elapses. That called function then performs the SendKeys and then clears the timer.

[tstav]

Well....... now that the case is closed and I for once am very happy because rory made my hope come true (post#2), and I'm sure xluser2007 is a lot happier than me, I would like to say a little something on the following.

Simon, in future please clarify before making rather loaded statements like the above!

xluser, really, how would you expect Simon (or anyone else in that case) to clarify something we cannot lay our hands on? Please remember, all we've got is words. Words coming out either as questions/requirements, or as explanations.
I say "please give me some code to break into my password protected workbook, because I foolishly have forgotten my own password".
All you've got, xluser, is my words, my requirements and my explanations.
Since you cannot prove the honesty or dishonesty in my words, I say it would be best for me (the one asking for help) to at least admit that I may be bringing you to a difficult situation.
Repremanding the ones who (for only good reasons) are expressing their thoughts, for making loaded statements? ........................

I would never ever tamper with anyones work without their permission. If I was that type of person, there is no need for me to go and learn new techniques from experts here.

But... quite the opposite! That would be exactly the reason for anyone to go find experts to teach them new techniques!
Remember please, that now, even YOU cannot "protect" a file of your own with a messagebox because many have learned a way to surpass it... (that is, if you ever decided to "protect" a file with a msgbox -highly unlikely I think).
I hope you won't misunderstand any of my words. Please don't. I'm just stating that I'm trying to be double careful with anything that might even whisper doubt in my ears... And I'm sure you would be too.
My very best regards
tstav

[Simon Lloyd]

tstav, thanks!, to tell you the truth i couldn't be bothered to explain that i meant no malice, its a perception thing! but you explained very eliquently.

[xluser2007]

I hope you won't misunderstand any of my words. Please don't. I'm just stating that I'm trying to be double careful with anything that might even whisper doubt in my ears... And I'm sure you would be too.

tstav, Firstly thank you for your post.

I know you mean know 'malice' , but you only want explain clearly what was meant in Simons post. So I understand clearly the situation now.


Please note: When I meant seek clarification in my post to Simon, I mean just ask a question like "by sneak do you mean to change someone elses code without theire permission? This way it does not asume that the OP is doing something wrong, but puts the onus on the Op, (me in this case) to clearly explain their reasons.

It is the same as asking someone "Is this question for homework". Straight away, without assuming any wrongdoing, you expect the OP to own up. Yes only words, but if they are found to lie in some way e.g. cross post, they will be banned and suffer as a result. That is all I meant. I hope this perspective is not taken in any wrong spirit.

tstav, thanks!, to tell you the truth i couldn't be bothered to explain that i meant no malice, its a perception thing! but you explained very eliquently.

Simon, mate, I realise it was a perception thing. I didn't mean to be so blunt, but I was genuinely trying to nip any thoughts of wrong-doing in the bud. Although you phrased it as a question (not a statement as I had said), I interepreted it to be insinuating that I would "sneakily" tamper with others work.

Yes all we've got is our words to explain ourselves, and so words is what I used to clarify, albeit very bluntly. And for being to blunt, and if I appeared rude to you, I apoligise, but not for trying clarifying my side (as it needed to be said for anyone else who may have interpreted my actions as tampering).

I'll just say, I really do enjoy coming on here and learning from you all, including you Simon .

I think that I try to be courteous and respectful to all who kindly help me and hope that this does not deter from the fact that i am a genuine learner and want to improve and that you all continue help me in this process.

regards