Checkbox That checks or unchecks rest of boxes

H

hoffman3

I have checkboxes in column A in my spreadsheet and I have one that
want to set as kind of a "master" checkbox, where...if you click it th
the rest will be clicked and if you unclick it then the rest wil
unclick. If anyone has any ideas let me know. Thanks
Eric Hoffma
 
B

Bernie Deitrick

Eric,

If you are using a checkbox from the "Forms" commandbar, you can assign a
macro to your "master" checkbox:

Sub MasteCB()
Dim Chbx As Object
For Each Chbx In Worksheets("Sheet1").CheckBoxes
Chbx.Value = Worksheets("Sheet1").CheckBoxes("Master Check Box").Value
Next
End Sub

HTH,
Bernie
MS Excel MVP
 
I

icestationzbra

i tried something with forms checkboxes and seems like what you want.

i created a checkbox. copied it and pasted it several times. in which
case, the cell link would be blank as there are several instances of
the same checkbox.

then i selected all the checkboxes, right-clicked on them while they
were selected. chose Format Control - Control. in the cell link box, i
selected a cell which became a constant. then i okayed out.

now, when i clicked on any one checkbox, all the others are checked.

now, i do not know whether this would help you in further programming
or not, but the checkbox function works, without the need for a macro.

mac.
 
D

Dave Peterson

This sounds like a nice way to make all the checkboxes behave as one--all
checked or all not checked.

But I bet the original poster had some of the checkboxes checked and some not
checked. And just wanted a technique to reset them to match the master
checkbox.
 
H

hoffman3

Alright I appreciate the help so far. Basically I have a checkbox in A
that I want to be a master checkbox and if clicked i want to change th
rest of the boxes in A4:A15 to be all clicked or unclicked, is ther
any way to say if value of B2(true/false) changes then change th
true/false value in B4:B15. I'm still trying to use the ones you guy
have said already but i haven't gotten one to work like i want it to
Thanks for the help.
Eric Hoffma
 
B

Bernie Deitrick

Eric,

You still haven't said what type of checkbox you are using: off the forms
commandbar, or off the controls toolbox commandbar. Also, if you have the
checkboxes named, the name of your master checkbox would help.

HTH,
Bernie
MS Excel MVP
 
H

hoffman3

Alright I haven't named any of my checkboxes yet, I ran a macro to pu
the checkboxes in the spreadsheet:
Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("A1, A18,A21:A35,A4:A15").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Offset(0, 1).Address(external:=True)
.Caption = "" 'or whatever you want
.Name = "CBX_" & myCell.Address(0, 0)
End With
End With
Next myCell
End With
End Su
 
B

Bernie Deitrick

Eric,

Copy both macros below. The checkbox in cell A1 will be the master, setting
all others to match it.

HTH,
Bernie
MS Excel MVP

Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
..CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("A1, A18,A21:A35,A4:A15").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
..LinkedCell = myCell.Offset(0, 1).Address(external:=True)
..Caption = "" 'or whatever you want
..Name = "CBX_" & myCell.Address(0, 0)
..OnAction = "MasterCB"
End With
End With
Next myCell
End With
End Sub

Sub MasterCB()
Dim Chbx As Object
For Each Chbx In Worksheets("Sheet1").CheckBoxes
Chbx.Value = Worksheets("Sheet1").CheckBoxes("CBX_A1").Value
Next
End Sub
 
B

Bernie Deitrick

Aaah! Brain freeze!

Change this line

..OnAction = "MasterCB"

to

If .Name = "CBX_A1" Then .OnAction = "MasterCB"

Sorry about that,
Bernie
MS Excel MVP

Bernie Deitrick said:
Eric,

Copy both macros below. The checkbox in cell A1 will be the master, setting
all others to match it.

HTH,
Bernie
MS Excel MVP

Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("A1, A18,A21:A35,A4:A15").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Offset(0, 1).Address(external:=True)
.Caption = "" 'or whatever you want
.Name = "CBX_" & myCell.Address(0, 0)
.OnAction = "MasterCB"
End With
End With
Next myCell
End With
End Sub

Sub MasterCB()
Dim Chbx As Object
For Each Chbx In Worksheets("Sheet1").CheckBoxes
Chbx.Value = Worksheets("Sheet1").CheckBoxes("CBX_A1").Value
Next
End Sub
Click to expand...
 
H

hoffman3

keep getting a compiler error trying to put it in with the period
before the rows. thanks
eri
 
B

Bernie Deitrick

eric,

I cut and pasted straight out of my codemodule, so I'm not sure why you are
getting an error with the leading periods. That is the typical code syntax
when using the "with" construction.

If you send me your email address, I will send you what, for me, is a
working version.

HTH,
Bernie
MS Excel MVP
 
D

Dave Peterson

I didn't add any dots to this reformatted version of your/Bernie's code:

Option Explicit

Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("A1, A18,A21:A35,A4:A15").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Offset(0, 1).Address(external:=True)
.Caption = "" 'or whatever you want
.Name = "CBX_" & myCell.Address(0, 0)
If .Name = "CBX_A1" Then
.OnAction = "MasterCB"
End If
End With
End With
Next myCell
End With
End Sub

Sub MasterCB()
Dim Chbx As Object
For Each Chbx In Worksheets("Sheet1").CheckBoxes
Chbx.Value = Worksheets("Sheet1").CheckBoxes("CBX_A1").Value
Next
End Sub
 
Top