• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to create clickable hyperlinks in pivot table?

Osmo

New Member
Hi Excel professionals,
I am working on a Pivot Table and need to keep the hyperlinks from the original data active and working. the hyperlinked data stored by one of the shared folder device.

anyone can help me on this with the exact VBA code to working on it. and appropriate way to apply the vba code to work the hyperlink in pivot table.

herewith attached a sample file (the original is an huge data file).
Your assistants will be highly appreciated .
Thank You.
 

Attachments

Osmo

New Member
Thank you very much Chihiro,
Here you talking about the hyperlink for websites, But actually i want hyperlink working on pivot table which i've already hyperlink data file, need to open the hyperlink in each area of pivot table, if click on a product it need to open the related file like licence, registration etc... which I've hyperlink in the main data sheet.

Hope you can help me this issue.
Thank you very much for your prompt response.
 

Chihiro

Excel Ninja
Hyper links, be it for Web sites or files work the same way.
The process described in the link shows how to embed Hyperlink in PivotTable. There's only 2 methods that I now how, and both are listed.
 

Osmo

New Member
Hi
Hyper links, be it for Web sites or files work the same way.
The process described in the link shows how to embed Hyperlink in PivotTable. There's only 2 methods that I now how, and both are listed.
Hi Chihiro,
the same vba code work with files ..?
 

Chihiro

Excel Ninja
Here's the variant of original link's code. Which checks for ":\" in string.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ptc As PivotTable
For Each ptc In ActiveSheet.PivotTables
    If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
        If InStr(Target.Text, ":\") > 1 Then
            ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
        End If
    End If
Next ptc
End Sub
 

Osmo

New Member
Thank you very much Chihiro, appreciate your knowledge in excel,
Its working with the hyperlink like ..\Documents\MY TASKS\WD My Cloud™ User M.pdf. But if i do text to display on hyperlink window its not working.

in place of the hyperlink address i need show the text to display as my product name... its work with pivot table ...?
Can u help me on these please.
Thank you.
 

Chihiro

Excel Ninja
That will require additional steps. I don't have time now. But let me think on it.

I'd imagine you can accomplish this through Match, Find or some other function and locating source on the data table and following link from there.
 

Osmo

New Member
Thank you very much,

I’ve tried to use match function with this,
When you get time please help me on this issue,
Thanks in advance and appreciate your prompt response.

That will require additional steps. I don't have time now. But let me think on it.

I'd imagine you can accomplish this through Match, Find or some other function and locating source on the data table and following link from there.
 

Chihiro

Excel Ninja
Here, try this code. It's somewhat specific to your sample sheet. Adjust as needed.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ptc As PivotTable
Dim tradeName As String, haddress As String
Dim c As Range, x As Double
Dim sWs As Worksheet, aWs As Worksheet

'Adjust Worksheet name as needed
Set aWs = ThisWorkbook.Worksheets("Pivot table")
Set sWs = ThisWorkbook.Worksheets("Master Data sheet ")

For Each ptc In ActiveSheet.PivotTables
    'Adjust for PivotTable header Row# as needed
    If Target.Row = 3 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
        tradeName = Cells(Target.Row, 1).Value
        With sWs.Cells
            Set c = .Find(tradeName, After:=Cells(1), LookIn:=xlValues, LookAt:=xlPart)
            If Not c Is Nothing Then
                Application.ScreenUpdating = False
                sWs.Select
                On Error GoTo ErrHandle:
                'Adjust source data column range as needed
                With sWs.Range("A" & c.Row & ":W" & c.Row)
                    x = Application.Evaluate("Match(" & Chr(34) & Target.Value & Chr(34) & "," & .Address & ",0)")
                    Application.ScreenUpdating = True
                    aWs.Activate
                    haddress = .Columns(x).Hyperlinks.Item(1).Address
                    ThisWorkbook.FollowHyperlink Address:=haddress, NewWindow:=True
                Exit Sub

                End With
            End If
        End With
    End If
Next ptc


ErrHandle:
For Each ptc In ActiveSheet.PivotTables
    If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
        MsgBox "Error" & Err.Number & ": Unable to follow HyperLink or HyperLink address not found"
        Application.ScreenUpdating = True
        aWs.Select
        Exit Sub
    End If
Next ptc
End Sub
 

Osmo

New Member
Thank you very much..... Its working now... May god bless you and increase you knowledge :)

Here, try this code. It's somewhat specific to your sample sheet. Adjust as needed.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ptc As PivotTable
Dim tradeName As String, haddress As String
Dim c As Range, x As Double
Dim sWs As Worksheet, aWs As Worksheet

'Adjust Worksheet name as needed
Set aWs = ThisWorkbook.Worksheets("Pivot table")
Set sWs = ThisWorkbook.Worksheets("Master Data sheet ")

For Each ptc In ActiveSheet.PivotTables
    'Adjust for PivotTable header Row# as needed
    If Target.Row = 3 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
        tradeName = Cells(Target.Row, 1).Value
        With sWs.Cells
            Set c = .Find(tradeName, After:=Cells(1), LookIn:=xlValues, LookAt:=xlPart)
            If Not c Is Nothing Then
                Application.ScreenUpdating = False
                sWs.Select
                On Error GoTo ErrHandle:
                'Adjust source data column range as needed
                With sWs.Range("A" & c.Row & ":W" & c.Row)
                    x = Application.Evaluate("Match(" & Chr(34) & Target.Value & Chr(34) & "," & .Address & ",0)")
                    Application.ScreenUpdating = True
                    aWs.Activate
                    haddress = .Columns(x).Hyperlinks.Item(1).Address
                    ThisWorkbook.FollowHyperlink Address:=haddress, NewWindow:=True
                Exit Sub

                End With
            End If
        End With
    End If
Next ptc


ErrHandle:
For Each ptc In ActiveSheet.PivotTables
    If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
        MsgBox "Error" & Err.Number & ": Unable to follow HyperLink or HyperLink address not found"
        Application.ScreenUpdating = True
        aWs.Select
        Exit Sub
    End If
Next ptc
End Sub
 

Osmo

New Member
Hi Chihiro,
Have a Good Day!
If i click on Registration#. like 5031,29567 etc...its go to Master Data Sheet, not open the hyperlink (blank cells also the same).

the Registration#. like 40-23-99,49-23-97 etc.. and if the data available in pivot table range like test its working smoothly.

what is the solution to resolve it..?
can i add one more hyperlink pivot table in the same work sheet ..?

I'm not familiar with VBA, That's why I'm here too disturbing you.
Thanks in advance and appreciate your knowledge in excel.

Thank You.

Here, try this code. It's somewhat specific to your sample sheet. Adjust as needed.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ptc As PivotTable
Dim tradeName As String, haddress As String
Dim c As Range, x As Double
Dim sWs As Worksheet, aWs As Worksheet

'Adjust Worksheet name as needed
Set aWs = ThisWorkbook.Worksheets("Pivot table")
Set sWs = ThisWorkbook.Worksheets("Master Data sheet ")

For Each ptc In ActiveSheet.PivotTables
    'Adjust for PivotTable header Row# as needed
    If Target.Row = 3 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
        tradeName = Cells(Target.Row, 1).Value
        With sWs.Cells
            Set c = .Find(tradeName, After:=Cells(1), LookIn:=xlValues, LookAt:=xlPart)
            If Not c Is Nothing Then
                Application.ScreenUpdating = False
                sWs.Select
                On Error GoTo ErrHandle:
                'Adjust source data column range as needed
                With sWs.Range("A" & c.Row & ":W" & c.Row)
                    x = Application.Evaluate("Match(" & Chr(34) & Target.Value & Chr(34) & "," & .Address & ",0)")
                    Application.ScreenUpdating = True
                    aWs.Activate
                    haddress = .Columns(x).Hyperlinks.Item(1).Address
                    ThisWorkbook.FollowHyperlink Address:=haddress, NewWindow:=True
                Exit Sub

                End With
            End If
        End With
    End If
Next ptc


ErrHandle:
For Each ptc In ActiveSheet.PivotTables
    If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
        MsgBox "Error" & Err.Number & ": Unable to follow HyperLink or HyperLink address not found"
        Application.ScreenUpdating = True
        aWs.Select
        Exit Sub
    End If
Next ptc
End Sub
 

Chihiro

Excel Ninja
So, what do you want the code to do when there's no hyperlink available on the Master sheet?

And yes, you can have multiple pivot with hyperlink and code will work on all (as long as they are all on Pivot sheet).
 

Osmo

New Member
with like this format numbers (5031,29567 ...)hyperlink available but its not open


So, what do you want the code to do when there's no hyperlink available on the Master sheet?

And yes, you can have multiple pivot with hyperlink and code will work on all (as long as they are all on Pivot sheet).
 

Chihiro

Excel Ninja
Ah I see. That's because Match function I wrote was designed for text string.

In that case add following line after "On Error GoTo" line.
Code:
If IsNumeric(Target.Value) Then
    tString = Target.Value
Else
    tString = Chr(34) & Target.Value & Chr(34)
End If
And change "x = Application...." line to
Code:
x = Application.Evaluate("Match(" & tString & "," & .Address & ",0)")
See attached sample.
 

Attachments

Osmo

New Member
Ah I see. That's because Match function I wrote was designed for text string.

In that case add following line after "On Error GoTo" line.
Code:
If IsNumeric(Target.Value) Then
    tString = Target.Value
Else
    tString = Chr(34) & Target.Value & Chr(34)
End If
And change "x = Application...." line to
Code:
x = Application.Evaluate("Match(" & tString & "," & .Address & ",0)")
See attached sample.
Thank You so much,
I've updated with a new code which you've send to me, but still some of issues,
herewith enclosed the file for your kind review and advise.

Thank you,
 

Attachments

sateeshk

New Member
Hi, i referred to the above which is very helpful, but my case is little different and need help,

From the attached file i have Column C (which is a hyperlink to open browser) in data tab and the same is being pulled into Summary tab. I need a macro so when i click on the opportunity in Summary tab it should link the data tab and open up a browser. Also i see from the above Macro that i just place the cursor or move around, the link will work automatically, the users of the report often gets moved around to data tab and summary tab, which is not an good ideal case for the users to navigate around, can this be made to double click or hover the mouse and when you see hand symbol then press CTRL and open up the browser.

Really need help with this.
 

Attachments

Top