Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
It seems so simple...
I've done a number of posts here and tried a dozen different ways, but I
can't get the following code to trap a user input error (no input) and leave the userform open so they can enter the data, then just continue the program. The userform code for if they click "Enter": Private Sub cbEnter_Click() sTribeNameUI = frmTribeNameSMCY.cbTribeName.Text sCYUI = frmTribeNameSMCY.cbCY sServMonthUI = frmTribeNameSMCY.cbServMonth If sTribeNameUI = "" Or sCYUI = "" Or sServMonthUI = "" 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 End If End Sub The main routine: Public Sub CreateTribalSheet() Set wbTribal = ThisWorkbook Set wsSource = wbTribal.Sheets("Source") The following is a subprocedure to enter some data from a userform. 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 The userform has 3 comboboxes (so user's entries are consistent to make it easier to work with the dates) and the code for the Enter button is: Private Sub cbEnter_Click() sTribeNameUI = frmTribeNameSMCY.cbTribeName.Text sCYUI = frmTribeNameSMCY.cbCY sServMonthUI = frmTribeNameSMCY.cbServMonth If sTribeNameUI = "" Or sCYUI = "" Or sServMonthUI = "" _ Or sCYUI = 0 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 End If End Sub What I want to happen is that if any of the 3 data entries are blank, the userform reappears and gives the user another chance to enter the data, and the progam continues as before. How do I do this? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
It seems so simple...
On Mon, 6 Apr 2009 11:04:16 -0600, salgud wrote:
I've done a number of posts here and tried a dozen different ways, but I can't get the following code to trap a user input error (no input) and leave the userform open so they can enter the data, then just continue the program. The userform code for if they click "Enter": Private Sub cbEnter_Click() sTribeNameUI = frmTribeNameSMCY.cbTribeName.Text sCYUI = frmTribeNameSMCY.cbCY sServMonthUI = frmTribeNameSMCY.cbServMonth If sTribeNameUI = "" Or sCYUI = "" Or sServMonthUI = "" 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 End If End Sub The main routine: Public Sub CreateTribalSheet() Set wbTribal = ThisWorkbook Set wsSource = wbTribal.Sheets("Source") The following is a subprocedure to enter some data from a userform. 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 The userform has 3 comboboxes (so user's entries are consistent to make it easier to work with the dates) and the code for the Enter button is: Private Sub cbEnter_Click() sTribeNameUI = frmTribeNameSMCY.cbTribeName.Text sCYUI = frmTribeNameSMCY.cbCY sServMonthUI = frmTribeNameSMCY.cbServMonth If sTribeNameUI = "" Or sCYUI = "" Or sServMonthUI = "" _ Or sCYUI = 0 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 End If End Sub What I want to happen is that if any of the 3 data entries are blank, the userform reappears and gives the user another chance to enter the data, and the progam continues as before. How do I do this? Thanks! last paragraph above, should have read, "...are blank, the userform reappears after the message box and gives..." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
Make it more simple or intuitive to do simple things | Charts and Charting in Excel | |||
simple question, hopefully a simple answer! | Excel Programming |