VBA Excel - Check the validation of multiple textboxes upon clicking a button

Question

I'm new to this VBA thing. My concern is I'm trying to validate multiple textboxes to only accept numbers between 60 to 100. I just want to ask if it is possible to use Loop for it or I need to create a Sub for each textbox or is there something else that I can do?

Userform:

My idea is that when the 'Calculate' button is clicked, the validation runs.

I tried to use the IF statement but I think I used it in the wrong way.

If tb_mtb <=60 And tb_mtb >=100 And tb_fil <=60 And tb_mtb >=100 And tb_eng <=60 And tb_eng >=100 And _
tb_math <=60 And tb_math >=100 And tb_sci <=60 And tb_sci >=100 And tb_ap <=60 And tb_ap >=100 And _
tb_mapeh <=60 And tb_mapeh >=100 And tb_music <=60 And tb_music >=100 And tb_arts <=60 And _
tb_arts >=100 And tb_pe <=60 And tb_pe >=100 And tb_health <=60 And tb_health >=100 And _
tb_esp <=60 And tb_esp >=100 Then
'More codes here
End If

I also tried this Loop as well, but it checks the value and opens a MsgBox one by one.

For Each Ctrl In Me.Frame3.Controls
        If TypeName(Ctrl) = "TextBox" Then
            If Ctrl.Text >= 60 And Ctrl.Text <= 100 Then
                CheckTB
                MsgBox "Yey!"
            Else
                MsgBox "Error"
            End If
        End If
    Next Ctrl

question from:https://stackoverflow.com/questions/65911864/vba-excel-check-the-validation-of-multiple-textboxes-upon-clicking-a-button

Answer 1

You would keep count of any problems and then message once the loop is done.

Untested so may need tweaks:

Dim v, nProb As Long

For Each ctrl In Me.Frame3.Controls
    If TypeName(ctrl) = "TextBox" Then
        v = ctrl.Text
        ctrl.Font.Color = vbBlack
        If Len(v) > 0 Then
            v = CDbl(v) 'or Clng() ?
            If v < 60 Or v > 100 Then
                nProb = nProb + 1
                ctrl.Font.Color = vbRed 'flag error
            End If
        End If
    End If
Next ctrl

If nProb > 0 Then
    MsgBox "'Subjects' values must be between 60 and 100"
Else
    MsgBox "All good!"
End If