Binding an excel forms textbox exit event to a class module

Question

Binding an excel forms textbox exit event to a class module

Alex Baja 21

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?

Accepted answer

7 additional answers

Your answer

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

HansV 462K MVP Volunteer Moderator

I think you meant myEBtn_Exit instead of myEBtn_end. But unfortunately, a custom control does not have an Exit event, nor a BeforeUpdate or AfterUpdate event.

Perhaps Controling colors of controls in userform will help, although it's quite old.

Answer 3

Alex Baja 21

thanks. then if I have 20 controls on a form, how can I format them as "#,##0" through a class module

Answer 4

Alex Baja 21

I understand that and what you have posted is common for each individual text box. I am trying to avoid 20 afterupdates in My code. If I was reseloved to that from the beginning, I would have done that, but there must be a way to bind that into 1 code snippet for all 20 textboxes through a class module.

HansV 462K Reputation points MVP Volunteer Moderator

2025-08-21T16:01:01.71+00:00

The problem is that a control in a class module does not have an Exit or AfterUpdate event...

Answer 5

Alex Baja 21

Thanks for your help. It was instructional. And as a result I found a way to accomplish this through the class module event.

HansV 462K Reputation points MVP Volunteer Moderator

2025-08-21T18:35:36.96+00:00

Congratulations! If you like, you could post your solution - it might be useful for others.

Share via

Binding an excel forms textbox exit event to a class module

7 additional answers

Your answer