Solved

Convert selected cells to web address link

Posted on 2011-03-10
6
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

724 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