Userform - what am I missing?

[salgud]

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!

 

[Chip Pearson]

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)

 

[Salgud]

Thanks for your reply. My response inline:

The problem is with the line


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?

 

[Chip Pearson]

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)

 

[salgud]

On Sat, 14 Mar 2009 14:49:36 -0500, Chip Pearson wrote:
Thanks, Chip! Reply inline

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.