VBA challenge, intending to write the resul of a Survey in a Worksheet. COMFUSED

[Gilcano]

Hi all, I'm learning VBA for Excell and I created a survey/inspection form with 13 multipage-tabs and 214 option buttons. I have four columns in every TAB = “Poor, Fair, Good and Excellent”. Under these ratings I have topics like “Carpet, Floor Appearance, Spot Cleaning, etc.” Depending of the user selection I write an evaluation note next to the last column. Ex. If the user chooses POOR I write “Needs Improvement” next to the last column (same row) and so on. I created a worksheet with the same layout in which I want to do the following: if the user chooses “POOR” I want to replace the corresponding CELL (“B6”) in the worksheet with 1 point, If the user chooses ‘’FAIR” replace the corresponding CELL (“C6”) with 2 points, if the user chooses “GOOD” replace the corresponding CELL (“D6”) with 3, if user chooses “EXCELLENT” replace the corresponding CELL (“E6”) with 4 points. I want to do the same for ALL 13 TAB then print the results in Range("A4:E71”). I’m confused on how to send the results to the worksheet.

This is what I did for every option buttons:
------------------------------------------------------------------------------------------------------------------------------------------
Private Sub OptCarpetPoor_Click()
If OptCarpetPoor.Value = True Then
CarpetComment.Caption = "Needs Improvement"
CarpetComment.ForeColor = 255
Else
CarpetComment.Caption = ""
End If
End Sub
-----------------------------------------------------------------------------------------------------------------------------------
Help and thanks

[shg]

Why not just do all of this with some validated cells (dropdowns), and use some formulas to generate the results?

[Gilcano]

I decided to put everything on a “userform” in which people just “click” an option instead of navigate on a worksheet because most of the people using the survey/inspection form are not very familiar with Excel. I just want to display the userform when the worksheet is open, have the user complete the options, give them the option to print it and get done, that’s all.

[shg]

I think you are underestimating people's skill at the cost of a lot of unneeded complexity.