VBA insert dynamic hyperlink using cell value

nryan

Board Regular
Joined
Apr 3, 2015
Messages
61
Hello. I'm trying to insert hyperlinks into a table using VBA.
I have values in column A (starting at "A5" because of headers). Each of those values are a suffix of a web address.
I want to insert a hyperlink in each cell using a web address ("www. website .com/") and the cell value so the link is www. website .com/"cell A value".

I looked at a few other posts to help me get started:
1. Use VBA to hyperlink based on cell text (Mr. Excel)
2. Adding Hyperlinks based on cell contents (Mr. Excel)
3. VBA to create Hyperlink including cell value (Mr. Excel)

I tried dropping the code from these posts in my workbook but they didn't work for me. The first link almost worked but stopped after the first row and I couldn't figure out how to get it to work for all the other rows.
Here's what my table looks like (couldn't figure out how to shrink column width):
rowA
1Table info stuff
2More table info
3
4Header
5100445.1
6100552.1
7100556.3
8100652.1
...

My code:
VBA Code:
Sub createLink()

    Dim x As Integer
    Dim LastRow As Integer
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    With ThisWorkbook.Worksheets("Sheet2").Activate
        For x = 5 To LastRow
            Cells(x, "A").Activate
            .Hyperlinks.Add Anchor:=ActiveCell, Address:="www.website.com/" & ActiveCell.Text, TextToDisplay:=ActiveCell.Text
        Next x
    End With

End Sub

I'm getting a run-time error (424, object required) at the .Hyperlinks.Add line that I cannot figure out how to fix.
I checked my variables and syntax and they look okay to me. In the debugger I see that my variables are showing up (see image below).
vba hyperlink snip.PNG

I've been troubleshooting for a few hours with no luck.

Important note: My table is a database table I created using power query. I suspected this was maybe causing problems so I created a mock table on another sheet that is not a power query table, just the column "A" data I copied over. My code has the same error on both tables, power query and non-power query. So maybe it's trivial.

Thank you for reading.
-Nick
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
142
Office Version
  1. 2010
Platform
  1. Windows
Hello!
Try to change the line
VBA Code:
With ThisWorkbook.Worksheets("Sheet2").Activate
with
VBA Code:
With ActiveWorkbook.Worksheets("Sheet2")
 

nryan

Board Regular
Joined
Apr 3, 2015
Messages
61
Hi Lazybug. That change did the trick!!
Thank you.
Seems so basic of a thing to get wrong on my end. I'm glad you could help me.
 

nryan

Board Regular
Joined
Apr 3, 2015
Messages
61
Update.
In the Power Query table this code works! But after clicking one of the hyperlinks all hyperlinks are "deactivated" meaning they don't work anymore. Maybe the power query table updates and erases the hyperlinks. I'm not sure. I found a workaround by placing the code in the worksheet instead of in a module. Code in the worksheet:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim x As Integer
    Dim LastRow As Integer
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    With ActiveWorkbook.Worksheets("Sheet1")
        For x = 5 To LastRow
            Cells(x, "A").Activate
            .Hyperlinks.Add Anchor:=ActiveCell, Address:="www.website.com/" & ActiveCell.Text, TextToDisplay:=ActiveCell.Text
        Next x
    End With

Range("A3").Select  'Useful for long tables. Takes you back to the top of the table.

End Sub

Thanks again LazyBug.
 

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
142
Office Version
  1. 2010
Platform
  1. Windows
Unfortunately PQ isn't my strong point, so can't help with this question. I don't understand your goal by the hyperlinks adding with every Worksheet_Change event, but i'm glad if it works for you. Thanks for the feedback,
 

Watch MrExcel Video

Forum statistics

Threads
1,113,888
Messages
5,544,874
Members
410,642
Latest member
Launayvolone
Top