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!