VBA Forms - Passing a Variable

[Dimitriy]

Hey Guys,

I have the following problem: I have a "Before Update" Private Sub for a form textbox that checks to make sure that the input is numeric and then converts it to a currency format. Here is the code:[VBA]Private Sub txtIncBudAmount2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'check for currency format
With txtIncBudAmount2
If .Text = "" Then
ElseIf IsNumeric(.Text) Then
.Text = Format(.Text, "Currency")
Else
MsgBox "Budget Amount format is incorrect. Standard format is #.##"
Cancel = True
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub[/VBA]

I have about 35 textboxes like this. So in order to save some code lines I wanted to write a general Sub that will do this and then just pass it to a "Before Update" sub for each textbox.

For some reason I either getting an error or it throws Excel into a loop when I try to do this. I would really appreciate it if you could suggest a good way get this done.

Thanks!

[Bob Phillips]

This should work

[vba]



Private Sub txtIncBudAmount2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Call TB_BeforeUpdate(txtIncBudAmount2, Cancel)
End Sub

Private Sub TB_BeforeUpdate(ByRef TB As MSForms.TextBox, ByRef Cancel As MSForms.ReturnBoolean)
'check for currency format
With TB
If .Text = "" Then
ElseIf IsNumeric(.Text) Then
.Text = Format(.Text, "Currency")
Else
MsgBox "Budget Amount format is incorrect. Standard format is #.##"
Cancel = True
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub
[/vba]

[Dimitriy]

Thank you so much! Worked like a charm. Couple of follow-up questions for you:

1. Is it possible to create a loop for these procedures (for example, loop through textbox1 to textbox30 with a single string of code)?

2. How would I do about creating a dynamic code where the number of textboxes can change later on?

Thank you very much for your help.

-Dimitriy

[Bob Phillips]

1. [vba]

Private Sub TB_All_BeforeUpdate()
Dim i As Long
'check for currency format
For i = 1 To 30

With Me.Controls("TextBox" & i)

If .Text = "" Then

ElseIf IsNumeric(.Text) Then

.Text = Format(.Text, "Currency")
Else

MsgBox "Budget Amount format is incorrect. Standard format is #.##"
End If
End With
Next i
End Sub
[/vba]

2. How would these textboxes get added in this dynamic situation?

[Dimitriy]

Thank you!