Macros and Hyperlinks

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
823
I have a hyperlink in Cell A4 [ =HYPERLINK(B4,E4) ] I can click on it and it opens the webpage just fine.
I would like to have a macro open the web page but I am having no luck trying to figure out from Microsoft Visual Basic Help how to do that.

It's interesting, hyperlinks copied directly in from the web like the one I have in Cell J4 opens with a macro I recorded just as nicely as you please:

Code:
Range("J4").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
But change that to:

Code:
Range("A4").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=False
in order to open my formula generated link, and it crashes.

So I need help
 
Don't use excel to hide your internet fantasies...

Code:
AddHistory:=False

oh yes, I made myself laugh...

The code works for me. Just to be sure, you actually have a hypelink in A4?

EDIT:

The reason I ask that is because the both macros activate a different cell.
 
Last edited:
Upvote 0
Cell A4 is a formula cell [ =HYPERLINK(B4,E4) ] and it's not the same as a Hyperlink.

From the formula in cell A4, this will extract the cell reference (B4) that has the text of the link. It then calls the B4 text as a link.

Code:
    Dim Link As String
    Link = Range(Mid(Range("A4").Formula, 12, InStr(Range("A4").Formula, ",") - 12)).Value
    ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True

If you already know B4 has the link text, you can just call it using...
Code:
ActiveWorkbook.FollowHyperlink Address:=Range("B4").Text, NewWindow:=True
 
Upvote 0
Don't use excel to hide your internet fantasies...

Code:
AddHistory:=False
oh yes, I made myself laugh...

The code works for me. Just to be sure, you actually have a hypelink in A4?

EDIT:

The reason I ask that is because the both macros activate a different cell.

Cell A4 has a hyperlink formula and J4 has a link copied directly from the
internet. I tried the true and false tags on both and they both work on Cell
J4 and the both don't work on Cell A4.

On an earlier thread today, I explained that on my copy of Excel 2000 and
Windows XP typing in http://www.bla bla... doesn't generate a hyper link.

So thanks for the ha ha funny and the bad news that the code works on
your set up. Um you did try the code on a formula generated hyperlink?
 
Upvote 0
Cell A4 is a formula cell [ =HYPERLINK(B4,E4) ] and it's not the same as a Hyperlink.

From the formula in cell A4, this will extract the cell reference (B4) that has the text of the link. It then calls the B4 text as a link.

Code:
    Dim Link As String
    Link = Range(Mid(Range("A4").Formula, 12, InStr(Range("A4").Formula, ",") - 12)).Value
    ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
If you already know B4 has the link text, you can just call it using...
Code:
ActiveWorkbook.FollowHyperlink Address:=Range("B4").Text, NewWindow:=True

I tried inserting B4 into the code, didn't work, it's just text, no blue no underline. But I will try your Dim Link as String stuff, and I will be right back.
 
Upvote 0
Cell A4 is a formula cell [ =HYPERLINK(B4,E4) ] and it's not the same as a Hyperlink.

From the formula in cell A4, this will extract the cell reference (B4) that has the text of the link. It then calls the B4 text as a link.

Code:
    Dim Link As String
    Link = Range(Mid(Range("A4").Formula, 12, InStr(Range("A4").Formula, ",") - 12)).Value
    ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
If you already know B4 has the link text, you can just call it using...
Code:
ActiveWorkbook.FollowHyperlink Address:=Range("B4").Text, NewWindow:=True

Yes it worked!!

T H A N K S

So now I really should be able to make the rest of this thing go. I'm going to have to get Cells A4 through Cell A1300 or so to call up the links one at a time in sequence.

But I'll probably be back.
 
Upvote 0
This macro will put an actual hyperlink in column A (replaces the =HYPERLINK formulas)

If goes from A4 to the last used cell in Column A.
It uses the column B text as the link address and the column E text as the display text.
Code:
Sub Convert_To_Hyperlinks()
    Dim Cell As Range
    For Each Cell In Range("A4", Range("A" & Rows.Count).End(xlUp))
        If Not IsEmpty(Cell) Then
            ActiveSheet.Hyperlinks.Add Cell.Offset(, 1).Value, Cell.Offset(, 4).Value
        End If
    Next Cell
End Sub

You could then use your original code to call the link...
Code:
Range("A4").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=False
 
Upvote 0
Corrected code
Code:
Sub Convert_To_Hyperlinks()
    Dim Cell As Range
    For Each Cell In Range("B4", Range("B" & Rows.Count).End(xlUp))
        If Not IsEmpty(Cell) Then
            ActiveSheet.Hyperlinks.Add Anchor:=Cell.Offset(, -1), _
                                        Address:=Cell.Value, _
                                        ScreenTip:=Cell.Offset(, 3).Value, _
                                        TextToDisplay:=Cell.Offset(, 3).Value

        End If
    Next Cell
End Sub
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top