Customizing popup menus
 

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)


"VanS" wrote in message
...
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

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(tempo rary:=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(tempo rary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"
End With
End Sub


Gord Dibben Excel MVP
On Fri, 11 Mar 2005 14:03:06 -0800, "VanS"
wrote:

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

No provision made for deleting the item when switching workbooks.

Try this amended.

Private Sub Workbook_Activate()
With Application.CommandBars("Cell").Controls.Add(tempo rary:=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

On Fri, 11 Mar 2005 16:04:35 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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(tempo rary:=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(tempo rary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"
End With
End Sub


Gord Dibben Excel MVP
On Fri, 11 Mar 2005 14:03:06 -0800, "VanS"
wrote:

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

Thanks Bob. Will check it out.
God bless
Van

"Bob Phillips" wrote:

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)


"VanS" wrote in message
...
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

Gord,
Many thanks for your help. Your suggestions are quite helpful.
God bless
Van

"Gord Dibben" wrote:

Van

No provision made for deleting the item when switching workbooks.

Try this amended.

Private Sub Workbook_Activate()
With Application.CommandBars("Cell").Controls.Add(tempo rary:=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

On Fri, 11 Mar 2005 16:04:35 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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(tempo rary:=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(tempo rary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"
End With
End Sub


Gord Dibben Excel MVP
On Fri, 11 Mar 2005 14:03:06 -0800, "VanS"
wrote:

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