How to handle user input error

[salgud]

I've written the following to create a new worksheet in the existing
workbook after getting some key data from the user:

Public 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
lStartRow = 7


Do
'Show the Facility entry form
frmFacil.Show
Unload frmFacil

If bNewData = True Then
If bDataEnt = False Then
'Add and format new worksheet
Call AddFormatNewWksht
bDataEnt = True
End If
End If
' Application.ScreenUpdating = True
Call EnterFacilData

Loop Until bFinish = True

Call EnterMonthlyTotals
Call TribeNameServDate
Call FileNameandSave
Application.ScreenUpdating = True
ws.Protect Password:=PWORD
End Sub

It calls frmFacil which has the following code for the "Finish" button:

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
'### WHAT TO PUT HERE? ###
Else
bNewData = False
bFinish = True
End If
End If
Unload frmFacil
End Sub

What I don't know is what to do if they fail to fill in the form properly
(the turnover rate is very high, mostly people with NO xl experience at
all). What I want is for the form to reappear and give them another chance
to fill it in. I've tried putting in a goto (or resume) back to the
CreateTribalSheet macro where I show '### WHAT TO PUT HERE ###, even made
that macro public, but I get a compile error, "Label not defined" on the
CreateTribalSheet (even though it knows how to capitalize it when I enter
it!). I'm sure there's a simple error handling procedure of which I'm
unaware. So who is?

Thanks.

 

[Chip Pearson]

You should put all of your validation logic within the form's code
module and do no let the user close the form until all of the values
have been filled out with valid data. Don't close the form if it
contains bad or missing data. Something like


Private Sub btnFinish_Click()
Dim DataIsValid As Boolean
''''''''''''''''''''''''''
' validate all values here.
' if all is OK, bIsDataValue = True
''''''''''''''''''''''''''
If bIsDataValid = True Then
Me.Hide
Else
MsgBox "Incomplete or invalid data"
Exit Sub
End If
End Sub

When validating controls values, if an invalid value was entered, it
is nice to set focus to the offending control. For example if the
value in textbox1 is not valid, use

With Me.TextBox1
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End If

This code will highlight all the text in TextBox1 and make it the
active control. This makes it easier for the user to correct his
mistake.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[salgud]

You should put all of your validation logic within the form's code
module and do no let the user close the form until all of the values
have been filled out with valid data. Don't close the form if it
contains bad or missing data.

Thanks. That part I have right at least.

Something like


Private Sub btnFinish_Click()
Dim DataIsValid As Boolean
''''''''''''''''''''''''''
' validate all values here.
' if all is OK, bIsDataValue = True
''''''''''''''''''''''''''
If bIsDataValid = True Then
Me.Hide
Else
MsgBox "Incomplete or invalid data"
Exit Sub
End If
End Sub

When validating controls values, if an invalid value was entered, it
is nice to set focus to the offending control. For example if the
value in textbox1 is not valid, use

With Me.TextBox1
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End If

This code will highlight all the text in TextBox1 and make it the
active control. This makes it easier for the user to correct his
mistake.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

Thanks, Chip. I spent well over an hour yesterday searching through
Walkenbach's book and online trying to find this.