fill down populates rows with check boxes, with same link cell

[lalbatros]

Hello,

I created a checkbox that fits within the cell it is linked to.
By filling down the link-cell, I can multiply the number of check box.
This is almost what I wanted to do: create check boxes by simply filling down.

However, the link cell remains the same.
This is not what I would like to do.
I would like a new check box to be linked to the new cell where it fit in.
In that way, I could have the check box working exactly as it would in an Access form, for example.

Would there be a way to do that?
Without this possibility, I would rather use a simple "x" mark in a cell for the same purpose.

Thanks,

Michel

 

[Weaver]

Rather than copying & pasting, you could use some macro code to insert the checkboxes. The following code will insert checkboxes into A1:A10, name them, resize them to fit the cell, set up the linked cells and change the caption.

Sub insertCheckboxes()
    For x = 1 To 10
        Set c = Cells(x, "A")
        n = "Choice_" & Format(x, "00")
        m = "My Check Box " & Format(x, "00")
        With ActiveSheet.OLEObjects.Add( _
            ClassType:="Forms.CheckBox.1", _
            Link:=False, _
            DisplayAsIcon:=False, _
            Left:=c.Left, _
            Top:=c.Top, _
            Width:=c.Width, _
            Height:=c.Height)
            .Name = n
            .LinkedCell = c.Address
            .Object.Caption = m
        End With
        ActiveSheet.Shapes(n).Placement = xlMoveAndSize
        c.Value = False
    Next x
End Sub

Thanks to Rorya who helped out with this code

 

[reemt]

Hello,

what if i want to insert the tick boxes in the column conditional of cell in another column not to be empty- so basically I am working on a model and I want to insert tickboxes in column N10 to N21279 only if the correspondant cell in J column has value.

the same goes for column O10 to O21279 till V10 to V21279

Looking forward to your feedback!!

Thanks
Reem

Rather than copying & pasting, you could use some macro code to insert the checkboxes. The following code will insert checkboxes into A1:A10, name them, resize them to fit the cell, set up the linked cells and change the caption.

Sub insertCheckboxes()
    For x = 1 To 10
        Set c = Cells(x, "A")
        n = "Choice_" & Format(x, "00")
        m = "My Check Box " & Format(x, "00")
        With ActiveSheet.OLEObjects.Add( _
            ClassType:="Forms.CheckBox.1", _
            Link:=False, _
            DisplayAsIcon:=False, _
            Left:=c.Left, _
            Top:=c.Top, _
            Width:=c.Width, _
            Height:=c.Height)
            .Name = n
            .LinkedCell = c.Address
            .Object.Caption = m
        End With
        ActiveSheet.Shapes(n).Placement = xlMoveAndSize
        c.Value = False
    Next x
End Sub

Thanks to Rorya who helped out with this code