I am trying to insert multiple checkboxes into an excel table. As I need to filter rows in the table, the checkboxes need to be able to resize and move. So I need to programmatically set Object Positioning to "Move And Size with cells". I have tried but this property does not change with my code. I am using Excel 2010 and if I try to change Object positioning manually, it is greyed out. However if I click on Format> Size and Properties, it is enabled there. I need to do this through a macro. I tried recording the code when I go to Formats to change this property but the code is just the same like under Selection.
Sub at1()
Dim list1 As ListObject
Dim s1 As Worksheet
Set s1 = Sheets("RawD")
Set list1 = ActiveSheet.ListObjects("RawData1")
i = 1
'MsgBox list1.DataBodyRange.Rows.Count
s1.CheckBoxes.Add(Left:=list1.DataBodyRange.Cells(1, 1).Left + 20, Top:=list1.DataBodyRange.Cells(1, 1).Top, Width:=list1.DataBodyRange.Cells(1, 1).Width, Height:=list1.DataBodyRange.Cells(1, 1).Height).Select
With Selection
.Placement = xlMoveAndSize ' this does not work
.Characters.Caption = ""
End With
list1.DataBodyRange.Cells(1, 1).Select
Selection.AutoFill Destination:=list1.DataBodyRange.Columns(1), Type:=xlFillDefault
End Sub
Aucun commentaire:
Enregistrer un commentaire