如何在Excel 2019自定义右键菜单中添加仅图标按钮？

你猜的没错，纯msoBarPopup类型的右键菜单默认是垂直堆叠显示菜单项的，没法直接让按钮横向排列，但可以通过嵌套一个横向工具栏的方式实现你要的紧凑效果。下面是修改后的完整代码，以及关键修改说明：

修改后的代码 #

On Error Resume Next
CommandBars("MyBar").Delete
CommandBars("MyIconBar").Delete ' 清理临时工具栏

Dim cb As CommandBar
Set cb = CommandBars.Add("MyBar", msoBarPopup, , True)
Dim iconBar As CommandBar
Set iconBar = CommandBars.Add("MyIconBar", msoBarTop, , True) ' 创建临时横向工具栏
iconBar.Orientation = msoBarHorizontal ' 设置按钮横向排列
iconBar.Visible = False ' 不单独显示该工具栏

Dim bItem As CommandBarControl, sbItem As CommandBarControl

' 向横向工具栏添加仅图标按钮
Set bItem = iconBar.Controls.Add(msoControlButton, , , , True)
With bItem
     .Style = msoButtonIcon ' 仅显示图标
     .FaceId = 1907
     .OnAction = "CompleteItm"
     .TooltipText = "Complete" ' 鼠标悬停显示功能提示
End With

Set bItem = iconBar.Controls.Add(msoControlButton, , , , True)
With bItem
     .Style = msoButtonIcon
     .FaceId = 478
     .OnAction = "CancelItm"
     .TooltipText = "Cancel"
End With

Set bItem = iconBar.Controls.Add(msoControlButton, , , , True)
With bItem
     .Style = msoButtonIcon
     .FaceId = 67
     .OnAction = "RemoveItm"
     .TooltipText = "Remove"
End With

Set bItem = iconBar.Controls.Add(msoControlButton, , , , True)
With bItem
     .Style = msoButtonIcon
     .FaceId = 463
     .OnAction = "PutOnHold"
     .TooltipText = "On hold"
End With

' 将横向工具栏嵌入到右键菜单
Set bItem = cb.Controls.Add(msoControlBar, , , , True)
With bItem
     .CommandBar = iconBar
End With

' 保留原有的"Add"弹出菜单
Set bItem = cb.Controls.Add(msoControlPopup, , , , True)
With bItem
     .Caption = "Add"
     
     Set sbItem = bItem.Controls.Add(msoControlButton, , , , True)
     With sbItem
           .Caption = "ADA.regular"
           .OnAction = "ARA_REG"
           .FaceId = 244
     End With
     Set sbItem = bItem.Controls.Add(msoControlButton, , , , True)
     With sbItem
           .Caption = "ADA.unique"
           .OnAction = "ARA_UNI"
           .FaceId = 244
     End With
     Set sbItem = bItem.Controls.Add(msoControlButton, , , , True)
     With sbItem
           .Caption = "ADX.dynamic"
           .OnAction = "ARX_DYN"
           .FaceId = 216
     End With
     
End With

CommandBars("MyBar").ShowPopup

关键修改说明 #

• 新增临时横向工具栏：创建了名为MyIconBar的工具栏，设置为横向排列且不单独显示，专门用来放置仅图标按钮
• 按钮仅显示图标：给每个按钮设置.Style = msoButtonIcon，隐藏标题，同时添加.TooltipText，鼠标悬停时会显示按钮功能说明
• 嵌入工具栏到右键菜单：通过msoControlBar类型的控件，把横向工具栏嵌入到主右键菜单中，实现图标横向排列的效果

内容的提问来源于stack exchange，提问作者Guyglk