none
Macro to select all check boxes in a range RRS feed

  • Question

  • Hi everyone,

    I need some help. I have a worksheet with numerous check boxes in several groups (months, vehicles etc.).

    What I want to do to help the users of the file is to add a check box above each group to select/deselect all other check boxes in that group. I tried a solution I found online to select all check boxes in the active worksheet, but that doesn't help.

    Is there a way to do this only for the Range below? My code below doesn't work and I'm rather stuck...

    (Check Box 151 is the "master" check box which I still need to rename.)

    Thanks in advance!

    Sub SelectAllMonths_Click()
    Dim CB As CheckBox
    Dim Months As Range
    Set Months = Range("B5:B16")
    For Each CB In Months
      If CB.Name <> ActiveSheet.CheckBoxes("Check Box 151").Name Then
        CB.Value = ActiveSheet.CheckBoxes("Check Box 151").Value
      End If
    Next CB
    End Sub
    
    

    Monday, March 20, 2017 11:09 AM

Answers

  • Assign this macro to Check Box 151: I have assumed that the sheet with all the checkboxes is codenamed Sheet1

    Sub SelectAllMonths_Click()
        Dim CB As CheckBox
        Dim Months As Range
        Set Months = Sheet1.Range("B5:B16")
        For Each CB In Sheet1.CheckBoxes
          If Not Intersect(CB.TopLeftCell, Months) Is Nothing Then
            CB.Value = Sheet1.CheckBoxes("Check Box 151").Value
          End If
        Next CB
    End Sub


    Monday, March 20, 2017 2:52 PM

All replies

  • Assign this macro to Check Box 151: I have assumed that the sheet with all the checkboxes is codenamed Sheet1

    Sub SelectAllMonths_Click()
        Dim CB As CheckBox
        Dim Months As Range
        Set Months = Sheet1.Range("B5:B16")
        For Each CB In Sheet1.CheckBoxes
          If Not Intersect(CB.TopLeftCell, Months) Is Nothing Then
            CB.Value = Sheet1.CheckBoxes("Check Box 151").Value
          End If
        Next CB
    End Sub


    Monday, March 20, 2017 2:52 PM
  • Thanks very much!

    After modifying the parameters a little bit and adjusting it for each group, it works like a charm.

    Much appreciated!

    Tuesday, March 21, 2017 8:23 AM