+ Reply to Thread
Results 1 to 17 of 17

Checkboxes - How to check/uncheck other checkboxes based on another checkbox

  1. #1
    Registered User
    Join Date
    12-11-2012
    Location
    South Dakota, USA
    MS-Off Ver
    2016
    Posts
    14

    Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    I don't have much experience/knowledge of macros, but I'm sure there's got to one out there that can do what I have in mind. Workbook with example is attached.

    I have 3 main scenarios that a customer could choose. Based on what scenario they choose, there are certain products that come automatically with that scenario.

    I would like someone to be able to:
    1. (COMPLETE) Check a box to select which scenario they want (I have checkboxes already set up in Row 2 that do this part)
    2. Once a scenario is selected, the other 2 scenarios cannot be selected at the same time (i.e. only scenario 1, 2, OR 3 can be checked, you can't check scenario 1 AND 2...)
    3. Once a scenario is selected, the appropriate product checkboxes automatically check and product checkboxes for the other scenarios get unchecked (i.e. if I chose scenario 1, the appropriate products under scenario 1 would get checked but all products under scenarios 2 and 3 would get unchecked)
    4. Keep the ability to check and uncheck manually (i.e. certain products come standard with scenario 1, but you have the option to check/uncheck a product to add/remove it ad-hoc)

    The way the spreadsheet is set up now shows which products should be included in each scenario. I just can't get the macro to automate what I laid out above.

    Thank you so much!!
    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
    18,809

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    only scenario 1, 2, OR 3 can be checked
    I will first give you the bad news that for this requirement, you should use option buttons instead of checkboxes. That's exactly what they are designed for and need no code for this behavior.

    #3 isn't hard to write a macro for but you should replace the checkboxes (all of them) with option buttons first. You are using Forms controls. I prefer ActiveX controls, and some people prefer Forms controls. If you want to use Forms controls for the option buttons, you need to group them in a Group box so the buttons know what group they belong to. First create a Group Box then put each group of buttons in a group box. I have done a couple of quick and dirty examples for you.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    12-11-2012
    Location
    South Dakota, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    Awesome - thanks. I replaced the checkboxes for Scenario 1, 2, and 3 with option buttons, which solves for my #2 requirement. Still looking for some assistance on #3 and #4:

    3. Once a scenario is selected, the appropriate product checkboxes automatically check and product checkboxes for the other scenarios get unchecked (i.e. if I chose scenario 1, the appropriate products under scenario 1 would get checked but all products under scenarios 2 and 3 would get unchecked)
    4. Keep the ability to check and uncheck manually (i.e. certain products come standard with scenario 1, but you have the option to check/uncheck a product to add/remove it ad-hoc)

    Thank you!!
    Attached Files Attached Files

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    Hi brooklyn_87

    Try this Code in the attached assigned to the Option Buttons...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Registered User
    Join Date
    12-11-2012
    Location
    South Dakota, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    Hey jaslake -

    Thanks so much! So this mostly gets what I'm looking for, except I don't want all of the checkboxes checked under scenario 1 and scenario 2...is there a way to modify that macro so that for each scenario, it only checks certain checkboxes, not all checkboxes?

    Thanks!

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    Hi brooklyn_87

    You'll need to be a bit more explicit...
    is there a way to modify that macro so that for each scenario, it only checks certain checkboxes, not all checkboxes?

  7. #7
    Registered User
    Join Date
    12-11-2012
    Location
    South Dakota, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    I think in my example spreadsheet I have 13 products that can be checked for each scenario. Say if Scenario 1 is chosen, I only want 7 of those products chosen/checked automatically; then if Scenario 2 is chosen, maybe I want 10 of those 13 products checked automatically; and if Scenario 3 is chosen then I'd want all checked.

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    Quote Originally Posted by brooklyn_87 View Post
    I think in my example spreadsheet I have 13 products that can be checked for each scenario. Say if Scenario 1 is chosen, I only want 7 of those products chosen/checked automatically; then if Scenario 2 is chosen, maybe I want 10 of those 13 products checked automatically; and if Scenario 3 is chosen then I'd want all checked.
    Will Scenario 1 ALWAYS be 7? Does it matter WHICH 7?
    Will Scenario 2 ALWAYS be 10? Does it matter WHICH 10?

  9. #9
    Registered User
    Join Date
    12-11-2012
    Location
    South Dakota, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    Reattached spreadsheet - so the options highlighted in yellow would be the checkboxes I want checked if that Scenario is chosen.
    Attached Files Attached Files

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    Hi brooklyn_87

    Will your Options 1 ALWAYS have 5 Products?
    Will your Options 2 ALWAYS have 1 Products?
    Will your Options 3 ALWAYS have 3 Products?
    Will your Options 4 ALWAYS have 3 Products?
    Will your Options 5 ALWAYS have 1 Products?

    Will you ALWAYS have ONLY 5 Options?

  11. #11
    Registered User
    Join Date
    12-11-2012
    Location
    South Dakota, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    Yep, the spreadsheet as it sits now is how it will always be setup.

  12. #12
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    Hi brooklyn_87

    Your Scenario 2 appears to have eleven (11) yellow Checkboxes highlighted...which is correct...10 or 11?

  13. #13
    Registered User
    Join Date
    12-11-2012
    Location
    South Dakota, USA
    MS-Off Ver
    2016
    Posts
    14
    11 wouod be correct...whatever is highlighted is the corrext one! Thanks!!

    Quote Originally Posted by jaslake View Post
    Hi brooklyn_87

    Your Scenario 2 appears to have eleven (11) yellow Checkboxes highlighted...which is correct...10 or 11?

  14. #14
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    Hi brooklyn_87

    Try this Code in the attached...appears to do as you describe...
    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,498

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    A suggestion:

    Named ranges corresponding to the linked cells of the checkboxes:
    Scenario1Products =Summary!$F$6:$F$8,Summary!$F$13,Summary!$F$16,Summary!$F$21:$F$22
    Scenario2Products =Summary!$G$6:$G$9,Summary!$G$13,Summary!$G$16:$G$17,Summary!$G$21:$G$23,Summary!$G$26
    Scenario3Products =Summary!$H$6:$H$10,Summary!$H$13,Summary!$H$16:$H$18,Summary!$H$21:$H$23,Summary!$H$26

    clear the linked cells on startup:

    Please Login or Register  to view this content.
    Option Explicit

    on option selection:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

  16. #16
    Registered User
    Join Date
    12-11-2012
    Location
    South Dakota, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    Thank you both! I think this gets what I was looking for!

  17. #17
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Checkboxes - How to check/uncheck other checkboxes based on another checkbox

    You're welcome...glad I could help. Thanks for the Rep.

+ 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] check and uncheck certain checkboxes with a button
    By 2mi3 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 03-29-2015, 03:55 PM
  2. Macro to automatically check or uncheck checkboxes based on a linked cell
    By lmshow in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-19-2013, 03:31 AM
  3. [SOLVED] multiple checkboxes macro check/uncheck
    By SHELTONUNDERDOG in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2012, 01:36 PM
  4. How to check and uncheck a column of checkboxes with 1 checkbox?
    By kteo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-10-2012, 08:42 PM
  5. Forms Check/Uncheck CheckBoxes
    By Kaziglu Bey in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-26-2008, 03:04 PM
  6. Check/Uncheck all checkboxes
    By JChandler22 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-23-2007, 03:03 PM
  7. Replies: 5
    Last Post: 01-04-2006, 07:15 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