Set "Move and Size With Cells" for checkbox created in VBA

Question

I've created a spreadsheet which has a lot of checkboxes in it within columns. Occasionally I am finished with a column, and wish to hide it from view.

However if I hide the column, it does not hide the checkboxes in that column.

If I manually change the checkbox properties to "Move and Size With Cells" this fixes the problem. But as I said there are a lot of checkboxes, and I create them with a macro.

So I tried to add the following to the VBA which creates the macro:

CBX.Placement = xlMoveAndSize

But it doesn't make the change.

Any ideas?

Here is the full VBA:

Sub CellCheckboxReview()
Dim myCell As Range
Dim myRng As Range
Dim CBX As CheckBox

With ActiveSheet
    'remove comment if you want to delete all .CheckBoxes.Delete
    Set myRng = .Range(ActiveCell.Offset(19, 0), ActiveCell.Offset(23, 0))
End With

For Each myCell In myRng.Cells
    With myCell
        Set CBX = .Parent.CheckBoxes.Add _
                    (Top:=.Top, _
                     Left:=.Left, _
                     Width:=.Width, _
                     Height:=.Height)
        CBX.Name = "Checkbox_" & .Address(0, 0)
        CBX.Caption = "" 'or what you want
        CBX.Value = xlOff
        CBX.LinkedCell = .Address(external:=True)
        CBX.Placement = xlMoveAndSize
        .NumberFormat = ";;;"
    End With
Next myCell

End Sub

A.S.H · Accepted Answer · 2017-02-06 12:38:30Z

1

The option xlMoveAndSize is not available for individual checkboxes, not even in the GUI of Excel. However, you can apply it if you Group your checkboxes.

Sub CellCheckboxReview()
    Dim myCell As Range, myRng As Range, CBX As CheckBox
    Set myRng = ActiveSheet.Range(ActiveCell.Offset(19, 0), ActiveCell.Offset(23, 0))

    Dim ar(1 To 5) As String ' <-- an array to be used to group the checkboxes
    Dim i As Long: i = 1
    For Each myCell In myRng.Cells
        With myCell
            Set CBX = .Parent.CheckBoxes.Add _
                        (Top:=.Top, _
                         Left:=.Left, _
                         Width:=.Width, _
                         Height:=.Height)
            CBX.Name = "Checkbox_" & .Address(0, 0)
            CBX.Caption = "" 'or what you want
            CBX.value = xlOff
            CBX.LinkedCell = .Address(external:=True)
            'CBX.Placement = xlMoveAndSize  ' <-- this has no effect for an individual checkbox
            .NumberFormat = ";;;"
            ar(i) = CBX.Name ' <-- add the shape's name to the array, for grouping later 
            i = i + 1
        End With
    Next myCell

    ' now group the checkboxes then set the desired placement
    With ActiveSheet.Shapes.Range(ar)
        .Group
        .Item(1).Placement = xlMoveAndSize
    End With
 End Sub

edited Feb 6, 2017 at 12:38

answered Feb 3, 2017 at 10:29

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

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

2 Comments

Jarrod P Over a year ago

That's fantastic, thanks a lot for your help! I had to remove the Cells(1, 1).Activate as it moved the checkbox locations and cleared the current values in column A. Other than that it worked perfectly.

A.S.H Over a year ago

@JarrodP yes that line is useless indeed. I actually did not change your code's body, I just added the grouping and Placement.

Collectives™ on Stack Overflow

Set "Move and Size With Cells" for checkbox created in VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related