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.

 

[Rick Rothstein]

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.

It looks like I missed a question mark sign (it was in there originally, but I think it got removed when I did one undo too many as part of my experimenting). See if this works...

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

 

[Darren Bartrup]

Sorry I didn't reply earlier Rick - I'm 5 hours ahead, so was getting ready to head home.

That question mark did the trick, just in time for me to say it's all working on my conference call in an hour.

Thank you.

 

[thanhchu6]

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

Hi Rick, I got a userform with multiple textboxes. How ever only some of them are used to collect number. It's be nice to have a module or a sub to call for in those specific textbox change function.

By the way, I do want the number putted in these textboxes to automatically format as "standard" which include "," as the thousand separator. So I add some change to your code: If .Text Like "*[!0-9,.+-]*" Or .Text Like "?*[+-]*" Or .Text Like "*.*.*" Then 'allow for ","

however, I can't find a way to make the code: textbox = format(textbox, "standard") work properly since after the first digit I put in, the number change to "#.##" without anyway to add more number before the ".". Can you help me.