Customizing popup menus

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have an Excel/Word VBA app and I need to be able to customize the popup
(right click) menu on an Excel tab so I can add a custom command to insert my
own custom worksheet. I can only find ways to alter the regular menu bar, but
not the popup.
Can anyone tell me how to do so linked to an item to perform a macro or sub
routine. Is there a place to edit such menus or does it need to be done with
code, and if the latter, how?
Thanks, God bless
Van
 
Van,

Here is some code to add to the menu.

Sub CreateRightClick()
With Application.CommandBars("Cell"­)
With .Controls.Add
.Caption = "Remove"
.OnAction = "Remove"
End With
With .Controls.Add
.Caption = "Remove2"
.OnAction = "Remove2"
End With

End With
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Van

Must be done through code.

Example to add a menu item to right-click which runs a macro.

Sub Workbook_Open()

With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"
End With
End Sub

Good idea to make sure you clear the item if already on the right-click menu
before adding it or you will get duplicates.

Do this by adding a delete line.

Revised code will look like this.

Sub Workbook_Open() 'or _Activate
Application.CommandBars("Cell").Controls("Clear Formats").Delete
With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"
End With
End Sub


Gord Dibben Excel MVP
 
Van

No provision made for deleting the item when switching workbooks.

Try this amended.

Private Sub Workbook_Activate()
With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"
End With
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Cell").Controls("Clear Formats").Delete
End Sub


Gord
 
Thanks Bob. Will check it out.
God bless
Van

Bob Phillips said:
Van,

Here is some code to add to the menu.

Sub CreateRightClick()
With Application.CommandBars("Cell"­)
With .Controls.Add
.Caption = "Remove"
.OnAction = "Remove"
End With
With .Controls.Add
.Caption = "Remove2"
.OnAction = "Remove2"
End With

End With
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
Back
Top