Textbox Data IsNumeric

[RyanH]

I guess we all know that IsNumeric does not work 100% of the time when used
with Userform Textboxes. This is the format that I am looking for from the
users #####-# or ADT##-#. How can I ensure I have this format? This is what
I have so far:


' must have correct Item # to apply to schedule
If Len(tbxItemNumber) <> 7 Then
strPrompt = "You must have a correct Item # to continue. It must be
" & _
"in this format: ''#####-#''"
intButtons = vbCritical
MsgBox strPrompt, intButtons, strTitle
DataValidator = True
Exit Function
End If

 

[Mike H.]

If Len(tbxItemNumber) <> 7 Then
'code you have
elseif left(tbxItemNumber,3)<>"ADT" then
strPrompt ="Must start with ADT, etc...."
elseif mid(tbxItemNumber,6,1)<>"-" then
strPrompt ="Must have a dash in position 6!"
end if

 

[Rick Rothstein]

Give this a try...

' must have correct Item # to apply to schedule
If Len(tbxItemNumber) <> 7 Or _
Not tbxItemNumber Like "[0-9Aa][0-9Dd][0-9Tt]##-##" Then
strPrompt = "You must have a correct Item # to continue. " & _
"It must be in this format: ''#####-#''"
intButtons = vbCritical
MsgBox strPrompt, intButtons, strTitle
DataValidator = True
Exit Function
End If

 

[RyanH]

I tried entering the following: 11111-1, ADT12-1 and I am getting the
MsgBox. Did I do something wrong?

' must have correct Item # to apply to schedule
If Len(tbxItemNumber) <> 7 Or Not tbxItemNumber Like
"[0-9A][0-9D][0-9T]##-##" Then
strPrompt = "You must have a correct Item # to continue. It must be
" & _
"in this format: ''#####-#''"
intButtons = vbCritical
MsgBox strPrompt, intButtons, strTitle
DataValidator = True
Exit Function
End If
--
Cheers,
Ryan

Give this a try...

' must have correct Item # to apply to schedule
If Len(tbxItemNumber) <> 7 Or _
Not tbxItemNumber Like "[0-9Aa][0-9Dd][0-9Tt]##-##" Then
strPrompt = "You must have a correct Item # to continue. " & _
"It must be in this format: ''#####-#''"
intButtons = vbCritical
MsgBox strPrompt, intButtons, strTitle
DataValidator = True
Exit Function
End If

 

[Rick Rothstein]

No, you didn't do anything wrong... I did. I added an extra digit
requirement after the dash by mistake. Try this instead...

' must have correct Item # to apply to schedule
If Len(tbxItemNumber) <> 7 Or _
Not tbxItemNumber Like "[0-9Aa][0-9Dd][0-9Tt]##-#" Then
strPrompt = "You must have a correct Item # to continue. " & _
"It must be in this format: ''#####-#''"
intButtons = vbCritical
MsgBox strPrompt, intButtons, strTitle
DataValidator = True
Exit Function
End If


--
Rick (MVP - Excel)

I tried entering the following: 11111-1, ADT12-1 and I am getting the
MsgBox. Did I do something wrong?

' must have correct Item # to apply to schedule
If Len(tbxItemNumber) <> 7 Or Not tbxItemNumber Like
"[0-9A][0-9D][0-9T]##-##" Then
strPrompt = "You must have a correct Item # to continue. It must
be
" & _
"in this format: ''#####-#''"
intButtons = vbCritical
MsgBox strPrompt, intButtons, strTitle
DataValidator = True
Exit Function
End If
--
Cheers,
Ryan

Give this a try...

' must have correct Item # to apply to schedule
If Len(tbxItemNumber) <> 7 Or _
Not tbxItemNumber Like "[0-9Aa][0-9Dd][0-9Tt]##-##" Then
strPrompt = "You must have a correct Item # to continue. " & _
"It must be in this format: ''#####-#''"
intButtons = vbCritical
MsgBox strPrompt, intButtons, strTitle
DataValidator = True
Exit Function
End If

--
Rick (MVP - Excel)

I guess we all know that IsNumeric does not work 100% of the time when
used
with Userform Textboxes. This is the format that I am looking for from
the
users #####-# or ADT##-#. How can I ensure I have this format? This
is
what
I have so far:


' must have correct Item # to apply to schedule
If Len(tbxItemNumber) <> 7 Then
strPrompt = "You must have a correct Item # to continue. It
must
be
" & _
"in this format: ''#####-#''"
intButtons = vbCritical
MsgBox strPrompt, intButtons, strTitle
DataValidator = True
Exit Function
End If

 

[RyanH]

I should have seen that myself. It works great! Thanks again.
--
Cheers,
Ryan

No, you didn't do anything wrong... I did. I added an extra digit
requirement after the dash by mistake. Try this instead...

' must have correct Item # to apply to schedule
If Len(tbxItemNumber) <> 7 Or _
Not tbxItemNumber Like "[0-9Aa][0-9Dd][0-9Tt]##-#" Then
strPrompt = "You must have a correct Item # to continue. " & _
"It must be in this format: ''#####-#''"
intButtons = vbCritical
MsgBox strPrompt, intButtons, strTitle
DataValidator = True
Exit Function
End If


--
Rick (MVP - Excel)

I tried entering the following: 11111-1, ADT12-1 and I am getting the
MsgBox. Did I do something wrong?

' must have correct Item # to apply to schedule
If Len(tbxItemNumber) <> 7 Or Not tbxItemNumber Like
"[0-9A][0-9D][0-9T]##-##" Then
strPrompt = "You must have a correct Item # to continue. It must
be
" & _
"in this format: ''#####-#''"
intButtons = vbCritical
MsgBox strPrompt, intButtons, strTitle
DataValidator = True
Exit Function
End If
--
Cheers,
Ryan

Give this a try...

' must have correct Item # to apply to schedule
If Len(tbxItemNumber) <> 7 Or _
Not tbxItemNumber Like "[0-9Aa][0-9Dd][0-9Tt]##-##" Then
strPrompt = "You must have a correct Item # to continue. " & _
"It must be in this format: ''#####-#''"
intButtons = vbCritical
MsgBox strPrompt, intButtons, strTitle
DataValidator = True
Exit Function
End If

--
Rick (MVP - Excel)


I guess we all know that IsNumeric does not work 100% of the time when
used
with Userform Textboxes. This is the format that I am looking for from
the
users #####-# or ADT##-#. How can I ensure I have this format? This
is
what
I have so far:


' must have correct Item # to apply to schedule
If Len(tbxItemNumber) <> 7 Then
strPrompt = "You must have a correct Item # to continue. It
must
be
" & _
"in this format: ''#####-#''"
intButtons = vbCritical
MsgBox strPrompt, intButtons, strTitle
DataValidator = True
Exit Function
End If