Dynamically Adding new form control check boxes based on worksheet names (Excel VBA)

Question

I am trying to make a system that adds checkboxes (on the click of a button) if there are any new worksheets added to the workbook. My code is below, It was able to create the checkboxes until I tried changing the location, so I am assuming that the reason it doesn't work is due to that.

Private Sub Update_Click()
Dim cb As CheckBox
Dim Exists As Boolean
'I think these location/ dimension variables are perhaps wrong (I'm not sure what values they take)
Dim TopLocation As Double
Dim LeftLocation As Double
Dim Width As Double
Dim Height As Double

    For Each ws In ActiveWorkbook.Worksheets
'This loop is simply to stop it from making duplicate checkboxes
Exists=False
        For Each cb In ThisWorkbook.Worksheets("Summary").CheckBoxes
            If cb.name = ws.name Or ws.name = "Summary" Or ws.name = "Price List (2)" Then
                Exists = True

            End If

        Next

        If Exists = False Then

        TopLocation = 0
    LeftLocation = 0
    Width = 0
    Height = 0
'The following loop is an attempt to find the checkbox that is furthest down the page, the problem is that I am not too familiar with the location attribute so am just assuming that it increases as you move down the page
    For Each cb In ThisWorkbook.Worksheets("Summary").CheckBoxes
        If cb.Top > TopLocation Then
            TopLocation = cb.Top
        End If
         If cb.Left > LeftLocation Then
            LeftLocation = cb.Left
        End If
         If cb.Width > Width Then
            Width = cb.Width
        End If
         If cb.Height > Height Then
            Height = cb.Height
        End If

    Next
'The following is where I believe the problem to be, I thought that I could simply use the variables I had created to place the new one in this location
            With ThisWorkbook.Worksheets("Summary").CheckBoxes.Add(LeftLocation, TopLocation + Height, Width, Height)
                .name = ws.name
                .Caption = ws.name
            End With

        End If
    Next ws
End Sub

I am thinking that perhaps this is a misunderstanding of the checkboxes syntax and was hoping that someone could help me to understand where I have gone wrong. Any help is appreciated, Thanks :)

Answer 1

you have two typos..

in the line

With ThisWorkbook.Worksheets("Summary").CheckBoxes.Add(LocationLeft, LocationTop + Height, Width, Height)

the vars should be LeftLocation (not LocationLeft ) and TopLocation (not LocationTop)

good luck