PDA

View Full Version : [SOLVED] Dynamically adding check boxes based on worksheet name, updating error



georgedixon
09-11-2018, 12:49 PM
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
09-12-2018, 03:33 AM
I seem to have solved it, by calling the "AllCheckboxes" sub at the end of the "Update" Sub.