Consulting

Results 1 to 4 of 4

Thread: Generating a checkbox corresponding to the each worksheet on click

  1. #1

    Generating a checkbox corresponding to the each worksheet on click

    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

  2. #2
    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
    Last edited by georgedixon; 09-04-2018 at 05:54 AM.

  3. #3
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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

  4. #4
    Quote Originally Posted by mikerickson View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •