跟着王老师学Excel VBA #12 | 使用ActiveX控件
概要
- ActiveX控件与表单控件的区别
- ActiveX控件：属性 | 常用事件
- 按钮控件 | CommandButton
- 标签控件 | Label
- 单选按钮控件 | OptionButton
- 微调按钮控件 | SpinButton
演示案例
- 开发自动选题及评分的考试系统
Notes
- ActiveX控件（也是一个单独的对象，可在VBA中获取其属性，或对其属性赋值） 编辑 — 设计模式
- ActiveX常用属性 Caption：标题（显示的文字） Enable：可用性 True or False Visible：可见性 True or False
DEMO-1 命令按钮 CommandButton
Sheet1.CommandButton1.Caption="结束"
Range("A1") = Sheet1.CommandButton1.Caption
Sheet1.CommandButton1.Top = Sheet1.CommandButton1.Top + Sheet1.CommandButton1.Height '移动位置
事件 — 可在ActiveX控件所在的VBA工作表中选到这个对象
Private Sub CommandButton1_Click()
Range("C2") = 8
End Sub
'一个无聊的小玩意
Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) '鼠标悬停
Sheet1.CommandButton1.Caption="不服点我"
Sheet1.CommandButton1.Top = Sheet1.CommandButton1.Top + Sheet1.CommandButton1.Height
End Sub
DEMO-2 标签 Label
只是显示文字，给用户查看（不同于文本框，可输入文字）
Sheet1.Label1.Caption = "只是给你看看的"
DEMO-3 选项按钮（单选）OptionButton
属性
GroupName 组名 '同组互斥
Value 返回值 'True | False
With Sheet1
If .OptionButton1.Value = True Then
MsgBox "你选的是男"
ElseIf .OptionButton2.Value = True Then
MsgBox "你选的是女"
Else
MsgBox "你没选"
End If
End With
事件
Private Sub OptionButton1_Click()
MsgBox "男"
End Sub
DEMO-4 数值调节钮（微调按钮）SpinButton
属性
无Caption属性
Value
Max | Min
（存在LinkedCell，但一般不在此处链接单元格）
（类比
表单控件中，也存在数值调节钮
设置控件格式 - 单元格链接
用于调节链接到的单元格内的数字）
Sub xieru() '写在模块里
Range("A1") = Sheet1. SpinButton1. Value
End Sub
事件
Private Sub SpinButton1_Change() '写在Sheet1里
Call xieru
End Sub
代码
From DEMO-5 综合练习：随堂小测试
⭐案例 — 开发自动选题及评分的考试系统
---#写在模块里#---
'写入数据
Sub xieru(i As Integer) '带参数的过程，可被多个过程调用
With Sheet2
'清空四个选项 — 环境准备
.OptionButton1. Value = False
.OptionButton2. Value = False
.OptionButton3. Value = False
.OptionButton4. Value = False
'写入题目
.Label2. Caption = i
.Label3. Caption = Sheet3. Range("A" & i+1)
.Label4. Caption = Sheet3. Range("B" & i+1)
.Label5. Caption = Sheet3. Range("C" & i+1)
.Label6. Caption = Sheet3. Range("D" & i+1)
.Label7. Caption = Sheet3. Range("E" & i+1)
'查看是否有CD两个选项
If .Label6. Caption = "" Then
.OptionButton3. Visible = False
Else
.OptionButton3. Visible = True
End If
If .Label7. Caption = "" Then
.OptionButton4. Visible = False
Else
.OptionButton4. Visible = True
End If
'返回之前的答案
If Sheet3. Range("G" & i+1) = "A" Then
.OptionButton1. Value = True
ElseIf Sheet3. Range("G" & i+1) = "B" Then
.OptionButton2. Value = True
ElseIf Sheet3. Range("G" & i+1) = "C" Then
.OptionButton3. Value = True
ElseIf Sheet3. Range("G" & i+1) = "D" Then
.OptionButton4. Value = True
End If
End With
End Sub
'查询成绩
Sub chengji()
Dim i, k As Integer
Sheet2.SpinButton1.Max = Sheet3.Range("A65536").End(xlUp).Row - 1
For i = 1 To Sheet2.SpinButton1.Max
If Sheet3.Range("G" & i + 1) = Sheet3.Range("F" & i + 1) Then
k = k + 1
End If
Next
MsgBox "共做对" & k & " 道题"
Sheet2.OptionButton1.Enabled = False
Sheet2.OptionButton2.Enabled = False
Sheet2.OptionButton3.Enabled = False
Sheet2.OptionButton4.Enabled = False
Sheet2.CommandButton3.Enabled = False
End Sub
---#写在Sheet2里#---
'写入答案
Private Sub OptionButton1_Click()
Sheet3. Range("G" & Sheet2. SpinButton1. Value +1) = "A"
End Sub
Private Sub OptionButton2_Click()
Sheet3. Range("G" & Sheet2. SpinButton1. Value +1) = "B"
End Sub
Private Sub OptionButton3_Click()
Sheet3. Range("G" & Sheet2. SpinButton1. Value +1) = "C"
End Sub
Private Sub OptionButton4_Click()
Sheet3. Range("G" & Sheet2. SpinButton1. Value +1) = "D"
End Sub
'通过SpinButton读取题目和数据
Private Sub SpinButton1_Change()
Call xieru(Sheet2. SpinButton1. Value)
End Sub
'读取第一题
Private Sub CommandButton1_Change()
Call xieru(1)
Sheet2. SpinButton1. Value = 1
End Sub
'读取最后一题
Private Sub CommandButton2_Change()
Call xieru(8)
Sheet2. SpinButton1. Value = 8
End Sub
'查询成绩
Private Sub CommandButton3_Click()
Call chengji
End Sub
---#写在ThisWorkbook里#---
'隐藏题目 & 正确答案
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet3. Visible = xlSheetVeryHidden
End Sub
Private Sub Workbook_Open()
Sheet3. Visible = xlSheetVeryHidden
End Sub
#设置SpinButton的属性 Max = 8 Min = 1
