Excel VBA Cells and Hyperlinks

I track some information in Excel, and one of the columns is for a link to some location (blogs, Web sites, etc.). One of the things is that I don't want the URL to appear as it creates display issues (as shown here).

I really want it to look like this:

I prefer to just display the word "Link" that is marked up with the actual link. Problem is-it's time consuming to go through and do this to many dozens of links manually as I work along. It's way faster for me to write the following lines of VBA and quickly solve the problem. Now, I keep adding rows and just re-run the macro periodically to dress it all up.

    Private Sub ConvertToLink()

        Dim oCell As Range

        Dim iRow As Integer

        Dim iCount As Integer

        iRow = Cells.SpecialCells(xlLastCell).Row

        For iCount = 2 To iRow

            oCell = Cells(iCount, 5)

            If oCell.Text <> "Link" And Len(oCell.Text) > 0 Then

                oCell.Hyperlinks.Add(oCell, oCell.Text, , , "Link")

    End If

        Next iCount

    End Sub

Rock Thought of the Day:

I posted the best 10 rock albums of 2009 with commentary here: https://bit.ly/5hRSOw. The top 3 are these:

Modest Mouse: The Moon & Antarctica

U2: No Line On The Horizon

Iggy Pop: Préliminaires

Rock On

Technorati Tags: VBA,Office 2010,Excel,Office,Macro,Microsoft,Developer

Comments

• Anonymous
  January 08, 2010
  How about something simpler and automated like:

---

Private Sub Worksheet_Change(ByVal Target As Range)   Dim islink As Long   islink = InStr(Target.Text, "http://") + InStr(Target.Text, "www.") + InStr(Target.Text, "https://")   If islink >= 1 Then        Target.Hyperlinks.Add Target, Target.Text, , , "Link"   End If End Sub

• Anonymous
  January 08, 2010
  Thanks, Pranav. Your solution is great. One of the things I love about programming in Office is that one can quickly scrap together some code to just "get the job done" and then improve it over time. Yours is a definite improvement. You would need to make one adjustment: my code will work no matter what kind of link it is. For example, if it is a mailto address, my code will work the same. For yours to accomodate this, you'd make one small adjustment by adding it to your string search statement. Keep up the great work!