Two sets of checkboxes on one worksheet

xenedra13

Board Regular
Joined
Aug 15, 2014
Messages
58
I have two sets of checkboxes on the same worksheet. Previously, I have used this code to make a box that will select all checkboxes.
Code:
Sub CheckBox8_Click()
Dim CB As CheckBox
    For Each CB In ActiveSheet.CheckBoxes
        If CB.name <> ActiveSheet.CheckBoxes("Check Box 8").name Then
        CB.Value = ActiveSheet.CheckBoxes("Check Box 8").Value
        End If
    Next CB
End Sub
Sub Mixed_State8()
Dim CB As CheckBox
    For Each CB In ActiveSheet.CheckBoxes
        If CB.name <> ActiveSheet.CheckBoxes("Check Box 8").name And CB.Value <> ActiveSheet.CheckBoxes("Check Box 8").Value And ActiveSheet.CheckBoxes("Check Box 8").Value <> 2 Then
        ActiveSheet.CheckBoxes("Check Box 8").Value = 2
        Exit For
        Else
        ActiveSheet.CheckBoxes("Check Box 8").Value = CB.Value
        End If
    Next CB
End Sub
But this code does the same thing for all the checkboxes. I need to be able to separate the two sets of checkboxes somehow without having to rename every single checkbox. I tried changing "ActiveSheet" in this line
Code:
For Each CB In ActiveSheet.CheckBoxes
to a range value. And I tried adding the range after "ActiveSheet" and both of those gave me an error that said this object or method is not supported. Can someone show me how to change these macros so that I can have two sets of checkboxes that each have a checkbox that selects all in that set? Thanks in advance!
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
4,732
Office Version
2013
Platform
Windows
xenedra13,

Assuming that you have two groups of 8 checkboxes where names are 'Check Box 1' to 'Check Box 8' for one group and 'Check Box 9' to 'Check Box 16' for the other and that 8 and 16 are the 'controlling' check boxes.

Try revising the code in this fashion...

Code:
Sub CheckBox8_Click()
Dim i As Integer
   For i = 1 To 7
        ActiveSheet.CheckBoxes("Check Box " & i).Value = ActiveSheet.CheckBoxes("Check Box 8").Value
    Next i
End Sub






Sub CheckBox16_Click()
Dim i As Integer
   For i = 9 To 15
        ActiveSheet.CheckBoxes("Check Box " & i).Value = ActiveSheet.CheckBoxes("Check Box 16").Value
    Next i
End Sub




Sub Mixed_State8()
Dim i As Integer
Dim CB As CheckBox
    For i = 1 To 7
    Set CB = ActiveSheet.CheckBoxes("Check Box " & i)
        If CB.Name <> ActiveSheet.CheckBoxes("Check Box 8").Name And CB.Value <> ActiveSheet.CheckBoxes("Check Box 8").Value And ActiveSheet.CheckBoxes("Check Box 8").Value <> 2 Then
        ActiveSheet.CheckBoxes("Check Box 8").Value = 2
        Exit For
        Else
        ActiveSheet.CheckBoxes("Check Box 8").Value = CB.Value
        End If
    Next i
End Sub




Sub Mixed_State16()
Dim i As Integer
Dim CB As CheckBox
    For i = 9 To 16
    Set CB = ActiveSheet.CheckBoxes("Check Box " & i)
        If CB.Name <> ActiveSheet.CheckBoxes("Check Box 16").Name And CB.Value <> ActiveSheet.CheckBoxes("Check Box 16").Value And ActiveSheet.CheckBoxes("Check Box 16").Value <> 2 Then
        ActiveSheet.CheckBoxes("Check Box 16").Value = 2
        Exit For
        Else
        ActiveSheet.CheckBoxes("Check Box 16").Value = CB.Value
        End If
    Next i
End Sub
Hope that helps.
 

xenedra13

Board Regular
Joined
Aug 15, 2014
Messages
58
I thought this would work perfectly, but when I ran it I got a run-time error '1004' Unable to get the Checkboxes property of the worksheet class on the marked line.
Code:
Sub CheckBoxRun_Click()
Dim i As Integer
   For i = 56 To 67
        ActiveSheet.CheckBoxes("Check Box " & i).Value = ActiveSheet.CheckBoxes("Check Box 56").Value             'ERROR 1004
    Next i
End Sub
Any suggestions on how to fix this?
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top