• 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?

Status
Not open for further replies.

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

  • Hyperlink Pivot table.xlsm
    34.3 KB · Views: 75
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.
 
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
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 ..?
 
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
 
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.
 
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.
 
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.
 
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
 
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
 
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
 
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).
 
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).
 
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

  • Hyperlink Pivot table_Sample.xlsb
    35.6 KB · Views: 42
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

  • New Data Table.xlsm
    199.1 KB · Views: 17
Try this version.

However, note that if there's hyperlink missing it will still go to Master Data.
 

Attachments

  • New Data Table (1).xlsm
    186.9 KB · Views: 94
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

  • Hyperlink_SKR.xlsx
    29.8 KB · Views: 7
Hello everybody, I have read your comments and tried to apply the same macro on my sheet but there is a problem, hyperlinks are active in some cells not in all cells. I am not really good at macro so I couldn't figure out what is the problem. The yellow cells contain hyperlinks in data sheet but when I click on the cell it goes to data sheet not to the hyperlink. Could you please help me? You can find file attached.. Thank you very much... :D
 

Attachments

  • file.xlsm
    34.8 KB · Views: 7
fatimssi
As You've just read from previous reply and
before that from Forum Rules
  • Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.
Please reread those Forum Rules
This .. old ... thread is closed now.
 
Status
Not open for further replies.
Back
Top