CheckBoxes
 

Does any body know how ya can create a whole column of checkboxes which will
be in line with rows without having to add them one by one?

Laur

Saved from a previous post:

The checkboxes from the Forms toolbar are less taxing on the system:

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("B2:B413").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
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


One more option:

Maybe just give the cells a custom format.

Format the cells by:
selecting them
format|cells|number tab|custom category
In the "type:" box, put this:

alt-0252;alt-0252;alt-0252;alt-0252

But hit and hold the alt key while you're typing the 0252 from the numeric
keypad.

It should look something like this when you're done.
ü;ü;ü;ü
(umlaut over the lower case u separated by semicolons)

And format that range of cells as Wingdings.

Now, no matter what you type (spacebar, x, anyoldtextatall), you'll see a check
mark.

Hit the delete key on the keyboard to clear the cell.

If you have to use that "checkmark" in later formulas:

=if(a1="","no checkmark","Yes checkmark")

You can just see if the cell is empty.


Laur wrote:

Does any body know how ya can create a whole column of checkboxes which will
be in line with rows without having to add them one by one?

Laur

--

Dave Peterson

Hi!

I've done this by simply copying!

Place one of the checkboxes where you want it. If you=20
place it properly you can still "grab" the fill handle and=20
drag copy down. Every checkbox is perfectly aligned!

If you don't do it correctly, you'll end up with just=20
copies of the one checkbox in that if you tick one=20
checkbox it ticks all of the checkboxes.=20

It's much easier to do than it is to explain!

Try it!

Biff

-----Original Message-----
Saved from a previous post:

The checkboxes from the Forms toolbar are less taxing on=20
the system:

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

If you're new to macros, you may want to read David=20
McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


One more option:

Maybe just give the cells a custom format.

Format the cells by:
selecting them
format|cells|number tab|custom category
In the "type:" box, put this:

alt-0252;alt-0252;alt-0252;alt-0252

But hit and hold the alt key while you're typing the 0252=20
from the numeric
keypad.

It should look something like this when you're done.
=FC;=FC;=FC;=FC
(umlaut over the lower case u separated by semicolons)

And format that range of cells as Wingdings.

Now, no matter what you type (spacebar, x,=20
anyoldtextatall), you'll see a check
mark.

Hit the delete key on the keyboard to clear the cell.

If you have to use that "checkmark" in later formulas:

=3Dif(a1=3D"","no checkmark","Yes checkmark")

You can just see if the cell is empty.


Laur wrote:
=20
Does any body know how ya can create a whole column of=20
checkboxes which will
be in line with rows without having to add them one by=20
one?
=20
Laur

--=20

Dave Peterson
.