Add Check Boxes to Many Cells
 

Hello,

I have a spreadsheet that I need to put a Check Box in
every cell in the cell range of B2 to BO413; I would like
to do this automatically using VBA code to fill the cell
range I mentioned. I would like to put a button on the
sheet and just click the button to add the check boxes.
Could someone please help me with the code I need to
acomplish this task? Any help will be greatly appreciated.

Thank you,
Dave Y

Add Check Boxes to Many Cells
 

Dave

See he
http://www.dicks-blog.com/archives/2...ksheet-in-vba/

That should get you started, but post back if you need more help.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Dave Y wrote:
Hello,

I have a spreadsheet that I need to put a Check Box in
every cell in the cell range of B2 to BO413; I would like
to do this automatically using VBA code to fill the cell
range I mentioned. I would like to put a button on the
sheet and just click the button to add the check boxes.
Could someone please help me with the code I need to
acomplish this task? Any help will be greatly appreciated.

Thank you,
Dave Y

Add Check Boxes to Many Cells
 

Mr. Kusleika,

Thank you very much for your reply. The code seems to work
although it keeps freezing my computer after few minutes.
I will keep playing with it. I greatly appreciate your
help.

Dave Y
-----Original Message-----
Dave

See he
http://www.dicks-blog.com/archives/2004/10/06/adding-
activex-controls-to-worksheet-in-vba/

That should get you started, but post back if you need
more help.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Dave Y wrote:
Hello,

I have a spreadsheet that I need to put a Check Box in
every cell in the cell range of B2 to BO413; I would
like
to do this automatically using VBA code to fill the cell
range I mentioned. I would like to put a button on the
sheet and just click the button to add the check boxes.
Could someone please help me with the code I need to
acomplish this task? Any help will be greatly
appreciated.

Thank you,
Dave Y


.

Add Check Boxes to Many Cells
 

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:BO413").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

But that's a lot of checkboxes.

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.

wrote:

Mr. Kusleika,

Thank you very much for your reply. The code seems to work
although it keeps freezing my computer after few minutes.
I will keep playing with it. I greatly appreciate your
help.

Dave Y
-----Original Message-----
Dave

See he
http://www.dicks-blog.com/archives/2004/10/06/adding-
activex-controls-to-worksheet-in-vba/

That should get you started, but post back if you need
more help.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Dave Y wrote:
Hello,

I have a spreadsheet that I need to put a Check Box in
every cell in the cell range of B2 to BO413; I would
like
to do this automatically using VBA code to fill the cell
range I mentioned. I would like to put a button on the
sheet and just click the button to add the check boxes.
Could someone please help me with the code I need to
acomplish this task? Any help will be greatly
appreciated.

Thank you,
Dave Y


.


--

Dave Peterson

Add Check Boxes to Many Cells
 

Just to add...

When I ran this code, I broke out of the routine and it wasn't half done--after
a few minutes.

I think I'd use the formatting option for this many cells.



Dave Peterson wrote:

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:BO413").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

But that's a lot of checkboxes.

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.

wrote:

Mr. Kusleika,

Thank you very much for your reply. The code seems to work
although it keeps freezing my computer after few minutes.
I will keep playing with it. I greatly appreciate your
help.

Dave Y
-----Original Message-----
Dave

See he
http://www.dicks-blog.com/archives/2004/10/06/adding-
activex-controls-to-worksheet-in-vba/

That should get you started, but post back if you need
more help.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Dave Y wrote:
Hello,

I have a spreadsheet that I need to put a Check Box in
every cell in the cell range of B2 to BO413; I would
like
to do this automatically using VBA code to fill the cell
range I mentioned. I would like to put a button on the
sheet and just click the button to add the check boxes.
Could someone please help me with the code I need to
acomplish this task? Any help will be greatly
appreciated.

Thank you,
Dave Y


.


--

Dave Peterson

--

Dave Peterson