[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Convert selected cells to web address link

Posted on 2011-03-10
6
Medium Priority
?
289 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
Industry Leaders: 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 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

649 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