Forum Discussion
LPONG0413
Jan 05, 2020Copper Contributor
Copying a Check Box to other cells
I am doing a sheet where I need a check box in each of the 5000 rows. I would appreciate any help in doing it other than pasting a check box 4999 times. Thank you! lpong0413
Riny_van_Eekelen
Jan 06, 2020Platinum Contributor
Notice SergeiBaklan 's comment! You still need to link each and every box to a cell 4999 times after copying it. The following macro could help out here. It creates 5000 checkboxes with a text label "YES", each linking to its own cell in column B. In this particular example the size of each box is 120 pixels wide by 30 pixels high. The first one will be put at coordinate (0, 0) i.e. top left corner of the sheet. Each following box will be placed 30 pixels below the previous one. If you set the row height to 30 for all rows, it'll work nicely. Of course, you may change the parameters to meet your own needs.
Sub Macro1()
Dim NextLoc, i As Integer
NextLoc = 0
For i = 1 To 5000
ActiveSheet.CheckBoxes.Add(0, NextLoc, 120, 30).Select
With Selection
.Value = xlOff
.Characters.Text = "YES"
.LinkedCell = "$B$" & i
.Display3DShading = False
End With
NextLoc = NextLoc + 30
Next i
End Sub
LPONG0413
Jan 07, 2020Copper Contributor
I will keep this macro for future reference.
Thank you very much for educating me. Hope you don't get tired of helping me.