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.