+ Reply to Thread
Results 1 to 5 of 5

Looping through checkboxes - HELP NEEDED

  1. #1
    Registered User
    Join Date
    07-10-2018
    Location
    Manchester, England
    MS-Off Ver
    Office 2010
    Posts
    8

    Post Looping through checkboxes - HELP NEEDED

    Hi

    In my spreadsheet, I have 30 x rows of checkboxes with 12 x checkboxes on each row.

    On each row, one checkbox is used as a master control to select / de-select all the other eleven checkboxes. The ‘master’ checkboxes are all logically named from “MasterL01” on Line 1 to “MasterL30” on Line 30

    On each line, the other eleven checkboxes are named logically as follows:

    Line 1:L01CB01”, “L01CB02”, “L01CB03” up to “L01CB11
    Line 2: L02CB01”, “L02CB02”, “L02CB03” up to “L02CB11”
    Line 3: L03CB01”, “L03CB02”, “L03CB03” up to “L03CB11”
    etc

    To each of the ‘master’ checkboxes I have assigned a macro such as this one, assigned to “MasterL01”:

    Sub Master_L01()

    ‘ This macro is used to select all / deselect all checkboxes on Line 1

    ActiveSheet.CheckBoxes("L01CB01").Value = ActiveSheet.CheckBoxes("MasterL01").Value
    ActiveSheet.CheckBoxes("L01CB02").Value = ActiveSheet.CheckBoxes("MasterL01").Value
    ActiveSheet.CheckBoxes("L01CB03").Value = ActiveSheet.CheckBoxes("MasterL01").Value
    ActiveSheet.CheckBoxes("L01CB04").Value = ActiveSheet.CheckBoxes("MasterL01").Value
    ActiveSheet.CheckBoxes("L01CB05").Value = ActiveSheet.CheckBoxes("MasterL01").Value
    ActiveSheet.CheckBoxes("L01CB06").Value = ActiveSheet.CheckBoxes("MasterL01").Value
    ActiveSheet.CheckBoxes("L01CB07").Value = ActiveSheet.CheckBoxes("MasterL01").Value
    ActiveSheet.CheckBoxes("L01CB08").Value = ActiveSheet.CheckBoxes("MasterL01").Value
    ActiveSheet.CheckBoxes("L01CB09").Value = ActiveSheet.CheckBoxes("MasterL01").Value
    ActiveSheet.CheckBoxes("L01CB10").Value = ActiveSheet.CheckBoxes("MasterL01").Value
    ActiveSheet.CheckBoxes("L01CB11").Value = ActiveSheet.CheckBoxes("MasterL01").Value

    End Sub

    As you can see, I am clearly not a programmer(!) and have written this code in a very simplistic, line-by-line fashion. I know there is a better way to write this code – looping through each of the checkboxes – but I have no idea how to do this. Any help, or advice, would be most welcome!

    Sample file attached...

    Many thanks
    Mike
    Attached Files Attached Files

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,907

    Re: Looping through checkboxes - HELP NEEDED

    You could probably use ONE macro for all your master boxes:

    Please Login or Register  to view this content.
    That code, assigned to ALL your masters, would control their own group, since you have used a very useful naming convention

    Please read the forum rules, especially #2 about code 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, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window.
    Last edited by Arkadi; 01-31-2019 at 02:53 PM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    07-10-2018
    Location
    Manchester, England
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Looping through checkboxes - HELP NEEDED

    Hi Arkadi - thanks for the advice. I will definitely make sure that I use the
    Please Login or Register  to view this content.
    tags in future. You can tell I'm new at this can't you?!

    I'm afraid I don't really understand your reply but will try to work it out. Like I said, I'm not a programmer!

    One thing point to note is that each master check box should only affect the other checkboxes on the same row - so I think I will probably need 30 macros or will your suggested code allow for that?

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,907

    Re: Looping through checkboxes - HELP NEEDED

    Hi Mike,

    No worries we all must learn
    Please edit your original post to add those code tags?

    The code I posted removes the word Master from the checkbox that calls my macro. Then it takes what is left (Say L01) and checks all the checkboxes in the active sheet and if their name starts with that text, it makes their value equal to what was checked. This means you can assign the macro to all 30 MasterLXX checkboxes, and they should only affect their own row (again, because your naming of the boxes is very useful and allows us to identify them by name).

    In my macro, Application.Caller is the name of the checkbox that was clicked to trigger the macro, so it identifies WHICH master caused the code to run, and then uses its name to identify its "children"

    In short what I have in Post #2 should do what you expect, without the need for 30 separate macros.
    Last edited by Arkadi; 01-31-2019 at 03:53 PM.

  5. #5
    Registered User
    Join Date
    07-10-2018
    Location
    Manchester, England
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Looping through checkboxes - HELP NEEDED

    Hello again Arkadi! I hope all is well in Smiths Falls, Ontario - any signs of Spring yet or are you still frozen up?

    I'm sorry not to have replied sooner but, for various reasons, I had to put this project on hold but now it has been resurrected.

    I have implemented your solution and it works just beautifully, thanks. So, now I have just one macro assigned to all the 'Master' check boxes and it behaves exactly as you described. Simple, elegant, just what the doctor ordered! so HUGE THANKS!

    But now, I have another challenge! What I want to do is this:

    WHEN clicking a checkbox whose name starts with "L01" CHECK the value of ALL "L01" checkboxes in the sheet
    IF the value of ALL "L01" checkboxes = TRUE
    THEN set the value of the Checkbox MasterL01 as TRUE
    ELSE set the value of the Checkbox MasterL01 as FALSE

    In this way, if I manually uncheck one or more "L01" check boxes, the Master will be unchecked. If I then make all "L01" boxes = TRUE, then the Master will be set as TRUE.

    The same logic would have to apply for all lines i.e. "L01", "L02", "L03" etc

    My feeble brain is struggling to work out the code required to do this but hope you might be able to help (again)!

    Many thanks and I look forward to hearing from you!

    Cheers
    Mike

    p.s. I'm a bit of an aviation geek and follow one of your countrymen on YouTube "Flight Chops". Have you heard of him?

+ 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] Looping through Form Controls checkboxes
    By tmallin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2015, 03:22 PM
  2. Looping though checkboxes and rows
    By venice.may1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-29-2015, 01:16 PM
  3. [SOLVED] Help Needed with Checkboxes to Hide or Unhide Rows
    By dgibney in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-25-2014, 11:39 AM
  4. [SOLVED] Making Rectangles or Checkboxes Appear as Needed
    By bmasella in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-24-2014, 08:46 PM
  5. Help needed with Checkboxes
    By Teacher in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-06-2009, 04:31 AM
  6. [SOLVED] Looping through a group of checkboxes
    By asdfasdf in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-12-2005, 10:05 PM
  7. [SOLVED] Looping Checkboxes on Worksheet
    By Robbyn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2005, 05:05 PM

Tags for this Thread

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