Macro to select vbYesNo when calling another Macro

Have you considered simply modifying the macro by removing the code that calls the prompts, and that would solve your request, seeing as you want to say Yes or OK anyway, why prompt in the first place?

Hi Tom,

Thanks as always, for your prompt (no pun intended) reply.

The macro that I'm calling in the workbook is used by others and I. I require for my purposes the prompt to be vbYes always for the first macro, others may not, so deleting is not an option.

Similar argument for Macro 2.

Is there a way to do this easily?

Just off the top of my head, which is getting easier to see with hair loss, you can surround the prompt with a conditional If structure to only appear if the username is not you.

Example:

If Application.UserName <> "exceluser2007" then

the code for prompt goes here

end if



Code continues here, where you forced the code's execution to skip to this point because your username is "exceluser2007".


Theory-wise, not seing your macro to suggest specifics, that's one way to go about bypassing the prompts.

Hi Tom,

Firstly, this makes it post number 100 on this awesome forum!

Thanks for your suggestion. A few clarifications:

If Application.UserName <> "exceluser2007" then

the code for prompt goes here

end if

Click to expand...

I'm not sure what you mean by the "code for the prompt goes here". This "code" is intrinsically built into the first macro i.e "PopulateSheetlist" for example. If it is me "exceuser2007" riunning the code, I'm not quite sure how the above will by pass the prompts, or how to separate the "prompt code" in each of the macros.

As an alternative thought, I was wondering, similarly to how we can loop through Worksheets(i), where i is an integer representing a left-to-right ordering, is there something like a prompts(i) style command.

That is as a pseudo-code example only:


Is there a way to properly syntax the italicized pseudo-code above? I know there is an object called vbMsgboxresult, but don;t know how to work with it above, any ideas on this approach?

Thanks for your patience.

regards

These prompts (presumably Message Boxes and Input Boxes) are such that they temporarily suspend macro execution until a flesh and blood user actually does something, usually clicking Yes, No, or entering some string in an InputBox and clicking OK or Cancel.

Or, you might mean those Excel prompts that pop up when you want to delete a sheet, in which case you can surround the deletion codeline with

Application.DisplayAlerts = False

code to delete

Application.DisplayAlerts = True


Again, I am at a disadvantage, not seeing your code because you have not posted it, so I am unable to say for sure what you can do because I haven't seen the code. I suggest you post your macros and point out where you want to not see the prompts.

It's almost 2:30 AM here in San Francisco, been a long day, so I will be off the board until later today. I'll look in on this thread and try to help if you still need assistance with it at that time.

Hi Tom,

Thanks for your persistence, appreciate it.

Firstly:

These prompts (presumably Message Boxes and Input Boxes) are such that they temporarily suspend macro execution until a flesh and blood user actually does something, usually clicking Yes, No, or entering some string in an InputBox and clicking OK or Cancel.

Click to expand...

Yes, these are exactly the types of prompts I'm referring to, not like the Updatelinks prompt that Excel generates when you opena new workbook with external references.


Also, you requested for example code, please find it below:


Note: I initially didn't put this code up, because I wanted to know whether it is possible to loop through vbMsgBoxResult objects (using general numbered references), and pre-selecting a response.

Aside from my specific question of pre-selecting prompts to the macro above, please let me know if the general looping is possible with vbMsgbox results.

Speak to you tomorrow.

regards,

Following up on my suggestion to bypass the prompts if it is you running the macro on your machine, then invoke your username to determine if you can bypass the prompts or, if another person is running the amcro, they will see the prompts.

Below is your code, which I modified where I bolded it to show how you can do this.

Note, you will probably need to modify the username because your name really is not exceluser2007. To see your username, press Alt+F11 to get into the VBE, then press Ctrl+G to show the Immediate Window.

Type in
? Application.Username
and press Enter
which will show your username that Excel will recognize for purposes of this macro.

Modify the below macro's codeline
myUserName = "exceluser2007"

to whatever your username is, depending on what the Immediate Window revealed, example
myUserName = "John Doe"

When you are done, press Alt+Q to return to the worksheet.


Your macro, modified where bolded:



Sub PopulateSheetlist()

Dim myUserName$
myUserName = "exceluser2007"

If Application.UserName <> myUserName Then

If Not ActiveWorkbook.Saved Then

usersave = MsgBox("Changes have been made since document last saved." & Chr(13) & Chr(13) & _
"Yes - Proceed, but save changes first" & Chr(13) & _
"No - Proceed, but lose changes once finished" & Chr(13) & _
"Cancel - To stop", vbYesNoCancel, "Save changes")

'MsgBox vbYes & "" & vbNo & "" & vbCancel & " " & usersave

If usersave = vbYes Then
ThisWorkbook.Save
ProduceOutput = True
ElseIf usersave = vbNo Then
ProduceOutput = True
Else: ProduceOutput = False
End If

End If

End If

ThisWorkbook.Save

If Application.UserName = myUserName or ProduceOutput = True Then DoOutput

End Sub

Thanks for your reply Tom and your code.

From the code you've given, I think I may need to better re-explain my scenario, just so I can clarify that it is doing what I would like to.

Basically I have a workbook called master.xls. This is going to have a workbook path e.g. C:\Targetworkbook.xls stored as a string in say Sheet1 A1, as an example.

master.xls will have some macros in it. One of the macros in master.xls, lets call it macro "automateshtprompt", will read this workbook fullpath in Sheet1 A1, and open the workbook. In this example it will run 1 macro within the Targetworkbook.xls i.e. Application.Run "Targetworkbook.xls!'PopulateSheetlist" for example.

Ordinarily, if a flesh and blood user (great description btw) was sitting there as this ran, there would be a vbYesNoCancel prompt that PopulateSheetlist would inititate. I want the "automateshtprompt" to automatically select yes to the Msgbox prompt that pops up when the PopulateSheetlist sheet list is run in the opened workbook. That is "automateshtprompt" would do the job of the flesh and blood user and click vbYes to the prompts that come up.

Now your solution, although good, required the PopulateSheetlist macro to be modified. This I can't do in practice, as it exists in the Targetworkbook.xls which is not my ownership. I can simply run stuff from Targetworkbook.xls in this case. If I were allowed to edit the macros in Targetworkbook.xls to my specifications, then your solution is fantastic, and noted for future use and good macro design.

However, given that I can only write and control a macro such as "automateshtprompt" in master.xls, how can modify the code in "automateshtprompt" to achieve the same effect of clicking vbYes always for the PopulateSheetlist macro prompts in the targetworkbook.

Again, sorry if this wasn't clear earlier, I realised I may have framed my initial question poorly.

Thanks for your time on this, hoping to learn from you if what I'm saying is possible.

Thought I would give this a bump.

My problem is best described succinctly as follows:

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


For more details please refer to the post #9 as above.

Thanks, will appreciate any help with this and again sorry if it wasn't clear in post #1.

regards