Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. Code:
Range("G6").Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "'Executive Summary - Charts'!A10" Code:
Cells.Find(What:="All Data, Vendor 1", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Code:
Range("S6").Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ Cells.Find(What:="All Data, Vendor 1", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Last edited by Nalij : September 6th 12 at 03:04 PM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
|
|||
|
|||
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. Quote:
|
#7
|
|||
|
|||
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
Code:
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "All_Data_All_Vendors", TextToDisplay:="Vendor1" Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More on Combined IF Function | Excel Worksheet Functions | |||
Find earliest date combined with status | Excel Discussion (Misc queries) | |||
lookup combined with search to find keywords and then assign a cat | Excel Programming | |||
vlookup combined with AND-function | Excel Worksheet Functions | |||
Find min/max in 2 combined columns | Excel Worksheet Functions |