POS机怎么进去菜单(VBA｜自定义菜单栏、工具栏、功能区)

Excel2007中，功能区替代了以前的分层菜单、工具栏和任务窗格系统。

自定义菜单栏、工具栏的效果：

主要是使用CommandBars对象模型：

VBA代码：

1 自定义菜单命令组

'自定义菜单命令组Public Sub creatediyMenu() For Each con In Application.CommandBars("Data").Controls con.Delete Next Dim diyMenu As Object Dim MenuItem As Variant Dim Menusub As Variant MenuItem = Array( _ "首行标题和冻结及边框", "创建工作表目录", _ "工作表按名称排序", _ "设置页眉页脚", _ "隔行插入空行", "删除空行", "删除选定列空单元格的行", _ "删除超级链接", _ "删除形状(图形、文本框等)", _ "设置最后一个字符为上标", _ "多条件排序(ABCD列)", _ "多条件筛选(AB列)", _ "Setting", _ "选区字符统计", _ "借书记录", _ "定位到D列最后一行", _ "显示或隐藏批注", "删除当前表中批注") Menusub = Array( _ "PERSONAL.XLSB!行操作.首行标题和冻结及边框", _ "PERSONAL.XLSB!表操作.创建工作表目录", _ "PERSONAL.XLSB!表操作.sortShtByName", _ "PERSONAL.XLSB!表操作.设置页眉页脚", _ "PERSONAL.XLSB!行操作.insertBlankRow", _ "PERSONAL.XLSB!行操作.DeleteBlankRow", _ "PERSONAL.XLSB!行操作.批量删除空行_先选定列", _ "PERSONAL.XLSB!数据编辑.删除超级链接", _ "PERSONAL.XLSB!数据编辑.删除形状", _ "PERSONAL.XLSB!数据编辑.设置单元格最后一个字符为上标", _ "PERSONAL.XLSB!排序与筛选和统计.MoreKeySort", _ "PERSONAL.XLSB!排序与筛选和统计.Filter_MoreCriteria", _ "PERSONAL.XLSB!自定义菜单.ExcelSetting", _ "PERSONAL.XLSB!排序与筛选和统计.textcount", _ "PERSONAL.XLSB!借书记录.借书记录", "PERSONAL.XLSB!自定义菜单.newRowPos", _ "PERSONAL.XLSB!排序与筛选和统计.显示或隐藏批注", _ "PERSONAL.XLSB!排序与筛选和统计.删除当前表中批注") For i = 0 To UBound(MenuItem) Set diyMenu = Application.CommandBars("Data").Controls.Add(Type:=msoControlButton) With diyMenu .Caption = MenuItem(i) .OnAction = Menusub(i) End With Next i Set diyMenu = NothingEnd Sub

2 插入自定义工具栏命令

Sub 插入自定义工具栏命令() Dim cmb As Office.CommandBar Dim bt As Office.CommandBarButton 'Call 删除菜单栏 Set cmb = Application.CommandBars("Formatting") Set bt = cmb.Controls.Add(Type:=Office.MsoControlType.msoControlButton) With bt .Caption = "借书记录" .FaceId = 2560 .Style = msoButtonIconAndCaption .OnAction = "PERSONAL.XLSB!借书记录.借书记录" End WithEnd Sub'下面的Right 过程是自定义按钮的回调函数?Sub Right() Selection.HorizontalAlignment = Excel.Constants.xlRightEnd Sub

3 创建新的菜单栏

Const strBarName As String = "我的菜单栏"Public Sub 创建新的菜单栏() Dim cbr As CommandBar, ctl As CommandBarControl Call 删除菜单栏 'For Each con In Application.CommandBars(strBarName).Controls 'con.Delete 'Next Set cbr = Application.CommandBars.Add(strBarName, MenuBar:=True) cbr.Visible = True Set ctl = Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=) ctl.Copy Application.CommandBars(strBarName) ' 文件下拉菜单 Set ctl = Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=) 'Set ctl = Application.CommandBars("Chart Menu Bar").FindControl(ID:=) '编辑、视图、插入、格式、工具 ctl.Copy Application.CommandBars(strBarName) With cbr.Controls.Add(msoControlPopup) .Caption = "帮助" With .Controls.Add(msoControlButton) .Caption = "重置" .OnAction = "删除菜单栏" End With With .Controls.Add(msoControlButton) .Caption = "关于" .OnAction = "关于" End With End WithEnd SubSub 删除菜单栏() On Error Resume Next Application.CommandBars(strBarName).Delete On Error GoTo 0End SubSub 关于() Dim strMsg As String strMsg = "这是一个新的菜单栏" & vbCrLf strMsg = strMsg & "由" & Application.UserName & "创建" MsgBox strMsg, , "关于"End Sub

自定义功能区参照：

Excel2007｜RibbonX控件 & 自定义功能区

在Excel2013中，创建功能区选项卡的操作非常简单，用户可通过打开“Excel选项"对话框直接进行功能区选项卡和组的创建。

－End－