列表框操作实例ControlFormat，VBA编程就是这么容易

Excel 中的列表框操作，可以很有序地实现选择功能，本文将介绍一个对象，以实现对列表框的新建、删除等操作。

ControlFormat对象可以通过Shape对象的ControlFormat属性返回。

如下代码：

Set xListObj = Shapes(2).ControlFormat

xListObj就是一个ControlFormat对象。

ControlFormat方法和属性

如上图所示，本示例实现多种方法给ListBox控件添加列表值。

首先新建一个列表框，代码如下：

Private Sub AddListBox()
'新建ListBox列表框
Dim xShape As Object
Set xShape = Me.Shapes.AddFormControl(xlListBox, 100, 100, 210, 280)
Set xShape = Nothing
End Sub

利用Additem 方法添加列表值

Private Sub AddListItems()
'添加列表值
On Error Resume Next
Dim xShape As Object
Dim xListObj As Object
Set xListObj = Shapes(2).ControlFormat
With xListObj
    .RemoveAllItems
    .AddItem "列表1"
    .AddItem "列表2"
    .AddItem "列表3"
End With
Set xListObj = Nothing
Set xShape = Nothing
End Sub

List方法添加列表值

Private Sub AddListItems()
'添加列表值
On Error Resume Next
Dim xShape As Object
Dim xListObj As Object
Set xListObj = Shapes(2).ControlFormat
xListObj.List = Array("eee", "dddd", "fff")
Set xListObj = Nothing
Set xShape = Nothing
End Sub

ListFillRange属性设置列表值

Private Sub AddlistRange()
'添加列表值
On Error Resume Next
Dim xShape As Object
Dim xListObj As Object
Set xListObj = Shapes(2).ControlFormat
xListObj.ListFillRange = "B3:B10"
Set xListObj = Nothing
Set xShape = NothingistFillRange
End Sub

删除列表值

Private Sub DelListItems()
'删除列表值
On Error Resume Next
Dim xShape As Object
Dim xListObj As Object
Set xListObj = Shapes(2).ControlFormat
With xListObj
    .RemoveItem .ListIndex
End With
Set xListObj = Nothing
Set xShape = Nothing
End Sub

ListBox列表框在编写种类功能性应用时，非常方便，熟练掌握可大大提高对Excel表格的自动化应用技巧。

欢迎关注、收藏

---END---