Hi,

Someone asked me to prepare a sheet with a large number of check boxes. When
I try to copy the check box the reference field (even if it is a relative
reference) gets copied as well. This means that if I copied the checkbox
twice, all three checkboxes alter the same reference field (and eachother).

I would like to keep using the check box control, in stead of a smartly
offered wingding alternative I read in another question. So I am looking for
a way to copy check box controls and somehow create a relative reference.
Should I somehow lock a check box to a certain cell before I can do any kind
of relative copying?

Please help me out.

Try this neat macro from Dave Peterson.

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("A2:A10").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = "" 'or whatever you want
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With

This will insert check boxes in the range A2:A10. Change the range to suit
your needs.
It sets the linked cell to be the same cell the check box is "in". It sets
the font of the linked cell to be "invisible" so that you won't see the TRUE
or FALSE.
It sets the caption to be nothing. If you want unique captions you'd have to
do it manually for each check box (which could be a real pita) or, you could
enter the captions in an adjacent cell and then change this line of code:

..Caption = "" 'or whatever you want

To:

..Caption = myCell.Offset(0, 1) 'or whatever you want

If the check box cell is A2 then the caption cell would be B2. Then you
could hide the column of caption names.


--
Biff
Microsoft Excel MVP


"hoffjero" wrote in message
...
Hi,

Someone asked me to prepare a sheet with a large number of check boxes.
When
I try to copy the check box the reference field (even if it is a relative
reference) gets copied as well. This means that if I copied the checkbox
twice, all three checkboxes alter the same reference field (and
eachother).

I would like to keep using the check box control, in stead of a smartly
offered wingding alternative I read in another question. So I am looking
for
a way to copy check box controls and somehow create a relative reference.
Should I somehow lock a check box to a certain cell before I can do any
kind
of relative copying?

Please help me out.

Then you could hide the column of caption names.

Or, delete the column of caption names since it can only be used once.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this neat macro from Dave Peterson.

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("A2:A10").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = "" 'or whatever you want
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With

This will insert check boxes in the range A2:A10. Change the range to suit
your needs.
It sets the linked cell to be the same cell the check box is "in". It sets
the font of the linked cell to be "invisible" so that you won't see the
TRUE or FALSE.
It sets the caption to be nothing. If you want unique captions you'd have
to do it manually for each check box (which could be a real pita) or, you
could enter the captions in an adjacent cell and then change this line of
code:

.Caption = "" 'or whatever you want

To:

.Caption = myCell.Offset(0, 1) 'or whatever you want

If the check box cell is A2 then the caption cell would be B2. Then you
could hide the column of caption names.


--
Biff
Microsoft Excel MVP


"hoffjero" wrote in message
...
Hi,

Someone asked me to prepare a sheet with a large number of check boxes.
When
I try to copy the check box the reference field (even if it is a relative
reference) gets copied as well. This means that if I copied the checkbox
twice, all three checkboxes alter the same reference field (and
eachother).

I would like to keep using the check box control, in stead of a smartly
offered wingding alternative I read in another question. So I am looking
for
a way to copy check box controls and somehow create a relative reference.
Should I somehow lock a check box to a certain cell before I can do any
kind
of relative copying?

Please help me out.

Thanks a lot for your help!!!!



"T. Valko" wrote:

Then you could hide the column of caption names.

Or, delete the column of caption names since it can only be used once.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this neat macro from Dave Peterson.

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("A2:A10").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = "" 'or whatever you want
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With

This will insert check boxes in the range A2:A10. Change the range to suit
your needs.
It sets the linked cell to be the same cell the check box is "in". It sets
the font of the linked cell to be "invisible" so that you won't see the
TRUE or FALSE.
It sets the caption to be nothing. If you want unique captions you'd have
to do it manually for each check box (which could be a real pita) or, you
could enter the captions in an adjacent cell and then change this line of
code:

.Caption = "" 'or whatever you want

To:

.Caption = myCell.Offset(0, 1) 'or whatever you want

If the check box cell is A2 then the caption cell would be B2. Then you
could hide the column of caption names.


--
Biff
Microsoft Excel MVP


"hoffjero" wrote in message
...
Hi,

Someone asked me to prepare a sheet with a large number of check boxes.
When
I try to copy the check box the reference field (even if it is a relative
reference) gets copied as well. This means that if I copied the checkbox
twice, all three checkboxes alter the same reference field (and
eachother).

I would like to keep using the check box control, in stead of a smartly
offered wingding alternative I read in another question. So I am looking
for
a way to copy check box controls and somehow create a relative reference.
Should I somehow lock a check box to a certain cell before I can do any
kind
of relative copying?

Please help me out.

You're welcome!

--
Biff
Microsoft Excel MVP


"hoffjero" wrote in message
...
Thanks a lot for your help!!!!



"T. Valko" wrote:

Then you could hide the column of caption names.

Or, delete the column of caption names since it can only be used once.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this neat macro from Dave Peterson.

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("A2:A10").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = "" 'or whatever you want
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With

This will insert check boxes in the range A2:A10. Change the range to
suit
your needs.
It sets the linked cell to be the same cell the check box is "in". It
sets
the font of the linked cell to be "invisible" so that you won't see the
TRUE or FALSE.
It sets the caption to be nothing. If you want unique captions you'd
have
to do it manually for each check box (which could be a real pita) or,
you
could enter the captions in an adjacent cell and then change this line
of
code:

.Caption = "" 'or whatever you want

To:

.Caption = myCell.Offset(0, 1) 'or whatever you want

If the check box cell is A2 then the caption cell would be B2. Then you
could hide the column of caption names.


--
Biff
Microsoft Excel MVP


"hoffjero" wrote in message
...
Hi,

Someone asked me to prepare a sheet with a large number of check
boxes.
When
I try to copy the check box the reference field (even if it is a
relative
reference) gets copied as well. This means that if I copied the
checkbox
twice, all three checkboxes alter the same reference field (and
eachother).

I would like to keep using the check box control, in stead of a
smartly
offered wingding alternative I read in another question. So I am
looking
for
a way to copy check box controls and somehow create a relative
reference.
Should I somehow lock a check box to a certain cell before I can do
any
kind
of relative copying?

Please help me out.

How would I modify this macro to make the check boxex centered in their
linked cells?

"T. Valko" wrote:

Try this neat macro from Dave Peterson.

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("A2:A10").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = "" 'or whatever you want
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With

This will insert check boxes in the range A2:A10. Change the range to suit
your needs.
It sets the linked cell to be the same cell the check box is "in". It sets
the font of the linked cell to be "invisible" so that you won't see the TRUE
or FALSE.
It sets the caption to be nothing. If you want unique captions you'd have to
do it manually for each check box (which could be a real pita) or, you could
enter the captions in an adjacent cell and then change this line of code:

..Caption = "" 'or whatever you want

To:

..Caption = myCell.Offset(0, 1) 'or whatever you want

If the check box cell is A2 then the caption cell would be B2. Then you
could hide the column of caption names.


--
Biff
Microsoft Excel MVP


"hoffjero" wrote in message
...
Hi,

Someone asked me to prepare a sheet with a large number of check boxes.
When
I try to copy the check box the reference field (even if it is a relative
reference) gets copied as well. This means that if I copied the checkbox
twice, all three checkboxes alter the same reference field (and
eachother).

I would like to keep using the check box control, in stead of a smartly
offered wingding alternative I read in another question. So I am looking
for
a way to copy check box controls and somehow create a relative reference.
Should I somehow lock a check box to a certain cell before I can do any
kind
of relative copying?

Please help me out.

Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width / 2, _
Left:=.Left + (.Width / 2), Height:=.Height)

You may want to fiddle around with that divisor to make it look pretty.



broncojim wrote:

How would I modify this macro to make the check boxex centered in their
linked cells?

"T. Valko" wrote:

Try this neat macro from Dave Peterson.

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("A2:A10").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = "" 'or whatever you want
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With

This will insert check boxes in the range A2:A10. Change the range to suit
your needs.
It sets the linked cell to be the same cell the check box is "in". It sets
the font of the linked cell to be "invisible" so that you won't see the TRUE
or FALSE.
It sets the caption to be nothing. If you want unique captions you'd have to
do it manually for each check box (which could be a real pita) or, you could
enter the captions in an adjacent cell and then change this line of code:

..Caption = "" 'or whatever you want

To:

..Caption = myCell.Offset(0, 1) 'or whatever you want

If the check box cell is A2 then the caption cell would be B2. Then you
could hide the column of caption names.


--
Biff
Microsoft Excel MVP


"hoffjero" wrote in message
...
Hi,

Someone asked me to prepare a sheet with a large number of check boxes.
When
I try to copy the check box the reference field (even if it is a relative
reference) gets copied as well. This means that if I copied the checkbox
twice, all three checkboxes alter the same reference field (and
eachother).

I would like to keep using the check box control, in stead of a smartly
offered wingding alternative I read in another question. So I am looking
for
a way to copy check box controls and somehow create a relative reference.
Should I somehow lock a check box to a certain cell before I can do any
kind
of relative copying?

Please help me out.




--

Dave Peterson