Can you capture worksheet double click event from an Add-In????

[JGeniti]

Is there a way for me to add code to my add-in file that will capture
the before_doubleclick event of the worksheet that is referencing the
add-in? I currently have thousands of worksheets that are referencing a
single add-in and I need to add a new procedure that gets triggered
when a user double clicks on a specific sheet.

I have tried using the CreateEventProc (example 1) and the InsertLine
(example 2) technique to try to add the event procedure to the
activeworkbook. This has proved to be very unstable. It will work with
some worksheets and crash on others. From what I gathered from my
research, there is an issue with trying to add code to during run-time.

I was just wondering if there is a way that I can just add code to my
add-in that will get triggered when a user double clicks the worksheet.

Any help would be greatly appreciated.
James

Example 1
With ActiveWorkbook.VBProject.VBComponents("Sheet7").CodeModule
StartLine = .CreateEventProc("BeforeDoubleClick",
"Worksheet") + 1
.InsertLines StartLine, "PurchasePartsDblClick Target,
Cancel"
End With

Example 2
Sub AddDblClickEvent()
Dim myVBComp As VBIDE.VBComponent

Set myVBComp = ActiveWorkbook.VBProject.VBComponents("Sheet7")

myVBComp.CodeModule.InsertLines 1, "Private Sub
Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)"
myVBComp.CodeModule.InsertLines 2, "PurchasePartsDblClick Target,
Cancel"
myVBComp.CodeModule.InsertLines 3, "End Sub"
End Sub

 

[Tom Ogilvy]

Sure. Just have your addin instantiate application level events.

See Chip Pearson's page on Application Level events
http://www.cpearson.com/excel/appevent.htm

or look in Excel VBA help at application level events.

 

[JGeniti]

Tom,
I think this might be more of my lack of understanding of how to
incorporate Chips example into what I'm trying to do. I have been
researching this for a couple of hours and all of the examples of
application events that I have seen seem to reference application
events like opening, closing or creating new workbooks. How would I
break it down to the worksheet level? I hate to seem like I'm asking
you to force feed me this, but for some reason I have been unable to
get the examples that I have seen to work for me.

If you have any type of example of capturing the worksheet events it
would be very helpful.

Thank you,
James

 

[Tom Ogilvy]

Occurs when any worksheet is double-clicked, before the default double-click
action.

Private Sub object_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Range, ByVal Cancel As Boolean)

object Application or Workbook. For more information about using events
with the Application object, see Using Events with the Application Object.

Sh A Worksheet object that represents the sheet.

Target The cell nearest to the mouse pointer when the double-click
occurred.

Cancel False when the event occurs. If the event procedure sets this
argument to True, the default double-click action isn't performed when the
procedure is finished.

Remarks
This event doesn't occur on chart sheets.



so where it shows Object, if using Chip's page example, that Object would be
replace with App



In the class module it would be declared as:



Public WithEvents App As Application

Private Sub App_SheetBeforeDoubleClick(ByVal Sh As Object, _

ByVal Target As Range, ByVal Cancel As Boolean)

if lcase(sh.Name) = "mysheet" and lcase(sh.parent.name) =
"myworkbook.xls" then
sh.Range("A1").Value = "You double clicked"
end if

End Sub

then in the event, you can test if this is the sheet (sh.Name) and workbook
(sh.parent.name) that you want to react to.


for an example workbook, Chip offers one at the page I cited.

 

[Tom Ogilvy]

If Chip's workbook doesn't help and you still "don't get it", then send me
an email and I will put together a sample for you.

(e-mail address removed)