T
tinks
What is the most efficient way to add numerous checkboxes into an excel
worksheet?
worksheet?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
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
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
D
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.)
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
T. Valko
This worked ok in xl2003, but I didn't test it in xl2007:
Works OK in Excel 2007.
Works OK in Excel 2007.
D
Dave Peterson
Thanks, Biff.
T. Valko said:This worked ok in xl2003, but I didn't test it in xl2007:
Works OK in Excel 2007.
E
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
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
X
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.