?
Solved

Convert selected cells to web address link

Posted on 2011-03-10
6
Medium Priority
?
288 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

770 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