macro to insert x checkboxes down a column

[clickmagundi]

Hello,

I'm trying to automate the process of putting checkboxes with their
associated cell link down column Q - say 100. It is tedious to do it
manually. The macro I've recorded looks like this:

Sub CheckboxesDown()


ActiveSheet.CheckBoxes.Add(866, 189.75, 24, 17.25).Select '189.75
need to increment by 14.25 ?? each loop as below
Selection.Characters.Text = ""
With Selection
.Value = xlOff
.LinkedCell = "Q14" ' needs to increment by 1 each loop as
below
.Display3DShading = False
End With


ActiveSheet.CheckBoxes.Add(866, 204, 24, 17.25).Select
Selection.Characters.Text = ""
With Selection
.Value = xlOff
.LinkedCell = "Q15"
.Display3DShading = False
End With


ActiveSheet.CheckBoxes.Add(866, 218.25, 24, 17.25).Select
Selection.Characters.Text = ""
With Selection
.Value = xlOff
.LinkedCell = "Q16"
.Display3DShading = False
End With

ActiveSheet.CheckBoxes.Add(866, 232.5, 24, 17.25).Select
Selection.Characters.Text = ""
With Selection
.Value = xlOff
.LinkedCell = "Q17"
.Display3DShading = False
End With

ActiveSheet.CheckBoxes.Add(866, 246.75, 24, 17.25).Select
Selection.Characters.Text = ""
With Selection
.Value = xlOff
.LinkedCell = "Q18"
.Display3DShading = False
End With

ActiveSheet.CheckBoxes.Add(866, 261, 24, 17.25).Select
Selection.Characters.Text = ""
With Selection
.Value = xlOff
.LinkedCell = "Q19"
.Display3DShading = False
End With

End Sub

As I've shown I believe it needs a do loop for two elements the
position of the checkbox and the cell link.

The "189.75 to increment by 14.25" isn't quite right as the
checkboxes nudge up each time so I guess it needs something like plus
14.25, next one plus 14.26?. it needs to be in the same position above
the linked cell each time.

Would appreciate some help.

Many thanks

 

[Greg Wilson]

This is best done using loop code:

For the example code, you must first select the cells you want to insert
checkboxes. The code assumes you want to set the linked cells to the cells
two columns to the right of each checkbox. Change to suit. Also assumed is
that you want to size the checkboxes to 15 points high and they should fit
inside the selected cells. Change to suit.

Sub AddCBoxes()
Dim c As Range
For Each c In Selection.Cells
c.RowHeight = 15
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height) _
.LinkedCell = c(1, 3).Address 'c(1, 4) would offset 3 columns to right
Next
End Sub

Regards,
Greg

 

[clickmagundi]

That's perfect - many thanks.