Count number of TextBoxes with certain name(s)

I have a userform that is a judging score sheet.
There are a couple of judging categories and each category has a few judging items.
I have devised a naming convention for the textboxes for scores that goes something like this...

txt[category name]&"Score"&[No.]
e.g. for "Routine Content" category, there are txtRoutineCategoryScore1 to txtRoutineCategoryScore3

I want to loop through these scores in each category to add up scores and display the category subtotal in a textbox that also bears a name like...

txt[category name]&"Subtotal"

I've searched the ways to count a number of score textboxes that has the same category name but so far hasn't found anything seemingly usable.
Could someone help me on this?

There are also maximum point for each item in a category and this is displayed in a label on the left of the score textbox;
e.g. where lblRoutineContentMaxP1 has a caption "10", the value in txtRoutineCategoryScore1 cannot exceed 10.
How would one write a code to do a validation like this?

Many thanks in advance.

Maki

Hi Maki,

See the attached workbook per your instructions, which has command buttons on a UserForm that:
a. Clear Totals.
b. Verify Values (WHITE if OK, MAGENTA if out of range, RED if not a number).
c. Calculates Totals assuming the number of TextBoxes of each type are known ahead of time.
d. Calculates Totals without knowing the number of TextBoxes of each type.

It also clears the totals each time a value for a category is changed.

My personal preference is NOT to calculate totals each time a keystroke is made in a TextBox, but to use a Command Button to initiate the calculations. My reason for this is that if calculations are done on each keystroke, warning and error indications will be visible each time the User presses an incorrect key, even if the source of the incorrect key is a 'FAT FINGER KEYSTROKE', which the user usually will correct immediately without prompting.

I suggest you follow what I've done by setting breakpoints and single stepping through the code in the debugger.

Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

Lewis

Hi Lewis

Massive thanks for the sample workbook.

Having studied your codes, I've created attached workbook.
I thought I could simplify the codes by putting Base Name in an array and do nested For Loops.

This, however, does not work, stops at 28th line "Next k" saying that there's no For statement.
I don't understand why because I meant the line 20 ("For k = 1 to j") to be the one to pair it to form the loop.

I wouldn't be surprised if there were more bugs but could you have a look at it to see what I'm doing wrong?

Many many thanks. Maki

Experiment.xls

Sub Calculate()

Dim ctrl As Control
Dim judgecategory As Variant
Dim i, j, k As Integer
Dim iscore, maxscore, subtotal As Double
Dim ctrlname, scoreboxname, maxscorelabelname As String

    judgecategory = Array("RoutineContent", "TechnicalMerit", "MusicalInterpretation")
        For i = LBound(judgecategory) To UBound(judgecategory)
            j = 0
            For Each ctrl In Me.Controls
                ctrlname = ctrl.Name
                If Left(ctrlname, Len(ctrlname) - 1) = "txt" & judgecategory & "Score" Then
                j = j + 1
                End If
            Next ctrl
            
            subtotal = 0
                For k = 1 To j
                    scoreboxname = CStr("txt" & judgecategory & "Score" & k)
                    maxscorelabelname = CStr("lbl" & judgecategory & "MaxP" & k)
                    iscore = CDbl(Me.Controls(scoreboxname).Value)
                    maxscore = CDbl(Me.Controls(maxscorelabelname).Caption)
                        If iscore > maxscore Then
                            Me.Controls(scoreboxname).BackColor = vbRed
                    subtotal = subtotal + iscore
                Next k
            Me.Controls("txt" & judgecategory & "Subtotal").Value = subtotal
        Next i

End Sub

Hi Maki and Norie,

Thanks Norie for updating Maki's code. In Norie's code I got a run time error when one of the Score Text Boxes was BLANK or contained a non-numeric character. See the attached (hopefully) corrected workbook and code that follows.

The dangling If without an Endif caused the for loop compile error.

Norie's changes in GREEN. My changes in Red:

Sub CalculateScores()
Dim ctrl As MSForms.Control
Dim judgecategory As Variant
Dim i As Long, j As Long, k As Long
Dim iscore As Double, maxscore As Double, subtotal As Double
Dim ctrlname As String, scoreboxname As String, maxscorelabelname As String

    judgecategory = Array("RoutineContent", "TechnicalMerit", "MusicalInterpretation")
    For i = LBound(judgecategory) To UBound(judgecategory)
        j = 0
        For Each ctrl In Me.Controls
            ctrlname = ctrl.Name
            If Left(ctrlname, Len(ctrlname) - 1) = "txt" & judgecategory(i) & "Score" Then
                j = j + 1
            End If
        Next ctrl

        subtotal = 0
        For k = 1 To j
            scoreboxname = CStr("txt" & judgecategory(i) & "Score" & k)
            maxscorelabelname = CStr("lbl" & judgecategory(i) & "MaxP" & k)
            iscore = 0
            On Error Resume Next
            iscore = CDbl(Me.Controls(scoreboxname).Value)
            On Error GoTo 0
            maxscore = CDbl(Me.Controls(maxscorelabelname).Caption)
            If iscore > maxscore Then
                Me.Controls(scoreboxname).BackColor = vbRed
            End If
            subtotal = subtotal + iscore
        Next k
        Me.Controls("txt" & judgecategory(i) & "Subtotal").Value = subtotal
    Next i

End Sub

Thank you, thank you, thank you to Lewis and Norie!

I have to say that dealing with array isn't my forte (nor everything else with vba ) but glad I persevered!

Again, thank you both and especially to Lewis for creating the sample workbooks.
It was such an inspiration!

Maki