HYPERLINK() function not working with VBA .Follow

Babolat25

New Member
Joined
Mar 12, 2020
Messages
2
Office Version
365
Platform
MacOS
Hello,
I'm not a power user but I tried to google my problem for hours and couldn't find an answer so I landed here seeking for help.

The situation
I have made a URL with CONCAT function that looks something like https://blablabla.com/date/something/variables. In another cell I used HYPERLINK() to get a link out of that. With hundreds of these entries my goal is to run a VBA macro that opens in new tabs the ones in selected cells:
VBA Code:
Sub OpenLinks()
    Dim a As Hyperlink
    For Each a In Selection.Hyperlinks
        a.Follow
    Next a
End Sub
The problems
1) HYPERLINK()
What I get is a text formatted as an hyperlink that works if I click on it but is not recognized as hyperlink by the macro. The only links opened by the macro are the ones I copy from the browser. Is it normal? What can I do?

2) Copy-Paste a list of links
A secondary problem I encountered while testing a fix: if I copy a single link into excel it's correctly seen as a link but if I copy a list of links they're pasted as text. I couldn't find a way to paste them as link. Can you help me with that too?

Thanks in advance,
Andrea
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,758
Office Version
365, 2019, 2016
Platform
Windows
The 'Hyperlink' function isn't the same as actually adding a hyperlink that can be referenced in the way you are doing above.

Something like this seems to work. This loops through each cell in the selection. Adjust as needed.

VBA Code:
Sub OpenLinks()
Dim cel As Range
For Each cel In Selection
    ActiveWorkbook.FollowHyperlink cel.Value
Next cel
End Sub
 

Babolat25

New Member
Joined
Mar 12, 2020
Messages
2
Office Version
365
Platform
MacOS
Thank you very much.
When I run it the first link is opened correctly and then it pops-up "runtime error 5: invalid procedure call or argument" on this line "ActiveWorkbook.FollowHyperlink cel.Value". What should I change?

I'm sorry if it's an obvious adjustment but I'm new to vba coding.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,858
Messages
5,465,104
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top