Can I skip application-level event-triggered macros if that event triggered a workbook-level event

[taigovinda]

Hi,

I have a workbook-level double-click event that I put into a certain workbook awhile ago.

It checks if there is a record identifier in a certain format either in the target cell or in the current range, within the double-clicked row; if so it queries an SQL server to find backup documentation for that record identifier and opens the document(s) in internet explorer.

Turns out this procedure, which is working fine, would be good to have in other workbooks or just to have on all the time. So, I inserted it into an existing add-in that can be redistributed to all the folks that would use it. I did that using the App double-click event in the ThisWorkbook module of the add-in, as described here: http://www.cpearson.com/excel/appevent.aspx ...that is working fine too - but I don't like the way the two are interacting.

My question is... if people are double-clicking in workbooks that already have the event-driven macro coded into there, the event will fire twice - once at the workbook level and once at the application level from the add-in. Then it will try to find and open the backup documents twice. Given that the workbooks firing the event are already coded and distributed all over the place so I can't change that code, is there a way for my add-in to know that this particular double-click already caused a workbook-level event to trigger a macro? I could try to read the query command string from the active workbook and see if it contains the same record identifier that my add-in would be querying for, but wondered if there is a more universal way to do it.

Thanks for any help!

Tai

 

[Jaafar Tribak]

The only workaraound I can think of is to override the default double-clicking events in workbooks that already have the event-driven macro coded into.

The following code should be placed in the ThisWorkbook module of the add-in :

Option Explicit

Private WithEvents Wb As Workbook
Private WithEvents cmb As CommandBars

Private Sub Workbook_Open()
    Call cmb_OnUpdate
End Sub

Private Sub cmb_OnUpdate()
    Set cmb = Application.CommandBars
    Set Wb = ActiveWorkbook
End Sub

Private Sub Wb_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Application.OnTime Now, Me.CodeName & ".cmb_OnUpdate"
    Debug.Print "You double-clicked sheet: " & Sh.Name & " of " & "Workbook : " & Sh.Parent.Name
    [B][COLOR=#008000]'Run your SQL query code here....
[/COLOR][/B]    End
End Sub

Unfortynately, the code uses the End statement which reinitializes all the module-level variables and intantiated classes. If that is not a problem for you then you can use it.

 

[taigovinda]

Hi Jaafar, this is definitely universal. I'm going to give it a try and see if it cancels anything that people will miss.

Thanks!

Tai

 

[mikerickson]

Your workbook level event could test for the presence of the Application object that drives that routine. If the App object is present, the WB level routine exits.

 

[Jaafar Tribak]

Your workbook level event could test for the presence of the Application object that drives that routine. If the App object is present, the WB level routine exits.

The problem is that he can't change the codes in the workbooks :

Given that the workbooks firing the event are already coded and distributed all over the place so I can't change that code,...

 

[mikerickson]

The app level event fires after the workbook level event.
If the permissions are set appropriately, it could look through the workbooks to see if one of them has the WB. double click code