0

I am trying to create a process that, through the use of a button and a drop down table, allows a user to load a PDF file associated with a different report. I have created a table with two columns, one column for storing the name of the report and one containing the appropriate PDF link.

In testing, I created a macro that simply selected a cell and then clicked on the hyperlink. The code for this particular Macro is as follows

Sub PDFLinkLaunch()
Range("B2").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub

This code works fine, as expected.

I then carried on with the project and created a cell which, using the following equation in Excel, contained the Hyperlink associated with the contents of the drop down menu.

=HYPERLINK(VLOOKUP(L17,Table1,2,0))

This also appears to work fine as it produces the appropriate hyperlink within the cell.

I therefore created a new macro with the intention of doing the same thing as the test code for this new cell, containing the hyperlink found using the VLOOKUP and the code for that Macro is as follows.

Sub LaunchHyperlinkMacro()
Range("L18").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub

However, this Macro appears to not work and produces the following error message:

Run time error 9, Subscript out of range

Does anybody here know how to fix this problem? If not but they are aware of an alternate solution to this problem, that would also be greatly appreciated.

braX
  • 11,506
  • 5
  • 20
  • 33
Ben Deacon
  • 1
  • 4

1 Answers1

0

VBA is unable to recognize the HYPERLINK formula as an actual hyperlink.

Instead you could try using the FollowHyperlink method.

Sub LaunchHyperlinkMacro()
     Range("L18").Select
     ActiveWorkbook.FollowHyperlink Address:=Selection.Value, NewWindow:=False, AddHistory:=True
End Sub

Which I expect would work in your case.

Netloh
  • 4,338
  • 4
  • 25
  • 38