Multiple active X checkboxes...

[largeselection]

Hi again.

I have a workbook that has a sheet with a lot of columns. I also have a sheet before it which I want to put checkboxes on which will toggle columns on the second sheet hidden/unhidden.

I'm using an active X control checkbox form, but I can't figure out how to add multiple checkboxes. Is it possible? Or do I have to just create multiple individual checkboxes?

Here is the code that I'm using in the active X control:
If CheckBox1.Value = False Then
Sheets("Best").Columns("A:A").Hidden = False
Else
Sheets("Best").Columns("A:A").Hidden = True
End If

So is the only way to do this to create a lot of individual checkboxes each with this code? I was hoping I might be able to create a checkbox form that has maybe 20 checkboxes and then have them all reference one set of code which would be like this:
If CheckBox1.Value = False Then
Sheets("Best").Columns("A:A").Hidden = False
Else
Sheets("Best").Columns("A:A").Hidden = True
End If
If CheckBox2.Value = False Then
Sheets("Best").Columns("B:B").Hidden = False
Else
Sheets("Best").Columns("B;B").Hidden = True
End If

etc...

Anyway to get this done?

Second, related question, providing I can get that done. Is it possible to add a checkbox and have the code say to check certain boxes?

Maybe like this:
If Checkbox10.value=false then
checkbox1.value=false
checkbox2.value=false
checkbox3.value=false
else
checkbox1.value=true
checkbox2.value=true
checkbox3.value=true

Sorry if I've asked this in a confusing way. Ask any questions you may have and thanks in advance for your help!

 

[xld]

Just use

With Sheets("Best")

    .Columns("A:A").Hidden = CheckBox1.Value
    .Columns("B:B").Hidden = CheckBox2.Value
    'etc
End With

 

[largeselection]

AHA! That does seem simpler. However, I mistyped... I'm looking for the reverse:

If CheckBox1.Value = False Then
Sheets("Best").Columns("A:A").Hidden = True
Else
Sheets("Best").Columns("A:A").Hidden = False
End If

I want the column to be visible if checked, not hidden.

I like the simplicity of your code, but how do I add the multiple checkboxes so I can use this code? When I add an active x checkbox it only shows one box.

 

[xld]

With Sheets("Best")

    .Columns("A:A").Hidden = Not CheckBox1.Value
    .Columns("B:B").Hidden = Not CheckBox2.Value
    'etc
End With

 

[largeselection]

Hi again,

When I put that code in, I get an error because there is no checkbox 2. I'm sure this is straight forward, but how can I add checkbox 2 (3,4,...) to the active x box?

 

[xld]

I don't understand what you are asking.

 

[largeselection]

Sorry for the confusion. I'll talk through my process.

To add the checkboxes I went to the developer tab -> insert activeX checkbox.

Then I drew a rectangle and I had a rectangle with 1 checkbox in it. I attached my code for the checkbox to that activeX box. So my question is how can I have multiple checkboxes in that box rather than just 1.

Or do I have to just keep clicking insert activeX checkbox and repeating the above process 20 times?

The code that you sent (thank you!) seems like it would apply to multiple checkboxes. So do you just apply this to the whole sheet? How can you apply this code to multiple activeX boxes each with 1 checkbox in it?

When I double click on one of those checkboxes I get
Private Sub Checkbox1_click()

Then my code.

So I can't put in the code you sent:
With Sheets("Best")

.Columns("A:A").Hidden = Not CheckBox1.Value
.Columns("B:B").Hidden = Not CheckBox2.Value
'etc
End With

because there is no checkbox2 in this activeX box.

So is it possible to have 1 group that has multiple checkboxes in it so I can use:

With Sheets("Best")

.Columns("A:A").Hidden = Not CheckBox1.Value
.Columns("B:B").Hidden = Not CheckBox2.Value
'etc
End With

Or do I have to have multiple checkboxes and each one is set up like this:

Private Sub Checkbox1_click()

With Sheets("Best")

.Columns("A:A").Hidden = Not CheckBox1.Value
End With

End Sub


Private Sub Checkbox2_click()

With Sheets("Best")

.Columns("B:B").Hidden = Not CheckBox2.Value
End With

End Sub

etc...

Sorry if I'm being confusing.

 

[xld]

Here is some code to create the checkboxes (one-time run)

Sub Macro2()
'<<<<<<<< tweak these values to suit
Const StartTop As Double = 26.25
Const StartLeft As Double = 527.25
Const CBWidth As Double = 240
Const CBHeight As Double = 24
Const HeightPlus As Double = 25
Dim CB As Object
Dim i As Long

    With ActiveSheet
    
        For i = 1 To 20
        
            Set CB = .OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
                                     Link:=False, _
                                     DisplayAsIcon:=False, _
                                     Left:=StartLeft, _
                                     Top:=StartTop + (i - 1) * HeightPlus, _
                                     Width:=CBWidth, _
                                     Height:=CBHeight)
        Next
    End With
            
End Sub

and here is your event code

Private Sub Checkbox1_Click()	
	Call SetColumns(CheckBox1.Value, "A")
End Sub	
Private Sub Checkbox2_Click()	
	Call SetColumns(Checkbox2.Value, "B")
End Sub	
Private Sub Checkbox3_Click()	
	Call SetColumns(Checkbox3.Value, "C")
End Sub	
Private Sub Checkbox4_Click()	
	Call SetColumns(Checkbox4.Value, "D")
End Sub	
Private Sub Checkbox5_Click()	
	Call SetColumns(Checkbox5.Value, "E")
End Sub	
Private Sub Checkbox6_Click()	
	Call SetColumns(Checkbox6.Value, "F")
End Sub	
Private Sub Checkbox7_Click()	
	Call SetColumns(Checkbox7.Value, "G")
End Sub	
Private Sub Checkbox8_Click()	
	Call SetColumns(Checkbox8.Value, "H")
End Sub	
Private Sub Checkbox9_Click()	
	Call SetColumns(Checkbox9.Value, "I")
End Sub	
Private Sub Checkbox10_Click()	
	Call SetColumns(Checkbox10.Value, "J")
End Sub	
Private Sub Checkbox11_Click()	
	Call SetColumns(Checkbox11.Value, "K")
End Sub	
Private Sub Checkbox12_Click()	
	Call SetColumns(Checkbox12.Value, "L")
End Sub	
Private Sub Checkbox13_Click()	
	Call SetColumns(Checkbox13.Value, "M")
End Sub	
Private Sub Checkbox14_Click()	
	Call SetColumns(Checkbox14.Value, "N")
End Sub	
Private Sub Checkbox15_Click()	
	Call SetColumns(Checkbox15.Value, "O")
End Sub	
Private Sub Checkbox16_Click()	
	Call SetColumns(Checkbox16.Value, "P")
End Sub	
Private Sub Checkbox17_Click()	
	Call SetColumns(Checkbox17.Value, "Q")
End Sub	
Private Sub Checkbox18_Click()	
	Call SetColumns(Checkbox18.Value, "R")
End Sub	
Private Sub Checkbox19_Click()	
	Call SetColumns(Checkbox19.Value, "S")
End Sub	
Private Sub Checkbox20_Click()	
	Call SetColumns(Checkbox20.Value, "T")
End Sub

 

[largeselection]

Thanks!

That's exactly what I was looking for. So it is kind of like individual check boxes. Thanks for the creation code for those checkboxes. That would have been a pain!

 

[xld]

Yes it is individual checkboxes. You can emulate control arrays in VBA so that each checkbox uses the same click event code, but that is probably overkill IMO.