Efficiency Question about formatting 20+ text boxes on a form

[D3Pratt]

Currently I'm using this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Len(TextBox1.Value) > 3 Then
        TextBox1.Value = Format$(TextBox1.Value, "0,000")
    End If

End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Len(TextBox2.Value) > 3 Then
        TextBox2.Value = Format$(TextBox2.Value, "0,000")
    End If

End Sub

But I have more than 20 of that type of entry box on the form...

Since I need to do the "If Len(TextBox1.Value) >3" Check after the user inputs the data I can't think of a different way to do this.

Additionally there is no guarantee that the user will even NEED to put anything in more than half the boxes.

[D3Pratt]

I just realized I also need to include a value check for Numerical input only.

[protonLeah]

Partial Sol'n:

    For Each ctl In Userform1.Controls
        If TypeName(ctl) = "TextBox" AND Len(CTL.Value) > 3 Then
             CTL.Value = Format$(CTL.Value, "0,000")
        End If
    Next ctl

[D3Pratt]

Thanks for the reply.

Looks like it would cut out a bit of code...

...but where do I plug that in?

I tried to put it into the UserForm_Initialize(), it Kacked.


I'm still more than a bit of a noob at this.

[D3Pratt]

It just occurred to me that what I need to do is a subroutine that I can call out from the "on exit" sub of the Text cells.

Something like:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    CurValue = TextBox1.Value
    Call CheckNum
    TextBox1.value = CurValue

End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    CurValue = TextBox2.Value
    Call CheckNum
    TextBox2.value = CurValue

End Sub
Private Sub CheckNum


    (check for number here)
    If Len(CurValue) > 3 Then
        Curvalue = Format$(CurValue, "0,000")
    End If

End Sub

Or something like that.. I'll start playing with it.

[broro183]

Yes, you are on the right track :-)

I would change the CheckNum sub to a Function & then the code can be
modified along the lines of...

option explicit
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With TextBox1
        .Text = CheckNum(.Text)
    End With
End Sub
'...

Private Function CheckNum(CurValue As String)
'    (check for number here)
Select Case IsNumeric(CurValue)
    Case True
        CheckNum = Format$(CurValue, "#,###")
    Case Else
        CheckNum = "Please enter a number!"
End Select
End Function

From my brief testing, the use of the hash symbol appears to prevent the need for checking the length of the textbox string.

hth
Rob

[broro183]

Thanks for the feedback - I'm pleased we could help you to learn :-)

Rob