in vba, How can I get access to the textbox name in a textbox_change sub?

Question

I have a userform with 70 textboxes and want make sure numerical data in entered by the user. I want the textbox_change sub to self refer to its name and sue the name in sub that porcessess the error with a message box. Is this doable?

Code: (# = a variable integer that is appended to end of "TextBox")

Private Sub Textbox#_change()

     IF not isnumeric(Textbox#.value) then WrongDataType #
End Sub

Private Sub WrongDataType #

     SendMsg("Please Enter Numerical Data", VBOKonly, "Error")
     TextBox#.value = ""

end sub

A message that tells user to enter a number and then empty the text box.

Inside your subroutine, you can get the object that triggered the subroutine with Application.Caller. — JNevill
– JNevill, Commented Oct 2, 2023 at 18:17
My approach would be similar to this answer so you only write your validation code once and don't have to recreate it if you add or delete textboxes. It's a clean solution to wrap the textbox control in a class. — PeterT
– PeterT, Commented Oct 2, 2023 at 18:22
PeterT: I thought about waiting till all text boxes were populated and then using "for each..next" to test each one for correct data type. It seemed better to let the user know immediately, so they didn't have to look for the incorrect box in the form filled with text entry boxes. — Kerry Collins
– Kerry Collins, Commented Oct 2, 2023 at 19:27

taller · Accepted Answer · 2023-10-02 19:30:12Z

3

Given that the OP has multiple textboxes on the userform, it's advisable to implement shared event code using a class module. This approach not only saves time in terms of code maintenance but also ensures scalability, as the code won't require changes even if the OP adds more textboxes in the future.

Open Excel and press Alt +F11 to open the VBA editor.
In VBE, "Insert" > "Class Module" to create a new class module. Name it "clsTextBox" (or any other preferred name)
In the class module, add the following code:

Option Explicit
Public WithEvents TextBox As MSForms.TextBox
Private Sub TextBox_Change()
    If Len(TextBox.Text) > 0 Then
        If Not VBA.IsNumeric(TextBox.Text) Then
            MsgBox "Input is invalid on " & TextBox.Name
            TextBox.Text = ""
        End If
    End If
End Sub

In the UserForm's code window, declare a class object variable and initialize it:

Dim TextBoxEvents As Collection
Private Sub UserForm_Initialize()
    Dim TextBoxEvent As clsTextBox
    Set TextBoxEvents = New Collection
    Dim ctrl As MSForms.Control
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.TextBox Then
            Set TextBoxEvent = New clsTextBox
            Set TextBoxEvent.TextBox = ctrl
            TextBoxEvents.Add TextBoxEvent
        End If
    Next ctrl
End Sub

edited Oct 2, 2023 at 19:30

answered Oct 2, 2023 at 18:29

taller

19.7k2 gold badges9 silver badges24 bronze badges

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

9 Comments

Kerry Collins Over a year ago

When I run the above code to initialize the user form, I get:

Kerry Collins Over a year ago

Compile Error: User_defined type not defined. With "TextBoxEvent As clsTextBox " highlighted.

taller Over a year ago

Please follow the procedure to create class module.

taller Over a year ago

@braX There's no doubt that it will require some time to learn. When dealing with a substantial number of textboxes like 70, learning and implementing shared event code with a class module becomes highly valuable.

taller Over a year ago

You could add an extra If as If TypeOf ctrl Is MSForms.TextBox Then If ctrl.Name <> "SpecialTextBox" Then

|

Kerry Collins · Accepted Answer · 2023-10-02 20:30:25Z

1

I have decided to put a single validation For each....next loop after all the boxes have been populated. I will make the bad data boxes turn red, so the user can see which one(s) was in error.

answered Oct 2, 2023 at 20:30

Kerry Collins

112 bronze badges

2 Comments

Kerry Collins Over a year ago

This way, I will have flexibility to keep some text boxes open for text or text only.

Sgdva Over a year ago

There are 2 answers that you can use, personally if you expect numbers my suggestion would be not let them type anything but numbers and the second if you really need to know the names on the userform, I did it like so

Collectives™ on Stack Overflow

in vba, How can I get access to the textbox name in a textbox_change sub?

2 Answers 2

9 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related