Hyperlink Help

[Randy S]

Is there any way to add/remove/edit hyperlinks for
multiple cells at once? I'm running Excel 9.0.4402 SR-1 on
Windows 2000. Does any version of excel support this?

[Bill Manville]

You would need to write macros to help you with this.
For example, the remove macro might be:

Sub RemoveHyperlinksFromSelection()
Selection.Hyperlinks.Delete
End Sub

and the edit macro might be

Sub EditHyperlinksInSelection()
Dim H As Hyperlink
Dim stFrom As String
Dim stTo As String
stFrom = InputBox("Replace what?")
If stFrom Then Exit Sub
stTo = InputBox("Replace with?")
For Each H In Selection.Hyperlinks
H.Address = Replace(H.Address, stFrom, stTo)
H.SubAddress = Replace(H.SubAddress, stFrom, stTo)
Next
End Sub

the adding macro I leave to you - no idea what you would want it to do.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

[Randy S]

Thank you! That's very helpful!

Another question please:
Is there a way to make a hyperlink "move" with the data it
points to in the same document? That is, if my hyperlink
is pointing to another location within the same document,
and that location gets moved to another location in the
same document, the underlying link I have moves with it
but the hyperlink does not. For example, document A has a
cell A1 with a link to cell D42 (A1 contains '=D42'), and
a hyperlink to D42. If I cut and paste D42 to G59, A1 now
contains '=G59', but the hyperlink is still pointing to
D42. If there's no way to make the hyperlink "relative",
is there a macro that could accomplish this?

FYI: My overriding goal is to create a spreadsheet that I
can put on the web with active hyperlinks for navigation
within the document.

Perhaps there's a good reference site for Excel hyperlinks
that you're aware of that would help me? The sections in
the Excel manuals I've seen cover very little on managing
and maintaining hyperlinks.

Thank you!

>.
>

[Bill Manville]

Randy S wrote:
> Is there a way to make a hyperlink "move" with the data it
> points to in the same document?
>

Yes.
Name the cell that is the destiation and use the name as the
"sub-address" of the hyperlink.

The name will move with the cell and the hyperlink will still work.

> Perhaps there's a good reference site for Excel hyperlinks
> that you're aware of that would help me?
>

Not that I am aware of. I wish there were.
The number of unanswered questions about hyperlinks in this newsgroup
is a testament to the lack of knowledge on the subject.