Hyperlink Address Partly On Cell Content

  • Hi all,

    -> In Excel Cell L4 to L9999, I would like to have Excel have that cell Hyperlinked of a Google search of the contents which were entered in that cell. (or, if that same cell can't be Hyperlinked, then a neighboring cell is OK)

    -> So, for example, if cell L4 contained the word: "testing",
    I would like to be able to click on that text in the cell to open a browser and take me to: "http://www.google.com/search?q=testing"

    ...I hope this can be done without writing a macro (maybe by a formula instead?) BUT, if it's the only way of doing it, then yes, please, in that case, I would LOVE to know how to write/enter the macro.

    Thanks very much,

  • Re: Cell Hyperlinks Based On Cell Contents

    Thanks AAE! That's it! I was able to put it in the next column over to make the link...

    By any chance, would you or anyone else know how to do it with a Macro?

    (I'm sorry--I know I asked for preferably a formula, but I realized it takes up too much space when printing.) :( A macro should make this work, by just making that text added in L4 simply a hyperlink.

  • Thanks VBA Noob,


    Cross Post!

    Please read this page RULES YOU AGREED TO in full. Rule 16 clearly spells out what you agreed to in terms of Cross Posting. After you have done so please ClickHere and Private Message me or one of the other Moderators: Richie(UK);Dave Hawley with your acceptance, or non-acceptance of the rules. Please also include a link to this Thread. You can copy this directly from the Address bar of your Browser. When this is done your thread will be re-opened.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]OP now understands and agrees to the rules on cross posting. Thread unlocked.

  • Re: Cell Hyperlinks Based On Cell Contents


    Apologies all--

    I have skimmed over the agreement the first time around, reading about "crossposting", but thinking it only meant not to flood all the channels of this same web site. I'm not the type of person who would have done that anyway. However, now that I know what crossposting is, if I post another question on this web site, I will now wait 48 hours before asking elsewhere. Apologies to anyone I might have offended by asking the same question somewhere else on the Internet.
    I've noticed that the links are posted above, so I shouldn't flood this thread with the same links (as it's getting pretty long already), so please go there, or look below at someone else's post for the answer or look for my post with the answer from elsewhere. Unfortunately I have not received an answer as of yet, but if I do receive an answer elsewhere, I will copy the answer to this web site as well to make it easier for someone else who may be looking for the answer in this thread.

    *back on-topic below:

  • Re: Cell Hyperlinks Based On Cell Contents

    My understanding is you want to type something in the L column and the have code that turns it into a hyperlink for a google search labeled as "Click Here"

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 12 And Not Target.Value = "" Then
            Application.EnableEvents = False
            Hyperlinks.Add Target, Address:="http://www.google.com/search?q=" & Target.Value, TextToDisplay:="Click Here"
            Application.EnableEvents = True
        End If
    End Sub
  • Re: Cell Hyperlinks Based On Cell Contents [solved!]

    As promised, here is the answer to the question. I'm unable to link to their web site since that thread has been closed, but I'd like to thank Corine Reyes for the answer!

    Private Sub Worksheet_Activate()
    Dim stext As String
    stext = Worksheets("Sheet2").Range("C2")
    With Worksheets(2)
       .Hyperlinks.Add .Range("B2"), "http://www.google.com/search?q=" & stext
    End With
    End Sub

    In sheet 2, enter the following:
    B2 = google search
    C2 = Test (or any word you wish)

    Save the workbook, when sheet2 is activated, the above code runs.

  • Re: Cell Hyperlinks Based On Cell Contents

    Thanks Reafidy!

    Actually I like yours even better than the first, because you can use it in any sheet (the other one was limited to one sheet at a time). Still, I'd like to thank AAE and Corine for their help! (And I'll have to admit, while I apologize for my screwup, that I'm surprised as many came to help, and within the same day actually. After all this, you can be sure I'd only need to connect to Ozgrid in the future.) Links are already pointing here from the other two sites, but I will also give you credit on the other sites and will post your answer there as well.

    Thanks again,

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!