469,142 Members | 1,151 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

How to implement control array with vba.

257 256MB
The control array is not implemented in vba.
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
  1. Option Explicit
  2.  
  3. Dim myTb() As Class1
  4.  
  5. Private Sub UserForm_Initialize()
  6.     Dim i As Long
  7.     ReDim myTb(1 To 9)
  8.     For i = 1 To 3
  9.         Set myTb(i) = New Class1
  10.         Set myTb(i).Tb = Me.Controls("TextBox" & CStr(i))
  11.         myTb(i).ck = 1 'Allows only numeric
  12.     Next
  13.     For i = 4 To 6
  14.         Set myTb(i) = New Class1
  15.         Set myTb(i).Tb = Me.Controls("TextBox" & CStr(i))
  16.         myTb(i).ck = 2 'Allows only Uppercase letters
  17.     Next
  18.     For i = 7 To 9
  19.         Set myTb(i) = New Class1
  20.         Set myTb(i).Tb = Me.Controls("TextBox" & CStr(i))
  21.         myTb(i).ck = 3 'Allows numeric and Uppercase letters
  22.     Next
  23. End Sub
  24.  
2. Add the class module and write the following code in class1.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private WithEvents myTb As MSForms.TextBox
  4. Private myCkType As Long
  5.  
  6. Public Property Set Tb(setTb As MSForms.TextBox)
  7.     Set myTb = setTb
  8. End Property
  9.  
  10. Public Property Get Tb() As MSForms.TextBox
  11. End Property
  12.  
  13. Public Property Let ck(setCk As Long)
  14.     myCkType = setCk
  15. End Property
  16.  
  17. Public Property Get ck() As Long
  18. End Property
  19.  
  20. Private Sub myTb_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  21.     Select Case myCkType
  22.         Case 1
  23.             MsgBox "Allows you to enter numbers only.", vbOKOnly + vbCritical, "Error"
  24.         Case 2
  25.             MsgBox "Allows you to enter uppercase letters only.", vbOKOnly + vbCritical, "Error"
  26.         Case 3
  27.             MsgBox "Allows you to enter numeric and uppercase letters only.", vbOKOnly + vbCritical, "Error"
  28.      End Select
  29. End Sub
  30.  
  31. Private Sub myTb_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  32.     Select Case myCkType
  33.         Case 1
  34.             'Allows only numeric
  35.             If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then
  36.                 KeyAscii = 0
  37.             End If
  38.         Case 2
  39.             'Allows only uppercase letters
  40.             If KeyAscii >= Asc("A") And KeyAscii <= Asc("Z") Then
  41.             Else
  42.                 KeyAscii = 0
  43.             End If
  44.         Case 3
  45.             'Allows numeric and uppercase letters
  46.             If (KeyAscii >= Asc("0") And KeyAscii <= Asc("9")) Or _
  47.                 (KeyAscii >= Asc("A") And KeyAscii <= Asc("Z")) Then
  48.             Else
  49.                 KeyAscii = 0
  50.             End If
  51.     End Select
  52. End Sub
  53.  
Aug 18 '21 #1
0 1004

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by The Mess | last post: by
10 posts views Thread by James McGivney | last post: by
2 posts views Thread by RBohannon | last post: by
20 posts views Thread by samean | last post: by
14 posts views Thread by Jim Burns | last post: by
7 posts views Thread by Igor | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.