Excel个性化菜单攻略大全！

dj_sap · 发表于 2013-9-4 22:10

顶，好贴，期待附件，感谢楼主分享！

aman1516 · 发表于 2013-9-5 22:39

非常好的学习资料，谢谢分享!

banjinjiu · 发表于 2013-9-6 10:46

本帖最后由 banjinjiu 于 2013-9-6 10:48 编辑

我编辑了一部分，不全，并且删除一些，和楼主的意思不完全相同，供大家参考。

Sub Id_Control() '返回活动菜单栏的 ID
Dim myId As Object
Set myId = CommandBars("Worksheet Menu Bar").Controls("Tools")
MsgBox myId.Caption & Chr(13) & myId.ID
End Sub
Sub MenuBars_GetName() '返回活动菜单栏的名称
MsgBox CommandBars.ActiveMenuBar.Name
End Sub
Public OriginalMenuBar As Object
Sub MenuBars_Capture() '重置菜单栏
Set OriginalMenuBar = CommandBars.ActiveMenuBar
End Sub
Sub MenuBar_Display() '从可用菜单栏列表中删除内置“图表”菜单栏
CommandBars("Chart").Enabled = False
End Sub
Sub MenuBar_Display2() '从可用菜单栏中添加内置“图表”菜单栏
CommandBars("Chart").Enabled = True
End Sub
Sub MenuBar_Restore() '还原内置“图表”菜单栏
CommandBars("Chart").Reset
End Sub
Sub Menu_Restore() '还原“工作表”菜单栏上的内置“图表”菜单栏
Dim myMnu As Object
Set myMnu = CommandBars("Chart")
myMnu.Reset
End Sub
Sub MenuBar_Create() '创建自定义命令栏
Application.CommandBars.Add Name:="张妍"
End Sub
Sub MenuBar_Delete() '删除自定义命令栏
CommandBars("张妍").Delete
End Sub
Sub Menu_Create() '栏添加自定义菜单控件
Dim myMnu As Object
Set myMnu = CommandBars("Worksheet menu bar").Controls.Add(Type:=msoControlPopup, before:=3)
With myMnu
.Caption = "New & Menu"
End With
End Sub
Sub Menu_Disable() '禁用“New Menu”菜单
CommandBars("Worksheet menu bar").Controls("New & Menu").Enabled = False
End Sub
Sub Menu_Disable2() '启用命令栏上的菜单控件
CommandBars("Worksheet menu bar").Controls("New & Menu").Enabled = True
End Sub
Sub Menu_Delete() '删除命令栏上的菜单控件
CommandBars("Worksheet menu bar").Controls("张妍").Delete
End Sub
Sub menuItem_AddSeparator() '在“插入”菜单上的工作表命令之前添加分隔
CommandBars("Worksheet menu bar").Controls("Insert").Controls("Worksheet").BeginGroup = True
End Sub
Sub menuItem_AddSeparator2() '删除在“插入”菜单上的工作表命令之前添加分隔
CommandBars("Worksheet menu bar").Controls("Insert").Controls("Worksheet").BeginGroup = False
End Sub
Sub menuItem_Create() '在“工作表”菜单栏的“工具”菜单上创建名为 Custom1 的新命令
With CommandBars("Worksheet menu bar").Controls("Tools")
.Controls.Add(Type:=msoControlButton, before:=1).Caption = "Custom1"
.Controls("Custom1").OnAction = "Code_Custom1"
End With
End Sub
Sub menuItem_checkMark() '在 Custom1 命令未选中的情况下在其旁边放置一个选中标记；如果 Custom1 命令已选中，则将删除该选中标记
Dim myPopup As Object
Set myPopup = CommandBars("Worksheet menu bar").Controls("Tools")
If myPopup.Controls("Custom1").State = msoButtonDown Then
' Remove check mark next to menu item.
myPopup.Controls("Custom1").State = msoButtonUp
MsgBox "Custom1 is now unchecked"
Else
' Add check mark next to menu item.
myPopup.Controls("Custom1").State = msoButtonDown
MsgBox "Custom1 is now checked"
End If
End Sub
Sub MenuItem_Disable() '禁用在“工具”菜单上创建的 Custom1 命令
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("Tools")
myCmd.Controls("Custom1").Enabled = False
End Sub
Sub MenuItem_Enable() '启用禁用的 Custom1 命令
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("Tools")
myCmd.Controls("Custom1").Enabled = True
End Sub
Sub menuItem_Delete() '删除“文件”菜单上的“保存”命令
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("File")
myCmd.Controls("Save").Delete
End Sub
Sub menuItem_Restore() '先删除“保存”命令，然后又将其还原
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("文件")
' Id 3 refers to the Save menu item control.
myCmd.Controls.Add Type:=msoControlButton, ID:=3, before:=5
End Sub
Sub SubMenu_Create() '向“工作表”菜单栏上的“工具”菜单添加名为“NewSub”的新子菜单
Dim newSub As Object
Set newSub = CommandBars("Worksheet menu bar").Controls("Tools")
With newSub
.Controls.Add(Type:=msoControlPopup, before:=1).Caption = "NewSub"
End With
End Sub
Sub SubMenu_AddItem() '向“NewSub”子菜单添加名为“SubItem1”的新命令，单击“SubItem1”时，它将运行 Code_SubItem1 宏
Dim newSubItem As Object
Set newSubItem = CommandBars("Worksheet menu bar") _
.Controls("Tools").Controls("NewSub")
With newSubItem
.Controls.Add(Type:=msoControlButton, before:=1).Caption = "SubItem1"
.Controls("SubItem1").OnAction = "Code_SubItem1"
End With
End Sub
Sub SubMenu_DisableItem() '禁用创建的 SubItem 命令
CommandBars("Worksheet menu bar").Controls("Tools") _
.Controls("NewSub").Controls("SubItem1").Enabled = False
End Sub
Sub SubMenu_DisableItem2() '启用同一 SubItem 命令
CommandBars("Worksheet menu bar").Controls("Tools") _
.Controls("NewSub").Controls("SubItem1").Enabled = True
End Sub
Sub SubMenu_DeleteItem() '删除'在“NewSub”子菜单上创建的 SubItem1 命令
CommandBars("Worksheet menu bar").Controls("Tools") _
.Controls("NewSub").Controls("SubItem1").Delete
End Sub
Sub SubMenu_DisableSub() '禁用在“工具”菜单上创建的“NewSub”子菜单
CommandBars("Worksheet menu bar").Controls("Tools").Controls("NewSub").Enabled = False
End Sub
Sub SubMenu_DisableSub3() '启用在“工具”菜单上创建的“NewSub”子菜单
CommandBars("Worksheet menu bar").Controls("Tools").Controls("NewSub").Enabled = True
End Sub
Sub SubMenu_DeleteSub() '删除在“工具”菜单上创建的“NewSub”子菜单
CommandBars("Worksheet menu bar").Controls("Tools").Controls("NewSub").Delete
End Sub
Sub Shortcut_Create() '创建右键快捷菜单栏
Dim myShtCtBar As Object
Set myShtCtBar = CommandBars.Add(Name:="myShortcutBar", Position:=msoBarPopup)
'This displays the shortcut menu bar.
'200, 200 refers to the screen position in pixels as x and y coordinates.
myShtCtBar.ShowPopup 200, 200
End Sub
Sub Shortcut_AddItem() '在“myShortcutBar”快捷菜单栏上创建名为 Item1 的新菜单命令。当您单击“Item1”时，它将运行 Code_Item1 宏
Dim myBar As Object
Set myBar = CommandBars("myShortcutBar")
With myBar
.Controls.Add(Type:=msoControlButton, before:=1).Caption = "Item1"
.Controls("Item1").OnAction = "Code_Item1"
End With
myBar.ShowPopup 200, 200
End Sub
Sub Shortcut_DisableItem() '禁用创建的 Item1 命令
Set myBar = CommandBars("myShortcutBar")
myBar.Controls("Item1").Enabled = False
myBar.ShowPopup 200, 200
End Sub
Sub Shortcut_DisableItem2() '启用创建的 Item1 命令
Set myBar = CommandBars("myShortcutBar")
myBar.Controls("Item1").Enabled = True
myBar.ShowPopup 200, 200
End Sub
Sub Shortcut_DeleteItem() '删除“myShortcutBar”快捷菜单栏上的名为 Item1 的菜单命令
Set myBar = CommandBars("myShortcutBar")
myBar.Controls("Item1").Delete
myBar.ShowPopup 200, 200
End Sub
Sub Shortcut_DeleteShortCutBar() '删除创建的“myShortCutBar”快捷菜单栏
CommandBars("MyShortCutBar").Delete
End Sub
Sub Shortcut_RestoreItem() '还原工作表“单元格”快捷菜单栏上的默认命令
CommandBars("Cell").Reset
End Sub
Sub ShortcutSub_Create() '在工作表“单元格”快捷菜单上添加名为“NewSub”的新子菜单
CommandBars("Cell").Controls.Add(Type:=msoControlPopup, before:=1) _
.Caption = "NewSub"
' This displays the shortcut menu bar.
' 200, 200 refers to the screen position in pixels as x and y coordinates.
CommandBars("Cell").ShowPopup 200, 200
End Sub
Sub ShortcutSub_AddItem() '向创建的子菜单“NewSub”中添加 SubItem1 命令。然后，当您单击“SubItem1”时，它将运行 Code_SubItem1 宏
Dim newSubItem As Object
Set newSubItem = CommandBars("Cell").Controls("NewSub")
With newSubItem
.Controls.Add(Type:=msoControlButton, before:=1).Caption = "subItem1"
' This will run the subItem1_Code macro when subItem1 is clicked.
.Controls("subItem1").OnAction = "Code_subItem1"
End With
' This displays the Cell shortcut menu bar.
' 200, 200 refers to the screen position in pixels as x and y coordinates CommandBars("Cell").ShowPopup 200, 200
End Sub
Sub ShortcutSub_DisableItem() '禁用“NewSub”子菜单上的 SubItem1 命令
CommandBars("Cell").Controls("NewSub").Controls("subItem1").Enabled = False
' This displays the Cell shortcut menu bar.
' 200, 200 refers to the screen position in pixels as x and y coordinates.
CommandBars("Cell").ShowPopup 200, 200
End Sub
Sub ShortcutSub_DisableItem2() '启用“NewSub”子菜单上的 SubItem1 命令
CommandBars("Cell").Controls("NewSub").Controls("subItem1").Enabled = True
' This displays the Cell shortcut menu bar.
' 200, 200 refers to the screen position in pixels as x and y coordinates.
CommandBars("Cell").ShowPopup 200, 200
End Sub
Sub ShortcutSub_DeleteItem() '删除“NewSub”子菜单上的 SubItem1 命令
CommandBars("Cell").Controls("NewSub").Controls("subItem1").Delete
' This displays the Cell shortcut menu bar.
' 200, 200 refers to the screen position in pixels as x and y coordinates.
CommandBars("Cell").ShowPopup 200, 200
End Sub
Sub ShortcutSub_DisableSub() '禁用“单元格”快捷菜单栏上的“NewSub”子菜单
CommandBars("Cell").Controls("NewSub").Enabled = False
' This displays the Cell shortcut menu bar.
' 200, 200 refers to the screen position in pixels as x and y coordinates.
CommandBars("Cell").ShowPopup 200, 200
End Sub
Sub ShortcutSub_DisableSub2() '启用“单元格”快捷菜单栏上的“NewSub”子菜单
CommandBars("Cell").Controls("NewSub").Enabled = True
' This displays the Cell shortcut menu bar.
' 200, 200 refers to the screen position in pixels as x and y coordinates.
CommandBars("Cell").ShowPopup 200, 200
End Sub
Sub ShortcutSub_DeleteSub() '删除您在“单元格”快捷菜单栏上创建的“NewSub”子菜单
CommandBars("Cell").Controls("NewSub").Delete
' This displays the Cell shortcut menu bar.
' 200, 200 refers to the screen position in pixels as x and y coordinates.
CommandBars("Cell").ShowPopup 200, 200
End Sub

复制代码

luyibiao · 发表于 2013-9-6 11:52

感谢分享！

闻启学 · 发表于 2013-10-22 07:17

感谢分享！

wqfzqgk · 发表于 2013-10-22 08:08

总结的不错，论坛还得靠这样的人推动

huazai688 · 发表于 2013-10-24 17:05

{:soso_e179:}

aman1516 · 发表于 2013-10-25 19:01

做个标记，总结得很好，谢谢分享

hunanahonghong · 发表于 2015-6-23 21:27

{:soso_e179:}，继续学习中，多谢大侠们。

xxxxbbhiox · 发表于 2015-12-19 11:53

子菜单实现超链接打开工作簿

		自动登录	找回密码
密码			免费注册

[分享] Excel个性化菜单攻略大全！