CODEWITHSUNDEEP
excelvba
Sam
Sam

Reputation: 33

userform textbox required field-accept only a pattern of numbers

I need help trying to make an excel userform textbox to accept only a pattern of numbers. My pattern of numbers are 12 digits.00.4 digits or 8 digits so the textbox should only accept a number similar to 772344456566.00.0001 or 77186238. I have searched for clues to similar issues but none give me any direction. My current code is this but not close to my target goal:

Private Sub Textbox3_Exit(ByVal Cancel As MsForms.ReturnBoolean)
 IF TextBox3.Value <>"" Or TextBox3.Value < 0 Then
      MsgBox "Invalid sales order number"
    TextBox3.SetFocus
 End If
End Sub

Upvotes: 1

Views: 1067

Answers (4)

FaneDuru
FaneDuru

Reputation: 42256

Use the next function, please:

Function textOK(strText As String) As Boolean
    If strText Like "############.00.####" Or strText Like "########" Then
        textOK = True
    End If
End Function

It can be used in your code in this way:

  If textOK(TextBox3.Text) Then
    'do whatever you need
  Else
    'send a warning message... or do something else.
  End If

Edited:

You can test the above function in this relevant way:

Sub testTextOK()
 Dim x As String
  x = "123456787812.00.0014"
  'x = "12345678"
  'x = "123457j8"
  'x = "123456789"
  Debug.Print textOK(x)
  'or use MsgBox and comment the above code line
   MsgBox textOK(x)
End Sub

Please, un-comment from bottom to the top the x lines allocating values and see the return in Immediate Window (being in VBE: `Ctrl + G)...

Does it look now a little easier to understand how to use it?

Upvotes: 1

Spencer Barnes
Spencer Barnes

Reputation: 2877

Try this:

Private Sub Textbox3_Exit(ByVal Cancel As MsForms.ReturnBoolean)
If Not TextBox3.Value Like "########" Or _
Not TextBox3.Value Like "############.00.####" Then
      MsgBox "Invalid sales order number"
    TextBox3.SetFocus
End If
End Sub

and/or have a look at This. Basically, when used with the Like operator, # checks for any digit, so ## checks for a 2 digit number and so on.

Upvotes: 2

ENIAC
ENIAC

Reputation: 1038

Firstly, check the length of the value. Then, just break the value into separate parts and check each of them. Something like this (cannot check it as on Linux now):

val = CStr(TextBox3.Value)
len_val = Len(val)
If len_val = 8 Then
    If Not IsNumeric(val) Then
        MsgBox "Error"
    End If
ElseIf len_val = 20 Then
    If (Not IsNumeric(CInt(Left(val, 12)))) Or _
        Mid(val, 13, 4) <> ".00." Or _
        (Not IsNumeric(CInt(Right(val, 4)))) Then
        MsgBox "Error"
    End If
Else
    MsgBox "Error"
End If

Upvotes: 0

ed2
ed2

Reputation: 1497

Your code seems to be trying to reject values that are anything other than blank or negative.

Below is a selection of questions to review regarding validation of textbox forms in VBA.

Validating the format of a userform textbox entry

Validate textbox input

Making VBA Form TextBox accept Numbers only (including +, - and .)

Validating textbox entry on Userform (Excel VBA)

Userform entry validation

Upvotes: 0

Related Questions