.LinkCell in a Do While Loop? 1

[BillyMcNeal]

I'm new to VBA (taught myself yesterday) and was wondering the notation I would need to use to create a large number of check boxes linking to different cells.

I'm sure you can get the gist of what I'm looking for with how I tried to do it:

Dim Row, Column As Integer
Row = 9
Column = 14
Do While Row < 12
    ActiveSheet.CheckBoxes.Add(717, 124.5 + (Row - 9) * 12.75, 24, 17.25).Select
    Selection.Characters.Text = ""
    Selection.ShapeRange.IncrementLeft -1.5
    Selection.ShapeRange.IncrementTop -4.5
    With Selection
        .Value = xlOff
        .LinkedCell = Worksheets("Turning Movements").Cells(Row, Column)
        .Display3DShading = True
    End With
    Range("O12").Select
    Row = Row + 1
Loop
End Sub

Thanks for your help.

 

[SkipVought]

Hi,

    Dim Row, Column As Integer, oCBX As Object
    Row = 9
    Column = 14
    Do While Row < 12
        Set oCBX = ActiveSheet.CheckBoxes.Add(1, 1, 1, 1)
        With oCBX
            .Top = .TopLeftCell.Top
            .Left = .TopLeftCell.Left
            .Width = .TopLeftCell.Width
            .Height = .TopLeftCell.Height
            .Value = xlOff
            .LinkedCell = Worksheets("Turning Movements").Cells(Row, Column)
            .Display3DShading = True
        End With
        Row = Row + 1
    Loop
    Set oCBX = Nothing

Skip,

_[red][/red]

 

[BillyMcNeal]

Thanks Skip, but your code doesn't seem to be linking the check boxes to the cells. I want to put in a few hundred check boxes, so I'd rather not link them manually. Any suggestions?

 

[SkipVought]

.LinkedCell = Worksheets("Turning Movements").Cells(Row, Column).address

Skip,

_[red][/red]

 

[SkipVought]

.LinkedCell = "'" & Worksheets("Turning Movements").Name & "'!" & Worksheets("Turning Movements").Cells(Row, Column).address

Skip,

_[red][/red]

 

[BillyMcNeal]

Thanks, that worked perfectly.