Solved

Form that prompts within a macro

Posted on 2007-04-06
13
259 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

707 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

16 Experts available now in Live!

Get 1:1 Help Now