Binding an excel forms textbox exit event to a class module

Question

I am having trouble binding a series of textboxe exit events on an excel form to a class module;

Here is My class code:

Public WithEvents myEBtn As MSForms.TextBox

Private Sub myEBtn_end(ByVal Cancel As MSForms.ReturnBoolean)

'MsgBox "hello"

myEBtn.Value = Format(myEBtn.Value, "#,##0")

End Sub

Here is My form initialization code"

Private MyETextBox(1 To 3) As New ExitBtn

Private Sub UserForm_Initialize()

Dim i As Long

Dim ctl As Control

ctl = Me.Controls("txt1")

ctl = Me.Controls("txt" & i)

For i = 1 To 3

Set MyETextBox(i).myEBtn = Me.Controls("txt" & i)

Next i

End Sub

can someone help me?

Answer 1

As the link that I posted shows, you have to make the code jump through a lot of hoops to simulate the event in a class module. So I wouldn't use one. I'd create a common procedure in the userform module itself, or in a standard module if you want to apply it to multiple userforms.

And I'd use the After Update event instead of the Exit event - we only need to format the value if the control's value has changed.

Private Sub txt1_AfterUpdate()
    Call FormatMe(txt1)
End Sub
Private Sub txt2_AfterUpdate()
    Call FormatMe(txt2)
End Sub
Private Sub txt3_AfterUpdate()
    Call FormatMe(txt3)
End Sub
Private Sub FormatMe(ctl As MSForms.TextBox)
    If IsNumeric(ctl) Then
        ctl = Format(ctl, "#,##0")
    End If
End Sub

Answer 2

After figuring out the solution, it seemed so simple. Use the KeyDown event. It's available in a class module variable. Trap the "enter" and 'tab" key. that tells excel that entry into the textbox is over and there fore, the text box can be formatted.

in the class module:

Private Sub myTextBox_KeyDown(ByVal KeyCode as.......

dim x as interger

if KeyCode = 13 or KeyCode = 9 then

myTextBox = Format(myTextBox, "#,##0")

end if

end sub

Works perfectly

Answer 3

To bind a series of TextBox exit events in an Excel UserForm to a class module using VBA collections, you'll need to set up the event handling in a way that allows each TextBox to be treated dynamically. This way, instead of writing individual exit handlers for each TextBox, you can handle them via a central class.

Steps to Achieve This:

1. Create the Class Module to store each TextBox and its associated event.
2. Set up the TextBox Exit Events dynamically, using a VBA Collection to hold references to each TextBox and its corresponding exit event handler.
3. Bind the Exit Event to the TextBox in the UserForm.

1. Create the Class Module (TextBoxHandler)

This class will handle the Exit events for each TextBox dynamically.

1. Open the VBA editor (Alt + F11), and in the Insert menu, select Class Module. Rename it to TextBoxHandler.
2. In the TextBoxHandler class, write the following code:

' Class Module: TextBoxHandler
Private WithEvents txtBox As MSForms.TextBox

' This is the constructor that sets the TextBox reference
Public Sub SetTextBox(ByVal textbox As MSForms.TextBox)
    Set txtBox = textbox
End Sub

' Event handler for TextBox Exit event
Private Sub txtBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox "You exited the TextBox with value: " & txtBox.Text
End Sub

2. UserForm Code

In the UserForm code, you will use a Collection to store each TextBox and its event handler (an instance of the TextBoxHandler class).

1. Open your UserForm code.
2. Declare a Collection to store the TextBoxHandler instances.
3. Set up a loop to dynamically bind the exit events of all TextBox controls on the form.

' UserForm Code
Dim TextBoxHandlers As Collection

Private Sub UserForm_Initialize()
    Dim txtBox As MSForms.TextBox
    Dim handler As TextBoxHandler
    
    ' Initialize the Collection
    Set TextBoxHandlers = New Collection
    
    ' Loop through all the TextBoxes and bind their Exit events
    For Each txtBox In Me.Controls
        If TypeOf txtBox Is MSForms.TextBox Then
            Set handler = New TextBoxHandler
            handler.SetTextBox txtBox
            TextBoxHandlers.Add handler
        End If
    Next txtBox
End Sub

3. How It Works

• TextBoxHandler Class: The TextBoxHandler class has an Exit event handler that triggers when the user exits the TextBox. When the event is triggered, it displays the value of the TextBox in a message box.
• UserForm Code: The UserForm_Initialize method loops through all the controls on the form and checks if they are TextBox controls. For each TextBox, it creates a new TextBoxHandler instance and binds it to that TextBox using the SetTextBox method.

4. Dynamic Binding

By using a collection to store the handlers, you don't need to write individual exit event handlers for each TextBox on the form. Instead, this method allows you to dynamically bind all TextBox controls to their exit events in a clean and scalable way.

Example:

• When a user exits a TextBox, the Exit event will be triggered, and the message box will show the value entered in that TextBox. This is achieved without needing to handle each TextBox exit separately.

Notes:

• This method works well for a large number of controls, especially if they are dynamically created or you want to reduce redundancy in your code.
• You can further modify the TextBoxHandler class to handle more events or add additional functionality.

This should give you a clean, scalable way to manage the TextBox exit events using VBA collections and class modules. Let me know if you need further help!

Answer 4

I don't think that will work since there is no Exit event available in a class module