VBA专题10-19：使用VBA操控Excel界面之在功能区中添加不同类型的自定义控件

fanjy

发布于 2021-03-12 17:02:03

1.8K0

发布于 2021-03-12 17:02:03

文章被收录于专栏：完美Excel完美Excel

excelperfect

本文是前面一系列文章的综合，前面每篇文章讲解如何在功能区中添加一类自定义控件，本文讲解如何将在功能区中同时添加这些控件。

添加该控件的步骤与前面文章介绍的相同，新建一个启用宏的工作簿并保存，关闭该工作簿，然后在CustomUI Editor中打开该工作簿，输入下面的XML代码：

在Excel中打开该工作簿，然后打开VBE，插入一个标准的VBA模块，输入下面的代码：

'Callback for button1 onAction
Sub Macro1(control As IRibbonControl)
    MsgBox "单击了Button1."
End Sub
 
'Callback for button2 onAction
Sub Macro2(control As IRibbonControl)
    MsgBox "单击了Button2."
End Sub
 
'Callback for button3 onAction
Sub Macro3(control As IRibbonControl)
    MsgBox "单击了Button3."
End Sub
 
'Callback for button4 onAction
Sub Macro4(control As IRibbonControl)
    MsgBox "单击了Button4."
End Sub
 
'Callback for button5 onAction
Sub Macro5(control As IRibbonControl)
    MsgBox "单击了Button5."
End Sub
 
'Callback for button6 onAction
Sub Macro6(control As IRibbonControl)
    MsgBox "单击了Button6."
End Sub
 
'Callback for button7 onAction
Sub Macro7(control As IRibbonControl)
    MsgBox "单击了Button7."
End Sub
 
'Callback for toggleBtn1 onAction
Sub Macro8(control As IRibbonControl, pressed As Boolean)
    MsgBox "切换按钮的状态为: "& pressed
End Sub
 
'Callback for toggleBtn2 onAction
Sub Macro9(control As IRibbonControl, pressed As Boolean)
    MsgBox "切换按钮的状态为: "& pressed
End Sub
 
'Callback for Btn1 onAction
Sub Macro10(control As IRibbonControl)
    MsgBox control.Tag & " 被单击."
End Sub
 
'Callback for menuButton2 onAction
Sub Macro11(control As IRibbonControl)
    MsgBox "单击了Button11."
End Sub
 
'Callback for menuButton3 onAction
Sub Macro12(control As IRibbonControl)
    MsgBox "单击了Button12."
End Sub
 
'Callback for comboBox1 onChange
Sub Combo1_onChange(control As IRibbonControl, text As String)
    MsgBox "组合框中显示的文本为: "& text
End Sub
 
'Callback for dropDown1 onAction
Sub SelectedItem(control As IRibbonControl, id As String, index As Integer)
    MsgBox "你选择了Template"& index + 1
End Sub
 
'Callback for button14 onAction
Sub Macro14(control As IRibbonControl)
    MsgBox "单击了Button14."
End Sub
 
'Callback for button15 onAction
Sub Macro15(control As IRibbonControl)
    MsgBox "单击了Button15."
End Sub
 
'Callback for button16 onAction
Sub Macro16(control As IRibbonControl)
    MsgBox "单击了Button16."
End Sub
 
'Callback for button17a onAction
Sub Macro17A(control As IRibbonControl)
    MsgBox "单击了Button17A."
End Sub
 
'Callback for button17b onAction
Sub Macro17B(control As IRibbonControl)
    MsgBox "单击了Button17B."
End Sub
 
'Callback for button18 onAction
Sub Macro18(control As IRibbonControl)
    MsgBox "单击了Button18."
End Sub
 
'Callback for gallery1 onAction
Sub ModuleSelected(control As IRibbonControl, id As String, index As Integer)
    MsgBox "你选择了Module"& index + 1
End Sub
 
'Callback for gallery2 onAction
Sub SelectedColor(control As IRibbonControl, id As String, index As Integer)
    MsgBox "你选择了" & id
End Sub
 
'Callback for label1 getLabel
Sub getLabel1(control As IRibbonControl, ByRef returnedVal)
    If Time() < 0.5 Then
        returnedVal = "Good morning," & Application.UserName
    Else
        returnedVal = "Good day, "& Application.UserName
    End If
End Sub
 
'Callback for label2 getLabel
Sub getLabel2(control As IRibbonControl, ByRef returnedVal)
    returnedVal = "今天是"& Format(Date, "dddd")
End Sub
 
'Callback for EditBox1 onChange
Sub EditBox1_onChange(control As IRibbonControl, text As String)
    On Error Resume Next
    Range("A1").Interior.ColorIndex =text
    If Err.Number <> 0 Then _
        MsgBox "请输入0至56之间的整数."
End Sub
 
'Callback for checkBox1 onAction
Sub Checkbox1_Change(control As IRibbonControl, pressed As Boolean)
    MsgBox "复选框被选取: " &pressed
End Sub

其中，在首次打开工作簿或者使标签控件无效时执行getLabel1和getLabel2回调过程。

在该工作簿的自定义选项卡中的不同类型的控件如下图所示：