VBA Code to format userform textbox without naming the textbox?

jacobrcotton

Board Regular
Joined
Jan 28, 2017
Messages
50
Hello,

I've created a data input userform (really, a series of userforms) in which end users will input data, many of which are dollar values, sometimes as a high as the tens of millions of dollars. Most users of the form will input the value of $10,432,765 as "10432765", which for me, is fine. However, for ease of checking the numbers by their supervisors/peers/whatever, I would like that number to always be formatted as "###,###,##0". Again, this is fairly easy. I'm using the code below:

Code:
Private Sub OperatingCashflows_AfterUpdate()
  
    IsNumericTest OperatingCashflows
    NotLessThanZeroTest OperatingCashflows
    
    OperatingCashflows = Format(OperatingCashflows.Value, "###,###,##0")
 
End Sub

However, there are a lot of these inputs and the format code I have above is specifically formatting the OperatingCashflows textbox. So for each textbox, that code needs to be copied and updated individually for each textbox. I'm looking to avoid wasting time by monotonously having to copy/paste hundreds of times.

What I'm hoping for is an easier way, and my vision is something like this code:
***NOT A FUNCTIONING CODE***

Code:
Private Sub OperatingCashflows_AfterUpdate()
  
    IsNumericTest Me
    NotLessThanZeroTest Me
    
    Me = Format(Me.Value, "###,###,##0")
 
End Sub

Any thoughts here?

Thanks in advance.

-jacob
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,929
Office Version
  1. 2016
Platform
  1. Windows
Hi jacob,

This is normally achieved via a Class Module which can let you define a generic event handler for all the textboxes... look here for an explanation.

There is an API based clever alternative explained here by AddinBox_Tsunoda but is rather involved .

Another alternative that is less complicated is to monitor each textbox via a background loop so that if the textbox being exited has had its text changed then run the Pseudo-AfterUpdate event handler.

Here is an example :

Code in the UserForm Module:
Code:
Option Explicit

Private bXit As Boolean

Private Sub UserForm_Activate()

   [COLOR=#008000] 'Excute any existing form setup code here before calling the 'StartMonitoring' routine[/COLOR]
  [COLOR=#008000]  '..................................................[/COLOR]
    
    Call StartMonitoring
    
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Call StopMonitoring
End Sub

Private Sub StartMonitoring()
    Dim oPrevTextBox As Control, oCurTextBox As Control
    Dim sPrevVal As String, sInitVal As String
    
    On Error Resume Next
    Do
        Set oCurTextBox = GetRealActiveTextBox
        If oPrevTextBox.Name <> oCurTextBox.Name Then
            If oPrevTextBox <> sInitVal Then
                If Not oPrevTextBox Is Nothing Then
                    Call Generic_AfterUpdate(oPrevTextBox)
                End If
            End If
            sInitVal = oCurTextBox.Value
        End If
        Set oPrevTextBox = GetRealActiveTextBox
        sPrevVal = oPrevTextBox.Value
        DoEvents
    Loop Until bXit

End Sub

Private Sub StopMonitoring()
    bXit = True
End Sub

Private Function GetRealActiveTextBox() As Control
    Dim oCtl As Control, lCurX As Long
    
    On Error Resume Next
    For Each oCtl In Me.Controls
        If TypeName(oCtl) = "TextBox" Then
            lCurX = oCtl.CurX
            If Err.Number = 0 Then Set GetRealActiveTextBox = oCtl: Exit Function
        End If
        Err.Clear
    Next oCtl
End Function

Private Sub Generic_AfterUpdate(ByVal oCtl As Control)

    Debug.Print
    Debug.Print "AfterUpdate Generic Event"
    Debug.Print "========================="
    
    Debug.Print "Textbox Name: "; "'" & oCtl.Name & "'"; vbTab; _
        "TextBox Text: "; "'" & oCtl.Text & "'"
End Sub

Note that the above code works even with textboxes located in MultiPage and/or Frame controls.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,702
Messages
5,524,410
Members
409,575
Latest member
navarrov74

This Week's Hot Topics

Top