具有搜索功能的Excel超链接 [英] Excel-Hyperlink with a search function
问题描述
Excel-我希望能够单击工作表1上的单元格,并将其带到工作表2中的单元格.现在,我不需要简单的超链接,我需要工作表2中的单元格为成立.例如,我单击包含单词"start"的单元格A1,然后单击它,将带到带有相同短语"start"的工作表2中的单元格.现在在工作表2上,它可以是单元格A1或A4或A57.我希望找到它,以防它在该列中的位置发生变化.本质上,我需要具有查找功能的超链接.
Excel - I would like to be able to click on a cell on sheet 1 and have it take me to a cell in sheet 2. Now I do not want a simple hyperlink, I would need the cell in sheet 2 to be found. For example, I click on cell A1 that contains the word "start" and upon clicking on it I am taken to the cell in sheet 2 with the same phrase "start". Now on sheet 2 this could be cell A1 or A4 or A57. I would like it found in case it ever changes position within that column. Essentially I need a hyperlink with a find function.
推荐答案
单击链接后,可以使用Worksheet_FollowHyperlink
VBA事件移动选择.
You can use the Worksheet_FollowHyperlink
VBA event to move the selection after the link is clicked.
向所需目标工作表上的任何单元格添加普通超链接
Add a normal hyperlink to any cell on the desired destination sheet
将此代码添加到源工作表模块中
Add this code to the source worksheet module
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim rngDest As Range
Dim shDest As Worksheet
Dim cl As Range
Set shDest = ActiveSheet
Set rngDest = shDest.Range("A:A") ' <--- change this to your target search range
With rngDest
Set cl = .Find(Target.Range.Cells(1, 1).Value, .Cells(.Rows.Count, .Columns.Count), xlValues, xlWhole, xlByRows, xlNext)
If Not cl Is Nothing Then
cl.Select
Else
' value not found, return to original sheet
Target.Range.Worksheet.Activate
MsgBox Target.Range.Cells(1, 1).Value & " not found", vbOKOnly, "Not Found"
End If
End With
End Sub
这篇关于具有搜索功能的Excel超链接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!