I'm trying to do something that seems simple but I can't get it to do what
I want. I query the user for 3 pieces of information using 3 comboxes. The
userform code for the "Enter" button to capture the data is:

Private Sub cbEnter_Click()
sTribeNameUI = frmTribeNameSMCY.cbTribeName.Text
sCYUI = frmTribeNameSMCY.cbCY
sServMonthUI = frmTribeNameSMCY.cbServMonth


If sTribeNameUI = "" Or sCYUI = "" Or sServMonthUI = "" _
Or sCYUI = "" Then
MsgBox "Please select a Tribe Name, a Service Month and" & Chr(10) & _
" a Calendar Year!", vbOKOnly
With Me.cbTribeName
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
Me.Hide
End If

End Sub

The subroutine that calls the userform is:

Public Sub TribeNameServDate()
Application.ScreenUpdating = True
frmFacil.Hide
frmTribeNameSMCY.Show
Application.ScreenUpdating = False

Unload frmTribeNameSMCY

ws.Range("A1").Value = sTribeNameUI & " Turnaround Report"

ws.Range("D3").Value = sServMonthUI

iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))

iPayrollMonth = iServMonth + 1

If iPayrollMonth 12 Then
iPayrollMonth = iPayrollMonth - 12
End If

sPayrollMonth = Format(28 * iPayrollMonth, "MMM") 'converts integer month
to text month
ws.Range("C3").Value = sPayrollMonth

If iPayrollMonth < 6 Then
sSFY = Right(sCYUI, 2)
Else
sSFY = Right(sCYUI + 1, 2)
End If

ws.Range("J3:K3").Select
Selection.NumberFormat = "@"
ws.Range("J3") = Right(sCYUI, 2)
ws.Range("K3") = sSFY
ws.Range("A1").Select

End Sub

But this stops after the message box warns the user they haven't entered at
least one of the required pieces of data. I want to return them to the
userform to give them another change to fill it in. Does anyone see how I
can modify this code, either in the userform or the other sub, or both, to
get the program to return to the userform, ready to continue with the
program, if they fail to enter the requisite data?
Thanks!

What about putting the msgbox code into a While loop and move the
Me.Hide to outside the loop, eg

While sTribeNameUI = "" Or sCYUI = "" Or sServMonthUI = "" _
Or sCYUI = ""
MsgBox "Please select a Tribe Name, a Service Month and" & Chr(10) &
_
" a Calendar Year!", vbOKOnly
With Me.cbTribeName
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
Wend

me.Hide

You may want to give the user and option to cancel and exit

Cheers,
Andrew

Hi

You don't want to hide the userform if user still need to enter data:

Look at this:

--- cut ---
If sTribeNameUI = "" Or sCYUI = "" Or sServMonthUI = "" _
Or sCYUI = "" Then
MsgBox "Please select a Tribe Name, a Service Month and" & Chr(10) & _
" a Calendar Year!", vbOKOnly
With Me.cbTribeName
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
Else
Me.Hide
End If

End Sub

Regards,
Per

"salgud" skrev i meddelelsen
.. .
I'm trying to do something that seems simple but I can't get it to do what
I want. I query the user for 3 pieces of information using 3 comboxes. The
userform code for the "Enter" button to capture the data is:

Private Sub cbEnter_Click()
sTribeNameUI = frmTribeNameSMCY.cbTribeName.Text
sCYUI = frmTribeNameSMCY.cbCY
sServMonthUI = frmTribeNameSMCY.cbServMonth


If sTribeNameUI = "" Or sCYUI = "" Or sServMonthUI = "" _
Or sCYUI = "" Then
MsgBox "Please select a Tribe Name, a Service Month and" & Chr(10) & _
" a Calendar Year!", vbOKOnly
With Me.cbTribeName
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
Me.Hide
End If

End Sub

The subroutine that calls the userform is:

Public Sub TribeNameServDate()
Application.ScreenUpdating = True
frmFacil.Hide
frmTribeNameSMCY.Show
Application.ScreenUpdating = False

Unload frmTribeNameSMCY

ws.Range("A1").Value = sTribeNameUI & " Turnaround Report"

ws.Range("D3").Value = sServMonthUI

iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))

iPayrollMonth = iServMonth + 1

If iPayrollMonth 12 Then
iPayrollMonth = iPayrollMonth - 12
End If

sPayrollMonth = Format(28 * iPayrollMonth, "MMM") 'converts integer month
to text month
ws.Range("C3").Value = sPayrollMonth

If iPayrollMonth < 6 Then
sSFY = Right(sCYUI, 2)
Else
sSFY = Right(sCYUI + 1, 2)
End If

ws.Range("J3:K3").Select
Selection.NumberFormat = "@"
ws.Range("J3") = Right(sCYUI, 2)
ws.Range("K3") = sSFY
ws.Range("A1").Select

End Sub

But this stops after the message box warns the user they haven't entered
at
least one of the required pieces of data. I want to return them to the
userform to give them another change to fill it in. Does anyone see how I
can modify this code, either in the userform or the other sub, or both, to
get the program to return to the userform, ready to continue with the
program, if they fail to enter the requisite data?
Thanks!

You're right - no need for the loop in this context - another case of
typing faster than my brain.

On Tue, 7 Apr 2009 17:09:34 +0200, Per Jessen wrote:

Hi

You don't want to hide the userform if user still need to enter data:

Look at this:

--- cut ---
If sTribeNameUI = "" Or sCYUI = "" Or sServMonthUI = "" _
Or sCYUI = "" Then
MsgBox "Please select a Tribe Name, a Service Month and" & Chr(10) & _
" a Calendar Year!", vbOKOnly
With Me.cbTribeName
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
Else
Me.Hide
End If

End Sub

Regards,
Per

"salgud" skrev i meddelelsen
.. .
I'm trying to do something that seems simple but I can't get it to do what
I want. I query the user for 3 pieces of information using 3 comboxes. The
userform code for the "Enter" button to capture the data is:

Private Sub cbEnter_Click()
sTribeNameUI = frmTribeNameSMCY.cbTribeName.Text
sCYUI = frmTribeNameSMCY.cbCY
sServMonthUI = frmTribeNameSMCY.cbServMonth


If sTribeNameUI = "" Or sCYUI = "" Or sServMonthUI = "" _
Or sCYUI = "" Then
MsgBox "Please select a Tribe Name, a Service Month and" & Chr(10) & _
" a Calendar Year!", vbOKOnly
With Me.cbTribeName
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
Me.Hide
End If

End Sub

The subroutine that calls the userform is:

Public Sub TribeNameServDate()
Application.ScreenUpdating = True
frmFacil.Hide
frmTribeNameSMCY.Show
Application.ScreenUpdating = False

Unload frmTribeNameSMCY

ws.Range("A1").Value = sTribeNameUI & " Turnaround Report"

ws.Range("D3").Value = sServMonthUI

iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))

iPayrollMonth = iServMonth + 1

If iPayrollMonth 12 Then
iPayrollMonth = iPayrollMonth - 12
End If

sPayrollMonth = Format(28 * iPayrollMonth, "MMM") 'converts integer month
to text month
ws.Range("C3").Value = sPayrollMonth

If iPayrollMonth < 6 Then
sSFY = Right(sCYUI, 2)
Else
sSFY = Right(sCYUI + 1, 2)
End If

ws.Range("J3:K3").Select
Selection.NumberFormat = "@"
ws.Range("J3") = Right(sCYUI, 2)
ws.Range("K3") = sSFY
ws.Range("A1").Select

End Sub

But this stops after the message box warns the user they haven't entered
at
least one of the required pieces of data. I want to return them to the
userform to give them another change to fill it in. Does anyone see how I
can modify this code, either in the userform or the other sub, or both, to
get the program to return to the userform, ready to continue with the
program, if they fail to enter the requisite data?
Thanks!

Thank you both, problem solved. Now I can take this item off my desk and
move on.