copy check boxes (made from form toolbar)

[hoffjero]

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.

 

[T. Valko]

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.

 

[T. Valko]

Then you could hide the column of caption names.

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

 

[hoffjero]

Thanks a lot for your help!!!!

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

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks a lot for your help!!!!

 

[broncojim]

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

 

[Dave Peterson]

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.