VBA Excel - Working with multiple textboxes with the same code

user_6729785 0
Viewed 738

So I'm new to this area. I just want to ask if how can I minimize the use of the code below since I do have 13 textboxes with the same code. Is there a short way to do this?

Here's the UserForm that I'm using ->

enter image description here

Here's the code

Private Sub tb_mtb_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Not IsNumeric(tb_mtb.Value) Then
        MsgBox "Only numbers allowed!", vbOKOnly + vbCritical, "Title"
        tb_mtb.Value = ""
    End If
End Sub

Private Sub tb_fil_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Not IsNumeric(tb_fil.Value) Then
        MsgBox "Only numbers allowed!", vbOKOnly + vbCritical, "Title"
        tb_fil.Value = ""
    End If
End Sub

I tried this solution but I can't make it work.

textbox vba excel
2 Answers

Make it a subroutine and pass the control as an argument

(Make it Public if you want to put it into a module to make it re-usable for any form):

Public Sub CheckNumeric(ctl as Control)
    If Not IsNumeric(ctl.Value) Then
        MsgBox "Only numbers allowed!", vbOKOnly Or vbCritical, "Title"
        ctl.Value = ""
    End If
End Sub

And then for each control on the form:

Private Sub tb_fil_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    CheckNumeric tb_fil
End Sub

Although, a better way might be to check the KeyAscii value in the KeyPress event, and not allow non-numeric characters at all.

Making VBA Form TextBox accept Numbers only

user_84229530

The "normal" way to avoid writing the same event handler code over and over (or to avoid having to write even a "stub" handler for each like control) is to use a "control array".

Here's a basic example.

First a small custom class clsTxt which can be used to capture events from a text box:

Private WithEvents tb As MSForms.TextBox   'note the "WithEvents"

Sub Init(tbox As Object)
    Set tb = tbox 'assigns the textbox to the "tb" global
End Sub

'Event handler works as in a form (you should get choices for "tb" in the
'  drop-downs at the top of the class module) 
Private Sub tb_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii >= 48 And KeyAscii <= 57 Then
        Debug.Print tb.Name, "number"
    Else
        Debug.Print tb.Name, "other"
        KeyAscii = 0
    End If
End Sub

Then in your userform you can (for example) grab all textboxes inside the frame frmTest and create an instance of clsTxt for each one, storing it in a Collection (which is Global and so does not go out of scope when the Activate event completes.

Private colTB As Collection 'holds your class instances
                            ' and keeps them in scope

'This performs the setup
Private Sub UserForm_Activate()
    Dim c As Object
    Set colTB = New Collection
    'loop all controls in the frame
    For Each c In Me.frmTest.Controls
        'look for text boxes
        If TypeName(c) = "TextBox" Then
            Debug.Print "setting up " & c.Name
            colTB.Add TbHandler(c) ' create and store an instance of your class
        End If
    Next c
End Sub

' "factory" method
Private Function TbHandler(tb As Object) As clsTxt
    Dim o As New clsTxt
    o.Init tb
    Set TbHandler = o
End Function

Once the setup is complete then events for each "wired up" textbox are handled by the class instances (you can add more events to the class if you need to manage different things like Change etc) and any new textbox added to the frame will automatically get handled without the need to write a handler for it.

user_4788840
Related