Check boxes do not go into a cell, they are a shape that sits ontop of the
spreadsheet. There is little documentation on excel functions or they are
written to give you no real information. I basically experiment every time a
new question is asked until I find the answer.

The way I experiment is stepping through the code and add variable to the
VBA watch window. In your case, I right clicked the variable x and added it
to the watch window. then steped through the code and expanded X in the
watch window by pressing the plus sign. Then start looking at all the
properties.

The second method I use is recording macros. I see how excel creates a
macro to do what I need it to do. Then modify the macro instructions. I
your case I never wrote a macro to link a check box to a cell. Excel told
me in the macro I need to use the property linkedcell. This gave me an
error. I went through the watch window and found the LinkedCell was under
x.ControlFormat.

My third method is to use the object browser. If you right click the VBA
window you can select the object browser. Entering Linkedcell in the search
window on the top also tells youi that LinkedCell is a controlformat.

I hate VBA. I hate microsoft. How can anybody release a tool with such bad
documentation!!!!!!!!!!!!!

I learned somethin new today. That cells have top and left properties. I
knew shapes had these properties. I put two and tow together and figured you
can place a check box into a cell by using the cell top, and left property.
this also applies to the height and width.

the only problem is the boxes will not move if you change the height and
width of the cells. See code below.

Sub AddCheckboxes()
'

' Macro recorded 9/13/2007 by Joel
'

'
Set CheckRange = Range("E5:I14")

For Each cell In CheckRange

Set x = ActiveSheet.Shapes.AddFormControl _
(xlCheckBox, cell.Left, _
cell.Top, cell.Width, cell.Height)

x.ControlFormat.LinkedCell = Cells(cell.Row + 10, _
cell.Column).Address

Next cell

End Sub

"RD" wrote:

Amazing!, must really learn VBA one day. Joel, I would like to be able to
control the size of the box (without text) and to be able to control which
cells the boxes go into. As its a survey I have the names on the left and the
questions across the top. So each checkbox needs to be placed into a cell.
For example can I have the routine create checkboxes starting from E5 and
filling in 5 across and 10 down from there?

RD

"Joel" wrote:

This code will add check boxes. I made it only 5 x 10 becuase the 60 x 100
take a few minutes to run. I also included a macro to remove all checkboxes.

Sub AddCheckboxes()
'

' Macro recorded 9/13/2007 by Joel
'

'
For NCol = 1 To 5
For NRow = 1 To 10

Set x = ActiveSheet.Shapes.AddFormControl _
(xlCheckBox, (100 * (NCol - 1)) + 3, _
(25 * (NRow - 1)) + 5, 80, 25)
Next NRow
Next NCol

End Sub

Sub removecheckbox()

For Each sh In ActiveSheet.Shapes
sh.Delete
Next sh
End Sub

"RD" wrote:

I am doing a survey and would like a grid of checkboxes probably 100 rows x
50 columns. I really really don't want to make themone at a time. Can they be
copied from one cell to multiple cells?

Rd