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

excelperfect

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

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

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

'Callback for button1 onActionSub Macro1(control As IRibbonControl) MsgBox '单击了Button1.'End Sub 'Callback for button2 onActionSub Macro2(control As IRibbonControl) MsgBox '单击了Button2.'End Sub 'Callback for button3 onActionSub Macro3(control As IRibbonControl) MsgBox '单击了Button3.'End Sub 'Callback for button4 onActionSub Macro4(control As IRibbonControl) MsgBox '单击了Button4.'End Sub 'Callback for button5 onActionSub Macro5(control As IRibbonControl) MsgBox '单击了Button5.'End Sub 'Callback for button6 onActionSub Macro6(control As IRibbonControl) MsgBox '单击了Button6.'End Sub 'Callback for button7 onActionSub Macro7(control As IRibbonControl) MsgBox '单击了Button7.'End Sub 'Callback for toggleBtn1 onActionSub Macro8(control As IRibbonControl, pressed As Boolean) MsgBox '切换按钮的状态为: '& pressedEnd Sub 'Callback for toggleBtn2 onActionSub Macro9(control As IRibbonControl, pressed As Boolean) MsgBox '切换按钮的状态为: '& pressedEnd Sub 'Callback for Btn1 onActionSub Macro10(control As IRibbonControl) MsgBox control.Tag & ' 被单击.'End Sub 'Callback for menuButton2 onActionSub Macro11(control As IRibbonControl) MsgBox '单击了Button11.'End Sub 'Callback for menuButton3 onActionSub Macro12(control As IRibbonControl) MsgBox '单击了Button12.'End Sub 'Callback for comboBox1 onChangeSub Combo1_onChange(control As IRibbonControl, text As String) MsgBox '组合框中显示的文本为: '& textEnd Sub 'Callback for dropDown1 onActionSub SelectedItem(control As IRibbonControl, id As String, index As Integer) MsgBox '你选择了Template'& index + 1End Sub 'Callback for button14 onActionSub Macro14(control As IRibbonControl) MsgBox '单击了Button14.'End Sub 'Callback for button15 onActionSub Macro15(control As IRibbonControl) MsgBox '单击了Button15.'End Sub 'Callback for button16 onActionSub Macro16(control As IRibbonControl) MsgBox '单击了Button16.'End Sub 'Callback for button17a onActionSub Macro17A(control As IRibbonControl) MsgBox '单击了Button17A.'End Sub 'Callback for button17b onActionSub Macro17B(control As IRibbonControl) MsgBox '单击了Button17B.'End Sub 'Callback for button18 onActionSub Macro18(control As IRibbonControl) MsgBox '单击了Button18.'End Sub 'Callback for gallery1 onActionSub ModuleSelected(control As IRibbonControl, id As String, index As Integer) MsgBox '你选择了Module'& index + 1End Sub 'Callback for gallery2 onActionSub SelectedColor(control As IRibbonControl, id As String, index As Integer) MsgBox '你选择了' & idEnd Sub 'Callback for label1 getLabelSub getLabel1(control As IRibbonControl, ByRef returnedVal) If Time() < 0.5 Then returnedVal = 'Good morning,' & Application.UserName Else returnedVal = 'Good day, '& Application.UserName End IfEnd Sub 'Callback for label2 getLabelSub getLabel2(control As IRibbonControl, ByRef returnedVal) returnedVal = '今天是'& Format(Date, 'dddd')End Sub 'Callback for EditBox1 onChangeSub 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 onActionSub Checkbox1_Change(control As IRibbonControl, pressed As Boolean) MsgBox '复选框被选取: ' &pressedEnd Sub

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

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

下图演示了在自定义选项卡中各类控件的效果：

说明：本专题系列大部分内容学习整理自《Dissectand Learn Excel VBA in 24 Hours：Changingworkbook appearance》，仅供学习研究。注：如果你有兴趣，你可以到知识星球App的完美Excel社群下载这本书的完整中文版电子书。