Sub ThisApp_SheetBeforeDoubleClick(ByVal Sht As Object, ByVal Target As Range, Cancel As Boolean)
In re the above:
Sht is a RAM clone of the Sheet that was double clicked
Target is a RAM Clone of the cell that was double clicked
Cancel must be set to True to prevent Excel from perfoming any actions on the original cell on the original sheet that would normally take place.
Sht.Parent is the Workbook that contains the Sheet
Anyways, this workbook will be a
constant named (Daily Fin_Exports)
and the workbook
name will always be named "Daily ExAccounts",
Here is a Hierarchical system for deciding which macro to run. It all starts from the App_DoubleClick Event Procedure.
'''''All DoubleClicks. Determine Workbook
Sub ThisApp_SheetBeforeDoubleClick(ByVal Sht As Object, ByVal Target As Range, Cancel As Boolean)
Dim WkBk As Workbook
If Sht.Parent Is ThisWorkbook Then Exit Sub 'Never run this sub against this Personal.xlsb
Set WkBk = Sht.Parent
'An exclusive test for different workbooks. If Book is not in list, the Sub exits
With WkBk
If Instr(.Name, "Daily Fin_Exports") then Fin_Reports WkBk, Sht, Target, Cancel
If Instr(.Name, "SomeOtherName") Then Macro2 WkBk, Sht, Target, Cancel
End With
'Macros set Cancel ByRef to reflect back here
End Sub
'''Double Clicks in certain Workbook. Determine Sheet
Sub Fin_Reports(ByVal WkBk As Object, ByVal Sht As Object, ByVal Target As Range, ByRef Cancel As Boolean)
If Sht.Name = "XYZ" Then MacroXYZ WkBk, Sht, Target, Cancel
If sht.Name = "ABC" Then MacroABC Sht, Target, Cancel
End Sub
''''DoubleClicks in Certain Sheet in Certain Workbook. Determine Cell
Sub MacroXYZ(ByVal WkBk As Object, ByVal Sht As Object, ByVal Target As Range, ByRef Cancel As Boolean)
'Test for appropriate ranges
If Not Intersect(WkBk.Sht.Range("G2:G1000"), Target) Is Nothing Then
''If a macro is going to handle the D-Click event, then set Cancel
Cancel = True '<---------First time set
'To Call a macro in Personal
PersonalMacro1 WkBk, Sht, Target 'Do NOT pass "Cancel"
'To Call a Macro in the D-Clicked workbook
'Wkbk.WorkbookMacro1 WkBk, Sht, Target 'Do NOT pass "Cancel"
End If
If Not Intersect(WkBk.Sht.Range(Some other Range), Target) Is Nothing Then
''If a macro is going to handle the D-Click event, then set Cancel
Cancel = True '<---------First time set
'Do Stuff
End If
'Repeat as needed
End Sub
Basically, The App Event Sub determines which Workbook has the Sheet that has the Cell that was D-Clicked and calls a Specific Sub to determine which Sheet in the relevant Workbook has the Cell that was D-Clicked. Then A Range determining sub is called. These Range Determining Subs are the ones that finally Set Cancel to True and which call the various subs that do the actual work on the Cells.
Note that it is the App Event Sub that Sets the WkBk Variable Value. All the Sub Parameters are always passed all the way down and it is the individual Macros that determine which Parameters to use. But they still pass all the parameters on to the next sub.
This means that every macro declaration is the same, except for the name.
Sub Name(ByVal WkBk As Object, ByVal Sht As Object, ByVal Target As Range, ByRef Cancel As Boolean)
Note: You don't want to pass the Cancel after it's been Set.
This system makes it very easy to edit and modify the Project Code Flow as needed. You have a library of "working" Macros and the Hierarchical decision tree merely chooses which one(s) to run.
Note: None of the "working" Subs should be in the Personal ThisWorkbook Code Page