Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you capture worksheet double click event from an Add-In????
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").Co deModule 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you capture worksheet double click event from an Add-In????
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. -- Regards, Tom Ogilvy "JGeniti" wrote in message oups.com... 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").Co deModule 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you capture worksheet double click event from an Add-In????
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you capture worksheet double click event from an Add-In????
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. -- Regards, Tom Ogilvy "JGeniti" wrote in message ups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you capture worksheet double click event from an Add-In????
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. -- Regards, Tom Ogilvy "JGeniti" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
capture right mouse button click event on cell | Excel Programming | |||
Mouse Over Graph, Capture Information on Click(Double Click) | Excel Programming | |||
Sheet After Double Click event? | Excel Programming | |||
Before Double Click Event | Excel Programming | |||
capture shift-double-click | Excel Programming |