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?
 
leonstrykerConnect With a Mentor Commented:
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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.