Solved

Convert selected cells to web address link

Posted on 2011-03-10
6
282 Views
Last Modified: 2012-05-11
In a sheet, there are cells contain something link "mydomain.com", "mydomain.info", "mydomain.org"...
Once I select them, I want to run a VBA that will convert the text to hyperlinks "http://www.mydomain.com", but keep its original formatting and without showing "http://www."
0
Comment
Question by:mmcompact
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 35097885
Try this:


Sub AddHyperlink()
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "http://www." & ActiveCell.Value, TextToDisplay:=ActiveCell.Value
End Sub
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 35097905
Without using VBA, you can right click the cell and then Insert a hyperlink.

In your example, mydomain.org you can type in the hyperlink address box as www.mydomain.org

Sincerely,
Ed
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35097939
If you want to select more than one and apply it, try this instead:


Sub AddHyperlink()
    Dim objCell As Range
    For Each objCell In Selection
        ActiveSheet.Hyperlinks.Add Anchor:=objCell, Address:= _
            "http://www." & objCell.Value, TextToDisplay:=objCell.Value
    Next
End Sub
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:mmcompact
ID: 35098653
rspahitz:

your solution works, but it changed the text formatting to system default. Can I keep the original formatting, like font, font size, no underline etc...
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 35099009
I don't know any easy way to restore the original font information, but this covers a lot of the parts that you might want (check the Dim statements for the different settings I save and restore; if it's missing one, let me know and I'll include it.)


Sub AddHyperlink()
    Dim objCell As Range
    Dim strFontName As String
    Dim dblFontSize As Double
    Dim objFontColor As Long
    Dim bFontBold As Boolean
    Dim bFontItalic As Boolean
    Dim lFontUnderline As Long
   
    For Each objCell In Selection
        With objCell.Font
            strFontName = .Name
            dblFontSize = .Size
            bFontBold = .Bold
            bFontItalic = .Italic
            lFontUnderline = .Underline
            objFontColor = .Color
        End With
       
        ActiveSheet.Hyperlinks.Add Anchor:=objCell, _
            Address:="http://www." & objCell.Value, _
            TextToDisplay:=objCell.Value
           
        With objCell.Font
            .Name = strFontName
            .Size = dblFontSize
            .Bold = bFontBold
            .Italic = bFontItalic
            .Underline = lFontUnderline
            .Color = objFontColor
        End With
    Next
End Sub
0
 

Author Comment

by:mmcompact
ID: 35099108
cool, thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now