Trying to create checkboxes and set their caption as the name of the existing worksheets in the Excel file

Trying to create checkboxes and set their caption as the name of the existing worksheets in the Excel file

I am trying to create a bunch of checkboxes, each with the same caption as the name of each worksheet in the active Excel file. Below is what I have so far:

Sub GetWorkSheetNames()
    Dim i As Integer
    For i = 3 To Application.Sheets.Count
        'Sets the checkboxes
        ActiveSheet.CheckBoxes.Add(Cells(i + 1, "A").Left, Cells(i + 1, "A").Top, 65, 16).Select
        
        'Prints the worksheet names in cells
        Cells(i + 1, 2).Value = ActiveWorkbook.Sheets(i).Name
    Next i
End Sub

I'm trying to edit the code so each checkbox is stored in a variable, so I can then set the caption of each checkbox. Also, the i starts at 3, since I don't want the first two worksheets to be interpreted by the code.

I am not sure how to store the checkbox into a variable. I would like a column of checkboxes to be made in the 1st worksheet.

Answer

Like this maybe:

Sub GetWorkSheetNames()
    
    Dim i As Long 'prefer Long over Integer
    Dim ws As Worksheet, c As Range
    
    Set ws = ActiveSheet 'or a specific worksheet
    'ws.DrawingObjects.Delete  'clear any previous objects on the sheet
    
    For i = 3 To ws.Parent.Worksheets.Count ' `Sheets` includes (eg) Chart sheets
        Set c = ws.Cells(i + 1, "a") 'target cell
        With ws.CheckBoxes.Add(c.Left, c.Top, 65, 16)
            .Caption = ws.Parent.Worksheets(i).Name 'set the caption
        End With
        'c.Offset(0, 1).Value = ws.Parent.Worksheets(i).Name
    Next i
    
End Sub

Enjoyed this article?

Check out more content on our blog or follow us on social media.

Browse more articles