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,

Cozmosis · Accepted Answer

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:&#92;workbook.xls", , False
Sheets(sht).Select
Range("A1").Select
Err:
End Sub



The macro looks for a file called C:&#92;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

Excel Hyperlink Macro

Prince H · Answer

Use the functions:

Workbooks.Open "c:&#92;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.

Excel macro to follow variable hyperlink in a cell?

Related Questions

Trending Questions