Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Excel macro to follow variable hyperlink in a cell?
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,
Thanks Prince H, but I get a "Compile Error: Invalid Use of Property" and it highlights the ".Sheets" part of the code.
3 Answers
- CozmosisLv 71 decade agoFavorite 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:\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.
Source(s): Is Workbook Open/Workbook Exists/Worksheet Exists http://www.ozgrid.com/VBA/IsWorkbookOpen.htm - 1 decade ago
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.
