check boxes - copy

[Guest]

I would like to copy (hundreds) of check boxes in a spreadsheet. The
checkbox must be assigned to a cell to work in a formula. When I copy the
checkbox down however, all check boxes will either be checked, or unchecked.

Is there a way to copy check boxes, when they are assigned to another cell,
so that each check box can be used individually? I created the check boxes
through the forms toolbar.

Thanks for your help, this is a great forum and I only hope to give some day
as much help as I am currently receiving!

 

[Dave O]

Hey, Mark-
Things got busy yesterday, and I missed your post.

You can think of a checkbox as a control that is layered on top of your
spreadsheet and works with it- but when you copy a checkbox you're
copying that control and not the cell references within it. That's why
you the "refers to" cell in the checkbox parameters don't change when
you move the checkbox.

I wrote some code that will create a number of checkboxes that you
specify, starting on a row that you specify. It creates a new checkbox
for each row and a unique cell reference on that same row.

Depending on how your spreadsheet it arranged and formatted, you may
need to change some things in the following lines of code. In this
line,
ActiveSheet.CheckBoxes.Add(10.5, 0 + ((K - 1) * 12.75), 5, 10).Select
10.5 is the horizontal distance from the left margin of your sprdsht
0 + ((K - 1) * 12.75 is the vertical distance from the top of the
sprdsht
5 is the control width
10 is the control height
The 10.5 setting lands the checkbox in column A- you may want to adjust
that
The 0 + ((K - 1) * 12.75 setting assumes the default row height of
12.75. You may need to adjust that to match the row height of your
spreadsheet.

This line
Adrs = "B" & K
....situates the TRUE / FALSE cell in column B

Input windows will appear asking which row to start on, and how many
checkboxes to create.

The code follows:
Sub Many_Checkboxes()
Dim Adrs As String
Dim K As Long
Dim Beg As Long, Fin As Long

Beg = InputBox("Start on what row?")
Fin = InputBox("How many checkboxes?")

For K = Beg To Fin
Adrs = "B" & K

ActiveSheet.CheckBoxes.Add(10.5, 0 + ((K - 1) * 12.75), 5, 10).Select
Selection.Characters.Text = ""

With Selection
.Value = xlOn
.LinkedCell = Adrs
.Display3DShading = False

End With
Next K
End Sub

 

[Guest]

Thanks Dave, your solution sounds exactly what I was looking for.

Thanks for all your help, I appreciate it very much.

Mark