Checkbox Modification

Question

I have a important question that I didn't find answer to, is it possible to increase the width and height of a check box and Is it possible to export to another sheet the check box with the values that were selected?

And is it possible to create multiple check boxes like for 500 or 1000 rows?

Gary's Student · Accepted Answer · 2015-06-16 12:35:55Z

1

To your second question..............to populate multiple CheckBoxes by rows:

Sub BoxMaker()
    For i = 1 To 4
        ActiveSheet.CheckBoxes.Add(358.5, 50, 100, 60).Select
    Next

    Dim s As Shape
    i = 2

    For Each s In ActiveSheet.Shapes
        s.Top = Cells(i, 1).Top
        s.Height = Cells(i, 1).Height
        s.Left = Cells(i, 1).Left
        s.Width = Cells(i, 1).Width
        i = i + 1
    Next
End Sub

enter image description here

To your first question, just update the .Height and .Width

answered Jun 16, 2015 at 12:35

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Luis Over a year ago

do you know how to put like 4 checkboxes per cell, the code is working very well and I thank you for the reply but in terms of doing this and putting 4 checkboxes per cell?

Luis Over a year ago

by the way If it doesn't mind you how do I change this to another cell range perhaps "H" because this is specified for the column "A"

Gary's Student Over a year ago

@Luis Use Cells(i,8) rather than Cells(i,1)

MikeD · Accepted Answer · 2015-06-17 12:22:18Z

1

Regarding export, link the value of the checkboxes to any cells in the sheet and export these cell values ... there are plenty of descriptions here on SO how to export cell data.

To link a checkbox to a cell use

Sub test()
Dim S As Shape

    Set S = ActiveSheet.Shapes(1)
    S.ControlFormat.LinkedCell = "B1"

End Sub

To add this to Gary's Student's code ...

Sub BoxMaker()
    For i = 1 To 4
        ActiveSheet.CheckBoxes.Add(358.5, 50, 100, 60).Select
    Next

    Dim s As Shape
    i = 2

    For Each s In ActiveSheet.Shapes
        s.Top = Cells(i, 1).Top
        s.Height = Cells(i, 1).Height
        s.Left = Cells(i, 1).Left
        s.Width = Cells(i, 1).Width

        ' add Cell Link
        ' 2nd parameter of Cells(i, 2).Address sets column of linked cell ... in this case column B
        s.ControlFormat.LinkedCell = Cells(i, 2).Address

        i = i + 1
    Next
End Sub

fields will be filled after first click to the checkboxes.

edited Jun 17, 2015 at 12:22

answered Jun 17, 2015 at 10:24

MikeD

8,9412 gold badges32 silver badges50 bronze badges

2 Comments

Luis Over a year ago

Thanks for the help, put how do I put this to make the value appear in multiple rows? like B1 to B500 and could you just explain what is the "Shapes(1)"?

MikeD Over a year ago

Shapes(1) references the first shape you created ... just an example to demonstrate the use of .ControlFormat.LinkedCell ... see edit ... I inserted this code in to the routine of @Gary

Collectives™ on Stack Overflow

Checkbox Modification

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related