?
Solved

Form that prompts within a macro

Posted on 2007-04-06
13
Medium Priority
?
271 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Suggested Courses
Course of the Month11 days, 14 hours left to enroll

752 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