PDA

View Full Version : Generating a checkbox corresponding to the each worksheet on click



georgedixon
09-04-2018, 02:29 AM
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:


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

georgedixon
09-04-2018, 04:17 AM
I have changed the code, it now creates the checkboxes successfully, just it doesn't put them in correct place:


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 :)

mikerickson
09-04-2018, 07:10 AM
Storing the location of the checkboxes as you loop through them will help place the new one.


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

georgedixon
09-04-2018, 10:21 AM
Storing the location of the checkboxes as you loop through them will help place the new one.


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:


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.