Text box validation problems

HELP. I need to make sure textbox entries follow certain rules so that values passed to a modules doesn't crash while calculating. Textbox entries must be positive numbers and must not conatain any alpha characters or invalid characters such as blanks, symbols, etc. When tabbing to the next textbox, the textbox needs to be tested and if invalid entries are found then focus needs to be put back on that textbox until a valid number is entered. Not all of the message box calls are in their final form. I've been having problems getting some of the message boxes to close and return to the textboxes for new entries. (See below, attached .xlsm file might make more sense; 1st tab explains validation rules)

**************************************************************************************************************
**************************************************************************************************************

Private Sub TextBox_a_Exit(ByVal Cancel As MSForms.ReturnBoolean)

' Validate textbox on exit by checking for text entry, blanks, negative numbers, and zero
Dim avalue As Double
Dim ContainsInvalidCharacter As Boolean
Dim Counter As Integer
Dim Mbxret As Integer
Dim StrPrompt1 As String
Dim StrPrompt2 As String
Dim StrPrompt3 As String

Dim StrTitle As String

StrPrompt1 = "Sorry, only non-negative numbers allowed."
StrPrompt2 = "Sorry, must have a value" & vbCrLf & "Only non-zero and non-negative numbers allowed."
StrPrompt3 = "Sorry, invalid character entered"
StrTitle = "Constant a"

With Me.ActiveControl

Do While (Not IsNumeric(TextBox_a.Value))
TextBox_a.SetFocus


Select Case (Not IsNumeric(Me.ActiveControl))

Case (Not IsNumeric(Me.ActiveControl))
Mbxret = MsgBox(StrPrompt1, vbOKOnly, StrTitle)
If Mbxret = vbOK Then Cancel = True
TextBox_a.SetFocus
Exit Do
Case vbNullString
Mbxret = MsgBox(StrPrompt2, vbOKOnly, StrTitle)
If Mbxret = vbOK Then Cancel = True
TextBox_a.SetFocus

End Select

Loop

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'ContainsInvalidCharacter = True



If Me.TextBox_a.Value <> vbNullString Then
Do
ContainsInvalidCharacter = True
'Iterate through each character and determine if its a number,
'letter, or non-number of string.
For Counter = 1 To Len(TextBox_a.Value)

Select Case Asc(Mid(TextBox_a.Value, Counter, 1))
Case 45 To 46
ContainsInvalidCharacter = False

Case 48 To 57, 65 To 90, 97 To 122
ContainsInvalidCharacter = False

Case Else

ContainsInvalidCharacter = True
'TextBox_a.Value = vbNullString
'SendKeys "+{TAB}" ' This goes back to TextBox
TextBox_a.SetFocus
Exit For

End Select

Next


'If ContainsInvalidCharacter Then
'Contains a non alpha or numeric character
Mbxret = MsgBox(StrPrompt3, vbOKOnly, StrTitle)
If Mbxret = vbOK Then Cancel = True
TextBox_a.SetFocus
Exit Do
Loop Until ContainsInvalidCharacter = False


'Else no invalid characters found

'Else


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
avalue = CDbl(Val(Me.TextBox_a.Value))
Select Case avalue
Case Is < 0
MsgBox "Sorry, only non-negative numbers allowed.", vbOKOnly, "Constant a"
SendKeys "+{TAB}" ' This goes back to TextBox
TextBox_a.SetFocus

Case Is = 0
MsgBox "Constant a is non-zero and positive", vbOKOnly, "Constant a"
SendKeys "+{TAB}" ' This goes back to TextBox
TextBox_a.SetFocus

End Select
End If
'Else:
' MsgBox "Sorry, must have a value" & vbCrLf & "Only non-zero and non-negative numbers allowed.", vbOKOnly, "Constant a"

'End If

'SendKeys "+{TAB}" ' This goes back to TextBox
'TextBox_a.SetFocus
End With
End Sub

Interesting...this is pretty much the same post you made 8 months ago?

http://www.excelforum.com/excel-prog...alidation.html

Also, I have closed the other 2 duplicate threads you posted, you may continue on this 1

Hi paxtrodamus

The Code in the attached appears to do as you require. You'll need to do this

Not all of the message box calls are in their final form

Let me know of issues...

Hi paxtrodamus

It's a bit late today...perhaps tomorrow I'll get into the confusion...and I think I understand the confusion...perhaps...maybe...

Hi paxtrodamus

I'm also from the old school...retired CPA...have used Flow Charts extensively in system development. I'm a bit tired this evening...these 71 year old bones can't handle the beating they've taken these past several days.

I'll try to look at it later this week...promise...have more physical work yet to do this week.

I've also had issues with Focus on Exit Events...AfterUpdate appears to solve that issue...at least in this case...it's all a bit squirrelly at times.

I assume the Code works for you? If so... please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks.

Hi paxtrodamus

To edit your post (to add tags or change text etc, click the "edit post" button at the bottom right of that post