Restoring hyperlinks globally??

  • Thread starter Thread starter StargateFan
  • Start date Start date
S

StargateFan

A friend in a yahoo group just wrote that she recvd a workbook full of
hyperlinks that don't work. It's as if someone removed them or
something.

The only way I know how to do this is to copy the link and right-click
in that cell and then to choose hyperlink from the context menu and to
paste that link into the appropriate box in the "Insert hyperlink"
dialogue.

But was hoping someone knew of a way to make _all_ the links active
all at once without doing the above?

Thanks.
 
It sounds like the cells still have the hyperlink style, but there's no
hyperlink associated with them.

Depending on what's in the cell, I'd use a helper column and a worksheet
function:

=hyperlink(a1,"ClickMe")

or

=hyperlink("http://" & a1, "clickme")

I've had better luck with the worksheet formula version instead of using the
insert|hyperlink.

But here's a macro that does that Insert|hyperlink against the current
selection:

Or you could use a macro to add them in place:

Option Explicit
Sub MakeHyperlinks()

Dim myCell As Range
Dim myAddr As String
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "nothing found"
Exit Sub
End If

For Each myCell In myRng.Cells
myAddr = ""
If InStr(1, myCell.Value, "@") > 0 Then
If LCase(Left(myCell.Value, 7)) <> "mailto:" Then
myAddr = "mailto:" & myCell.Value
Else
myAddr = myCell.Value
End If
ElseIf LCase(Left(myCell.Value, 5)) <> "http:" Then
myAddr = "http://" & myCell.Value
Else
myAddr = myCell.Value
End If

If myAddr <> "" Then
With ActiveSheet
.Hyperlinks.Add Anchor:=myCell, _
Address:=myAddr, _
ScreenTip:=myAddr, _
TextToDisplay:=myCell.Value
End With
End If
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
It sounds like the cells still have the hyperlink style, but there's no
hyperlink associated with them.

Depending on what's in the cell, I'd use a helper column and a worksheet
function:

=hyperlink(a1,"ClickMe")
Click to expand...

This one worked just great for me on a test file! Thanks. Very neat
trick.

I posted it over to the yahoo group. I know that's going to help a
ton of people with their Excel files where they have all the URLs for
tuts, and resources for our graphics program.

Thanks on my and their behalf!
or

=hyperlink("http://" & a1, "clickme")

I've had better luck with the worksheet formula version instead of using the
insert|hyperlink.

But here's a macro that does that Insert|hyperlink against the current
selection:

Or you could use a macro to add them in place:
Click to expand...

I'll definitely keep this macro in mind for cases where I'd be
tampering with a file that couldn't have an extra column added.

Cheerio!
 
This one worked just great for me on a test file! Thanks. Very neat
trick.
Click to expand...

[snip]

Dave, hi!

I posted this over to the yahoo graphics group yesterday and got a lot
of positive responses. The group thanks you!

But there was one person that had trouble with this. She says:
"For some reason it won't work on mine.
I paste it and it shows the "click here" but when I
click it I get this " Cannot open the specified file "
anyone have any ideas why thats happening?"

Someone else suggested that the problem might lie with the version,
though he wasn't sure. He went on to mention that on his Excel 2003
this worked but not on Excel 98. I have Excel 2000 and this code
worked just fine on it, too.

Any ideas why she gets that message, by any chance, without knowing
what version she has just yet?

Thanks so much!
 
Dave Peterson gave some great code for making hyperlinks. If the URL
is in column column A then one could put this in column B:

=hyperlink(a1,"ClickMe")

This works fine for me and for several other people. But a lady in
our yahoo group came back to say that:

"For some reason it won't work on mine. I paste it and it shows the
"click here" but when I click it I get this " Cannot open the
specified file " anyone have any ideas why thats happening?"

She just told me she has Excel 2000, too.

Anyone know what I can tell her to do to fix this, by any chance?
 
This one worked just great for me on a test file! Thanks. Very neat
trick.
Click to expand...

[snip]

Dave, hi!

I posted this over to the yahoo graphics group yesterday and got a lot
of positive responses. The group thanks you!

But there was one person that had trouble with this. She says:
"For some reason it won't work on mine.
I paste it and it shows the "click here" but when I
click it I get this " Cannot open the specified file "
anyone have any ideas why thats happening?"

Someone else suggested that the problem might lie with the version,
though he wasn't sure. He went on to mention that on his Excel 2003
this worked but not on Excel 98. I have Excel 2000 and this code
worked just fine on it, too.

Any ideas why she gets that message, by any chance, without knowing
what version she has just yet?

Thanks so much!
Click to expand...

Dave, sorry, I'm not sure if I did the right thing posting the
question above re the new problem with the code. I can't seem to
keep the procedure straight. We should keep it one question per
thread type of thing??

Anyway, just in case that's what I should have done, I started a new
thread called
"Hyperlinks give error "can't find specified file" in E2K?"

I have trouble keeping straight the proper procedure re posting, I
know. I'll try to get it straight.

Thanks.
 
I'd post followups in the same thread, so I think you did fine.

But my next question is "what's in A1 in that lady's worksheet?"

(and verify that the file/URL/whatever it is actually exists)
It sounds like the cells still have the hyperlink style, but there's no
hyperlink associated with them.

Depending on what's in the cell, I'd use a helper column and a worksheet
function:

=hyperlink(a1,"ClickMe")

This one worked just great for me on a test file! Thanks. Very neat
trick.
Click to expand...

[snip]

Dave, hi!

I posted this over to the yahoo graphics group yesterday and got a lot
of positive responses. The group thanks you!

But there was one person that had trouble with this. She says:
"For some reason it won't work on mine.
I paste it and it shows the "click here" but when I
click it I get this " Cannot open the specified file "
anyone have any ideas why thats happening?"

Someone else suggested that the problem might lie with the version,
though he wasn't sure. He went on to mention that on his Excel 2003
this worked but not on Excel 98. I have Excel 2000 and this code
worked just fine on it, too.

Any ideas why she gets that message, by any chance, without knowing
what version she has just yet?

Thanks so much!
Click to expand...

Dave, sorry, I'm not sure if I did the right thing posting the
question above re the new problem with the code. I can't seem to
keep the procedure straight. We should keep it one question per
thread type of thing??

Anyway, just in case that's what I should have done, I started a new
thread called
"Hyperlinks give error "can't find specified file" in E2K?"

I have trouble keeping straight the proper procedure re posting, I
know. I'll try to get it straight.

Thanks.
Click to expand...
 
I'd post followups in the same thread, so I think you did fine.

But my next question is "what's in A1 in that lady's worksheet?"
Click to expand...

OH, LIKE D'UH!! <lol>

I'll ask her. It never occurred to me that we might be dealing with
an inactive URL. I was assuming that the "file" in question was an
inability to open a browser window. <chuckle>

Perhaps it's as simple as that.

However, I won't assume again ... I'll see what she says.

Thanks!
(and verify that the file/URL/whatever it is actually exists)
On Wed, 04 Aug 2004 18:42:12 -0400, StargateFan

It sounds like the cells still have the hyperlink style, but there's no
hyperlink associated with them.

Depending on what's in the cell, I'd use a helper column and a worksheet
function:

=hyperlink(a1,"ClickMe")

This one worked just great for me on a test file! Thanks. Very neat
trick.

[snip]

Dave, hi!

I posted this over to the yahoo graphics group yesterday and got a lot
of positive responses. The group thanks you!

But there was one person that had trouble with this. She says:
"For some reason it won't work on mine.
I paste it and it shows the "click here" but when I
click it I get this " Cannot open the specified file "
anyone have any ideas why thats happening?"

Someone else suggested that the problem might lie with the version,
though he wasn't sure. He went on to mention that on his Excel 2003
this worked but not on Excel 98. I have Excel 2000 and this code
worked just fine on it, too.

Any ideas why she gets that message, by any chance, without knowing
what version she has just yet?

Thanks so much!
Click to expand...

Dave, sorry, I'm not sure if I did the right thing posting the
question above re the new problem with the code. I can't seem to
keep the procedure straight. We should keep it one question per
thread type of thing??

Anyway, just in case that's what I should have done, I started a new
thread called
"Hyperlinks give error "can't find specified file" in E2K?"

I have trouble keeping straight the proper procedure re posting, I
know. I'll try to get it straight.

Thanks.
Click to expand...
Click to expand...
 
Back
Top