Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform - what am I missing?
I've got a program that calls a userform and then processes the input, or
should. But control doesn't return after it goes to the userform, it just ends but says it's "running" as thought it still is, though it isn't. Here's the beginning of the main program: Sub CreateTribalSheet() Set wbTribal = ThisWorkbook Set wsSource = wbTribal.Sheets("Source") bDataEnt = False bCancel = False bFinish = False bNewData = False If ThisWorkbook.Name = ActiveWorkbook.Name Then MsgBox _ "Do not use this workbook to create your reports." _ & Chr(10) & _ "Open a previously used workbook or start" _ & Chr(10) & _ "a new one for the current fiscal year" End End If Application.ScreenUpdating = False ' Get facility name and no. of records from user lFacilRowsUI = 0 Do 'Show the Facility entry form frmFacil.Show Here's the frmFacil code: Private Sub btnCancel_Click() bCancel = True Unload frmFacil End End Sub Private Sub btnFinish_Click() sFacilNameUI = frmFacil.tbFacilName.Text On Error Resume Next lFacilRowsUI = frmFacil.tbFacilRows.Value On Error GoTo 0 If sFacilNameUI < "" And lFacilRowsUI < 0 Then bNewData = True bFinish = True Else If bDataEnt = False Then MsgBox "Please enter a both a Facility Name and" & Chr(10) & _ "the number of clients served!", vbOKOnly Else bNewData = False bFinish = True End If End If End Sub Private Sub cbNext_Click() sFacilNameUI = frmFacil.tbFacilName.Text On Error Resume Next lFacilRowsUI = frmFacil.tbFacilRows.Value On Error GoTo 0 If sFacilNameUI < "" Or lFacilRowsUI < 0 Then bNewData = True bFinish = False Else MsgBox "Please enter a both a Facility Name and" & Chr(10) & _ "the number of clients served!", vbOKOnly End If End Sub Private Sub UserForm_Click() tbFacilName.SetFocus End Sub So why isn't it going back to sub CreateTribalSheet after it captures the data? It just hangs, whether I click "Next" or "Finish" (two of the buttons on the userform). Any suggestions? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform - what am I missing?
The problem is with the line
frmFacil.Show This shows the form "modally", which means that code execution halts in the main procedure until the form is hidden or unloaded. Code within the form itself will run, but the line after the Show method will not run until the form is dismissed. You can show the form "modelessly", in which case the form will be shown but code execution will continue on once the form is shown. frmFacil.Show vbModeless Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 13 Mar 2009 15:50:11 -0600, salgud wrote: I've got a program that calls a userform and then processes the input, or should. But control doesn't return after it goes to the userform, it just ends but says it's "running" as thought it still is, though it isn't. Here's the beginning of the main program: Sub CreateTribalSheet() Set wbTribal = ThisWorkbook Set wsSource = wbTribal.Sheets("Source") bDataEnt = False bCancel = False bFinish = False bNewData = False If ThisWorkbook.Name = ActiveWorkbook.Name Then MsgBox _ "Do not use this workbook to create your reports." _ & Chr(10) & _ "Open a previously used workbook or start" _ & Chr(10) & _ "a new one for the current fiscal year" End End If Application.ScreenUpdating = False ' Get facility name and no. of records from user lFacilRowsUI = 0 Do 'Show the Facility entry form frmFacil.Show Here's the frmFacil code: Private Sub btnCancel_Click() bCancel = True Unload frmFacil End End Sub Private Sub btnFinish_Click() sFacilNameUI = frmFacil.tbFacilName.Text On Error Resume Next lFacilRowsUI = frmFacil.tbFacilRows.Value On Error GoTo 0 If sFacilNameUI < "" And lFacilRowsUI < 0 Then bNewData = True bFinish = True Else If bDataEnt = False Then MsgBox "Please enter a both a Facility Name and" & Chr(10) & _ "the number of clients served!", vbOKOnly Else bNewData = False bFinish = True End If End If End Sub Private Sub cbNext_Click() sFacilNameUI = frmFacil.tbFacilName.Text On Error Resume Next lFacilRowsUI = frmFacil.tbFacilRows.Value On Error GoTo 0 If sFacilNameUI < "" Or lFacilRowsUI < 0 Then bNewData = True bFinish = False Else MsgBox "Please enter a both a Facility Name and" & Chr(10) & _ "the number of clients served!", vbOKOnly End If End Sub Private Sub UserForm_Click() tbFacilName.SetFocus End Sub So why isn't it going back to sub CreateTribalSheet after it captures the data? It just hangs, whether I click "Next" or "Finish" (two of the buttons on the userform). Any suggestions? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform - what am I missing?
Thanks for your reply. My response inline:
Chip Pearson wrote: The problem is with the line frmFacil.Show This shows the form "modally", which means that code execution halts in the main procedure until the form is hidden or unloaded. Code within the form itself will run, but the line after the Show method will not run until the form is dismissed. That's exactly what I thought and what I want, but it's not running after I click "Next" or "Finish". You can show the form "modelessly", in which case the form will be shown but code execution will continue on once the form is shown. That's not what I want, at least not if I'm interpreting it correctly. I want to halt execution while the user inputs the data, then continue after they click "Next" or "Finish". I've used userforms before and not had to use "Modeless", so why is that the case here? I'll give it a try Monday when I get back to the office, but I'm not sure this is what I want. Is there another way to get the macro to continue after the user inputs the data? What would happen if I dismissed the form in the form code, rather than after I return to the module? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform - what am I missing?
That's exactly what I thought and what I want, but it's not running
after I click "Next" or "Finish". I assume that "Next" and "Finish" refer to buttons on the userform. Those buttons should (after anything else) Hide the form with Me.Hide or unload the form with Unload Me. If you use Unload, the form is dumped from memory and you cannot access the form's values. If you use Hide, you can still get form values after the form is closed. Do not use "End" (not to be confused with "End Sub" or other "End" code -- this means just "End" by itself). Using "End" terminates everything and dumps everything from memory and completely stops execution. For example, use Private Sub btnFinish_Click() Me.Hide End Sub Do NOT use Private Sub btnFinish_Click() End End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 14 Mar 2009 11:08:21 -0600, Salgud wrote: Thanks for your reply. My response inline: Chip Pearson wrote: The problem is with the line frmFacil.Show This shows the form "modally", which means that code execution halts in the main procedure until the form is hidden or unloaded. Code within the form itself will run, but the line after the Show method will not run until the form is dismissed. That's exactly what I thought and what I want, but it's not running after I click "Next" or "Finish". You can show the form "modelessly", in which case the form will be shown but code execution will continue on once the form is shown. That's not what I want, at least not if I'm interpreting it correctly. I want to halt execution while the user inputs the data, then continue after they click "Next" or "Finish". I've used userforms before and not had to use "Modeless", so why is that the case here? I'll give it a try Monday when I get back to the office, but I'm not sure this is what I want. Is there another way to get the macro to continue after the user inputs the data? What would happen if I dismissed the form in the form code, rather than after I return to the module? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform - what am I missing?
On Sat, 14 Mar 2009 14:49:36 -0500, Chip Pearson wrote:
Thanks, Chip! Reply inline That's exactly what I thought and what I want, but it's not running after I click "Next" or "Finish". I assume that "Next" and "Finish" refer to buttons on the userform. Those buttons should (after anything else) Hide the form with Me.Hide or unload the form with Unload Me. If you use Unload, the form is dumped from memory and you cannot access the form's values. If you use Hide, you can still get form values after the form is closed. Do not use "End" (not to be confused with "End Sub" or other "End" code -- this means just "End" by itself). Using "End" terminates everything and dumps everything from memory and completely stops execution. For example, use Private Sub btnFinish_Click() Me.Hide End Sub That's what I had wrong, I had the "userform.hide" in the module code thinking it would execute when I finished with the uerform. I put "Me.Hide" at the very end of each userform routine and it works fine. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForm is missing the most basic Win controls. WHAT the? | Excel Programming | |||
Is there an easy Copy/Paste of a Userform ? (Entire Userform Including tx & cbx's) | Excel Programming | |||
Toolbars Missing, And option to Add Missing | Excel Discussion (Misc queries) | |||
On Error? Creates 1 missing worksheet then never detects any other missing worksheets | Excel Programming | |||
Looping procedure calls userform; how to exit loop (via userform button)? | Excel Programming |