+ Reply to Thread
Results 1 to 17 of 17

Evaluation with Radio buttons and Option buttons

  1. #1
    Registered User
    Join Date
    05-12-2010
    Location
    Toronto, ontario
    MS-Off Ver
    Excel 2003
    Posts
    10

    Evaluation with Radio buttons and Option buttons

    I am very new to excel, and am trying to create a survey that will allow users to click one of the answers (radio or option button) and each answer to return a score in a hidden column, and then a total at the end. Is anyone familiar with this. I have attached my spreadsheet hoping that you wonderful people could help.

    THANKS IN ADVANCE
    Attached Files Attached Files
    Last edited by soph0101; 05-12-2010 at 09:44 AM.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Evaluation with Radio buttons and Option buttons

    You should avoid the use of the option buttons altogether. They will clutter the form and present an unprofessional appearance as well as require more work to implement.

    My suggestion is to use a Marlett check box and let the user "tick" the cell. It is cleaner and more professional looking.

    See the attached example using your workbook. At the bottom of the survey is the following formula to count which cells are ticked. (Note: I mention "Marlett", which is a font, but opted to use Wingdings in the code)

    =COUNTIF(G2:G52,"ü")

    Code for "ticking" the cell.

    Please Login or Register  to view this content.
    Note: you should avoid using merged cells, which I have unmerged, because they cause issues with VBA code.
    Attached Files Attached Files
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Registered User
    Join Date
    05-12-2010
    Location
    Toronto, ontario
    MS-Off Ver
    Excel 2003
    Posts
    10

    Smile Re: Evaluation with Radio buttons and Option buttons

    Quote Originally Posted by Palmetto View Post
    You should avoid the use of the option buttons altogether. They will clutter the form and present an unprofessional appearance as well as require more work to implement.

    My suggestion is to use a Marlett check box and let the user "tick" the cell. It is cleaner and more professional looking.

    See the attached example using your workbook. At the bottom of the survey is the following formula to count which cells are ticked. (Note: I mention "Marlett", which is a font, but opted to use Wingdings in the code)

    =COUNTIF(G2:G52,"ü")

    Code for "ticking" the cell.

    Please Login or Register  to view this content.
    Note: you should avoid using merged cells, which I have unmerged, because they cause issues with VBA code.

    Palmetto, I cannot begin to express my gratitude for your help with this. You managed to do exactly what I was looking for. Only one more question. Is there any way to make each column worth a different value. U=1 I=2 ME=3 EE=4 EX=5.
    Also rather than the total being at the end of each column there is a final score depending on which column is clicked. I don't know if that is possible and I know absolutely nothing about code and programming. THANKS YOU SO MUCH FOR ANY HELP!! This has been boggling me for weeks.

  4. #4
    Registered User
    Join Date
    05-12-2010
    Location
    Toronto, ontario
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Evaluation with Radio buttons and Option buttons

    Palmetto, I cannot begin to express my gratitude for your help with this. You managed to do exactly what I was looking for. Only one more question. Is there any way to make each column worth a different value. U=1 I=2 ME=3 EE=4 EX=5.
    Also rather than the total being at the end of each column there is a final score depending on which column is clicked. I don't know if that is possible and I know absolutely nothing about code and programming. THANKS YOU SO MUCH FOR ANY HELP!! This has been boggling me for weeks.

  5. #5
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Evaluation with Radio buttons and Option buttons

    soph0101,

    First, please do not quote entire posts as it clutters up the thread. Just copy the relevant portion you want to reference and then use the quote tags. You can easily apply the quote tags by highlighting the text and then clicking on the quote button (has the "comment" icon).

    I'm not clear on how you plan to calculate a total score or the score per column (if not using something like I showed). Using the formula I provided, you can assign the weight directly in the formula.

    =COUNTIF(H2:H52,"ü")*2
    =COUNTIF(I2:I52,"ü")*3
    etc.

    Column-G would be skipped as the sum of it's values is the score for that column.

    If this is not helpful, please be more clear and provide an example of what you mean.

  6. #6
    Registered User
    Join Date
    05-12-2010
    Location
    Toronto, ontario
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Evaluation with Radio buttons and Option buttons

    Please see the attached spreadsheet as an example with comments. hopefully I could get a solution to this. Thanks again!!! : )
    Attached Files Attached Files

  7. #7
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Evaluation with Radio buttons and Option buttons

    See attached for solution.
    On the same row as the question, beginning in column-R, this formula:
    =IF(COUNTBLANK(G7:K7)=5,0,MATCH("*",G7:K7,0))
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-12-2010
    Location
    Toronto, ontario
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Evaluation with Radio buttons and Option buttons

    Thanks for the help! It is perfect.

    Is there any way I could have a column that is N/A that can be checked but is not given any value? Please see attached. Thanks
    Attached Files Attached Files

  9. #9
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Evaluation with Radio buttons and Option buttons

    Replace the existing code with this. The only change required is to increase the column number in the code.
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    05-12-2010
    Location
    Toronto, ontario
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Evaluation with Radio buttons and Option buttons

    Sorry, do not mean to be a pain. But I have no idea how to work with codes. Could you possibly attach? I will never bother you again lol THANK YOU!!! Palmetto you have been very helpful

  11. #11
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Evaluation with Radio buttons and Option buttons

    But I have no idea how to work with codes
    No time the like the present for learning.

    With the workbook open and sheet1 as the active sheet:

    Right-click the sheet tab and choose "View Code" from the context menu
    Look for this line of code and change the 11 to 12
    Please Login or Register  to view this content.
    Next, look for this line of code and do the same
    Please Login or Register  to view this content.
    Press Alt + Q keys to close the VB Editor then save the workbook. The changes will take effect immediately.

  12. #12
    Registered User
    Join Date
    05-12-2010
    Location
    Toronto, ontario
    MS-Off Ver
    Excel 2003
    Posts
    10

    Smile Re: Evaluation with Radio buttons and Option buttons

    It worked! thank you once again your a genius!

  13. #13
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Evaluation with Radio buttons and Option buttons

    Glad to have been of help.

    If you are satisfied with your solution, please mark the thread as solved by going to the top of your first post and clicking on Thread Tools, then Go Advanced and choose the solved prefix.

  14. #14
    Forum Contributor
    Join Date
    06-14-2010
    Location
    Toronto,Canada
    MS-Off Ver
    Excel 2003
    Posts
    145

    Re: Evaluation with Radio buttons and Option buttons

    Hi Palmetto,
    I am amazed by the code you posted on this thread
    I don;t know if I can post a question on a different thread, but it kind of related to the code you posted.
    I have to make a macro that does something similar, and I am 1 week old to macros and needed some help, if possible

    Attached is the modified code of sorta what I want to do (arrange into columns instead of rows with no select only one option)
    I was wondering if u could help me write the part where I can select a block of cells (in same column), so that all the selected cells with have tick marks on them
    Any help from anyone would be greatly appreciated
    Thanks
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    06-14-2010
    Location
    Toronto,Canada
    MS-Off Ver
    Excel 2003
    Posts
    145

    Re: Evaluation with Radio buttons and Option buttons

    Hi,
    I was playing with it for awhile and figure it out, but this is a bit of code that I added and got exacly what i wanted

    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    01-21-2010
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    105

    Re: Evaluation with Radio buttons and Option buttons

    I have ustilised this code for a similar survey type evaluation. I was wondering if there was a way that if a row was inserted that the formuals moved down rather than having to change the code each time or perhaps a button to insert rows if questions need to be added or removed from the sections.
    Can anyone help?

  17. #17
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Evaluation with Radio buttons and Option buttons

    @Libster78, you've been around long enough to know the rules by now...

    Your post does not comply with Rule 2 of our Forum RULES.
    Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant (as is clearly the case here), provide a link to the other thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1