Subclassing TextBox1

[Rick Rothstein]

I opened the following page: Excel - Cool Worksheet Keydown Event (like The One For Textboxes) & W/out Subclassing - Hi all I have... - Free Excel Help

I was wondering if you could use this technique to manage a Textbox1 in Userform1

So you can handle numbers or letters

This is a perfect example of why it is a good idea to tell us what you are trying to do along with the specific question you want to ask... you do not need to subclass the TextBox (not sure if you even can) in order to restrict its input to, say, only digits. Below are 3 different event code procedures for you to consider. The first one restricts entry to a TextBox to only digits, the second one restricts entry to a TextBox to floating point numbers (be sure to read the comments at the beginning of the code) and the last one restricts entry to upper or lower case letters only (no digits or special characters allowed). These routines not only perform their filtering operation on typed in characters, but also on text attempted to be pasted into the TextBox as well.

'  Accept Digits Only

Dim LastPosition As Long

Private Sub TextBox1_Change()
  Static LastText As String
  Static SecondTime As Boolean
  If Not SecondTime Then
    With TextBox1
      If .Text Like "*[!0-9]*" Then
        Beep
        SecondTime = True
        .Text = LastText
        .SelStart = LastPosition
      Else
        LastText = .Text
      End If
    End With
  End If
  SecondTime = False
End Sub

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

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

'  Accept floating point numbers only

'  Set the maximum number of digits before the decimal point in the MaxWhole constant.
'  Set the maximum number of digits after the decimal point in the MaxDecimal constant.
'  Use any large number for each of these settings if you do not need to retrict the size of the entry.

Dim LastPosition As Long

Private Sub TextBox1_Change()
  Static LastText As String
  Static SecondTime As Boolean
  If Not SecondTime Then
    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 = LastPosition
        Else
          LastText = .Text
        End If
      End If
    End With
  End If
  SecondTime = False
End Sub

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

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

'  Accept upper/lower case letters only

Dim LastPosition As Long

Private Sub TextBox1_Change()
  Static LastText As String
  Static SecondTime As Boolean
  If Not SecondTime Then
    With TextBox1
     If .Text Like "*[!A-Za-z]*" Then
        Beep
        SecondTime = True
        .Text = LastText
        .SelStart = LastPosition
      Else
        LastText = .Text
      End If
    End With
  End If
  SecondTime = False
End Sub

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

Private Sub TextBox1_KeyPress(KeyAscii As Integer)
  With TextBox1
    LastPosition = .SelStart
    'Place any other KeyPress checking code here
  End With
End Sub