Which the best solution for using Likert Scale in a VBA Form?

Dear ones,
I need to do a form where the users can answer an evaluation quizz, using a likert Scale. This scale range goes from 1 to 10, where 1 stands for Poor and 10 for Excelent. So far the solution is to use "option buttons" from the VBA toolbox. But because the quizz is big, about 80 questions where user can answer 1 of 10 options, according the Likert scale, it implies that I have to create in my form one frame with 10 buttons options per question, which means 800 buttons. This means that my VBA code for processing the user quizz answers needs to check wich of the the 10 options was selected for each question, so 800 times is checking. I guess this will have impact in VBA code performance when being executed, and also huge lines of code. I am wondering if there is better solution since the end users want to use the likert scale with VBA button option, because is easier to fill for the end user.

So I ask you if there is any additional Controll Button of options that we can add in the VBA toolbox that overcomes this problem, and becomes easier to check the user answers. May there is a matrix of Button options, with a easier way to check the user options.

I attach a picture of likert scale that is so easier to add in Microsoft Form or even Google Form, for you to have an idea of what I need to implement in VBA code for an Excel Form.

Hope you can help me and thank you in advance!


My kind regards,
Carla Godinho
Likert_Scale.gif

Dear Torachan,
Thanks a lot with fast answer and your xlsm sample. Although I did not had time to check the VBA code, I cheked que Form and the excel table, and It gives me an ideas how to solve the problem considering that I will put in a table with the results of the evaluation per participant and then generate a pivot table to sumarize the results. I would like to ask you if I can adapt your VBA code in a way that the form will have several pages, 10 questions per page instead of just one per page. So each page of the form will have 10 questions. Example: page 1, questions from 1 to 10, page 2 questions from 11 to 20, page 3, questions from 21 to 30 and so on. I guess is possible although I am still a begginer programing in VBA.


I will let you know when I adap the code.

Once again thak you so much!

Kind regards,
Carla

Thanks for you availability, I will let you know!

Dear Torechan,
Just to let you know that I was able to adapt my cod to use Likert scale for several questions, each group of questions in different pages. Thanks a lot!

I would like to ask you if there is any property in each "option button" that we can unselect the option button (if we press again on the "option button" that was selected).

My next step is to garantee that the user when filling the Likert Scale form has the possibility to unselect the option button, and so not answer to specific questions (the questions are optional for answering).

Let' imagine that the user is filling the likert scale:

Question1: option 1 option2 option3

Once the user select one of the 3 options buttons of the group of the question1 there no possibility to unselect the option. For example the user may want for that question not answer, so no option button will be selected.

hope you can help me!

My kind regards,
Carla

Thanks Tora,
For all your help. My knowledge in VBA it is too lack for the wonderful code you did.
I undertood your ideas, and although I cannot readapt as you did your code, as I have already many code done and the Forms. But I have the main ideas that helped me not to be stucked.

I use a comand button instead of check box, did like this:

Private Sub cm1_1_1_Click()
Dim z As Integer
For z = 1 To 10
FormAvaliacao.Controls("op1_1_1_score" & z) = False
Next z
End Sub

It is working! Thanks a lot!

My kind regards,
Carla Godinho