Opening Selected Hyperlink DISPLAYING TEXT in Default Browser in MS Excel

Question

I was able to find an answer to part of my problem and was able to open hyperlinks in a range of selected cells in Excel. BUT... It only worked if the hyperlink is showing and not hidden by "friendly text".

When I use the code below to select cells with hyperlinks (URLs) everything is great and they open in the default browser. What I would like is to be able to open Hyperlinks that is displaying "friendly text". . =HYPERLINK("http://jsvp/MoveImage.aspx?t=1&sku="&A13,A4). This shows the word "upload" in the cells.

=IF(LEN(A3)>5,"",HYPERLINK("https://supercms.company.com/ManageProducts/"&LEFT(A3,5),A3)) This displays a value from Cell A3.

I get a Run-time error '-2147221014 (800401ea)' Cannot open specified file.

Would there be a way open the URLs even if the text was hiding the Hyperlink (URL)?

Credit to: PETER ALBERT (https://stackoverflow.com/users/1867581/peter-albert?tab=profile) for his code.

Sub Open_SelectedTextlinks()
    Dim c As Range

    If Not TypeOf Selection Is Range Then Exit Sub
    For Each c In Selection.Cells
        If c.Hyperlinks.Count = 0 Then
            ActiveSheet.Hyperlinks.Add Anchor:=c, _
                Address:="http://" & c.Value 'Depending on the content of your cell, remove the "http://" & part
        End If
        c.Hyperlinks(1).Follow
    Next
End Sub

*Would there be a way open the URLs even if the text was hiding the Hyperlink (URL)?* If you can determine the cell clicked on, otherwise its going to be more trouble than its worth. `Dim r As Long... = ShellExecute(0, "open", Target.Value, 0, 0, 1)` google the PInvoke signature: https://stackoverflow.com/a/11715021/495455 You're better off restricting a minimal column width AND Wrap Cells. Don't even need a coding solution! — Jeremy Thompson, Jan 27 '22 at 05:52
Does this answer your question? [How to display desired text in hyperlink using VBA code](https://stackoverflow.com/questions/43087818/how-to-display-desired-text-in-hyperlink-using-vba-code) — user692942, Jan 27 '22 at 08:32
On the `ActiveSheet.Hyperlinks.Add` use `TextToDisplay:="Whatever you want"` to set the hyperlink text to whatever you want. The duplicate question should help. — user692942, Jan 27 '22 at 09:50

score 0 · Answer 1 · answered Jan 27 '22 at 04:30

0

Here Is Your Code

  Sub Open_SelectedTextlinks()
If Not TypeOf Selection Is Range Then Exit Sub
For Each c In Selection.Cells
    If c.Hyperlinks.Count = 0 Then

With Worksheets(1)
 .Hyperlinks.Add Anchor:=.Range("a5"), _
 Address:="https://example.microsoft.com", _
 ScreenTip:="Microsoft Web Site", _
 TextToDisplay:="Microsoft"
  Range("A5").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End With
    End If
Next

End Sub

answered Jan 27 '22 at 04:30

Jahanzaib Sehar

59
7

Resolved Your Issue – Jahanzaib Sehar Jan 27 '22 at 05:04
Hi, this appears to simply write a hyperlink to A5 and then follow it to Microsoft's site if any cell is selected, with a hyperlink or not. It's not following the URL that is in the hyperlink within the selected cells. – Limey Jan 27 '22 at 05:28
I'm not sure why it is showing resolved as It is not, I appreciate your effort Jahanzaib but I need to follow any and all selected hyperlinks and open them in the default browser. As stated in my question, that is what the code does that I posted but it fails when there is text showing in the link rather than the URL. – Limey Jan 27 '22 at 06:00
Simple Change Range To Activecell – Jahanzaib Sehar Jan 27 '22 at 06:29
Activecell.select Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True – Jahanzaib Sehar Jan 27 '22 at 06:30
I have tried what you suggested but it is not working. Could you possibly try placing a few URLs with "friendly text" in a sheet and See if you can get your code to open them all in separate windows. I know enough to figure some things out but I don't understand how your code works. Adding your active cell line didn't help. Thanks for your patience. – Limey Jan 27 '22 at 09:39
@Limey if you want each link to open in a new window set `NewWindow:=True`. In your code add `c.Hyperlinks(1).Follow Newwindow:=True`. – user692942 Jan 27 '22 at 09:46
@Jahanzaib Sehar I can't get it to work. Could you assume, for simplicities sake, that you have 5 Hyperlinks somewhere non-specific in you current worksheet. Let's say those hyperlinks are to your friend's websites so instead of URLs you are showing their names in the links. You select a random number of your friend's cells, lets say 3 of them. Now you click an icon that has your macro, VBA code attached to it. Your friend's web pages open up in your default browser (or for my situation, Safari handles it better than Chrome as the tabs are bigger). Could you post the code to do that? Thanks! – Limey Jan 27 '22 at 18:31
Sub LoopUntil1() Dim i As Integer i = 1 Do Until IsEmpty(Cells(i, 1)) Cells(1, 1).Hyperlinks(1).Follow (True) i = i + 1 Loop End Sub – Jahanzaib Sehar Jan 28 '22 at 05:19
Sorry @Jahanzaib Sehar, I'm totally lost. All the changes and suggestions and I haven't got a clue where you are going with it. Is anyone able to get the code to work and can post it as a working script? All code snippets aren't helping I'm afraid. – Limey Jan 28 '22 at 06:55

Opening Selected Hyperlink DISPLAYING TEXT in Default Browser in MS Excel

1 Answers1