Userform Validation

survey4life

New Member
Joined
Oct 25, 2011
Messages
46
I need help with these simple scenarios, i need the textbox to limit the following things:
#1 Text Only
#2 Numbers Only
#3 Limit the number of characters. E.g : Max 4 characters allowed.
I would prefer if it was without an error but just with inability to type anymore.

Edit: Also a #2 and #3 combined.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Copy/paste all the code below into your UserForm's code module (its event procedures are meant to replace any identically named one's you may current have). Note the commented code at the beginning. Uncomment the line marked in red if you want you accept only digits being typed in; otherwise, uncomment the line marked in green if you want to accept only letters (as written, both upper and lower case). You can control the maximumn number of characters that are allowed to be typed by setting the MaxLen constant... I set if for 4 as per your posting, but to remove the length restriction, change the assigned value to (2^31-1) which is the maximum length for a String value.

Code:
Dim LastPosition As Long
 
'  For numbers, uncomment the next line only
'[COLOR=red]Const PatternFilter As String = "*[!0-9]*"[/COLOR]
 
'  For text, uncomment the next line only
'[COLOR=seagreen]Const PatternFilter As String = "*[!A-Za-z]*"[/COLOR]
 
'  If you do not want to restrict the number of characters,
'  simply set the MaxLen to a huge number like (2^31-1)
Const MaxLen As Long = 4
 
Private Sub TextBox1_Change()
  Static LastText As String
  Static SecondTime As Boolean
  If Not SecondTime Then
    With TextBox1
     If .Text Like PatternFilter Or Len(.Text) > MaxLen 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
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top