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 Integer
Dim LeftLocation As Integer
Dim Width As Integer
Dim Height As Integer
For Each ws In ActiveWorkbook.Worksheets
'This loop is simply to stop it from making duplicate checkboxes
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(LocationLeft, LocationTop + 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 :)
Aucun commentaire:
Enregistrer un commentaire