sugrue
06-26-2009, 12:37 PM
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
"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