Cityofglass8
05-09-2016, 07:51 AM
I have a short bit of code I need help changing from working on a single sheet, to work on the whole workbook.
End goal is to have URLs as a clickable hyperlink in whole workbook.
Current code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then
On Error Resume Next
ActiveWorkbook.FollowHyperlink _
Address:=CStr(Target.Value), _
NewWindow:=True
On Error GoTo 0
End If
End Sub
This code is working as desired when added to a single sheet. I am looking for a way to make this happen throughout the entire workbook.
Details:
Details regarding tickets are exported via SQL monthly Excel files. I then use PowerQuery to import that into my datamodel and transform the data in PowerPivot.
One field that is imported is the ticket number. In PowerQuery, I created a custom column changing the ticket number to the direct url starting with "https://". In the pivot tables and charts, you can do a quick explore to create a new sheet containing a table with the raw data referenced including the URL for the ticket. If I then go into VBA and add this code to that new sheet, it works perfectly. However, I am hoping to find a way to have this occur on all current and future worksheets that are created in workbook.
Any help would be appreciated!
End goal is to have URLs as a clickable hyperlink in whole workbook.
Current code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then
On Error Resume Next
ActiveWorkbook.FollowHyperlink _
Address:=CStr(Target.Value), _
NewWindow:=True
On Error GoTo 0
End If
End Sub
This code is working as desired when added to a single sheet. I am looking for a way to make this happen throughout the entire workbook.
Details:
Details regarding tickets are exported via SQL monthly Excel files. I then use PowerQuery to import that into my datamodel and transform the data in PowerPivot.
One field that is imported is the ticket number. In PowerQuery, I created a custom column changing the ticket number to the direct url starting with "https://". In the pivot tables and charts, you can do a quick explore to create a new sheet containing a table with the raw data referenced including the URL for the ticket. If I then go into VBA and add this code to that new sheet, it works perfectly. However, I am hoping to find a way to have this occur on all current and future worksheets that are created in workbook.
Any help would be appreciated!