Copying and pasting lost of form control tick boxes

[tonywatsonhelp]

Hi Every one,

I have to copy a form control tick box about 200 times,
when i copy and paste it the cell link does not move so i have 200 tick boxes all pointing to cell D21
this is a one off thing so anyone know a way i can get the cell links to follow down the column
or amacro that can copy paste and change the cell link?
any ideas please
Thanks
Tony

 

[DacEasy]

Hi, I tried to come up with something that would help and found this on mrexcel.com. I updated the code and included a link to the original thread so you can see other examples. This is for Checkboxes, I hope that is what you meant by tick boxes.

Option Explicit
Sub forCreateCheckboxes()
    Dim ToRow As Long
    Dim MyLeft As Double
    Dim MyTop As Double
    Dim MyHeight As Double
    Dim MyWidth As Double
    Dim BoxesNeeded As Long
    Dim i
    '--------------------------
    'https://www.mrexcel.com/board/threads/add-checkboxes-through-vba-code.180887/
    
    BoxesNeeded = 20 'Update to the amount of boxes you want
    
    For i = 1 To BoxesNeeded
        Range("E" & i) = i 'Fills D1 with 1 to BoxesNeeded
    Next i
    
    For ToRow = 1 To BoxesNeeded
        If Not IsEmpty(Cells(ToRow, "E")) Then 'IF E row# has number, C row# gets a box
            '-
            MyLeft = Cells(ToRow, "C").Left
            MyTop = Cells(ToRow, "C").Top
            MyHeight = Cells(ToRow, "C").Height
            MyWidth = MyHeight = Cells(ToRow, "C").Width
            '-
            ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
             With Selection
                .Caption = ""
                .Value = xlOff
                '.LinkedCell = "D" & ToRow 'Uncomment if you want to see true or false in Column D
                .Display3DShading = False
            End With
        End If
    Next

 

[tonywatsonhelp]

Thank you DacEasy this is great
thanks very much for your help
Tony