Format textbox input

[sharky12345]

I am trying to force the input of data into 2 different textboxes so that the user can't input an incorrect value or in the wrong format.

The textboxes are on a userform and I need them to format as the user types so they are formatted as follows;

12.12

0.500

In both examples, they should only be able to enter a numeric value, nothing else, including spaces.

In the first example, it must be a period before the last 2 characters and in the 2nd example, a period must be entered after the 1st character.

Can anyone help?

 

[mikerickson]

It would be easiest for the user to enter a number in any number format that they desire, and then have that entry converted to the desired formats.

Private Sub TextBox1_AfterUpdate()
    Const numFormat  As String = "0.00"
    TextBox1.Text = Format(TextBox1.Text, numFormat & ";-" & numFormat)
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim newString As String
    With TextBox1
        newString = Mid(.Text, 1, .SelStart) & Chr(KeyAscii) & Mid(.Text, .SelStart + .SelLength + 1)
        If Not (IsNumeric(newString & "0")) Then
            KeyAscii = 0
            Beep
        End If
    End With
End Sub

Private Sub TextBox2_AfterUpdate()
    Const numFormat  As String = "0.000"
    TextBox2.Text = Format(TextBox2.Text, numFormat & ";-" & numFormat)
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim newString As String
    With TextBox2
        newString = Mid(.Text, 1, .SelStart) & Chr(KeyAscii) & Mid(.Text, .SelStart + .SelLength + 1)
        If Not (IsNumeric(newString & "0")) Then
            KeyAscii = 0
            Beep
        End If
    End With
End Sub

 

[sharky12345]

OK Mike, many thanks!

 

[Rick Rothstein]

If you have other code (beside the event code below) in the UserForm code window, you will have to integrate it into the below code manually. You might want to test the code below on a new UserForm with just two TextBoxes (named TextBox1 and TextBox2) on it to see if you like how the code operates. With that said, copy the following code into a blank UserForm code window and then show the UserForm...

[table="width: 500"]
[tr]
	[td]Dim LastPosition1 As Long, LastPosition2 As Long

Private Sub TextBox1_Change()
  Static LastText As String
  Static SecondTime As Boolean
  Const MaxDecimal As Integer = 2
  Const MaxWhole As Integer = 99
  With TextBox1
    If Not SecondTime Then
    If .Text Like "*[!0-9.]*" Or _
       .Text Like "*.*.*" Or _
       .Text Like "*." & String$(1 + MaxDecimal, "#") Or _
       .Text Like String$(MaxWhole, "#") & "[!.]*" Then
       Beep
       SecondTime = True
       .Text = LastText
       .SelStart = LastPosition1
     Else
       LastText = .Text
     End If
    End If
  End With
  SecondTime = False
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  If Not TextBox1.Value Like "*.##" Then
    Cancel = True
    MsgBox "The number must have exactly two digits (zeroes if necessary) after the decimal point", vbCritical
  End If
End Sub

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  With TextBox1
    LastPosition1 = .SelStart
    'Place any other MouseDown event code here
  End With
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  With TextBox1
    LastPosition1 = .SelStart
    'Place any other KeyPress checking code here
  End With
End Sub

Private Sub TextBox2_Change()
  Static LastText As String
  Static SecondTime As Boolean
  Const MaxDecimal As Integer = 99
  Const MaxWhole As Integer = 1
  With TextBox2
    If Not SecondTime Then
    If .Text Like "*[!0-9.]*" Or _
       .Text Like "*.*.*" Or _
       .Text Like "*." & String$(1 + MaxDecimal, "#") Or _
       .Text Like String$(MaxWhole, "#") & "[!.]*" Then
       Beep
       SecondTime = True
       .Text = LastText
       .SelStart = LastPosition1
     Else
       LastText = .Text
     End If
    End If
  End With
  SecondTime = False
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  If Not TextBox2.Value Like "#.*" Then
    Cancel = True
    MsgBox "The number must have exactly one digit before the decimal point (even if that digit is 0)", vbCritical
  End If
End Sub

Private Sub TextBox2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  With TextBox2
    LastPosition1 = .SelStart
    'Place any other MouseDown event code here
  End With
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  With TextBox2
    LastPosition1 = .SelStart
    'Place any other KeyPress checking code here
  End With
End Sub[/td]
[/tr]
[/table]