Public Sub AddCustomMenu()
Dim cbWSMenuBar As CommandBar
Dim muCustom As CommandBarControl
Dim iHelpIndex As Integer
Set cbWSMenuBar = Application.CommandBars("Worksheet Menu Bar")
iHelpIndex = cbWSMenuBar.Controls("?").Index
Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, _
Before:=iHelpIndex)
With muCustom
.Caption = "&Custom"
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Show Data Form"
.OnAction = "ShowDataForm"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Print Data List"
.OnAction = "PrintDataList"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Sort Names &Ascending"
.BeginGroup = True
.OnAction = "SortList"
.Parameter = "Asc"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Sort Names &Descending"
.OnAction = "SortList"
.Parameter = "Dsc"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Show Products"
' .OnAction = "ShowProduct"
' .OnAction = "ShowProduct ""Apple"", 3, 4"
.OnAction = "'ShowProduct ""Apple"", 3, 4'"
'.OnAction = "'ShowProduct """ 'ShowProduct ""Apple"","", 3, 4'"4'"
End With
End With
End Sub
Public Sub RemoveCustomMenu()
Dim cbWSMenuBar As CommandBar
On Error Resume Next
Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
cbWSMenuBar.Controls("Custom").Delete
End Sub