Insert multiple checkboxes in Excel 2007

[tinks]

What is the most efficient way to add numerous checkboxes into an excel
worksheet?

 

[Dave Peterson]

I'd use a macro and I'd use checkboxes from the Forms toolbar--not the control
toolbox toolbar.

This worked ok in xl2003, but I didn't test it in xl2007:

Option Explicit
Sub testme()

Dim myRng As Range
Dim CBX As CheckBox
Dim myCell As Range

With Worksheets("sheet1")
.CheckBoxes.Delete 'nice for testing
Set myRng = .Range("A1:B10")
End With

For Each myCell In myRng.Cells
With myCell
Set CBX = .Parent.CheckBoxes.Add _
(Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)
.NumberFormat = ";;;"
CBX.Name = "CBX_" & .Address(0, 0)
CBX.LinkedCell = .Address(external:=True)
CBX.Caption = ""
End With
Next myCell

End Sub

 

[Dave Peterson]

ps.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[T. Valko]

This worked ok in xl2003, but I didn't test it in xl2007:

Works OK in Excel 2007.

 

[Dave Peterson]

Thanks, Biff.

This worked ok in xl2003, but I didn't test it in xl2007:

Works OK in Excel 2007.

 

[Ed Ferrero]

Hi tinks,

I like a non-macro solution. This avoids placing Form controls on the
worksheet.

Format a cell with Wingdings font
Then apply the following custom number format
(copy the next line with quotes and all, and use as the format code)
"ü";;"û"
Then apply Data - Validation - List add 0,1 to the List Source
Format the cell with all borders

You now have an in-cell drop-down;
0 for False displayed as a cross
1 for True displayed as a tick

Copy the cell as needed.

Ed Ferrero
www.edferrero.com

 

[Xt]

What is the most efficient way to add numerous checkboxes into an excel
worksheet?

You can just do one, make sure it is selected, hold down the ctrl key
and then drag new ones with the mouse.