Hi All,
I feel like this should be easy but for the life of me I can't work it out!
I have a UserForm with various controls on it including some Comboboxes. When a change is made it triggers the change event and calls a sub in another module, passing the relevant controls as parameters:
N.B. I've simplified it here to just 3 of each control, when there are actually several.
Private Sub Textbox3_Change()
Call UpdateTotal2(Me.TotalTB2, Me.SubTotalTB1, Me.TextBox3, Me.TextBox6, Me.TextBox9, Me.ComboBox5, Me.ComboBox10, Me.ComboBox15,)
End Sub
The UpdateTotal2 Sub then checks if there are different currencies listed in the adjacent comboboxes and if they are all the same totals the textbox values and populates a total:
Public Sub UpdateTotal2(tbv As msforms.TextBox, sttb As msforms.TextBox, tb1 As msforms.TextBox, tb2 As msforms.TextBox, tb3 As msforms.TextBox, cbo1 As msforms.ComboBox, _
cbo2 As msforms.ComboBox, cbo3 As msforms.ComboBox,)
'Adds up the values in the Gross Fraud textboxes and puts the value in the Total textbox - called from TransForm2 (TextBoxn_Change)
Dim bCcy As Boolean
Dim objNo As Integer
'Check if all currency combobox values are the same
For objNo = 2 To 3
If ("cbo" & objNo).value <> "" Then
If cbo1.Value = TransForm2.Controls("cbo" & objNo).Value Then
bCcy = True
Else
'More than one currency code
bCcy = False
Exit For
End If
End If
Next objNo
'If all currency codes are the same total cash values, otherwise enter "N/A"
If bCcy = True Then
tbv.Value = Val(sttb) + Val(tb1) + Val(tb2) + Val(tb3)
Else
tbv.Value = "N/A"
End If
End Sub
The problem occurs with this line of code:
If ("cbo" & objNo).value <> "" Then
If I simply use:
Then the code works fine, but that will require me to reiterate the same line of code for every combobox of which there are many (simplified here). I've tried every variation I can think of, msforms.combox, UserForm2.Controls, .name, Val("cbo" & objNo), etc, etc.
Is there a way that I can reference the parameter by string variable?
Thanks, TC
Bookmarks