Excel Userform - Check specific textboxes to ensure that only one in each section has a value entered

[jbeet]

Hello!
I have a userform which will be used to complete audits on employee work. There are 2 sections which will be audited with 1 of 3 possible responses (Yes, No, N/A). As the auditor completes the form they are pressing a button (Images - Thumbs Up, Down or N/A) which will populate a text box with the corresponding response. Only one of these text boxes in each section should be populated at any one time. So if they choose Yes but then change their mind to no, I would like the code to override the yes response, delete the text box value so that only the No response shows.
I know that this can be done with OptionButtons and grouping them together but I ran into an issue when I was trying to calculate the score once the form was filled in. I could not figure out how to get it to calculate basically in that scenario the same as I could when it was simply written (I'm sure it can be done by someone smarter than I).

An example of the result would be: If Yes is selected in the Payment Category and No is Selected in the Reserve Category the score would equal 50%. Or If Payment = NA and Reserve = Yes the score = 100%.

Below is the code I have. The First piece is the calculation. The last piece is currently giving and msgbox when there is a blank textbox but for this scenario I want a msgbox when there are more than one entry in each category. My textbox names are: (TXTPaymentPass, TXTPaymentFail, TXTPaymentNA) and (TXTReservePass, TXTReserveFail,TXTReserveNA)

```
Private Sub CommandButton1_Click()
Dim c As Control, nYes As Long, nNo As Long, nNA As Long
nYes = 0
nNo = 0
nNA = 0

For Each c In Me.Controls
If TypeName(c) = "TextBox" Then
If c.Value = "Yes" Then nYes = nYes + 1
If c.Value = "No" Then nNo = nNo + 1
If c.Value = "N/A" Then nNA = nNA + 1
End If
Next c
TXTScore = Format((nYes) / (nYes + nNo + nNA), "Percent")

For Each c In Me.Controls
If TypeName(c) = "TextBox" Then
If c.Value = "" Then na = MsgBox("Please choose Yes, No, or N/A before continuing.", vbOKOnly, "Quarterly Audits")
End If
Next c
End Sub
```

 

[AlphaFrog]

I know that this can be done with OptionButtons and grouping them together but I ran into an issue when I was trying to calculate the score once the form was filled in. I could not figure out how to get it to calculate basically in that scenario the same as I could when it was simply written (I'm sure it can be done by someone smarter than I).

An example of the result would be: If Yes is selected in the Payment Category and No is Selected in the Reserve Category the score would equal 50%. Or If Payment = NA and Reserve = Yes the score = 100%.

As you said, option buttons would be easier. Make the Captions of a grouped set of option buttons Yes, No, or N/A. This code checks the caption of the selected option and calculates the score.

    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.Value = True Then
                If c.Caption = "Yes" Then
                    nYes = nYes + 1
                ElseIf c.Caption = "No" Then
                    nNo = nNo + 1
                ElseIf c.Caption = "N/A" Then
                    nNA = nNA + 1
                End If
            End If
        End If
    Next c
    TXTScore = Format((nYes) / (nYes + nNo + nNA), "Percent")

 

[jbeet]

Thank you AlphaFrog- this is exactly what I needed and is so perfect! I really appreciate your help and quick response!