VBA Numbers only in Textbox

[LeonardH]

I am trying to get a text box in a userform to only allow numerical input. Does anyone know a simple code for this? Many that I have tried have failed.

 

[DeezNuts]

Rick I know this is an old thread but it came up in my search results and am curious can you code be adapted to work with only certain textboxs? I have 15 boxes in a form that require a number to be input and all are named txtBox1 - txtBox15 most code I can see a place to do Dim i as byte and "txtBox" & i but not certain on this one

Thank you

 

[DeezNuts]

Would anyone know if this code can be modified to work with a form that has several textboxs and 15 of them are called the same thing with sequential numbers wtbox1-15

 

[Rick Rothstein]

Would anyone know if this code can be modified to work with a form that has several textboxs and 15 of them are called the same thing with sequential numbers wtbox1-15

The following code requires all TextBoxes that you want to have the number entry functionality describe earlier in the thread have names that start with the letters "wtbox". You must put the code in the indicated code modules in order for it to work.

[COLOR=#008000][B]' Place the following in the UserForm's code module
' =======================================[/B][/COLOR]
Dim wtBoxes() As New Class1

Private Sub UserForm_Initialize()
    Dim intCtlCnt As Integer, objControl As Control
   
    For Each objControl In Me.Controls
        If TypeOf objControl Is MSForms.TextBox Then
            If LCase(Left(objControl.Name, 5)) = "wtbox" Then
                intCtlCnt = intCtlCnt + 1
                ReDim Preserve wtBoxes(1 To intCtlCnt)
                Set wtBoxes(intCtlCnt).TextBoxEvents = objControl
            End If
        End If
    Next objControl
    Set objControl = Nothing
    [COLOR=#008000]'Place any other Initialize event code here[/COLOR]
End Sub

[COLOR=#008000][B]' Place in a Class module, NOT a general module
'----------------------------------------------------------------------------
'  NOTE: Only one colored section below should be active
'             (the rest should be commented out) depending on
              what functionality you want for all the TextBoxes
' ==========================================[/B][/COLOR]
Public WithEvents TextBoxEvents As MSForms.TextBox

Dim LastPosition As Long

Private Sub TextBoxEvents_Change()
  Static LastText As String
  Static SecondTime As Boolean
  If Not SecondTime Then
    With TextBoxEvents
      [COLOR=#ff0000]' Digits Only (no plus or minus)[/COLOR]
      [COLOR=#ff0000][B]'If .Text Like "*[!0-9]*" Then[/B][/COLOR]
    [COLOR=#800080]  ' Digits Only (plus or minus allowed)[/COLOR]
      [COLOR=#800080][B]'If .Text Like "*[!0-9+-]*" Or .Text Like "?*[+-]*" Then[/B][/COLOR]
      [COLOR=#0000ff]' Floating Point Numbers (no plus or minus)[/COLOR]
      [B][COLOR=#0000ff]'If .Text Like "*[!0-9.]*" Or .Text Like "*.*.*" Then[/COLOR][/B]
      [COLOR=#008000]' Floating Point Numbers (plus or minus allowed)[/COLOR]
      [COLOR=#008000][B]If .Text Like "*[!0-9.+-]*" Or .Text Like "?*[+-]*" Or .Text Like "*.*.*" Then[/B][/COLOR]
        Beep
        SecondTime = True
        .Text = LastText
        .SelStart = LastPosition
      Else
        LastText = .Text
      End If
    End With
  End If
  SecondTime = False
End Sub
 
Private Sub TextBoxEvents_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, _
                               ByVal X As Single, ByVal Y As Single)
  With TextBoxEvents
    LastPosition = .SelStart
    [COLOR=#008000]'Place any other MouseDown event code here[/COLOR]
  End With
End Sub
 
Private Sub TextBoxEvents_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  With TextBoxEvents
    LastPosition = .SelStart
    [COLOR=#008000]'Place any other KeyPress checking code here[/COLOR]
  End With
End Sub

 

[DeezNuts]

Firstly thank you Rick for helping on this old thread. I know I typed wtbox in the question but should have been wtBox is it a tough fix this? Sorry I should have proof read prior to posting the question.


*** edited
Actually not sure it will matter much it is working perfectly as is with the names txBox and nothing edited in the code.

Thank you very much

 

[Rick Rothstein]

Firstly thank you Rick for helping on this old thread. I know I typed wtbox in the question but should have been wtBox is it a tough fix this? Sorry I should have proof read prior to posting the question.

Actually, it looks like the way I wrote the code, the letter casing of the first five letters does not matter as long as they are the letter w, t, b, o and x in any combination of upper/lower case letters. So, I think my code should work for you as written.

 

[DeezNuts]

This is an outstanding piece of code you made I like it. Thank you again.

 

[JoePoliti]

Hello Rick,
You saved me a lot of time with this code. I have some 15 numeric text boxes and your solution was so easy to implement. I made a subroutine out of it and for each textbox change called the subroutine with the textbox as an MSforms control. Cannot thank you enough for your tight and complete coding. Joe

 

[Darren Bartrup]

Resurrecting this post again.... sorry.

I've been using the Like operators from this post to limit entry to a userform textbox to numbers with a maximum of two decimal places (I don't think it was this actual post - was definitely a Rick Rothstein solution though).

Const MaxDecimal As Integer = 2
.
.
.
If Not SecondTime Then
    If .Text Like "[!0-9.-]*" Or _
        .Text Like "*.*.*" Or .Text Like "*." & String$(1 + MaxDecimal, "#") Or _
        .Text Like "?*[!0-9.]*" Then
        Beep
        SecondTime = True
        .Text = LastText
    Else
        LastText = .Text
    End If
End If
SecondTime = False

How would I update this to require a percent sign at the end of the entry?

I've tried

If .Text Like "[!0-9.-]*[!%]" Or _
    .Text Like "*." & String$(1 + MaxDecimal, "#") & "[!%]" Or _
    .Text Like "?*[!0-9.]*[!%]" Then

and numerous variations of that theme, but I haven't got there yet.

If anyone could help out I'd be very grateful.

 

[Rick Rothstein]

...(I don't think it was this actual post - was definitely a Rick Rothstein solution though).

How would I update this to require a percent sign at the end of the entry?

I've tried

If .Text Like "[!0-9.-]*[!%]" Or _
    .Text Like "*." & String$(1 + MaxDecimal, "#") & "[!%]" Or _
    .Text Like "?*[!0-9.]*[!%]" Then

Lightly tested, but I think this may do what you want...

If .Text Like "[!0-9.-]*" Or _
    .Text Like "*.*.*" Or _
    .Text Like "*." & String$(1 + MaxDecimal, "#") Or _
    .Text Like "*%*" Or _
    .Text Like "?*[!0-9.%]*" Then

 

[Darren Bartrup]

Thanks for the reply Rick.

Unfortunately, it's not working.

"1%" like "*%*" is returning TRUE and so is "1.1%" like "*%*"
- so the code executes the .Text = LastText line and removes the % sign.

I feel like I should be able to figure this out... I hate my brain in the evenings sometimes.