Help with LinkedCell when adding ActiveX checkboxes

[Moose75]

Hi guys,

So I'm trying to write a macro to add a row of 3 ActiveX checkboxes to multiple rows in a sheet, and link each individual checkbox to a unique cell. I can add the checkboxes easy enough, but when I go to link them they all link to the same 3 cells. I think I need to use the .LinkedCell function with a variable but everything I try either gives me an error or gets ignored. Here is what I have so far

Sub AddAXCB()
ActiveSheet.DrawingObjects.Delete  'optional to delete all objects on sheet while testing


STL = 40    'starting top location
SLL = 108   'starting left location
RD = 15     'row depth, used to designate spacing between top and bottom CBs
Y = 7         'total number of rows


For x = 1 To Y


    'first column
    With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=SLL, Top:=STL, Width:=12, Height:=12)
        .LinkedCell = "F3"
        .Select
    End With
        
            'second column
            With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
                DisplayAsIcon:=False, Left:=SLL + 20, Top:=STL, Width:=12, Height:=12)
                 .LinkedCell = "G3"
                 .Select
            End With
        
                    'third column
                    With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
                        DisplayAsIcon:=False, Left:=SLL + 40, Top:=STL, Width:=12, Height:=12)
                        .LinkedCell = "H3"
                        .Select
                    End With
            
            
    STL = STL + RD
    
Next x
         
End Sub

What I have now adds 7 rows of 3 checkboxes but links each column to F3, G3, H3 respectfully, what I need is to have one go to F3, one to G3, one to H3, then on the next row F4, G4, H4, etc through the For statement. Basically I need to add 1 to each LinkedCell row reference in each loop through the For statement.

I'm sorry if this post is too long or confusing. Any help is greatly appreciated. This has been driving me crazy.

Thank you!

 

[JMeendering]

For x = 1 To y
    
'first column
    With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=SLL, Top:=STL, Width:=12, Height:=12)
[COLOR=#ff0000]        .LinkedCell = "F" & x + 2[/COLOR]
        .Select

and follow that patter in the rest of the .LinkedCell=
.LinkedCell = "G" & x + 2
.LinkedCell = "H" & x + 2

 

[JoeMo]

Try:

Sub AddAXCB()
ActiveSheet.DrawingObjects.Delete  'optional to delete all objects on sheet while testing


STL = 40    'starting top location
SLL = 108   'starting left location
RD = 15     'row depth, used to designate spacing between top and bottom CBs
Y = 7         'total number of rows
firstRow = 3  'starting row number

For x = 1 To Y


    'first column
    With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=SLL, Top:=STL, Width:=12, Height:=12)
        .LinkedCell = Range("F" & firstRow).Offset(x - 1).Address(0, 0)
        .Select
    End With
        
            'second column
            With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
                DisplayAsIcon:=False, Left:=SLL + 20, Top:=STL, Width:=12, Height:=12)
                 .LinkedCell = Range("G" & firstRow).Offset(x - 1).Address(0, 0)
                 .Select
            End With
        
                    'third column
                    With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
                        DisplayAsIcon:=False, Left:=SLL + 40, Top:=STL, Width:=12, Height:=12)
                        .LinkedCell = Range("H" & firstRow).Offset(x - 1).Address(0, 0)
                        .Select
                    End With
            
            
    STL = STL + RD
    
Next x
         
End Sub

 

[Moose75]

This works perfectly, thank you!

 

[JoeMo]

This works perfectly, thank you!

When you have more than one solution offered, it's a good idea to indicate which solution you are referring to.