G'day there Bob,

It sounds as though you are exiting regardless of the msgbox answer
but I
think it would help to see all the code to see how and why.

You've hit the nail on the head, but I can't figure out how to ge
around it. Here's the code for the two routines:

Option Explicit
Private Sub CommandButton1_Click()
' Add Officer

Dim shData As Worksheet
Dim newRow As Integer
Dim msg, response As String
Dim test As String

' Assignments
Set shData = Worksheets("Data")
newRow = Worksheets("Main").Range("OfficerMaxRows").Value + 1

[ The 'newrow' above gets the next row for record entry from a functio
in the named range "OfficerMaxRows" on the "Data" worksheet]

shData.Activate

test = CheckOfficer(offNum.Text, offName.Text, offRank.Text)
If test = False Then

[I think this is where I have to put something to return to the star
of the sub if the test fails]

End If

'Insert record
shData.Cells(newRow, 1) = offNum.Text
shData.Cells(newRow, 2) = UCase(offName.Text)
shData.Cells(newRow, 3) = offRank.Text

shData.Range("Officer_Data").Sort _
Key1:=Worksheets("data").Columns("A")

Worksheets("Main").Range("P2").Calculate

frmAdmin.Hide
Worksheets("Main").Activate


End Sub

Private Function CheckOfficer(x, y, z)
' Data validation & error detection on entry of New Officer records
' Officer number must be in the format of 4 or 5 digits
' There can be no duplicates in this field
Dim a As Integer
Dim err(4, 1) As String
Dim bttn As Integer
Dim hdr As String
Dim dummy As Integer

err(0, 0) = Worksheets("Main").Range("AA2").Text
err(0, 1) = Worksheets("Main").Range("AB2").Text
err(1, 0) = Worksheets("Main").Range("AA3").Text
err(1, 1) = Worksheets("Main").Range("AB3").Text
err(2, 0) = Worksheets("Main").Range("AA4").Text
err(2, 1) = Worksheets("Main").Range("AB4").Text
err(3, 0) = Worksheets("Main").Range("AA5").Text
err(3, 1) = Worksheets("Main").Range("AB5").Text

a = (Len(x) * Len(y) * Len(z))
CheckOfficer = True
Select Case a
Case False
'Raise Blank Field error
CheckOfficer = False
dummy = MsgBox(err(0, 0), 0 + vbCritical, err(0, 1))
Case Else
' Too few characters in Officer number
If (Len(x) < 4) Then
CheckOfficer = False
dummy = MsgBox(err(1, 0), 0 + vbCritical, err(1, 1))
End If
' Too many characters in Officer number
If (Len(x) 5) Then
CheckOfficer = False
dummy = MsgBox(err(2, 0), 0 + vbCritical, err(2, 1))
End If
' Officer number is not a number
If Not IsNumeric(frmAdmin.offNum.Text) Then
CheckOfficer = False
dummy = MsgBox(err(3, 0), 0 + vbCritical, err(3, 1))
End If
End Select
frmAdmin.offNum.SetFocus

End Function


Hope it all makes sense.

See ya
Ke

--
Message posted from http://www.ExcelForum.com