Run VBA hyperlink module on open

[Brent E]

This is a cross post, but appears this group is more active.

I have a list of URLs in text format on Col E of a spreadsheet (for example:
http//google.com but in text, not as a link.) Data starts at Row 10.

Upon open, I am trying to use the following VBA module to select all data in
the col from E10 to end of col, then copy the URL text and convert them to
links upon opening the file.

Option Explicit
Sub Autpen()
'Select cells in column from Row 10 to End of Col. Alter Letter to
adjust column
Range("E10:E" & Cells(Rows.Count, "e").End(xlUp).Row).Select

'Make Links from URLs
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
End Sub

The code selects E10 but does not create a link from the text and does not
cycle thru the range. I placed a stop point before the For statement and the
range was not selected.

Any suggestions?

Thanks,

 

[Bernie Deitrick]

Brent,

Your code worked fine for me.

One thing that may be causing a problem is your use of xCell.Formula - try
using xCell.Text instead. Also, you don't need to select anything to work on
it:

'Find cells in column from Row 10 to End of Col.
'Alter Letter to adjust Column
Dim myRow As Long

myRow = Cells(Rows.Count, "e").End(xlUp).Row

'Make Links from URLs
For Each xCell In Range("E10:E" & myRow)
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Text
Next xCell


HTH,
Bernie
MS Excel MVP