跳到主要內容

 如何在數據透視表中創建可點擊的超鏈接?

您可以輕鬆地單擊超鏈接以鏈接到工作表中的網頁,但是,如果您創建一個包含超鏈接的數據透視表,則該超鏈接將不可單擊。 您有什麼好主意可以解決Excel中的此問題嗎?

使用VBA代碼在數據透視表中創建可點擊的超鏈接


箭頭藍色右氣泡 使用VBA代碼在數據透視表中創建可點擊的超鏈接

默認情況下,超鏈接在數據透視表中不起作用,但是幸運的是,您可以創建VBA代碼以使超鏈接在數據透視表中可單擊。

1。 按住 ALT + F11 鍵,然後打開 Microsoft Visual Basic for Applications窗口.

2。 然後從左側選擇您使用的工作表 項目瀏覽器 窗格,雙擊以打開一個 模塊,然後將以下VBA代碼複製並粘貼到空白處 模塊:

VBA代碼:在數據透視表中創建可單擊的超鏈接

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update 20140814
If Target.Cells.Count <> 1 Then Exit Sub
On Error Resume Next
Application.ActiveWorkbook.FollowHyperlink Address:=CStr(Target.Value), NewWindow:=True
End Sub

doc-make-hyperlink-click-pivottable-1

3. 然後保存並關閉此代碼,返回到工作表,現在,當您單擊數據透視表中的超鏈接時,該超鏈接將被激活並鏈接到其網頁。

備註:此代碼僅適用於以Http://開頭的網頁鏈接。

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Anyone know how to run this on only one column in the pivot table...as all my columns are clickable, even though there is no link behind them......
This comment was minimized by the moderator on the site
Muchas gracias. A mi me ha funcionado con enlaces a peliculas de mi disco duro, que tenia una base de datos de mis pelis. Asi que no solo funciona con http... o al menos a mi me ha servido.
This comment was minimized by the moderator on the site
I tried the oce but it doesn't work. I someone willing to take a look at my document ?
This comment was minimized by the moderator on the site
I have two types of data in my pivot table that would use a hyperlink: 1. a hyperlink to a document located on a shared drive. 2. an email address that I would like to use the mailto link to open an email. The macro above works for the document, but I can't figure out how to make it work for the email. Any ideas?
This comment was minimized by the moderator on the site
This works very well, but I'm trying to figure out how to have a shorter friendly name to click on rather than a long URL. I have 3 columns of different links and they all run together visually. A symbol or even a friendly name based on a previous cell in that row would be excellent.
This comment was minimized by the moderator on the site
What about Hyperlinking documents within the pivot table?
This comment was minimized by the moderator on the site
This will make it so it works across all workbooks. Enter this into "ThisWorkbook" rather than individual sheets. 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
This comment was minimized by the moderator on the site
Why is the hyperlink not working? Please help!! https://www.dropbox.com/s/zyp8e2g0sqygweb/zero%20test%202.xlsm?dl=0
I have tried in a number of ways, tab and worksheet specific - and to no avail..
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations