+ Reply to Thread
Results 1 to 19 of 19

master checkbox to activate vba in other checkboxes

  1. #1
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    365
    Posts
    16

    master checkbox to activate vba in other checkboxes

    Hello all! I'm looking to get some help with VBA to link a master checkbox to select/deselect other check-boxes that have macros attached to them. I've gotten the master checkbox to select/deselect the check-boxes, but it will not run the macro attached to those. Looking to get some help with that and maybe clean up my code to deselect the master checkbox should any of the sub check-boxes get deselected. I'm still very green but have been muddling along with VBA coding. Any help would be appreciated.

    This is the code I have for the master checkbox at the top of the sheet

    Please Login or Register  to view this content.
    I also call this one for another worksheet which is great because I don't have a range of cells that have check-boxes like the other one, but I feel it isn't as clean in the way it functions as it has to check each individual box making it slower, but probably better coding.

    Please Login or Register  to view this content.
    The Sub check-boxes the master controls has the following

    Please Login or Register  to view this content.
    Thanks in advance for any help!

    Edited to show code correctly per forum rules.
    Last edited by goalieman86; 07-26-2019 at 12:06 PM.

  2. #2
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    365
    Posts
    16

    Re: master checkbox to activate vba in other checkboxes

    I still need help on this and the question is just getting buried by other requests.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: master checkbox to activate vba in other checkboxes

    Administrative Note:

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    365
    Posts
    16

    Re: master checkbox to activate vba in other checkboxes

    AliGW,
    Did you see the updates and were they acceptable?

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: master checkbox to activate vba in other checkboxes

    Yes, that's fine, thanks.

    You aren't getting any help, however, so there must be something that potential helpers are not fully understanding. I suggest you attach your workbook so that helpers can see the issue in context.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  6. #6
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    365
    Posts
    16

    Re: master checkbox to activate vba in other checkboxes

    What i am after is a main checkbox to control sub check boxes like on a form fill where when you select the "master" checkbox, it will check all the other boxes on the page. So if the master is selected, then it will select all the other boxes. If it's un-selected it will deselect all the sub boxes.

    As stated above in my first post, I am able to select/deselect with the code I included. The issue is, those sub checkboxes run a macro that's attached to them to hide line items on other sheets in the workbook. I don't know how to tie the master checkbox to activate the macro on the sub checboxes. Do I need to call each individual macro in order for that to work? Or is there a cleaner way to see the event in VBA to automagically run the macros without a call to each one (30 of them)

    As a bonus I'd like it to deselect the master should any of the sub check boxes vary from the master:
    i.e. If all boxes are selected and 1 is unchecked, then it will deselect the master.

    I will try to get a sheet assembled to show my issue but it will take me some time as I am at work and won't be able to work on it till tonight.

  7. #7
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    365
    Posts
    16

    Re: master checkbox to activate vba in other checkboxes

    I've managed to do a quick and dirty clean-out of the sensitive data over my lunch.

    As you can see Sheet 1 & 2 will hide lines on Sheets 3,4,&5 when the line checkbox is true or not. The master at the top will control the boxes, but will not run the macro attached to those boxes on each line preventing the other sheets from hiding the appropriate lines.

    Thanks in advance for any help.
    Attached Files Attached Files
    Last edited by goalieman86; 07-30-2019 at 11:26 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: master checkbox to activate vba in other checkboxes

    Thanks for your message. I understand your frustration at not getting help here. All I can say is that the usual reason for this is a lack of clarity, but I don't think that's the case here. The only thing I can do is put a call out from help, but that won't necessarily guarantee a response. I will try it now.

    If this were something I could help with, I'd do so, but unfortunately I'm not a VBA expert.

  9. #9
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: master checkbox to activate vba in other checkboxes

    As far as I tried with my limited ability I could find no way for a macro to be triggered by the value being changed by another macro.

    You would have to loop through a written list of the macros and with the "_Click" extension not sure if they could be called.

    If they are only hiding / unhiding rows then the master macro could just reference a union of the ranges or some such rather than calling all macros

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: master checkbox to activate vba in other checkboxes

    You seem to have multiple checkboxes stacked on top of each other - is that intentional? (eg there are actually 94 checkboxes on sheet1).
    Rory

  11. #11
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    365
    Posts
    16

    Re: master checkbox to activate vba in other checkboxes

    No, that's not intentional, I have noticed that and been working on cleaning that part up. I'm not sure how that happened. This has been a pet project for about 6 years an may have accidentally gotten copied over each other and overlooked through that time that I've been slowly building and improving the spreadsheet and learning different functionality.
    Last edited by AliGW; 07-31-2019 at 10:35 AM. Reason: Please don't quote unnecessarily!

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: master checkbox to activate vba in other checkboxes

    OK, well I suggest you simply use a loop as you did before. It's not going to work without a loop, and really shouldn't be noticeably slower than setting them all at once anyway.

  13. #13
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    365
    Posts
    16

    Re: master checkbox to activate vba in other checkboxes

    Maybe I'm not clear on following what you're saying. I have no issues getting the checkboxes to change state. The issue is the macro won't run for those associated checboxes. I experimented and got the call function to work to run the macro to hide/show the line, but that is laborious in the coding as there are 45 macros to call, noticeably slowing down the sheet. Perhaps there isn't an easy or fast process to what I'm trying to achieve, but I want to make sure i exhaust my resources before going to the call process which slows down the end user.
    Last edited by goalieman86; 07-31-2019 at 11:07 AM.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: master checkbox to activate vba in other checkboxes

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

  15. #15
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: master checkbox to activate vba in other checkboxes

    What I'm saying is that you have to loop and explicitly change the value of each checkbox to get the macros to run. The looping is not what's slow, it's the fact that you are running 45 macros. It looks to me as though you'd be better off writing one macro that hides/shows all of the relevant rows and then just call that from the master checkbox. You could also, from my quick perusal of the code, just use one macro for all of the checkboxes that uses the TopLeft cell of the clicked checkbox to determine which rows need showing/hiding on the other sheets.

  16. #16
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    365
    Posts
    16

    Re: master checkbox to activate vba in other checkboxes

    And now we get into my infancy of my coding skills. I'm not sure how to go about getting a macro to affect that many rows without a breakdown of the way I have it. I've only begun to skim the power of VBA and have been piecing together what I know so far. I'm guessing my codes shows my lack of knowledge in this area. LOL

  17. #17
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    365
    Posts
    16

    Re: master checkbox to activate vba in other checkboxes

    On a side note about the multiple checkboxes. I did have those set as 3-D shading before as seen on Sh.4&5. Could that have created the multiple boxes when I disabled the 3-D shading?

  18. #18
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: master checkbox to activate vba in other checkboxes

    You just expand the ranges, something like this:

    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    365
    Posts
    16

    Re: master checkbox to activate vba in other checkboxes

    Ok! I understand now. I was thinking you meant individually as I was stuck on that line of thinking. Instead you're saying insert the above code to that "master" and have it control everything at once just like the sub checkboxes. Thanks for the clarification. I was a little tunnel visioned there.

+ 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] Checkboxes - How to check/uncheck other checkboxes based on another checkbox
    By brooklyn_87 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 10-27-2017, 01:20 PM
  2. master checkboxes to select a group of other checkboxes?
    By fael097 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-26-2016, 03:46 PM
  3. [SOLVED] a checkbox to select all checkboxes
    By almostgenius in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 07-22-2015, 11:26 AM
  4. Select All Checkboxes Checkbox That Also Calls Checkbox Macros
    By Intervigilium in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-23-2015, 06:41 PM
  5. Checkboxes VBA: name of checkbox to appear in a certain cell
    By Jungfuchs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-30-2013, 05:29 AM
  6. Trying to get checkbox to activate different worksheet
    By Nemo5 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2010, 03:45 AM
  7. Checkbox locking out other Checkboxes
    By Rich Kniatt in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-27-2005, 05:34 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