Form that prompts within a macro

Hi Experts,

I have a VBA macro running off a userform and need to prompt for user input.  I used to do it with a msgbox, but I'm trying to make it a bit more sophisticated.  I have made a form with "yes" and "no" buttons and a checkbox.  At the moment, users click a button on the main form.  That used to ask for a prompt like this:

If msgbox("This will make changes without a chance to check each one.  Are you sure you want to continue?", vbYesNo, "Change all") = vbYes Then
'run sub
end if

Now I want it to show a form and wait for their answer:  I load the form and show the form... the buttons are available and the variable from the tickbox feedbacks correctly into the main form .  And when they press the buttons, the subform hides itself and everything is fine.  However, if they choose to terminate the form (as in the close box in the upper right hand corner), the whole program ends, not just the subform.  This leads me to believe I have done something very wrong!

How should I link this subform (prompt) to the main form?  Where did this go wrong?

Thanks,

PatternNut
PatternNutAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ahammarCommented:
I assume you are using Excel or Word since you said VBA
Do you have any code in the subforms Close or Query_Close events that would end the program?
0
ahammarCommented:
Actually it would be the Query_Close or Terminate events you would have to check.  There is no Close event in Excel.

:-)
ah
0
PatternNutAuthor Commented:
Hi Ahammar,
Yup, I'm running Word and nope. the subform has no close / terminate events.

It works fine if I run the subform as modeless... but then the user doesn't have to answer the prompt so you can end up stuck with a floating prompt for no good reason.

Cheers,

PatternNut
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

leonstrykerCommented:
Could you show the code which call the second form please.
0
PatternNutAuthor Commented:
Of course Leon,

            If blnContinueWarning = True Then
                Load ContinueYesNo
                ContinueYesNo.ShowAgainCheck.Value = False
                ContinueYesNo.Show

blncontinuewarning is the status of my checkbox
ContinueYesNo is the name of my form
ShowAgainCheck is the name of the checkbox

0
PatternNutAuthor Commented:
Ahhh, one other thing that may be important. My main useform has a showmodal property of false... that is, the main one you can jump back and forward between Word and the form.
0
leonstrykerCommented:
What does your code look like afterwards?

Leon
0
PatternNutAuthor Commented:
That gets awful complicated... happy to paste it here, but it has the same problem even if I have no code after.

It's true for all subforms that I attempt to run.  One is just an "About" box... user clicks a button, an about form comes up (no buttons this time).  When the user presses the upper right hand "X" then both forms close and the whole program ends.

I'm sure there is something basic and fundamental that I am not doing... or that relates to these showmodal properties, I just have not had to do this before.

Thanks,

PatternNut
0
leonstrykerCommented:
Seems like you have a crash bug there, which may not have anything to do with your code.  Try this as a test:

Add two user forms (UserForm1 and UserForm2)

Open a new workbook and put a button on the spreadsheet and place this code behind it: UserForm1.Show

On UserForm1 place a button and this code UserForm2.Show

Now, click the button on the spread sheet. this should open UserForm1. Click the button on the form to open UserForm2. Hit the X on UserForm2.

If this shuts down your Excel, then you will need to reinstall it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatternNutAuthor Commented:
Hi Leon,

Thanks for this message... we're definitely closer!!!  Not got it yet, but I found another clue!

I tried that in Excel and it worked fine.  So I went into Word and started a whole new document with a new form.  The new main form is modeless as before.  It had oen button which launched a second form (a modal subform)... all worked perfectly.  I closed the subform and then the macro behind carried on running.  No termination.

So then I went back to my project in Word.  I added a new button to the mainform.  I linked that to show a new form (the subform with a showmodal = true).  It launced the subform... but it didn't stop when it showed it... so this code:

Private Sub AboutButton_Click()
Load AboutForm
AboutForm.Show
msgbox "hello"
End Sub

that showed me the aboutform and the dialog with "hello".    

Why would it do that?  The new form in the new document ran the exact same code with the exact same type of forms and did not show "hello" until the subform was closed.

Thanks for all your help on this... I definitely think we're nearer.  Any idea now?

Cheers,

PatternNut


0
leonstrykerCommented:
>Why would it do that?  The new form in the new document ran the exact same code with the exact same type of forms and did not show "hello" until the subform was closed.

Who knows, the file could have gotten corrupted, or it was just having a bad day. I would say copy all of the important info into a new file and port the code as well. If the problem does not resurface, call it a day and forget about it.

Leon
0
PatternNutAuthor Commented:
Thanks for all the help Leon,

Cheers,

PatternNut
0
leonstrykerCommented:
Sure, thanks for the grade,

Leon
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.