PDA

View Full Version : VBA Forms - Passing a Variable



Dimitriy
12-19-2010, 08:39 PM
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: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

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!: pray2:

Bob Phillips
12-20-2010, 01:40 AM
This should work





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

Dimitriy
12-20-2010, 04:58 PM
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
12-21-2010, 01:10 AM
1.

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


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

Dimitriy
12-21-2010, 12:26 PM
Thank you!