customizing right click menu

There are A LOT of right click pop up menus, for example, when you right click on a cell, depending wether you are in Normal View or in PageBreak mode, Excel displays one of two pop up menus.

This simple macro will give you the name and index of every command bar available on your computer.

Sub CBars()
Dim cmd As CommandBar
For Each cmd In Application.CommandBars
ActiveCell.Resize(1, 2) = Array(cmd.Name, cmd.Index)
ActiveCell.Offset(1).Select
Next cmd
End Sub

That way you can decide which one to work with, what to add and where.

Juan Pablo G.

So how do you customize what you see when you right click?

I ran the macro but still see the same menu?

That macro was only to tell the name and index of all CommandBars, it didn't serve any other purpose. To add something do this:

If i remember correctly, Cell Right Click menu is number 36 (I think, this is by memory), so, you would have to do something like

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim cmd As CommandBar
Dim btn As CommandBarButton

Set cmd = Application.CommandBars(28)
cmd.Reset

With cmd
Set btn = cmd.Controls.Add(Type:=msoControlButton, Temporary:=True)
btn.Caption = "Hello"
End With
End Sub

This menu control doesn't do anything, it just sits there, but this is the idea. Notice that if you go to Page Break Preview then this button doesn't appear. That's a different commandbar.

Juan Pablo G.

Jaun
Mate read this im super intrested, but need more that that, like compleat code and how to install, this is cool if we can choose what to appear on the right click, can i have UDF so click inputs my UDF as well...i have UDF code already, possabilitys are endless.

Im lazy so i like to carry a toolkit of JUST what i want, this is good, any more help please, i would love to know

Cheers Jaun
PS its 12PM midnight now in London so will pick up on this feed tommorow,

Jack in UK

JAUN

Sorry Jaun, forgot to highlight to you, pleae forgive me.

Jaun
Mate read this im super intrested, but need more that that, like compleat code and how to install, this is cool if we can choose what to appear on the right click, can i have UDF so click inputs my UDF as well...i have UDF code already, possabilitys are endless.

Im lazy so i like to carry a toolkit of JUST what i want, this is good, any more help please, i would love to know

Cheers Jaun
PS its 12PM midnight now in London so will pick up on this feed tommorow,

Jack in UK

Haven't tested Juan's code, but this is how I have added a command to the right click menu in the past.

'------------------------------------------------------------------------
' Shapes_Menu Method
'------------------------------------------------------------------------
' Adds a control button to the MS shapes bar. (popup menu that appearswhen you right
' click on an MS shape)
' This button will allow the user to edit parameter and variable values.
Public Sub Shapes_Menu()

Dim EditTag As CommandBarButton
Set EditTag = CommandBars("Shapes").Controls.add(Type:=msoControlButton, before:=1)
With EditTag
.Caption = "Change Tag Values"
.OnAction = "TextBoxCheck"
End With

End Sub

Oh yeah

Forgot to say this.

Before := 1 --- puts the new command button on top.
OnAction = "TextBoxCheck" --- runs a subroutine called TextBoxCheck.

Dim EditTag As CommandBarButton Set EditTag = CommandBars("Shapes").Controls.add(Type:=msoControlButton, before:=1) With EditTag .Caption = "Change Tag Values" .OnAction = "TextBoxCheck" End With

Re: JUAN

I think you would need to create a sub that applied your UDF to the selection.

Something like:

Sub InsertUDF()
Selection.Formula = "=" & YourUDF & "(" & ParametersHere & ")"
End Sub

Then, in the macro i provided you would need to specify the OnAction property, like this

Btn.OnAction = "InsertUDF"

I think, repeat, think, that would do it.

Juan Pablo G.

Re: JUAN

One last thing i forgot... Jaun is French for Juan, but i prefer the Spanish Version... :)

Juan Pablo G. Selection.Formula = "=" & YourUDF & "(" & ParametersHere & ")"

Help + Names

Sorry on the name front, translation i guess, i have translated to English/French of cause no offence i assure my mistake and im deeply sorry not ment to anoy i asure you.

Big thanks for your help and ill give it a go, look forward to some good fun, please except sorry once again.