Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 15th, 2007, 02:35 PM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to create a Hyperlink based on Cell Contents

Hi all,

  I've tried searching the forums for an answer, but had no luck. Will try my luck posting...and maybe it'll also help someone else as well:

-> In Excel Cells 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 SO much for your help!!
CG
 
Old October 15th, 2007, 03:25 PM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Update: I'm sorry... I realized this needs to be done via a macro, NOT a formula.

Would anyone know how to do this? ...I'm hoping for some kind of macro that will convert the text to a link (preferably converts on-the-fly, but if not, that's OK)


Thanks again. -CG
 
Old October 15th, 2007, 05:18 PM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

FYI - I'm still hoping for an answer :(, if anyone happens to know. Thank you!

  I have also posted this question on another web site (which I have later learned is called "Crossposting" and I PROMISE to post the answer here if I get the answer on another web site--I did not mean to break any rules, and will right the wrong as soon as I get the answer). In the interest to not break any NDA rules, if I get an answer, I will not post the name of the web site that I got the answer from, but I will post the answer here.
 
Old October 15th, 2007, 09:39 PM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The following is the answer for anyone else that may also need help with the code. Thanks for the help, Reafidy! -cg

Reafidy's code is as follows:
Code:
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
 
Old October 15th, 2007, 09:43 PM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Here you have CG
Sub Create_HyperLinks_To_Google()

Dim i1 As Integer
Dim sA As String

For i1 = 4 To Range("L:L").Cells.SpecialCells(xlCellTypeLastCell ).Row
    sA = Trim(Range("L" & CStr(i1)).Text)
    ActiveCell.Hyperlinks.Add Range("L" & CStr(i1)), "http://www.google.com/search?q=" & sA & ""
Next i1


End Sub

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)




Similar Threads
Thread Thread Starter Forum Replies Last Post
Pointer to contents of another cell swood829 Excel VBA 2 June 14th, 2005 03:51 PM
How to compare cell contents with a character? danwes Excel VBA 5 June 8th, 2005 11:36 AM
Accessing the contents of a hyperlink column in a rosalynb VS.NET 2002/2003 2 October 1st, 2003 06:33 PM
Accessing the contents of a hyperlink column in a rosalynb ADO.NET 0 September 29th, 2003 01:10 PM
Delet contents of a single cell marmer Classic ASP Basics 1 August 27th, 2003 08:14 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.