+ Reply to Thread
Results 1 to 4 of 4

Using checkboxes to change cell color...with a twist!

  1. #1
    Registered User
    Join Date
    11-12-2021
    Location
    Chicago, IL
    MS-Off Ver
    Office 365
    Posts
    1

    Using checkboxes to change cell color...with a twist!

    Hi everyone!

    Here's my need, and my issue: In my line of work I run regular assessments with patients. The assessment tests the same skills each time, but there can be different levels of fluency. For example if the skill we were assessing was "Can create an excel spreadsheet" a zero ranking would be no ability to use excel, a 1 could be "uses basic functionality with verbal prompts, 2 maybe "Uses basic functionality independently", 3: "Advanced skills with prompt" and 4: "Advanced skills independently". So the first time we assess the skill it might be a "0", 6 months later it might be a "2" and so on. The traditional method for recording data has been to use a spreadsheet and fill in between 1 and 4 boxes next to the question number to show fluency. I might use a blue fill the first time I complete the assessment, then red the next time I do it, to show where growth has occurred.

    The benefits of this system is that we have the ability to very quickly and easily gauge where we've seen growth (which is something we need to retain), and where deficits still remain. Drawbacks are that it's clunky, a pain to manage while performing the live assessment, and then we've got to manually count up how many boxes are filled out of how many possible, and just yuck. As an upgrade, I think I'd like to have a datasheet with checkboxes so I can very easily select the appropriate ranking (0-4), while easily unselecting if necessary that then displays the results visually on a "results" worksheet.

    I can handle the datasheet to results sheet stuff, as well as the calculations (44 out of 120 boxes checked, etc.), but am having a hard time figuring out if it's possible to easily and globally change the color that a new checkbox will display when checked without changing the color of previously checked boxes. So, in the attached example, we'd see that J. Doe ran the assessment in June of 2021 and for question 1, assigned a "1" ranking. When S. Doe ran the assessment again in November 2021, a "3" was assigned, showing significant improvement in the skillset. So I'm wondering if there's a way to tell the spreadsheet "now whenever a click a checkbox, make the connected cell red", but then be able to go green the next time and yellow the next and so on.

    To make things more inconvenient, I'd love to avoid Macro/VBA stuff as I don't trust my colleagues to not break it and automation seems to be shut off and locked by default once a file goes through our shared storage system. So, I'm hoping to crowdsource a creative solution to a very esoteric and specifically pedantic issue. Thanks in advance for any help!
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,131

    Re: Using checkboxes to change cell color...with a twist!

    I am not following what you want to be colored and under what circumstances. Your description says
    for question 1, assigned a "1" ranking
    and then
    in November 2021, a "3" was assigned
    but this is not evident in your sample.
    displays the results visually on a "results" worksheet.
    No such sheet is provided, so it is not clear what results you are looking for.
    now whenever a click a checkbox, make the connected cell red
    Which cell do you mean by the connected cell? In your example the checkboxes have no linked cells.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,772

    Re: Using checkboxes to change cell color...with a twist!

    I'm not sure how a check box is going to give you the ability to specify the different levels of attainment. You would need 4 checkboxes (either in a line across columns or in one column over different rows).

    Anyway, if you right-click on one of the checkboxes you can allocate a cell that it is linked to, by clicking on Format Control - you could link the first checkbox to cell D3, for example, by clicking in the Cell Link box and then clicking on that cell, and $D$3 will appear in the box. If the checkbox is empty, then FALSE will appear in the linked cell, but if it is checked then TRUE will appear. You could use these values in a Conditional Formatting rule to set the colour of the cell (or show it as blank). You would need to do this for other checkboxes, setting the colour of the cell appropriate to the level that they represent.

    Hope this helps.

    Pete

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,131

    Re: Using checkboxes to change cell color...with a twist!

    My first take on this is you should be using data validation with a dropdown list with 0-4 instead of using checkboxes. I think that everything else would be easier and you might be able to do your color-coding with conditional formatting. But I'm still unclear as to what you want your result to look like. Also, it sounds like you will be forever having to add new checkboxes. Adding more data validation cells is much easier than adding new checkboxes and having to link them to cells.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Color changing: change the color of one cell, and have other cells change colors too
    By hadydea in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2019, 04:51 PM
  2. Change cell format (cell color+font color) based on color of another cell
    By Dedaluss in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-30-2017, 03:27 AM
  3. Change color of multiple checkboxes
    By eddie12 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-18-2016, 12:26 PM
  4. Macro to change cell text color based on darkness of cell fill color
    By cincyshirm61 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-25-2014, 12:11 PM
  5. Replies: 4
    Last Post: 04-28-2013, 12:47 AM
  6. [SOLVED] Cell Change run macro- with a twist
    By j.lancaster1 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-20-2012, 01:48 PM
  7. Format cell color on change, how to change the color in other columns as well?
    By s80NL in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-28-2007, 09:24 PM

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