jeudi 2 février 2017

Set "Move and Size With Cells" for checkbox created in 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

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




Aucun commentaire:

Enregistrer un commentaire