Help me please! Shortening some VBA code

Posted by Anna Daly on November 17, 2000 9:53 AM

Hello All,

I need your help in shortening some VBA code. In a user form I have alot of textboxes some used for user input others that are used to display results. For example:

Input35.value = Input23.value * Input29.value

where Input referes to textbox name.

The way that the form is used means that I require the following code to ensure that it works okay and to format any text entries etc. The problem is that I have 60 textboxes where this needs to be done. is there any way of rewiting the following code so that it works when the active textbox value is changed? Otherwise I will have to write the following code out another 20 times, which seems a bit silly, and dull.

cheers for your help.

here is the code:

Function CheckNum(TB As control)
If Not ((IsNumeric(TB.Value)) Or TB.Value = "") Then
MsgBox "This field only accepts numeric values!", vbCritical
CheckNum = False
TB.Value = ""
Else
CheckNum = True
TB.Value = Format(TB.Value, "#,##0")
End If
End Function

Sub Input23_Change()
If CheckNum(Input23) = False Then tryagain = True
If Input23.Value &LT;&GT; "" And Input29.Value &LT;&GT; "" Then
Input35.Value = Format(Input23.Value * Input29.Value, "#,##0")
Else
Input35.Value = ""
End If
End Sub

Sub Input29_Change()
If CheckNum(Input29) = False Then tryagain = True
If Input23.Value &LT;&GT; "" And Input29.Value &LT;&GT; "" Then
Input35.Value = Format(Input23.Value * Input29.Value, "#,##0")
Else
Input35.Value = ""
End If
End Sub

Posted by Tim Francis-Wright on November 17, 2000 10:55 AM

You could rewrite the second two routines as follows: (warning: not completely checked)

Sub Input23_Change()
If CheckNum(Input23) = False Then tryagain = True
Call Mult (Input23,Input29,Input35)End Sub

Sub Input29_Change()
If CheckNum(Input29) = False Then tryagain = True
Call Mult(Input23, Input29, Input35)
End Sub

Sub Mult(TB1 as Control, TB2 As Control, TB3 As Control)
If TB1.Value &LT;&GT; "" And TB2.Value &LT;&GT; "" Then
TB3.Value = Format(TB1.Value * TB2.Value, "#,##0")
Else
TB3.Value = ""
End If
End Sub

You could then have each of the Inputxx_Change
subs call the Mult subroutine as needed.

-->By the way, the tryagain reference won't have
any affect in a Change event sub; it would
work in a BeforeUpdate sub:

Sub Input29_BeforeUpdate(ByVal tryagain As MSForms.ReturnBoolean)