Right Click auto run macro

[Steven]

I would like to change the Right Mouse click on a basic excel worksheet so it
does not work as the conventional method of showing a list of available
processes, like copy, paste etc.... What I would like for it to do is to run
a specific macro automatically right off the Right Click. Basically then the
Right Click would have only one function which is to run that macro. Can
this be done.

Thank you,

Steven

 

[Brotha Lee]

Hi Steven,

Yes this can be done. In the VBA editor click on ThisWorkbook and add the
following code there. This will cause a macro to run whenever you right click
on any sheet in your workbook.
The code should be something like this:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
application.run "YourMacroName"
cancel = true
End Sub

If you want to attach to a specific sheet use the respective worksheet event.

 

[Steven]

Brotha,

Thank you for the response. I forgot to mention another item. I want this
to work regardless of which file I am in. If I have 5 files open then I want
the same response, regardless of the file I am in.

I have run the folling macro to adjust the Right Click control for example:

With Application.CommandBars("Cell").Controls
With .Add
.Caption = "Windows"
.OnAction = "'MacroFile'!ShowWorkbooks"
.Move Before:=2
End With
End With

Which works but this means when I RightClick then I have to select off the
popup list.... where what I want is to Right Click and automatically run the
macro.

So in summary, I want to remove all the Right Click items and replace it so
when the RightClick is pressed it will run the macro.

Thank you,

Steven

 

[Brotha Lee]

Steven,

These are two separate issues:
1) After right click a macro should run
If you end the right click event with Cancel = True no popup box will appear
so in my example beneath a macro will run directly after the right mouse is
clicked
2) The second item is you would like to control right click events all over
your open workbooks, so all will act the same. If you put the following code
in ThisWorkbook you can capture the events from the application. (You have to
save and re-open the file before the events will be captured)

Public WithEvents App As Application

Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As
Range, Cancel As Boolean)
application.run "YourMacro"
Cancel = True
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub

 

[Steven]

Brotha,

That is a major smokin hit. Thank you very much.

Steven