Accepting dialog boxes (NOT ignoring them) - how?

[thanos54321]

Hi, I am currently testing an excel addin simulation program called crystal ball 7.1 (

http://www.crystalball.com

if you care), and I've encountered an annoying problem that I hope can be solved by VBA.

Specifically, I am using some inputs for simulation that might be in conflict with each other. When they are, Crystal ball tries to rectify by modifying the inputs. It then presents an excel dialog box showing three options:

1. (default, acceptable by enter) to modify inputs once.
2. to modify inputs permanently.
3. to cancel.

I am convinced that Application.DisplayAlerts = False and Application.EnableEvents = False are no good for this, since it's not an alert, it's an important choice necessary for the program to continue, and therefore, it is also a necessary event to deal with. Correct me if I'm wrong?

So, what I'm looking for is a setting/function that accepts multi-option dialog boxes by selecting the default choice. I'm not sure this is possible, but if anyone knows, it's you guys!

Thanks for a great forum, if you can solve this you'll really make my day...

Regards,

Thanos54321

 

[Zathras]

... It then presents an excel dialog box showing three options:...

It sounds like you are already displaying a user form with three buttons for the user to make a choice. Or are you speaking theoretically and that is what you are wanting to do?

There should be no problem displaying a user form and waiting for one of the buttons to be clicked.

Can you explain with a little more detail, and perhaps include some of the code you are currently using?

 

[thanos54321]

Hi again,

I'll explain in more detail.

I am running an add-in that is made by a company called Decisioneering - I didn't make it myself - and the dialog box that pops up is one that they made. Sadly, I can't record it using the macro recorder, or I would.

It works like this: You set up some cells to be the ones that the program (crystal ball, it's called) uses a random number generator to change during each "trial". The user (that's me) then uses the simulation cellvalues as inputs in other cells with formulas, and after all the trials are done, I can get a feel for the uncertainty in my problem by viewing the results of my target formulas for all the trials run during the simulation.

One of the neat options of the program is to force different simulation parameters to be correlated. If I have 3 simulation cells (called "assumptions" by CB) that are all correlated by a factor of 0.8 for example, there is an almost linear relation between the cells. What sometimes goes wrong is if you have three cells, cell one is positively correlated with cell two, and negatively correlated with cell three, but cell two and three are also positively correlated, then this is logically impossible, and CB tells you that your correlations are inconsistent.

This is the dialog box I'm talking about.

And in this dialog box there are three said options to pick and choose from, and I'm trying to force it to select option 1 automatically, if there is such a general possibility in excel (I hope).

I hope it's clearer?

 

[PHV]

Have you tried to play with the SendKeys instruction ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[thanos54321]

Nope, that's not something I've used before. Could you point me in the right direction, a faq or another thread that has some points on this? It sounds promising

 

[PHV]

When in VBE open the Immediate pane (Ctrl-G), type the word sendkeys and press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi thanos54321,

If I understand you correctly, it's the crystalball that's showing the dialog and the crystalball that's receiving the input from it. If that's the case then you may need to use Windows APIs to watch for it and issue the appropriate reply when it's displayed. This is a non-trivial task.

Alternatively, have you actually tried DisplayAlerts? Depending on how the program works it may meet your needs.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[thanos54321]

Hi both of you again,

I've tried both DisplayAlerts and SendKeys now, like this:



Application.DisplayAlerts = False

'reset simulation
cb.ResetND 'this part resets crystal ball simulation

'Get max number of trials from run preferences
NumTrials = cb.GetRunPrefs(1)

cb.Simulation NumTrials

'this runs the simulation for the number of trials. Between this step and the next comes the popup window, which I tried to automatically accept by using sendkeys below. It didn't work.

SendKeys "{ENTER}", True

cb.ExtractDataND cbExtChooseAsm, cbChaAll
cb.ExtractDataND cbExtDataType, cbDatValues
cb.ExtractDataND cbExtExistingSheet, True
cb.ExtractDataND cbExtOK

'The stuff above just extracts the simulation data to a new sheet. I just included it to show that there is some crystalball stuff coming afterwards in the code

Application.DisplayAlerts = True



So I guess you're right about API, whatever that is (now it really shows that I usually work with the more trivial parts of VBA, and in excel only, doesn't it?)

I made a search about this, is correctly understood that it is some sort of procedure that for example looks for a popup box and it's headline, and then does something?

Thanks for the input!

Thanos54321

 

[thanos54321]

BTW, there is about two minutes waiting time between the code:

cb.Simulation NumTrials


and the popup window, because CB is "digesting" all the correlations. Is it possible to make the sendkeys part wait two minutes before it sends the Enter signal?

 

[TonyJollans]

Hi thanos54321,

I would guess that the whole process happens inside the code behind the line:

[blue]cb.Simulation NumTrials[/blue]

DisplayAlerts should affect it if the dialog is displayed via Excel and the cb routine doesn't un-set it. But the cb routine may be invoking Windows services to display the dialog.

Sendkeys will send its keys after the cb routine has finished which would seem to be too late. It might be posible, though somewhat contrived, to delay the SendKeys but, even if you got the timing right, I don't think it would send to the right window (and AFAIK you can't control that).

You are correct in what you say about the API process. Good research!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[thanos54321]

Hi again,

you're probably right, hmmm, I guess I better go read up on API, then.

Thanks for all the input! You guys are the best.

 

[Zathras]

It wasn't pretty, but one thing I have used is WinBatch (Wilson WindowWare, Inc.) to create a program that ran in the background, waiting and watching for the appearance of a particular window (identified by its title bar), and then sending keystrokes to that window when it appeared. (It then closed itself.)

By launching such an .exe before executing the line that gives control to the simulator, you should be able to do something similar.

It doesn't have to be WinBatch, but that provides all of the functionality you would need for this work-around, without having to dip into any Windows API.

I assume you might not always get that dialog box from the simulator, so you would need to kill the .exe yourself in that case (when you get control back). One way to control that is by creating a file when you launch the .exe and have the .exe delete the file when it terminates. If the file still exists when you get control back from the simulator, you know the dialog didn't appear and so you know to send Alt-[F4] to the .exe to close it. (or if you prefer, the .exe could test for the existence of the file and go away if the file disappears, which you could do with a "Kill" statement in your code. -- Lots of ways to skin a cat.)

 

[thanos54321]

This sounds interesting. There is one issue, however. The point is to repeatedly accept the first option the simulator comes up with, that is, in a for...next structure, this will happen repeatedly, and yes, sometimes it will not happen. Can winbatch solve this?

 

[Zathras]

Don't have it kill itself. Let it stay active and it will continue do whatever you need (i.e. watch for a dialog with a specific title, or partial title.) then have your routine kill it (with Alt-F4 or by using a file as a sentinel). Maybe have it watch the clock and kill itself after 60 minutes or so, just in case.

WinBatch is a very powerful programming language. We use it quite a bit around here.

 

[thanos54321]

Hmmm. Sounds good. I don't know the programming language, however. Got a quickie guide thingy or something I can download, to get me started? Preferably something about clicking one of several buttons in a window, of course?

 

[Zathras]

http://www.winbatch.com/

Don't be intimidated by their web site. There's a lot there. Much more than you will need for your purpose.

The full compiler version is $500, but you can get an interpreter version for $100.

The interpreter version has all of the same features, it's just slower (by the nature of interpreted code vs. compiled code).

 

[thanos54321]

Thanks, I'll take a peek, see if I can figure it out.