Dynamically adding check boxes based on worksheet name, updating error

[georgedixon]

Hi, I have written the code below to ensure that there is a checkbox on a summary page, for each of the worksheets in my workbook and it works fine:

Private Sub Update_Click()
Dim cb As CheckBox
Dim Exists As Boolean
Dim TopLocation As Double
Dim LeftLocation As Double
Dim Width As Double
Dim Height As Double
Dim ws As Worksheet


    For Each ws In ActiveWorkbook.Worksheets
    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 = True Then
            GoTo endloop12
        End If
        
        If Exists = False Then
        
        TopLocation = 0
    LeftLocation = 0
    Width = 0
    Height = 0


    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
        Width = cb.Width
        Height = cb.Height
        
        
    Next




            With ThisWorkbook.Worksheets("Summary").CheckBoxes.Add(LeftLocation, TopLocation + 0.7 * Height, Width, Height)
                .name = ws.name
                .Caption = ws.name
            End With
            
        End If
        
endloop12:


    Next ws
End Sub

As I said, this works fine, but I also have other bits of code completing other tasks, which involves looping through each checkbox on the summary page. For example, the following is the code for a "Select/Deselect All" Checkbox:

Sub AllCheckboxes()


Dim cb As CheckBox


For Each cb In Sheets("Summary").CheckBoxes
    If cb.name <> Sheets("Summary").CheckBoxes("Check1").name Then
        cb.Value = Sheets("Summary").CheckBoxes("Check1").Value
    End If
Next




End Sub

This loops through all the checkboxes but after use of the "Update" Sub (the first bit of code), it doesn't pick up the newly added checkboxes. The code definitely works when you manually add in any new checkbox but not after adding them in by using the update sub.

I was just wondering if anyone has an idea why it's not working, I thought at first maybe the worksheet needs refreshing because it is clearly not picking up the new checkbox as a checkbox. I have tried adding in a line of code that refreshes the workbook after running the update sub, but it does nothing.

Any help is appreciated, thanks in advance

[georgedixon]

I seem to have solved it, by calling the "AllCheckboxes" sub at the end of the "Update" Sub.