Worksheet_BeforeDoubleClick question

[sugrue]

My son has asked me to help him with a VBA question:

"I have a VBA function that works. We have a series of Excel files on a SharePoint site that needs to have this function as well as any new workbooks we create. Do you know if there is a way to save this function somewhere and have all of our excel files inherit the function? "

Currently, he has the code tied just to Sheet3 of one of the spreadsheets. It needs to apply to all sheets in all of the workbooks and for anyone who opens the workbook. They also want it to apply to any new spreadsheets they create.

Any ideas?

Thanks, Jack

The code is as follows:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim strSheet As String
'On Error Resume Next
If InStr(1, Target.Formula, "!") Then
If InStr(1, Target.Formula, "'") Then
strSheet = Mid(Replace(Target.Formula, "'", ""), 2, InStr(1, Target.Formula, "!") - 4)
Else
strSheet = Mid(Target.Formula, 2, InStr(1, Target.Formula, "!") - 2)
End If
Sheets(strSheet).Activate
ActiveSheet.Range(CStr(Right(Target.Formula, Len(Target.Formula) - InStr(1, Target.Formula, "!")))).Select
End If
End Sub

[p45cal]

there is a
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

End Sub
event in the Thisworkbook code module available which should trigger on all sheets in the workbook. The code might need adapting to make use of the Sh object (the sheet in question).

As to having it as a default macro in all new workbooks, I'm not sure, others should be able to help on this.

[mikerickson]

One would have to create an Application level event.
WorkSheet_BeforeDoubleClick is a sheet level event that fires when a particular sheet is double clicked
Workbook_SheetBeforeDoubleClick is a workbook level event that fires when any sheet in a particular workbook is double clicked.
Using class modules, one can create an application level DoubleClick event that will fire whenever any cell in any sheet of any workbook is double clicked.

The VBEditor help system has an explaination (keyword: application event)

I put my App level code modules in my Personal Macro Workbook and initialize them in the PMW's Open event.

If you son has further, post back.