loop into multiple textboxes on userform to fill formatting numbers

[Mussa]

Hello

I have textboxes (textbox71,textbox78,textbox85,textbox92,textbox99,textbox106,textbox113,textbox120,textbox127,textbox134,textbox141)

and textboxes (textbox72,textbox79,textbox86,textbox93,textbox100,textbox107,textbox114,textbox121,textbox128,textbox135,textbox142)

I want filling number format when write number in any textbox then should show the number like this #,##0.00

is there any short procedure to does that by looping instead of using tradition way as known to put procedure for each textbox individually?
thanks

 

[Domenic]

Try . . .

    Dim i As Long
    For i = 71 To 141 Step 7
        'first set of TextBoxes
        With Me.Controls("TextBox" & i)
            .Value = Format(.Value, "$#,##0.00")
        End With
        'second set of TExtBoxes
        With Me.Controls("TextBox" & i + 1)
            .Value = Format(.Value, "$#,##0.00")
        End With
    Next i

Hope this helps!

 

[Mussa]

Thanks , but when I call your procedure from change event for any textbox the number shows like this
write 2500 ,then shows $2.00500 based on the code , but should show 2,500.00 .how can I fix it ,please?

 

[Domenic]

I would suggest using the BeforeUpdate event for each TextBox to validate and format the entry. For example, the code for TextBox1 might be something like this...

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.TextBox1
    
        If Len(.Value) = 0 Then Exit Sub
        
        If Not IsNumeric(.Value) Then
            Cancel = True
            .SelStart = 0
            .SelLength = Len(.Value)
            MsgBox "Please enter a number!", vbExclamation
            Exit Sub
        End If
    
        .Value = Format(.Value, "$#,##0.00")
        
    End With
    
End Sub

And the same thing for your other TextBoxes. Unfortunately, while it would normally be easier to use a class module to handle all of your TextBoxes with one event handler, the BeforeUpdate event among others are not available within a class module.

Hope this helps!

 

[Mussa]

Unfortunately, while it would normally be easier to use a class module to handle all of your TextBoxes with one event handler, the BeforeUpdate event among others are not available within a class module.

Bad news !!
this what I think to be possible .

I would suggest using the BeforeUpdate event for each TextBox to validate and format the entry

I know .
as you know sometimes when write in textbox when press enter there is no next textbox to show cursor and doesn't show format what you write the number or the user just write number without move cursor to next textbox whether by moving MOUSE or ENTER
that's why I ask about change event . may be function do that .
if you have any idea just inform me if you not I will satisfy your answering .
thanks

 

[Domenic]

Sorry, but I'm not familiar with any code that can be used in the Change event for this purpose. At best, I think it would be somewhat tricky. That's why I suggested using the BeforeUpdate event.

Cheers!

 

[Mussa]

don't worry .

just I ask you maybe you have better idea.

in reality you gives me two choices and you've solved what I want in post#2 and I prefer it, because I just put in userform module and call procedure from any textbox instead of write multiple lines as in second choice for each textbox .
two words are better than writing of multiple lines for each textbox .

thanks very much for your assistance .

 

[Domenic]

That's great, I'm glad you have what you need.

Cheers!