Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Create Multiple checkboxes together

Hi,

I have the below macro which helps me create multiple checkboxes in a range and link those checkboxes to specific cells. Here is an example what exactly is happening with the below macro.

The macro ask for a range where I need to put in the checkboxes for ex I enter A5:A20 and then next it asks me to enter a column name to which the checkboxes are linked for ex I enter B then the macro links A5:A20 to B5:B20 respectively.

What I want to achieve is that I enter multiple ranges initially for I enter A5:A20,B5:B10 and then I enter C,D then it should link A5:A20 to C5:C20 and B5:B10 to D5:D10 respectively.

I hope i have provided all inputs. Kindly let me know incase any more input is required.

Thanks in advance.

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 Kumar
Asked:
Vipin Kumar
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try
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 Idx = 1 To .Range(cellRange).Areas.Count
        For Each myCell In .Range(cellRange).Areas(Idx)
            With myCell
                Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, Width:=.Width, Left:=.Left, Height:=.Height)

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

                .NumberFormat = ";;;"
            End With

        Next myCell
        Next
    End With
End Sub

Open in new window

Regards
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now