3

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.

Improve this question
6
  • FYI you don't need to clear the whole content if a non-numeric key is pressed: you can just prevent non-numeric characters from being entered - e.g. see stackoverflow.com/questions/26138833/… Commented Jan 26, 2021 at 1:44
  • @TimWilliams I tried this and it kinda worked. But I'm looking for a solution that I don't need to create a Private Sub in each textbox. Commented Jan 26, 2021 at 1:54
  • Just noticed @braX gave you the same link ... Commented Jan 26, 2021 at 2:08
  • @TimWilliams yes but is it possible to minimize the code or should I really create Private Sub in each textbox? Commented Jan 26, 2021 at 2:21
  • 2
    VBA – Control Arrays Commented Jan 26, 2021 at 7:54

2 Answers 2

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This way is obviously better, I just tend to get confused when doing it this way for some reason. Just not used to it I guess.
@braX - yes, it's more complicated, and whether it's worth the overhead maybe depends on how many "similar" controls you have. It's really invaluable when adding controls at runtime though.
Is the clsTxt should be the Name of the Class? And then the frmTest should be the Name of the frame in which all the textboxes are located? I'm sorry. My English skill is not that great and I'm new to this language but I'm really trying. I'm getting an error that says User-defined type not defined.
It may also be worth noting for other people who may use this as currently written, it will not allow for negative numbers, nor non-integers. It's possible to handle that as well, but gets very complicated as the decimal and negative sign cannot be in the textbox more than once, and the negative sign would need to be only in the first position.
Yes, "clsTxt" is the name of the class (right-click >> Insert >> Class module). Your frame can be called whatever you want, as long as you adjust the name in the code to match.
3

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

Improve this answer

3 Comments

The solution that you provided needs to create a Private Sub in each textbox, right? Is there no other way to create it with one or two subs or maybe minimal code?
I'm getting an error that says "Object required".
oh sorry... remove the parenthesis... CheckNumeric tb_fil <-- use this instead

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.