Reducing VBA redundancy

[Av8tordude]

I have numerous textboxes that use the code below in their respective change event. In the effort to reduce redundancy, is it possible to have just one code and call the code in each textbox change event?

Select Case Len(tbEq)
    Case 1
        tbEq = Format(tbEq, "$0\.00")
    Case Is > 1
        v = Replace(tbEq, "$", "")
        v = Replace(v, ".", "")
        tbEq = Format(v / 100, "$#,#0.00")
    Case Else
End Select

 

[Skyybot]

You could create Subroutine and have the On_Change event call it?

 

[Av8tordude]

I'm not a savvy coder and the code I posted I found using google. Can someone assist with writing a sub-routine that I can use?

Thanks

 

[Skyybot]

All of the TextBoxes have that same code in them?

 

[Av8tordude]

yes

All of the TextBoxes have that same code in them?

Yes..only the name of the textboxes are different

 

[Skyybot]

Try this.

Sub test()

thisButton = Application.Caller
Call buttonCode

End Sub
Sub buttonCode()

' Your TextBox code here

End Sub

 

[Av8tordude]

Nothing happens, but I suspect its because each textbox has a different name. The example code I posted, the textbox name is tbEq.

 

[dmt32]

Hi
Try following & see if does what you want

Standard Module

Sub FormatTextBox(ByVal objTextBox As Object)
    Dim v           As Variant
    Select Case Len(objTextBox.Value)
        Case 1
            objTextBox.Value = Format(objTextBox.Value, "$0\.00")
        Case Is > 1
            v = Replace(objTextBox.Value, "$", "")
            v = Replace(v, ".", "")
            objTextBox.Value = Format(v / 100, "$#,#0.00")
        Case Else
    End Select
End Sub

Call from your textbox change events & pass the Textbox object as an argument

Private Sub TextBox1_Change()
    FormatTextBox Me.TextBox1
End Sub

Dave

 

[Alex Blakenburg]

@dmt32, I suspect you have more experience with forms than I have but this seems to work for me, unless you can see a downside.

Call FormatTextBox(Me.ActiveControl)

 

[dmt32]

@dmt32, I suspect you have more experience with forms than I have but this seems to work for me, unless you can see a downside.

Call FormatTextBox(Me.ActiveControl)

@Alex Blakenburg

When I post answers for OPs I try & make my intentions as clear as possible (although I do not always succeed) in this case, specifying the actual control .
I like your suggestion - Apart from Call statement not strictly necessary but I know many like to use it, I can see no downside with your suggestion.

Dave