自定义功能区

customui 用户自定义功能区

层次关系-选项卡-组-控件按钮

一 准备工作 ：

1、ribbon xml editor 安装-编写xml并写入工作簿的程序

二 步骤：

1、xml代码撰写并验证（注意大小写，书写规则，在关闭工作簿但打开excel程序的状态下查看验证效果）

2、查看回调（右键-查看回调-vba，复制到启用宏的工作簿的工程中写好模块代码）

3、保存1步骤文件至启用宏的工作簿（注意关闭该工作簿）

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

<ribbon startFromScratch="false">

<tabs>

<tab id="Tab1" label="我的插件">

<group id="Group1" label="常用功能">

<button id="Button1" imageMso="ColumnsDialog" label="启动记事本" onAction="notepad" size="large"/>

<button id="Button2" imageMso="DataTypeMediumTime" label="当前时间" onAction="currenttime" size="large"/>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

注意事项

一 使用内置图标 ----使用imageMsoViewer安装后-获取图标的名称（imageMso，size）

二 自动激活指定选项卡：

1、XML 程序修改：添加onLoad="OL"，即<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="OL">

2、模块添加事件：

Public R As Office.IRibbonUI

Public Sub OL(ribbon As Office.IRibbonUI)

Set R = ribbon

R.ActivateTab "Tab1"

End Sub

3、保存xml

三 制作隐藏的按钮

1、xml

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

<ribbon startFromScratch="false">

<tabs>

<tab id="Tab1" label="我的插件">

<group id="Group1" label="常用功能">

<button id="Button1" imageMso="ColumnsDialog" label="启动记事本" onAction="notepad" size="large"/>

<button id="Button2" imageMso="DataTypeMediumTime" label="当前时间" onAction="currenttime" size="large"/>

<dialogBoxLauncher>

<button id="Button3" onAction="showdialog"/>

</dialogBoxLauncher>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

2、模块添加事件

Public Sub showdialog(control As Office.IRibbonControl)

Application.Dialogs.Item(Excel.XlBuiltInDialog.xlDialogEditColor).Show ‘打开一个内置对话框

End Sub

3、保存xml

案例

1、button控件

复选框按钮+组内分组+自定义图标

<box id="buttonBox1" boxStyle="vertical"></box>，<box> 元素：这是一个容器控件，用于将其他控件（如按钮、复选框等）分组在一起

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

<ribbon>

<tabs>

<tab idMso="TabHome">

<group id="CustomGroup" insertAfterMso="GroupEditing" label="导航面板">

<checkBox id="chkNavigation" label="导航页开关" onAction="导航"/>

<box id="buttonBox1" boxStyle="vertical">

<button id="btnFinance" label="财务" image="finance.png" size="normal" onAction="财务"/>

<button id="btnOperation" label="经营" image="operate.png" size="normal" onAction="经营"/>

</box>

<box id="buttonBox2" boxStyle="vertical">

<button id="btnModel" label="模型" image="model.png" size="normal" onAction="模型"/>

<button id="btnOther" label="其他" image="else.png" size="normal" onAction="其他"/>

</box>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

<separator id="sep1"/>在组中放置一条分隔条，提供控件之间的分隔

screentip鼠标悬停时，首先会看到“对话框启动器”的简单提示；稍作停留，会看到一个更详细的提示，说明“单击此按钮通常会显示一个对话框。”

用户按下 Alt 键，屏幕上会显示各个控件的快捷键，这个按钮上可能会显示 LD（keytip）。通过按下 L 然后 D，用户就可以在不使用鼠标的情况下打开这个对话框（但是没效果还不知道怎么回事）

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

<ribbon startFromScratch="false">

<tabs>

<tab idMso="TabHome">

<group id="CustomGroup" insertAfterMso="GroupEditing" label="导航面板">

<checkBox id="chkNavigation" label="导航页开关" onAction="导航"/>

<separator id="sep1"/>

<button id="btnFinance" label="财务" image="finance.png" size="normal" onAction="财务"/>

<button id="btnOperation" label="经营" image="operate.png" size="normal" onAction="经营"/>

<separator id="sep2"/>

<button id="btnModel" label="模型" image="model.png" size="normal" onAction="模型"/>

<button id="btnOther" label="其他" image="else.png" size="normal" onAction="其他"/>

</group>

<group id="DialogBoxLaunch" label="使用对话框启动器的组">

<dialogBoxLauncher>

<button id="LaunchDialog" screentip="对话框启动器" supertip="单击此按钮通常会显示一个对话框." keytip="LD" onAction="showdialog"/>

</dialogBoxLauncher>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

2、checkBox控件

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

<ribbon startFromScratch="false">

<tabs>

<tab idMso="TabFormulas">

<group id="group1" label="其他设置" insertBeforeMso="GroupNamedCells">

<checkBox id="rxchkR1C1" label="R1C1转换" onAction="rxchkR1C1_click"/>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

Public Sub rxchkR1C1_click(control As Office.IRibbonControl, pressed As Boolean)

Select Case pressed

Case True

Application.ReferenceStyle = xlR1C1

Case False

Application.ReferenceStyle = xlA1

End Select

End Sub

3、editBox控件

说明：sizeString="123456"是编辑框视觉宽度，keytip是设置的快捷键，属性间的顺序可以打乱

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

<ribbon startFromScratch="false">

<tabs>

<tab idMso="TabFormulas">

<group id="gongshi" label="其他设置" insertBeforeMso="GroupNamedCells">

<checkBox id="rxchkR1C1" label="R1C1转换" onAction="rxchkR1C1_click"/>

<editBox id="editBox1" label="修改表名" sizeString="123456" imageMso="SignatureLineInsert" keytip="qq" onChange="rxtxtRename_Click"/>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

4、toggleButton

说明imageMso可以使用字母

<toggleButton id="toggleButton1" label="切换按钮" imageMso="R" onAction="切换按钮"/>

<toggleButton id="toggleButton1" label="切换按钮" imageMso="R" getPressed="获取按钮状态" onAction="切换按钮"/>

Public Sub RibbonOnLoad(ribbonUI As IRibbonUI)

Set ribbon = ribbonUI

End Sub

Public Sub 获取按钮状态(control As Office.IRibbonControl, ByRef returnedVal)

On Error Resume Next

If control.ID = "toggleButton1" Then

' 根据 Sheet1 的可见性设置按钮状态

returnedVal = (Sheet1.Visible <> xlSheetVisible)

End If

End Sub

Public Sub 切换按钮(control As Office.IRibbonControl, pressed As Boolean)

On Error Resume Next

Select Case pressed

Case True

Sheet1.Visible = xlSheetHidden

Case False

Sheet1.Visible = xlSheetVisible

End Select

' 刷新按钮状态（可选，确保状态同步）

If Not ribbon Is Nothing Then

ribbon.InvalidateControl "toggleButton1"

End If

End Sub

5、comboBox控件

说明：可以选择，也可以手动输入列表中的某个条目

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

<ribbon startFromScratch="false">

<tabs>

<tab id="Tab1" label="Ribbon XML Editor">

<group id="Group1" label="Group1">

<comboBox id="comboBox1" label="部门" onChange="部门">

<item id="item1" label="技术部"/>

<item id="item2" label="市场部"/>

<item id="item3" label="财务部"/>

<item id="item4" label="产品部"/>

<item id="item5" label="行政部"/>

<item id="item6" label="人力资源部"/>

</comboBox>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

Public Sub 部门(control As Office.IRibbonControl, text As String)

Dim lastcell As Range

Dim rng As Range

Sheet1.Range("a2：h200").Clear

Set lastcell = Sheets("data").Cells(Rows.Count, "e").End(xlUp)

For Each rng In Sheets("data").Range("E2", lastcell)

If rng.Value Like text Then

n = n + 1

Sheet1.Range("a" & n + 1).EntireRow.Range("a1:h1") = rng.EntireRow.Range("a1:h1").Value

End If

Next

End Sub

动态更新条目：

xml：

<comboBox id="comboBox1" label="部门" getItemCount="comboBoxcount" getItemID="comboBoxid" getItemLabel="comboBoxlabel" onChange="部门"/>

VBA回调：

Dim myribbon As IRibbonUI

Public Sub onLoad(ribbon As Office.IRibbonUI)

Set myribbon = ribbon

End Sub

Public Sub comboBoxcount(control As Office.IRibbonControl, ByRef count)

count = Sheet3.Range("a100").End(xlUp).Row

End Sub

Public Sub comboBoxid(control As Office.IRibbonControl, index As Integer, ByRef id)

id = "comboBoxid" & index + 1

End Sub

Public Sub comboBoxlabel(control As Office.IRibbonControl, index As Integer, ByRef label)

label = Sheet3.Cells(index + 1, 1).Value

End Sub

Public Sub 部门(control As Office.IRibbonControl, text As String)

If Not myribbon Is Nothing Then

myribbon.InvalidateControl control.id

End If

Selection = text

End Sub

6、dropDown

静态获取：

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

<ribbon startFromScratch="false">

<tabs>

<tab id="Tab1" label="Ribbon XML Editor">

<group id="Group1" label="Group1">

<dropDown id="dropDown1" label="打开工作表" onAction="激活工作表">

<item id="item1" label="Sheet1"/>

<item id="item2" label="Sheet2"/>

<item id="item3" label="Sheet3"/>

</dropDown>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

动态更新条目：

xml：<dropDown id="dropDown1" label="部门" getItemCount="dropDowncount" getItemID="dropDownid" getItemLabel="dropDownlabel" onAction="部门"/>

vba回调：

Dim myribbon As IRibbonUI

Public Sub onLoad(ribbon As Office.IRibbonUI)

Set myribbon = ribbon

End Sub

Public Sub dropDowncount(control As Office.IRibbonControl, ByRef count)

count = Sheet3.Range("a100").End(xlUp).Row

End Sub

Public Sub dropDownid(control As Office.IRibbonControl, index As Integer, ByRef id)

id = "comboBoxid" & index + 1

End Sub

Public Sub dropDownlabel(control As Office.IRibbonControl, index As Integer, ByRef label)

label = Sheet3.Cells(index + 1, 1).Value

End Sub

Public Sub 部门(control As Office.IRibbonControl, selectedId As String, selectedIndex As Integer)

If Not myribbon Is Nothing Then

myribbon.InvalidateControl control.id

End If

Selection = Sheet3.Cells(selectedIndex + 1, 1)

End Sub

7、menu

菜单的用途与下拉控件的用途非常相似。然而，下拉控件的一个主要局限是仅能包含“项目”，而菜单控件可以包含各种各样的其它控件，包括按钮、复选框、库、甚至是另一个菜单。

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

<ribbon startFromScratch="false">

<tabs>

<tab id="Tab1" label="Ribbon XML Editor">

<group id="Group1" label="Group1">

<menu id="munResources" imageMso="HyperlinkInsert" size="large" label="常用网站">

<menuSeparator id="rxmSepRibbon" title="公开查询"/>

<button id="button1" label="全国法院失信被执行人查询" onAction="网站" tag="http://zxgk.court.gov.cn/shixin/"/>

<button id="button2" label="全国法院信息综合查询" onAction="网站" tag="http://zxgk.court.gov.cn/zhzxgk/"/>

<button id="button3" label="企业信用公示系统" onAction="网站" tag="https://www.gsxt.gov.cn/index.html"/>

<button id="button4" label="税收违法失信信息" onAction="网站" tag="http://www.chinatax.gov.cn/chinatax/c101249/n2020011502/index.html"/>

<button id="button5" label="证券期货市场失信" onAction="网站" tag="https://neris.csrc.gov.cn/shixinchaxun/"/>

<button id="button6" label="事务所备案查询" onAction="网站" tag="http://www.csrc.gov.cn/csrc/c100102/common_list.shtml"/>

<button id="button7" label="信用中国" onAction="网站" tag="https://www.creditchina.gov.cn/"/>

<menuSeparator id="rxmSepAuthors" title="中证鹏元"/>

<button id="button8" label="中证鹏元公司官网" onAction="网站" tag="https://www.cspengyuan.com/"/>

<button id="button9" label="中证鹏元业务系统" onAction="网站" tag="http://172.16.1.74/PyCRIMS/Home/Index"/>

</menu>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

在餐单中插入菜单

<menu id="munResources" imageMso="HyperlinkInsert" size="large" label="常用网站">

<menuSeparator id="rxmSepRibbon" title="公开查询"/>

<button id="button1" label="全国法院失信被执行人查询" onAction="网站" tag="http://zxgk.court.gov.cn/shixin/"/>

<button id="button2" label="全国法院信息综合查询" onAction="网站" tag="http://zxgk.court.gov.cn/zhzxgk/"/>

<!-- 新增的菜单 -->

<menu id="munExtraWebsites" label="额外网站">

<button id="button3" label="企业信用公示系统" onAction="网站" tag="https://www.gsxt.gov.cn/index.html"/>

<button id="button4" label="税收违法失信信息" onAction="网站" tag="http://www.chinatax.gov.cn/chinatax/c101249/n2020011502/index.html"/>

</menu>

<button id="button5" label="证券期货市场失信" onAction="网站" tag="https://neris.csrc.gov.cn/shixinchaxun/"/>

<button id="button6" label="事务所备案查询" onAction="网站" tag="http://www.csrc.gov.cn/csrc/c100102/common_list.shtml"/>

<button id="button7" label="信用中国" onAction="网站" tag="https://www.creditchina.gov.cn/"/>

<menuSeparator id="rxmSepAuthors" title="中证鹏元"/>

<button id="button8" label="中证鹏元公司官网" onAction="网站" tag="https://www.cspengyuan.com/"/>

<button id="button9" label="中证鹏元业务系统" onAction="网站" tag="http://172.16.1.74/PyCRIMS/Home/Index"/>

</menu>

插入其他控件：

<menu id=“rxmnuViewsMenu“

itemSize=“normal“

imageMso=“FilePrintPreview“

label=“Views“

size=“large“>

<toggleButton idMso=“ViewPageBreakPreviewView“/>

<toggleButton idMso=“ViewFullScreenView“/>

<checkBox idMso=“ViewHeadings“/>

<checkBox idMso=“ViewFormulaBar“/>

<checkBox idMso=“GridlinesExcel“/>

</menu>