Trying to create checkboxes and set their caption as the name of the existing worksheets in the Excel file
typescript
Ethan JacksonI 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