Place a hyperlink on multiple cells with text in Microsoft Excel

[caykamanj1966]

I have 3 cells with text (Text Here) in them (Cell A1, D5 & F8).

I want to use a command button to change "Text Here" to the following hyperlink:

http://www.johndoe.com,

to add that URL for the text in A1, D5 & F8.

Can someone please tell me an easy way to do this?

Thanks so much in advance!

 

[Andrzejek]

Do you want to "change "Text Here" to the [...] hyperlink" or do you want to "add that URL for the text"?
These are 2 different requests.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[caykamanj1966]

I want the "Text Here" for the three cells to become a hyperlink, which will have that URL. So the end result would be, when clicked on Text Here, it will take me to that URL.

Sorry for not being more clear.

 

[Andrzejek]

In VBA you would do:

ActiveSheet.Hyperlinks.Add ActiveSheet.Range("[red]D5[/red]"), "[blue][URL unfurl="true"]http://www.johndoe.com[/URL][/blue]", , , "[blue]Text Here[/blue]"

A 'non-VBA' solution: Insert - Link

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[caykamanj1966]

Thanks Andrzejek!!!

That worked beautifully, but I see now I need a modification.

So this is what I pasted in:

ActiveSheet.Hyperlinks.Add ActiveSheet.Range("DI3:DO8"), "[URL unfurl="true"]http://www.johndoe.com",,,[/URL] ""

So actually this will need to be done over several cells as you can see from the range, but some of those cells are empty. So if any cells are empty, I don't want that cell to have a hyperlink.

Also, there is different text in each cell, but they will have the same hyperlink, so as you can see where I had "Text Here", I just left that part blank.

Is there anyway to not have the hyperlinks on the blank cells?

The bad thing about this, it is a running sheet of information, that will have a lot of cells with past months information (for the same year), which needs to be kept on the same sheet. With that being said, we would have to go into the code and change the range, everytime we update this information with the current information.

Maybe I will talk with my management, to see if we can reformat this excel spreadsheet, to put past months on different tabs and that way we would not have to update the code everytime.

So for now again, is there anyway to not have the hyperlinks on the blank cells?

 

[Andrzejek]

Looks to me you need to loop thru all columns/rows of data where you may have "Text Here" and that's where you need to do [tt]Hyperlinks.Add[/tt]

Without actual data it is hard to give you specific code, which you should ask for in forum707 ...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Simply COPY the URL string like www.tek-tips.com and PASTE into each cell.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

need to be done over several cells as you can see from the range [Columns DJ to DO], but some of those cells are empty. So if any cells are empty, I don't want that cell to have a hyperlink.

Also, there is different text in each cell, but they will have the same hyperlink, so as you can see where I had "Text Here", I just left that part blank.

Option Explicit

Sub caykamanj1966()
Dim intCol As Integer
Dim intRow As Integer

For intCol = 113 To 119     [green]'Columns DJ to DO[/green]
    For intRow = 3 To 8
        If Cells(intRow, intCol).Value <> "" Then
            ActiveSheet.Hyperlinks.Add ActiveSheet.Range(Cells(intRow, intCol), Cells(intRow, intCol)), "[URL unfurl="true"]http://www.johndoe.com",[/URL] , , "'" & Cells(intRow, intCol).Value
        End If
    Next intRow
Next intCol

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[caykamanj1966]

Thanks Andrzejek!

Now I am trying to figure out how to run the code you just provided.

Do I add a command button, and put that code behind it?

 

[Andrzejek]

How did you run the line of code I provided before? You said: "That worked beautifully, but I see now I need a modification." So, I assume you were able to run it somehow...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson