Solved

Form that prompts within a macro

Posted on 2007-04-06
13
263 Views
Last Modified: 2010-04-30
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
0
Comment
Question by:PatternNut
  • 6
  • 5
  • 2
13 Comments
 
LVL 23

Expert Comment

by:ahammar
ID: 18863901
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
 
LVL 23

Expert Comment

by:ahammar
ID: 18863913
Actually it would be the Query_Close or Terminate events you would have to check.  There is no Close event in Excel.

:-)
ah
0
 

Author Comment

by:PatternNut
ID: 18863958
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 29

Expert Comment

by:leonstryker
ID: 18863964
Could you show the code which call the second form please.
0
 

Author Comment

by:PatternNut
ID: 18864024
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
 

Author Comment

by:PatternNut
ID: 18864108
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 18864136
What does your code look like afterwards?

Leon
0
 

Author Comment

by:PatternNut
ID: 18864326
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
 
LVL 29

Accepted Solution

by:
leonstryker earned 500 total points
ID: 18865136
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
 

Author Comment

by:PatternNut
ID: 18865316
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 18865347
>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
 

Author Comment

by:PatternNut
ID: 18867198
Thanks for all the help Leon,

Cheers,

PatternNut
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 18867220
Sure, thanks for the grade,

Leon
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now