How to add multiple checkboxes using VBA in Excel 2010

Hi,

I am creating a checklist for my team, which includes tasks which are to be performed daily, weekly, bi-weekly or monthly based on the task.

Initially I created a checklist in which I inserted the check boxes and linked each of them to different cells. But I found that job to be tiring as every month tasks change as per the day.

Kindly let me know how this can be achieved using VBA. Attached is a screenshot of what I created manually.

Would appreciate if a link is provided where an example is there to achieve the above.

I hope you understand what I m trying to explain. Please let me know if any input is required.

Thanks in advance.
Screensht.jpg
LVL 1
Vipin KumarSr. Network EngineerAsked:
Who is Participating?
 
MacroShadowConnect With a Mentor Commented:
Sub InsertCheckboxes()

    Dim myBox As CheckBox
    Dim myCell As Range

    Dim cellRange As String
    Dim cboxLabel As String
    Dim linkedColumn As String
     
    ' Enter range to insert check boxes in i.e. A5:A20
    cellRange = InputBox(Prompt:="Cell Range", Title:="Cell Range")
    ' Enter bound column letter
    linkedColumn = InputBox(Prompt:="Linked Column", Title:="Linked Column")
    ' Enter combobox caption, "" for no caption
    cboxLabel = InputBox(Prompt:="Checkbox Label", Title:="Checkbox Label")

    With ActiveSheet
        For Each myCell In .Range(cellRange).Cells
            With myCell
                Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, Width:=.Width, Left:=.Left, Height:=.Height)

                With myBox
                    .LinkedCell = linkedColumn & myCell.Row
                    .Caption = cboxLabel
                    .Name = "checkbox_" & myCell.Address(0, 0)
                End With

                .NumberFormat = ";;;"
            End With

        Next myCell
    End With
End Sub

Open in new window

0
 
Vipin KumarSr. Network EngineerAuthor Commented:
This is working perfectly fine. Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.