Solved

Convert selected cells to web address link

Posted on 2011-03-10
6
283 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

772 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