How to add multiple checkboxes using VBA in Excel 2010

Posted on 2014-02-04
Medium Priority
Last Modified: 2014-02-04

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.
Question by:Vipin Kumar
LVL 28

Accepted Solution

MacroShadow earned 2000 total points
ID: 39832679
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


Author Comment

by:Vipin Kumar
ID: 39833253
This is working perfectly fine. Thanks for your help.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.

Join & Write a Comment

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question