Set “Move and Size With Cells” for checkbox create

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

Answer 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