Excel macro to follow variable hyperlink in a cell?

Question

The hyperlink below is in a cell (D4) and takes the user to a particular worksheet based on a selected drop-down. I can't find any VBA code that will allow me to follow this variable hyperlink, only static hyperlinks. I don't want the user to have access to the formula, but want to assign it to a button via a macro. Does anyone know the code that can do this?

=HYPERLINK("[workbook.xls]"&
VLOOKUP(B4,data!B2:H148,7,)
&"!A1","GO")

Thanks,

Answer 1

Try this...





Sub MyLink()

'

' MyLink Macro

' Macro recorded 1/8/2009

Dim sht As String

On Error GoTo Err



sht = Application.WorksheetFunction. _

VLookup(Range("B4"), Sheets("data"). _

Range("B2:H148"), 7)



Workbooks.Open "C:\workbook.xls", , False

Sheets(sht).Select

Range("A1").Select

Err:

End Sub







The macro looks for a file called C:\workbook.xls

You may have to change the path and file name.



If your file doesn't exist or if the sheet doesn't exist then the macro just exits. You could add more sophisticated error checking. See the link below.

Answer 2

Excel Hyperlink Macro

Answer 3

Use the functions:



Workbooks.Open "c:\workbook.xls"

ActiveWorkbook.Sheets("DesiredSheet").Activate

ActiveSheet("A1").Select



You can put the vlookup function on a hidden cell, and change "DesiredSheet" based on that cell.