Userform - TextBox Apply Currency on Change (multiple)

[delaing]

I have a Userform which has multiple TextBox's for inputting currency values.

value history UF.png

On other forms where I may have fewer of these TB's, I use a Change event on the TextBox so when the user clicks in the TB it will receive the numbers and display in a currency format as it is typed.
With the form shown here, is there a way that I can make the same thing happen without creating a Change procedure for every single TB? Something similar to the procedures which loop through all of a similar form Control type and applies some format or such?

Here is an example of my Change code for a TB to display currency as it is input:

Code: Select all

Private Sub txtEsplit_Change()
    txtEsplit.value = Format(txtEsplit.value, "$#,##0")
End Sub

Thanks in advance,
Delain

[HansV]

Step 1:

Select Insert > Class Module.
Name the class module clsTextBox.
Copy the following code into the class module:

Code: Select all

Public WithEvents MyTextBox As MSForms.TextBox

Private Sub MyTextBox_Change()
    Dim v As String
    v = Replace(MyTextBox.Value, "$", "")
    MyTextBox.Value = Format(v, "$#,##0")
End Sub

Step 2:

Activate the code module of one of the userforms in the Visual Basic Editor.
Delete the Change event procedures of the text boxes.
Copy the following code into the module, at the top, below Option Explicit but above all Subs:

Code: Select all

Private arrTextBoxes() As New clsTextBox

Private Sub UserForm_Initialize()
    Dim ctl As Control
    Dim n As Long
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Then
            n = n + 1
            ReDim Preserve arrTextBoxes(1 To n)
            Set arrTextBoxes(n).MyTextBox = ctl
        End If
    Next ctl
End Sub

Private Sub UserForm_Terminate()
    Erase arrTextBoxes
End Sub

If you already had a Userform_Initialize event procedure, you'll have to add its code to the above procedure. A userform can have only one Initialize event procedure.

Step 3:

Repeat step 2 for the other userforms.

[delaing]

Hans,

Well, so far, it didn't blow up on me. My implementing your code into my existing Initialize, that is. Works like a new, freshly, polished pair of clogs sliding down a snow-covered hill - a much faster way to get where I wanted to go.

Thank you for taking the time to create the sequence of how to implement this. I have yet to venture into Class module usage. I have to suppose there are other ways that using them would optimize my coding; just haven't gotten there yet.

How does "MyTextBox" in the Class sub relate to the original TextBox names that are defined during design? Are they being renamed temporarily in the Class somehow so it can apply the formatting?

Thank you,
Delain

[HansV]

The class module defines a Change event handler for a generic text box.

In the UserForm_Initialize event, we create a new instance of the class module for each text box in the array arrTextBoxes; this "binds" the generic event handler to the individual text boxes.

[delaing]

Beautiful !!

Solved !!

Delain