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

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

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

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

OK, good enough. Using this one did link it to a cell and place it where I
wanted. I too did some experimenting: I selected one the checkboxes, got rid
of the text, changed the size until I was happy with it. Then selecting the
cell,(not the checkbox) I was able to copy and paste to another cell.
Checking format control i learned that this one was now linked to the other.
I removed the link and then I was able to copy it to large grid of rows and
columns all at once! ......

Well something like that anyway. I went back to try my own method and it
wouldn't work the same. :( I got it once though so I can do it again. :)

Thanks for the help.





"Joel" wrote:

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