为在VBA中创建的复选框设置“移动和单元格大小”

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

因此，我尝试向创建宏CBX的vba添加以下内容。位置= xlMoveAndSize

But it doesn't make the change.

但这并没有改变。

Any ideas?

什么好主意吗?

Here is the full VBA:

以下是完整的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

终止子

1 个解决方案

#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.

xlMoveAndSize选项对于单独的复选框都不可用，甚至在Excel的GUI中也不可用。但是，如果您将复选框分组，则可以应用它。

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