MrExcel.com - Your One Stop for Excel Tips & Solutions

VBA HELP - sheetbeforerightclick event not responding?

Posted by Aron on January 19, 2002 2:46 AM

I have the following code in the ThisWorkbook section:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Call enablepaste
End Sub

The sub it calls works fine when I run it manually. But that's not the way it's supposed to work! WHY won't this event do anything? I switched it to Public Sub, then just Sub, and nothing. This has been driving me BATTY - please help!!

THANK YOU in advance,
Aron

Posted by Gary Bailey on January 19, 2002 4:36 AM

If you have it in the ThisWorkbook code module then the syntax should be

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)

End Sub

and it will capture any time someone right clicks on any of the worksheets. Your code will work in a sheet code module and will capture any right clicks on that particular sheet.

Gary

Posted by Aron on January 19, 2002 11:14 AM

Still not working

I have:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
MsgBox "Success!"
Call enablepaste

End Sub

In the ThisWorkbook code module, and it still won't capture the right-click event. I have no idea why not - I even tried assigning a new class module as described in JWalk's VBA 2002 book - still nothing.

Any ideas?
Aron

manually. But that's not the way it's supposed to work! WHY won't this event do anything? I switched it to Public Sub, then just Sub, and nothing. This has been driving me BATTY - please help!!

Posted by Aron on January 19, 2002 11:24 AM

Correction - ok in .xls, not in .xla

Correction - it works fine in an .xls workbook, but when I save it as an .xla it won't capture the event. Sorry - this info might've helped before...

Thanks for any help you can give...
Aron

manually. But that's not the way it's supposed to work! WHY won't this event do anything? I switched it to Public Sub, then just Sub, and nothing. This has been driving me BATTY - please help!!

Posted by Gary Bailey on January 19, 2002 11:37 AM

Re: Correction - ok in .xls, not in .xla

If its an .xla file surely you won't be able to the see the sheets to right click on them?

Gary

Posted by Aron on January 19, 2002 11:58 AM

Re: Correction - ok in .xls, not in .xla

Is there a way to capture a right-click event in an xl add-in? If its an .xla file surely you won't be able to the see the sheets to right click on them? : Correction - it works fine in an .xls workbook, but when I save it as an .xla it won't capture the event. Sorry - this info might've helped before... : Thanks for any help you can give... :

Posted by Ivan F Moala on January 19, 2002 12:11 PM

16099.html (NT)

Posted by Aron on January 19, 2002 1:55 PM

Re: 16099.html (NT)

I put the following in a new class module:

Public WithEvents App As Application

Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
MsgBox "Success!", vbExclamation
Call enablepaste
End Sub

...and it still won't capture.

Clueless,
Aron : Is there a way to capture a right-click event in an xl add-in?

Posted by Ivan F Moala on January 19, 2002 4:39 PM

Re: 16099.html (NT)

I put the same in an addin new class
THEN saved and UNloaded it THEN reloaded it.
Worked for me.


Ivan I put the following in a new class module: Public WithEvents App As Application Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)


Posted by Aron on January 21, 2002 9:10 PM

oops. It works now....thanks!