CODEWITHSUNDEEP
excelcheckboxvba
Luis
Luis

Reputation: 71

Checkbox Modification

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?

Upvotes: 0

Views: 61

Answers (2)

MikeD
MikeD

Reputation: 8941

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.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 1

Related Questions