I recently had a request for a better method of controlling multiple textboxes on a userform. "I have lots of textboxes on a userform I want the user to only be able to enter numbers in all of them!"
The solution: Making use of a class module. The method below will alow you to handle the event for all textboxs at once without having to add events for each individual textbox on the form.
Add a class module and name it "clsObjHandler"
Option Explicit Private WithEvents tbxCustom1 As MSForms.TextBox 'Custom Textbox Public Property Set Control(tbxNew As MSForms.TextBox) Set tbxCustom1 = tbxNew End Property Private Sub tbxCustom1_Change() 'Message Box To Display Which Textbox Was Changed MsgBox "You added A Number To: " & tbxCustom1.Name 'This is just to show you can handle multiple events of the textbox End Sub Private Sub tbxCustom1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) 'Allow only Numbers To be Entered Into Textbox Select Case KeyAscii Case 46 To 57 Case Else KeyAscii = 0 End Select End Sub Private Sub Class_Terminate() 'Destroy The Class Object And Free Up Memory Set tbxCustom1 = Nothing End Sub
Add a userform with as many textboxes as necessary and the following code:
Option Explicit Dim colTbxs As Collection 'Collection Of Custom Textboxes Private Sub UserForm_Initialize() Dim ctlLoop As MSForms.Control Dim clsObject As clsObjHandler 'Create New Collection To Store Custom Textboxes Set colTbxs = New Collection 'Loop Through Controls On Userform For Each ctlLoop In Me.Controls 'Check If Control Is A Textbox If TypeOf ctlLoop Is MSForms.TextBox Then 'Create A New Instance Of The Event Handler CLass Set clsObject = New clsObjHandler 'Set The New Instance To Handle The Events Of Our Textbox Set clsObject.Control = ctlLoop 'Add The Event Handler To Our Collection colTbxs.Add clsObject End If Next ctlLoop End Sub Private Sub UserForm_Terminate() 'Destroy The Collection To Free Memory Set colTbxs = Nothing End Sub
Hope this helps someone out.