Excel VBA创建自己的工具栏和菜单 收藏
Option Explicit 'msoBarTop工具栏的Position 'Type为msoControlPopup(As CommandBarPopup)的菜单下可以带子菜单,但是msoControlPopup不支持图标 'Type为msoControlButton(As CommandBarButton)的菜单是msoControlPopup的下级菜单,不带子菜单,支持图标 '以下代码可以实现将自己的菜单添加到Excel菜单栏上及创建自己的工具栏和菜单的功能,更改代码可以实现创建多级菜单,下面的代码只创建了二级菜单 '更改以下代码可以创建你所需要的菜单 'ShortcutText属性表示菜单的快捷键 '添加菜单到指定的现有工具栏上
Function AddMenuToCommandBar(ByVal Index As Integer, ByVal TopMenuName As String) On Error Resume Next Application.CommandBars(1).Controls(TopMenuName).Delete '如果存在就删除以前的菜单 On Error GoTo 0 Dim TopMenuItem As CommandBarPopup '顶层菜单
Dim FirstMenuItem As CommandBarPopup '一级子菜单 Dim SecondMenuItem As CommandBarButton '二级子菜单 '顶层菜单CommandBarPopup,不支持图标(只有最后一级菜单才支持图标)
Set TopMenuItem = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup) With TopMenuItem .Caption = TopMenuName '顶层菜单名 .TooltipText = "TopMenuItem TooltipText" '菜单提示文字 End With '一级子菜单CommandBarPopup,不支持图标(只有最后一级菜单才支持图标)
Set FirstMenuItem = TopMenuItem.Controls.Add(Type:=msoControlPopup) With FirstMenuItem .Caption = "FirstMenuItem(&F)" '一级菜单名 .TooltipText = "FirstMenuItem TooltipText" '菜单提示文字 End With '二级菜单CommandBarButton,支持图标(只有最后一级菜单才支持图标)
Set SecondMenuItem = FirstMenuItem.Controls.Add(Type:=msoControlButton) With SecondMenuItem .Caption = "SecondMenuItem(&S)" '二级菜单名 .TooltipText = "SecondMenuItem TooltipText" '菜单提示文字 .Style = msoButtonIconAndCaption '菜单样式(图标加文字) .FaceId = 263 '图标代号 .ShortcutText = "Ctrl+Shift+S" .OnAction = "Macro" '要执行的子程序 .BeginGroup = True '添加分割线 End With End Function '创建工具栏,并且添加自己的菜单到新建的工具栏
Function CreateCommandBarAndMenu(ByVal CommandBarName As String, ByVal TopMenuName As String) On Error Resume Next Application.CommandBars(CommandBarName).Delete '如果存在就删除以前的菜单 On Error GoTo 0 Dim MyCommandBar As CommandBar '工具栏
Dim TopMenuItem As CommandBarPopup '顶层菜单 Dim FirstMenuItem As CommandBarPopup '一级子菜单 Dim SecondMenuItem As CommandBarButton '二级子菜单 '工具栏
Set MyCommandBar = Application.CommandBars.Add() '创建工具栏(空白) With MyCommandBar .Visible = True .Name = CommandBarName '工具栏的名字 .Position = msoBarTop 'msoBarMenuBar '工具栏的Position End With '顶层菜单CommandBarPopup,不支持图标(只有最后一级菜单才支持图标) Set TopMenuItem = MyCommandBar.Controls.Add(Type:=msoControlPopup) With TopMenuItem .Caption = TopMenuName '顶层菜单名 .TooltipText = "TopMenuItem TooltipText" '菜单提示文字 End With '一级子菜单CommandBarPopup,不支持图标(只有最后一级菜单才支持图标)
Set FirstMenuItem = TopMenuItem.Controls.Add(Type:=msoControlPopup) With FirstMenuItem .Caption = "FirstMenuItem(&F)" '一级菜单名 .TooltipText = "FirstMenuItem TooltipText" '菜单提示文字 End With '二级菜单CommandBarButton,支持图标(只有最后一级菜单才支持图标) Set SecondMenuItem = FirstMenuItem.Controls.Add(Type:=msoControlButton) With SecondMenuItem .Caption = "SecondMenuItem(&S)" '二级菜单名 .TooltipText = "SecondMenuItem TooltipText" '菜单提示文字 .Style = msoButtonIconAndCaption '菜单样式(图标加文字) .FaceId = 263 '图标代号 .ShortcutText = "Ctrl+Shift+S" .OnAction = "Macro" '要执行的子程序 .BeginGroup = True '添加分割线 End With End Function 发表于 @
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/c_cwh/archive/2009/10/05/4633699.aspx |
|