深入了解Excel工作表中的控件

Excel工作表中可以使用两种控件：表单控件和ActiveX控件。

插入控件

使用VBA在工作表中插入控件非常简单。

Sub AddCtls()
    '表单控件
    ActiveSheet.Buttons.Add 87.75, 33, 86.25, 33
    ActiveSheet.CheckBoxes.Add 228.75, 33, 70.5, 33
    'ActiveX控件
    ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=86.25, Top:=99.75, Width:=90, Height:=33
    ActiveSheet.OLEObjects.Add ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=228, Top:=99, Width:=90, Height:=33
End Sub

在工作表中分别插入两种不同类型的按钮和复选框控件。

遍历控件

大家都知道，在用户窗体中，可以很容易的使用For Each Ctl in Userform1.Controls遍历窗体中的控件，但是如果需要遍历工作表中控件，并修改其相关属性就不像用户窗体中那么简单了。

Shapes集合

最简单的方法是遍历工作表中Shapes集合，什么！？别欺负我读书少，英文单词Shape是图形的意思，和控件根本不搭边！
在立即窗口的输出结果看着有些神奇，这些控件确实属于Shapes集合。

Sub GetCtlList1()
    For Each s In ActiveSheet.Shapes
        Debug.Print s.Name, s.Type
    Next
End Sub
' --- 立即窗口 ---
'Button 1           8 (msoFormControl) 
'Check Box 2        8
'CommandButton1     12 (msoOLEControlObject)
'CheckBox1          12

注：括号中常量值，为作者手工添加的
控件类型8（msoFormControl）是表单控件，类型12 （msoOLEControlObject）是ActiveX控件。

控件属性

已经定位到控件了，接下来就直接用Caption，Value …属性吧，对不起。Shape对象不支持这些属性。明明可以在工作表中操作这些控件，比如复选框的勾选，难道没有办法读取出来吗？答案是肯定的，可以读取，但是比较麻烦，属性的用法和用户窗体中的控件也不相同。

Sub GetCtlList2()
On Error Resume Next
    For Each s In ActiveSheet.Shapes
        If s.Type = 8 Then
        Debug.Print s.Name, s.AlternativeText, s.Type, s.FormControlType
        ElseIf s.Type = 12 Then
        Debug.Print s.Name, s.DrawingObject.Object.Caption, s.Type, s.OLEFormat.progID
        End If
    Next
End Sub
' --- 立即窗口 ---
'Button 1           按钮 1            8             0 (xlButtonControl)
'Check Box 2        复选框 2           8             1 (xlCheckBox)
'CommandButton1     CommandButton1 12           Forms.CommandButton.1
'CheckBox1          CheckBox1      12           Forms.CheckBox.1

例如：在工作表中看到的控件上显示的文字，对于表单控件需要访问OLEFormat.Object.Caption属性，但是对于ActiveX控件是DrawingObject.Object.Caption属性。

下表列出两种控件几个常用属性的对比。

名称	控件文字	控件类型	控件值
窗体按钮	OLEFormat.Object.Caption	FormControlType	-
窗体复选框	OLEFormat.Object.Caption	FormControlType	OLEFormat.Object.Value
ActiveX按钮	DrawingObject.Object.Caption	OLEFormat.progID	-
ActiveX复选框	DrawingObject.Object.Caption	OLEFormat.progID	OLEObjects(..).Object.Value

缺省情况下，表单控件的OLEFormat.Object.Caption属性与AlternativeText属性值相同。

实例

工作表控件经常被用来制作用户调查问卷，对于表单控件正确的用法是先设置单元格链接，最终统计调查结果时，只需要读取相应单元格的值就可以了。

但是日常工作中在用的问卷调查，很多都没有设置单元格链接，最终负责统计调查结果的表哥表妹们，只能加班加点人工去数数了。
如果表哥表妹们早点儿学习VBA，点个鼠标，喝口水的功夫，就有了统计结果。

Sub GetOptionValue()
    For Each s In ActiveSheet.Shapes
        If s.Type = 8 Then
          Debug.Print s.OLEFormat.Object.Caption, _
          IIf(s.OLEFormat.Object.Value = 1, "选中", "未选中")
        End If
    Next
End Sub
' --- 立即窗口 ---
'豆浆+油条     选中
'馄饨+包子     未选中
'牛奶+面包     未选中

如果单选按钮未被选中，其OLEFormat.Object.Value属性返回值为-4146 。