PDA

View Full Version : [SOLVED] VBA - Change code to work on whole workbook



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!

Paul_Hossler
05-09-2016, 08:57 AM
In the ThisWorkbook module, use something like this




Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
' MsgBox Sh.Name
' MsgBox Target.Address
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



ThisWorkbook module has a number of events that apply to the entire workbook, and most receive the worksheets in the Sh paramater

Cityofglass8
05-09-2016, 09:39 AM
Holy cow, man. It works!
You have officially made my morning.
Huge thank you!