VBA - Hyperlink Function Combined with Find
 

Thanks in advance to anyone offering assistance/suggestions to this problem.

Background: I am trying to embed the find function within my hyperlink destination address. Since I need to keep the code dynamic because the location of my information is always changing. I have a worksheet setup with a number of tables and headers. I have created a table of hyperlinks to easily access them, however, I cannot get the vba code to work.

Situation: Each header is unique, therefore, I need to combine a search function into the standard hyperlink vba.

Approach: In a nutshell, I want to be able to use find (or a variation of find) to locate my destination cell (which is a unique value), then pass the location (i.e.: B79) of the destination cell back to the hyperlink code within the sub-address section.

Here is how I have the VBA links structured.

I need to somehow combine the anchor with the find function.

So that it would ultimately read something like:

Any assistance would be greatly appreciated.

VBA - Hyperlink Function Combined with Find
 

hi,

assume that the destination address of the link on "sheet1: A1" is "sheet2: A1"

on the following code, if you changing the address of "Worksheets ("Sheet2").Range("A10")"
by your search result, you can go where you want.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Address = "$A$1" Then
Application.Goto Worksheets("Sheet2").Range("A10")
End If
End Sub

--
isabelle


Le 2012-09-06 10:01, Nalij a écrit :
Thanks in advance to anyone offering assistance/suggestions to this
problem.

Background: I am trying to embed the find function within my hyperlink
destination address. Since I need to keep the code dynamic because the
location of my information is always changing. I have a worksheet setup
with a number of tables and headers. I have created a table of
hyperlinks to easily access them, however, I cannot get the vba code to
work.

Situation: Each header is unique, therefore, I need to combine a search
function into the standard hyperlink vba.

Approach: In a nutshell, I want to be able to use find (or a variation
of find) to locate my destination cell (which is a unique value), then
pass the location (i.e.: B79) of the destination cell back to the
hyperlink code within the sub-address section.

VBA - Hyperlink Function Combined with Find
 

also you can use the same event for multiple links


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Select Case Target.Address(0, 0)
Case Is = "A1": Application.Goto Worksheets("Sheet2").Range("A10")
Case Is = "A2": Application.Goto Worksheets("Sheet2").Range("A20")
Case Is = "A3": Application.Goto Worksheets("Sheet2").Range("A30")
End Select
End Sub


--
isabelle



Le 2012-09-06 20:22, isabelle a écrit :
hi,

assume that the destination address of the link on "sheet1: A1" is "sheet2: A1"

on the following code, if you changing the address of "Worksheets ("Sheet2").Range("A10")"
by your search result, you can go where you want.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Address = "$A$1" Then
Application.Goto Worksheets("Sheet2").Range("A10")
End If
End Sub

VBA - Hyperlink Function Combined with Find
 

sorry, it's not "Target.Address(0, 0)" but "Target.Range.Address(0, 0)"


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Select Case Target.Range.Address(0, 0)
Case Is = "A1": Application.Goto Worksheets("Sheet2").Range("A10")
Case Is = "A2": Application.Goto Worksheets("Sheet2").Range("A20")
Case Is = "A3": Application.Goto Worksheets("Sheet2").Range("A30")
End Select
End Sub


--
isabelle


Le 2012-09-06 22:34, isabelle a écrit :
also you can use the same event for multiple links


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Select Case Target.Address(0, 0)
Case Is = "A1": Application.Goto Worksheets("Sheet2").Range("A10")
Case Is = "A2": Application.Goto Worksheets("Sheet2").Range("A20")
Case Is = "A3": Application.Goto Worksheets("Sheet2").Range("A30")
End Select
End Sub

VBA - Hyperlink Function Combined with Find
 

Nalij,

Try this:

Sub AddLink()
Dim sFind As String
Dim rDestCell As Range

sFind = "All Data, Vendor 1"

Set rDestCell = Cells.Find(What:=sFind, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)

If rDestCell Is Nothing Then
MsgBox "'" & sFind & "' not found."
Exit Sub
Else
ActiveSheet.Hyperlinks.Add Anchor:=Range("G6"), Address:="", SubAddress:= _
rDestCell.Address, TextToDisplay:=sFind
End If

End Sub

Hope it helps,

Ben

The problem is that I do not know what the range is going to be. I can define the following:

Case Is ="All Data, Vendor 1": Application.Goto Worksheets("Dataset").Range("?????")

That is why i need to combine this with the find function, so that once the cell with "All Data, Vendor 1" is identified (say B89) it can pass that to the vba code, so that it would read:

Case Is ="All Data, Vendor 1": Application.Goto Worksheets("Dataset").Range("B89")

The range is constantly changing and dynamic which is why i cannot hardcode the cell range - I really don't know what it will be.

So I figured out the solution. Instead of trying to use the find function. I made each header a defined range name then referenced that within the hyperlink

So now regardless of where the cell is within the worksheet, I will be able to hyperlink to it.