userform textbox required field-accept only a pattern of numbers

Question

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

You say something about a 'pattern'... Do you only mean the string length by that? Would be "772344456566.00.0001" a kind of pattern? But you also say that the pattern must have 12 digits. Can you better explain what do you mean by 'pattern'? Should you say that the whole pattern must have 16 digits (dots included) and you try explaining it in words instead of only showing it? If yes, is it a second accepted variant involving a number of 8 digits? — FaneDuru
– FaneDuru, Commented Aug 28, 2020 at 10:55
@FaneDuru I think what OP meant was 'my pattern of numbers are; 12 digits, then ".00." then 4 digits'. Stand to be corrected though...? — Spencer Barnes
– Spencer Barnes, Commented Aug 28, 2020 at 11:02
@Spencer Barnes: Now I could see your answer, too... I also assumes that this is what he wants. But I looked to existing answers and started asking myself if my understanding is correct... I tried a clarification question, only to be sure, but he did not answered it. Since, it was not difficult to write a piece of code, after dealing with something urgent at the office, I posted an answer. It is very similar to yours. It was easier for me to test it without involving any TextBox... — FaneDuru
– FaneDuru, Commented Aug 28, 2020 at 11:25
My apologies. yes i meant for example this would be the pattern here: "772344456566.00.0001" or "72344456". so the first example is a total of 20 numbers with two dots, then the second pattern is just 8 numbers with no dots. Thanks for the help. — Sam
– Sam, Commented Aug 28, 2020 at 12:59

Spencer Barnes · Accepted Answer · 2020-08-28 11:00:29Z

2

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.

answered Aug 28, 2020 at 11:00

Spencer Barnes

2,8851 gold badge10 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FaneDuru Over a year ago

Voted up, too... It was posted before mine. But I can swear, I did not see it before I wrote mine.

Sam Over a year ago

Thanks Spencer for the reference link to Like operators in vba.

FaneDuru · Accepted Answer · 2020-08-28 12:58:02Z

1

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?

edited Aug 28, 2020 at 12:58

answered Aug 28, 2020 at 11:16

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

12 Comments

Spencer Barnes Over a year ago

This is a good and useful answer, but can you explain why you make a function rather than just put it all in a single sub? I assume there's a reason? I just want to know if I should be doing that more as well.

FaneDuru Over a year ago

@Spencer Barnes: I tried explaining that in my answer to your comment... It was extremely easy to test it without involving any TextBox object, and to lazy in adapting it after... But, anyhow, I proceed in a similar way when (maybe) the same issue should be checked from more (let us say text boxes) other places.

FaneDuru Over a year ago

@Sam: And didn't it work as you need? If it worked, we here tick the code left side check box, in order to make it accepted answer. In this way, somebody else searching for a similar issue will know that the code works...

Sam Over a year ago

@FanDuru: Thanks for the help, that worked. I also ticked the accept check box. I am still learning this but thanks for your direction.

FaneDuru Over a year ago

@Sam: Did you changed your mind, or played with the check box? I do not care about notoriety, but if possible I would like to understand what happens. If you changed your mind, not necessary to explain anything...

|

ed2 · Accepted Answer · 2020-08-28 10:08:55Z

0

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

answered Aug 28, 2020 at 10:08

ed2

1,4971 gold badge12 silver badges29 bronze badges

Comments

ENIAC · Accepted Answer · 2020-08-28 10:14:31Z

0

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

answered Aug 28, 2020 at 10:14

ENIAC

1,1983 gold badges11 silver badges26 bronze badges

3 Comments

Kostas K. Over a year ago

CInt will explode if it contains text characters. Besides, there's no need to convert to integer to check IsNumeric(). A simple string will do.

Sam Over a year ago

I am receiving a Compile error "Function call on left-hand side of assignment must return variant or Object".

ENIAC Over a year ago

@KostasK., I agree with your comment. Didn't have access to VBA to check the code, so I intended to show a general idea only.

Collectives™ on Stack Overflow

userform textbox required field-accept only a pattern of numbers

4 Answers 4

2 Comments

12 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

12 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related