VBA Code to format userform textbox without naming the textbox?

[jacobrcotton]

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:

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***

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

Any thoughts here?

Thanks in advance.

-jacob

 

[Jaafar Tribak]

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:

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.