自定义功能区示例：创建用于工作表导航的动态组合框

标签：VBA，自定义功能区

在《自定义功能区示例：创建用于工作表导航的下拉列表》中，我们在Excel功能区中添加一个自定义的选项卡，然后再该选项卡中添加带有下拉列表的一个自定义组，用于从下拉列表中选择工作表，从而快速导航到该工作表，这对于工作簿中有大量工作表且要快速找到相应的工作表的用户来说，非常有用。

我们对此示例进行修改，使用组合框来实现，如下图1所示，并且当用户在该工作簿中新建或删除工作表时，组合框中的列表项会自动更新。

图1

假设该工作簿名称为CustomDynamicCombobox.xlsm，使用Custom UI Editor for Microsoft Office打开该工作簿，在其中输入代码：

代码语言：javascript复制

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="RibbonOnLoad">
 <ribbon >
  <tabs >
    <tab
       id="Tab1"
       insertBeforeMso="TabHome"
       label="My Menu">
       <group
         id="grpDropDowns"
         label="Worksheet Navigation">
         <comboBox
           id="Combobox1"
           label="goto: "
           getItemCount="Combobox1_getItemCount"
           getItemID="Combobox1_getItemID"
           getItemLabel="Combobox1_getItemLabel"
           onChange="Combobox1_onChange"/>
       </group >
     </tab >
   </tabs >
 </ribbon >
</customUI >

验证无误后，保存并关闭Custom UI Editor for Microsoft Office。

在Excel中打开CustomDynamicCombobox.xlsm，打开VBE，插入一个标准模块，输入下面的代码：

代码语言：javascript复制

Dim Rib As IRibbonUI
Dim mwkbNavigation As Workbook

Sub RibbonOnLoad(ribbon As IRibbonUI)
 Set Rib = ribbon
End Sub
Public Sub Combobox1_getItemCount(control As IRibbonControl, ByRef returnedVal)
 Dim lCount As Long
 Dim wksSheet As Worksheet
 Set mwkbNavigation = ThisWorkbook
 For Each wksSheet In mwkbNavigation.Worksheets
   If wksSheet.Visible = xlSheetVisible Then
     lCount = lCount   1
   End If
 Next wksSheet
 returnedVal = lCount
End Sub
Public Sub Combobox1_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
 If mwkbNavigation.Worksheets(index   1).Visible = xlSheetVisible Then
   returnedVal = mwkbNavigation.Worksheets(index   1).Name
 End If
End Sub
Public Sub Combobox1_onChange(control As IRibbonControl, Text As String)
 Worksheets(Text).Activate
 RefreshAddInsRibbon
End Sub
Public Sub RefreshAddInsRibbon()
 If Rib Is Nothing Then Exit Sub
 Rib.InvalidateControl ("Combobox1")
 DoEvents
End Sub
Public Sub Combobox1_getItemID(control As IRibbonControl, index As Integer, ByRef id)
'
' Code for getItemID callback. Ribbon control comboBox
'
End Sub

然后，打开ThisWorkbook代码模块，输入下面的代码：

代码语言：javascript复制

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 RefreshAddInsRibbon
End Sub
Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
 RefreshAddInsRibbon
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 RefreshAddInsRibbon
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
 RefreshAddInsRibbon
End Sub

保存并关闭该工作簿，然后重新打开该工作簿，即可以看到更新后的自定义功能区界面。

private public ribbon sh 工作

0 人点赞