Excel之VBA常用功能应用篇：列表框操作实例ControlFormat

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表格的自动化应用技巧。