Quick question about answer question on pop ups during running macro

[darbebo]

Hello,

This is a little different from the Display alerts = false coding i believe.
Bear with me here.........

Currently within our excel workbook that we have created in-house, we have added an add-in that allow us to select something from a drop down from a custom tool bar and it will populate the information we requested on a new excel sheet

So let's say I only have sheet 1 and i'm using that. Then i want information on "list of people's name and address", i go to the drop down, select that option, and that list of information is then populated for me on a newly created sheet 2 (which it will also rename that tab accordingly as well) - pretty neat.

The only exception is, if the add-in senses that I am already in a pre-saved file (not a brand new unsaved draft) it will ask if i want to create that sheet 2 on the current file that I'm in or output it on a completely new excel worksheet - also pretty neat.

I am presented with a Yes and No choice on the pop up prompt.

All is well until i need to create a macro in vba that needs to call that function but the macro stops when the prompt shows up. Is there anyway I can "answer" to the prompt?

the displayalert = false doesn't seem to work as i've tried that, and bypassing it doesn't tell the macro if i want the information displayed on the current file i'm on or a new worksheet.

How can i tell it to make that choice?

Am i stuck?

Thanks!

 

[ghrain22]

It's hard to tell with the given information. I don't know what kind of prompt it is or where it comes from. With the lack of information, I guess the easiest solution I could think of is to sendkey. Usually there's a default that can be selected with enter, which you could do via VBA.

https://msdn.microsoft.com/en-us/library/office/ff821075.aspx

[COLOR=#333333]Application.SendKeys "{ENTER}" [/COLOR]

 

[davesexcel]

You need an "if" statement in your code:

if something then
do this
else
do that
end if

application.displayalerts won't work as the msgbox comes from the code not from excel.

 

[darbebo]

You need an "if" statement in your code:

if something then
do this
else
do that
end if

application.displayalerts won't work as the msgbox comes from the code not from excel.

guys, im very sorry about the vagueness because it is proprietary i guess, and i am not good at describing as well, but davesexcel is right in that this is a popup/prompt that is put in place from an addin / another macro/something that is not innate from ms-excel.

Is there such a code that goes "if POPUP shows up" then....?

as far as using the application send, i can also give that a try as well


the biggest issue is that we do our work in a addin ribbon format, meaning even though it's a blank new excel sheet, all the macro/functionality that we need to pull client data is already embedded in there. So trying to code vba/macro that utilizes those function is the tough part here - and step one is to get over these prompts...

 

[Eric W]

Do you have the ability to change the add-in? If so, you can add an optional parameter on the function. If the original code was something like:

Public Sub GetReps(MyVal As String)

then you can change it to:

Public Sub GetReps(MyVal As String, Optional MyFlag As String = "N")

Existing callers won't need to change their code, but you can call it like:

Call GetReps("Hi", "Y")

Then use

If MyFlag = "Y' Then
    something
Else
    show msgbox
Endif

like Davesexcel suggested.