How to Open a Hyperlink with VBA Button

Question

How to Open a Hyperlink with VBA Button

Anonymous

Hello,

I have a worksheet with a button "Materials" that corresponds with a code entered in a cell (C9). When the user clicks "Materials", a userform opens with different buttons. I'd like the user to click a button on the userform, labeled "Brochure" and it opens the brochure that corresponds to the code entered in cell C9. I have a VLOOKUP that returns the correct hyperlink to the sheet (In cell, AO20), but I can't figure out how to make the "Brochure" button open that hyperlink??

Any help would be appreciated!

JP

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous

to the best of my knowledge, the hyperlink worksheet function does not create a hyperlink that can be accessed by VBA. So you would "go to the source" so to speak:

Private Sub BrochureBtn_Click()

Dim Test As String

Test = Application.VLOOKUP(Range("C9").value,worksheets("Data").Range($A:$BS"),71,FALSE)

ActiveWorkbook.FollowHyperlink Address:=Test, NewWindow:=True

End Sub

Again, assuming your vlookup function returns a string that can be used as the address argument.

--

Regards,

Tom Ogilvy

0 comments

5 additional answers

Answer 1

Thanks Tom,

"Test" is the variable that equates to the worksheet cell that contains the VLOOKUP formula that brings back the URL for the correct PDF brochure.

In Cell AO20 I have:

=IFERROR(HYPERLINK(VLOOKUP($C9,Data!$A:$BS,71,FALSE),"Link"),"")

So If the user types in 1234 in cell C9, the "Link" in cell AO20 would become a hyperlink to the "1234" Brochure.

My thought was to do it this way, so I could have my VBA always reference cell AO20, as it could always be a different brochure.

I hopes this helps and thank you for taking the time to help, as I am still pretty new at VBA

JP

Answer 2

Sure I have some thoughts.

What is the value of "Test"?

If it isn't a valid address argument for a hyperlink, then you would get an error.

You said "I have a VLOOKUP that returns the correct hyperlink to the sheet (In cell, AO20)"

so I assume you are building a URL to a file or site that displays the brochure.

If you have a sheet in your workbook that you call a "brochure" then you don't need to follow a hyperlink. You just need to activate the sheet. So you haven't stated explicitly how one gets to "brochure" or what is in AO20.

I provided you a vba command that executes a hyperlink address. You have to make sure you are passing the command an argument that it can execute. If Test as written would hold an valid address for a hyperlink, then I would expect the command to work. Since the command is not working, I suspect Test does not contain a valid address.

--

Regards,

Tom Ogilvy

Answer 3

Anonymous

Thanks Tom,

I am currently using the code below and am getting an Invalid procedure error? Any thoughts?

Private Sub BrochureBtn_Click()

Dim Test As String

Test = Sheets("Sheet1").Range("AO20")

ActiveWorkbook.FollowHyperlink Address:=Test, NewWindow:=True

End Sub

0 comments

Answer 4

In the button code you could have something like:

ActiveWorkbook.FollowHyperlink Address:="http://example.microsoft.com"

(this command takes additional arguments which could provide greater control - see help for details)

where you could possibly pickup the arguments from clel AO20.

--

Regards,

Tom Ogilvy

Share via

How to Open a Hyperlink with VBA Button

5 additional answers