Fill range with linked checkboxes

[viggenboy]

I'm sure in an ancient version of Excel you could simply format a cell as true/false and it put in a checkbox confused anyhow here I am in Excel 2010 and wanting to put lots of linked check boxes on worksheets.

So I created a macro that puts a linked checkbox in the selected cell- no problem. Now I'd like to fill the whole of selected range (column) with linked checkboxes. So I created this VBA macro:

Sub AddCheckInSel()

Dim Locn, x, y, z

Locn = ActiveCell.Address

x = Range(Locn).Left
y = Range(Locn).Top
z = Range(Locn).Height

For Each cell In Selection

ActiveSheet.CheckBoxes.Add(x, y, 72, z).Select
Selection.Characters.Text = ""
With Selection
.Value = xlOff
.LinkedCell = Locn
.Display3DShading = False
End With

y = y + z


Next cell

End Sub

Select a range, run the macro and I looks great. BUT all of the checkboxes are linked to the top cell (DOH!) ... so what am I doing wrong.

 

[texasalynn]

try adding this

For Each Cell In Selection
Cell.Activate
Locn = ActiveCell.Address
ActiveSheet.CheckBoxes.Add(x, y, 72, z).Select
Selection.Characters.Text = ""
With Selection
    .Value = xlOff
    .LinkedCell = Locn
    .Display3DShading = False
End With

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> AddCheckInSel()<br>    <br>    <SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Selection<br>        <SPAN style="color:#00007F">With</SPAN> ActiveSheet.CheckBoxes.Add(cell.Left, cell.Top, 72, cell.Height)<br>            .Characters.Text = ""<br>            .Value = xlOff<br>            .LinkedCell = cell.Address<br>            .Display3DShading = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> cell<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[viggenboy]

Thanks that's excellent.... wouldn't you think this was a common enough thing people wanted to do that it would be a built in function!