Generating a checkbox corresponding to the each worksheet on click

[georgedixon]

Storing the location of the checkboxes as you loop through them will help place the new one.

Sub test()
    Dim oneShape As Shape
    Dim maxTop As Single
    Dim Exists As Boolean
    Dim sh As Worksheet
    
    For Each sh In ThisWorkbook.Worksheets
        maxTop = 0: Exists = False
        For Each oneShape In sh.Shapes
            If oneShape.Type = msoFormControl Then
                If oneShape.FormControlType = xlCheckBox Then
                    maxTop = WorksheetFunction.Max(maxTop, oneShape.Top + oneShape.Height)
                    If oneShape.Name = oneShape.Parent.Name Then
                        Exists = True
                    End If
                End If
            End If
        Next oneShape
        
        If Exists Then
            Rem all good
        Else
            With sh.CheckBoxes.Add(sh.Range("H1").Left, maxTop, 100, 20)
                    .Name = sh.Name
                    .Caption = sh.Name
            End With
        End If
    Next sh
End Sub

Thanks, I have just had a look. Upon running it, however, I have seen that it is putting the checkboxes on each page in that certain cell. I have changed it in order to place them on the summary page (which is where all the checkboxes should be in a single columns, perhaps I didn't make it clear earlier) using the following code:

Sub test_click()
    Dim oneShape As Shape
    Dim maxTop As Single
    Dim Exists As Boolean
    Dim ws As Worksheet
    Dim Count As Integer
    Count = 0
    For Each ws In ThisWorkbook.Worksheets
    Count = Count + 1
        maxTop = 0: Exists = False
        For Each oneShape In ThisWorkbook.Worksheets("Summary").Shapes
            If oneShape.Type = msoFormControl Then
                If oneShape.FormControlType = xlCheckBox Then
                    maxTop = WorksheetFunction.Max(maxTop, oneShape.Top + oneShape.Height)
                    If oneShape.Name = oneShape.Parent.Name Then
                        Exists = True
                    End If
                End If
            End If
        Next oneShape
        
        If Exists Then
            
        Else
            With ThisWorkbook.Worksheets("Summary").CheckBoxes.Add(ThisWorkbook.Worksheets("Summary").Range("J" & Count).Left, maxTop, 100, 20)
                    .Name = ws.Name
                    .Caption = ws.Name
            End With
        End If
    Next ws
End Sub

This now does absolutely nothing. Note: I just switched sh. for ThisWorkbook.Worksheets("Summary") because all the checkboxes are located on the summary page. I am not sure what the oneshape loop does so I am unsure how to fix it. If you could take a look at it again that'd be great I also tried adding in a counter, I was thinking that It may fix the position issue... but it didn't.