Generating a checkbox corresponding to the each worksheet on click

Hi,
I have written the code below, which hopefully will search through all checkboxes on a page, and ensure that there is a checkbox corresponding to each worksheet, if there is not one for a certain page, I want it to add one in. The checkboxes are form control checkboxes (not ActiveX), they are all in one vertical column and I am looking for it to add one directly below if it is not already found in the search. However, I am stuck, as I don't know how to find the location of the 'lowest' checkbox and from there add another one below (with even spacing). Any help is appreciate, thanks and Here is my code:

Code:

---

Private Sub Update_Click()
Dim LowestCheck As CheckBox
Dim cb As CheckBox
Dim Exists As Boolean




    For Each ws In ActiveWorkbook.Worksheets
        For Each cb In ActiveSheet.CheckBoxes
            If cb.Name = ws.Name Then
                Exists = True
           
            End If
           
        Next
       
                     
        If Exists = False Then
            *FINDS LOWEST CHECKBOX AND ADDS IN CHECKBOX BELOW WITH*
           
    Next
End Sub

---

I have changed the code, it now creates the checkboxes successfully, just it doesn't put them in correct place:

Code:

---

Private Sub Update_Click()
Dim rng As Range
Dim LowestCheck As CheckBox
Dim cb As CheckBox
Dim Exists As Boolean




    For Each ws In ActiveWorkbook.Worksheets
        For Each cb In ThisWorkbook.Worksheets("Summary").CheckBoxes
            If cb.Name = ws.Name Then
                Exists = True
           
            End If
           
        Next
       
                     
        If Exists = False Then
            With ThisWorkbook.Worksheets("Summary").CheckBoxes.Add(Selection.Left, Selection.Top, Selection.Width, Selection.Height)
                .Name = ws.Name
                .Caption = ws.Name
            End With
           
        End If
    Next
End Sub

---

All the current checkboxes are within Column H, I just need a way to determine what is the lowest cell within that column that has a checkbox in and then from there, add a checkbox to the cell below. Again, any help would be appreciated :)

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

Code:

---

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

---

Quote:

---

Originally Posted by mikerickson

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

Code:

---

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:

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.