Class modules are used to achieve this artificially.
The example below shows a form with three text boxes that allow you to enter numbers only, three text boxes that allow you to enter only uppercase letters, and three text boxes that allow you to enter numbers and uppercase letters.
1. Place 9 text boxes on UserForm1 and write the following code.
Expand|Select|Wrap|Line Numbers
- Option Explicit
- Dim myTb() As Class1
- Private Sub UserForm_Initialize()
- Dim i As Long
- ReDim myTb(1 To 9)
- For i = 1 To 3
- Set myTb(i) = New Class1
- Set myTb(i).Tb = Me.Controls("TextBox" & CStr(i))
- myTb(i).ck = 1 'Allows only numeric
- Next
- For i = 4 To 6
- Set myTb(i) = New Class1
- Set myTb(i).Tb = Me.Controls("TextBox" & CStr(i))
- myTb(i).ck = 2 'Allows only Uppercase letters
- Next
- For i = 7 To 9
- Set myTb(i) = New Class1
- Set myTb(i).Tb = Me.Controls("TextBox" & CStr(i))
- myTb(i).ck = 3 'Allows numeric and Uppercase letters
- Next
- End Sub
Expand|Select|Wrap|Line Numbers
- Option Explicit
- Private WithEvents myTb As MSForms.TextBox
- Private myCkType As Long
- Public Property Set Tb(setTb As MSForms.TextBox)
- Set myTb = setTb
- End Property
- Public Property Get Tb() As MSForms.TextBox
- End Property
- Public Property Let ck(setCk As Long)
- myCkType = setCk
- End Property
- Public Property Get ck() As Long
- End Property
- Private Sub myTb_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
- Select Case myCkType
- Case 1
- MsgBox "Allows you to enter numbers only.", vbOKOnly + vbCritical, "Error"
- Case 2
- MsgBox "Allows you to enter uppercase letters only.", vbOKOnly + vbCritical, "Error"
- Case 3
- MsgBox "Allows you to enter numeric and uppercase letters only.", vbOKOnly + vbCritical, "Error"
- End Select
- End Sub
- Private Sub myTb_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
- Select Case myCkType
- Case 1
- 'Allows only numeric
- If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then
- KeyAscii = 0
- End If
- Case 2
- 'Allows only uppercase letters
- If KeyAscii >= Asc("A") And KeyAscii <= Asc("Z") Then
- Else
- KeyAscii = 0
- End If
- Case 3
- 'Allows numeric and uppercase letters
- If (KeyAscii >= Asc("0") And KeyAscii <= Asc("9")) Or _
- (KeyAscii >= Asc("A") And KeyAscii <= Asc("Z")) Then
- Else
- KeyAscii = 0
- End If
- End Select
- End Sub