Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create Multiple checkboxes together

Posted on 2014-02-05
1
Medium Priority
?
193 Views
Last Modified: 2014-02-10
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
Comment
Question by:Vipin Kumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39835622
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

721 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