Checkbox

Rita

unread,

Dec 16, 2002, 1:42:38 PM12/16/02

to

Is anyone help me to write a code.
I want to insert checkbox in C3 to E1500.

Please help me

Thanks

Rita

Debra Dalgleish

unread,

Dec 16, 2002, 7:28:27 PM12/16/02

to

I wouldn't add that many checkboxes to a worksheet, but here's some code
from Dave Peterson:

Sub loadCBX()
'by Dave Peterson
Dim myCell As Range
Dim myCBX As CheckBox

For Each myCell In Range("C3:E1500")
With myCell
Set myCBX = ActiveSheet.CheckBoxes.Add _
(Top:=.Top, Width:=.Width - 3, _
Height:=.Height, Left:=.Left)
End With
Next myCell

End Sub

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Dave Peterson

unread,

Dec 16, 2002, 7:37:03 PM12/16/02

to

That's a lot of checkboxes!

This might work for you:

Option Explicit
Sub loadCBX()

Dim myCell As Range
Dim myCBX As CheckBox

Dim wks As Worksheet

Set wks = ActiveSheet

wks.CheckBoxes.Delete 'nice for testing!

For Each myCell In wks.Range("c3:e15") 'modify to 1500 when you're happy

With myCell
Set myCBX = ActiveSheet.CheckBoxes.Add _

(Top:=.Top, Width:=.Width, _

Height:=.Height, Left:=.Left)
End With

With myCBX
.Name = "cbx_" & myCell.Address(0, 0)
.LinkedCell = myCell.Offset(0, 4).Address(external:=True)
.Caption = "what ever you want"
.OnAction = ThisWorkbook.Name & "!mycbxMacro"

End With
Next myCell

End Sub

Sub myCBXMacro()

MsgBox "You clicked the checkbox in " & _
ActiveSheet.CheckBoxes(Application.Caller).TopLeftCell.Address

End Sub

Delete the .onaction if you don't want to have a macro run when you click on the
checkbox. (the myCBXMacro was my example.)

=========================
On the other hand, it might be simpler to just use a cell with a nice border.
Give them a custom format of this:

Format|Cells|Number tab|Custom
hit and hold the alt key & type 0252
release the alt key, type ; (semicolon)

another alt-0252
another ;

(four alt-0252's, 3 semicolons)

When you're done, it should look like this:
ü;ü;ü;ü

Format the cell as Wingdings.

Any non-empty value in the cell will look like a checkmark.

Then you can do things like:

=counta(c1:c99)
to find the number checked in c1:c99.

or individually: =if(c1="","not checked","checked")

The user can type anything and get the checkmark.

=========
If you go with the code and checkboxes, you might want to read David McRitchie's
notes at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--

Dave Peterson
ec3...@msn.com

Peo Sjoblom

unread,

Dec 16, 2002, 7:45:23 PM12/16/02

to

Whoa!

--

Regards,

Peo Sjoblom

"Dave Peterson" <ec3...@msn.com> wrote in message
news:3DFE71AF...@msn.com...

Dave Peterson

unread,

Dec 16, 2002, 8:01:29 PM12/16/02

to

Too much info???

Too much useless info???

--

Dave Peterson
ec3...@msn.com

Peo Sjoblom

unread,

Dec 16, 2002, 8:20:24 PM12/16/02

to

LOL
I was reflecting over the usability of a sheet with
4494 checkboxes. Hope she has a fast computer

--

Regards,

Peo Sjoblom

"Dave Peterson" <ec3...@msn.com> wrote in message

news:3DFE7769...@msn.com...

Dave Peterson

unread,

Dec 16, 2002, 8:48:26 PM12/16/02

to

And some very friendly users. I'd hate to have to click on that many of them.

(My testing stopped at 15. But I'm chicken!)

--

Dave Peterson
ec3...@msn.com

Peo Sjoblom

unread,

Dec 16, 2002, 11:19:35 PM12/16/02

to

I tested the full amount and it took about
1 minute and 20 seconds.

Regards,

Peo Sjoblom

"Dave Peterson" <ec3...@msn.com> wrote in message

news:3DFE826A...@msn.com...

Dave Peterson

unread,

Dec 16, 2002, 11:57:19 PM12/16/02

to

I guess the next question is for the OP:

What are you going to do with about 4500 checkboxes???

--

Dave Peterson
ec3...@msn.com