Adding checkboxes to non contiguous ranges - addressing the range

[nigelog]

Hi All I am struggling to come up with a method of filling ranges on a worksheet with checkboxes.
I need i = 1 to 147 but not every integer and at one point (97 to 129) every alternate one. Any ideas how to go about this without creating separate blocks of code

Sub AddCheckBox()
Dim cell As Range
Dim cb As CheckBox
Dim i As Integer
Dim myArr As Variant
'Set myArr (7 - 20, 24 - 32, 35 - 67, 74 - 89, 97 - 129 Step 2 , 131 - 141)
DelCheckBox
    For i = 7 To 141
        For Each cell In Range("F" & i)
 
            With ActiveSheet.CheckBoxes.Add(cell.Left, _
            cell.Top, cell.Width, cell.Height)
            .LinkedCell = cell.Offset(, 0).Address(External:=T)
            .Interior.ColorIndex = 37
            .Caption = ""

        End With
    Next
    For Each cb In ActiveSheet.CheckBoxes
    cb.Caption = Range(cb.LinkedCell).Row
     Next cb
Next


End Sub

Any pointers appreciated

[nigelog]

Bit of a mess but works

Sub AddCheckBox()
Dim cell, c, rng As Range
Dim cb As CheckBox
DelCheckBox
Set rng = Range("F7:f20,F24:F32,f35:F67,F74:F89,f131:f141")

For Each cell In rng
  With ActiveSheet.CheckBoxes.Add(cell.Left, _
     cell.Top, cell.Width, cell.Height)
     .LinkedCell = cell.Offset(, 0).Address(External:=T)
     .Interior.ColorIndex = 37
     .Caption = ""
  End With
  Next
  
For i = 97 To 129 Step 2
For Each cell In Range("F" & i)
  With ActiveSheet.CheckBoxes.Add(cell.Left, _
     cell.Top, cell.Width, cell.Height)
     .LinkedCell = cell.Offset(, 0).Address(External:=T)
     .Interior.ColorIndex = 37
     .Caption = ""
  End With
Next
Next
    For Each cb In ActiveSheet.CheckBoxes
    cb.Caption = Range(cb.LinkedCell).Row
     Next cb

End Sub

[6StringJazzer]

There are clever ways to do this but frankly I think it would be overkill for your situation. I would just write 6 For loops, but put the guts of the For loop in another Sub.

Second, it does not make any sense to use nested For loops for this. Your inner For loop is just looping on one cell.

You have a bug in the way you constructed your loops. Your "For Each cb" loop is inside the "For i = 7 to 141" loop so you are captioning all of the checkboxes in the entire sheet every time you add one checkbox. This can be done just one time for each checkbox when it is created.

You are using a variable T that is undefined. This is for the External argument to Address, which in this case should be left the default of False.

I don't know what DelCheckBox does but I tested this without it and it works.

Sub AddCheckBox()

   Dim Cell As Range
   Dim cb As CheckBox
   Dim i As Integer
   Dim myArr As Variant
   'Set myArr (7 - 20, 24 - 32, 35 - 67, 74 - 89, 97 - 129 Step 2 , 131 - 141)
   DelCheckBox
       
       For i = 7 To 20
         AddCheckBoxes Cell:=Range("F" & i)
       Next
       For i = 24 To 32
         AddCheckBoxes Cell:=Range("F" & i)
       Next
       For i = 35 To 67
         AddCheckBoxes Cell:=Range("F" & i)
       Next
       For i = 74 To 89
         AddCheckBoxes Cell:=Range("F" & i)
       Next
       For i = 97 To 129 Step 2
         AddCheckBoxes Cell:=Range("F" & i)
       Next
       For i = 131 To 141
         AddCheckBoxes Cell:=Range("F" & i)
       Next
   

End Sub

Private Sub AddCheckBoxes(Cell As Range)
           
    
   With ActiveSheet.CheckBoxes.Add(Cell.Left, _
                                   Cell.Top, _
                                   Cell.Width, _
                                   Cell.Height)
   
      .LinkedCell = Cell.Offset(, 0).Address
      .Interior.ColorIndex = 37
      .Caption = Range(.LinkedCell).Row
   
   End With

End Sub

[nigelog]

Thanks 6 stringJazzer, much appreciated. That was the direction I had intended going but was trying to overthink the situation. I take your point on the loop cross over and have used your code