Hi there,
in a userform multipage, I need to create multiple textboxes and a label which sums them up in a "live" manner which means that as soon as a value is entered/changed in a textbox, the label value changes. This may happen on mutiple pages but depending on inputs, some multipage pages may not have these functionalities. I read some similar issues in this forum and using class modules, I managed to do that... almost. The problem is: If only one page has textboxes and the label, it works perfectly. As soon as a second page has the same functionality, it stops working. So my first thought went to the textbox naming but all textboxes have unique names and are called by them so I cannot spot the mistake. I hope you can help me with that.
Creation of textboxes (all within a for loop which goes through the multipage pages using i):
Dim weightingsCriteriaTextbox As Control
Set weightingsCriteriaTextbox = MultiPage1.Pages(i + 1).Controls.Add("Forms.TextBox.1", "weighting_criterion_" & decisionFactor(i, j), True)
Creation of sum label:
Dim sumOfCriteria As Control
Set sumOfCriteria = MultiPage1.Pages(i + 1).Controls.Add("Forms.Label.1", "sumOfCriteria" & i, True)
Class module stuff (from this forum. I don't fully understand it but it does the job):
Set tbCollectionCriteria = New Collection
For Each ctrl In Me.MultiPage1.Pages(i + 1).Controls
If TypeOf ctrl Is MSForms.TextBox Then
Set objCrit = New clsTextBoxCriteria
Set objCrit.Control = ctrl
tbCollectionCriteria.Add objCrit
End If
Next ctrl
Set objCrit = Nothing
Within the class module:
Private WithEvents MyTextBox As MSForms.TextBox
Public Property Set Control(tb As MSForms.TextBox)
Set MyTextBox = tb
End Property
Private Sub MyTextBox_Change()
AutoCalc
End Sub
AutoCalc:
For i = 0 To numberOfFactors - 1
For j = 1 To numberOfCriteria(i)
Weightings(i, j) = CInt(Form_Weighting.Controls("weighting_criterion_" & decisionFactor(i, j)).value)
sum = sum + Weightings(i, j)
Next j
For Each ctrl In Form_Weighting.MultiPage1.Pages(i).Controls
If Left(ctrl.Name, 13) = "sumOfCriteria" Then
ctrl.Caption = "Total: " & sum
End If
Next ctrl
Next i
I assume this is the essential code. Let me know if you need more. If required, I could try to attach a minimal example workbook but as the actual work is confidential, this would need a quite a bit of rework so I thought maybe this code is already enough to solve the issue.
Any help is much appreciated!
Bookmarks