Validating hyperlinks and text for hyperlink
 

I have an issue where I have hyperlinks that do not match the text that's
shown for them. I think what happened is that someone copied the cells down
and just changed the text, but not the hyperlink.

Here is an example of the problem.

Hyperlink displayed: www.microsoft.com
Link coded: www.yahoo.com

How can we validate which hyperlinks match and which ones don't?

Thanks in advance.

Barb Reinhardt

Barb,

The following code will put the each hyperlink's address next to the cell.
And some other stuff which you can rem out. Insert four columns after your
column of hyperlinks before you run it.

Sub ValidateHyperlinks()
Dim link As Hyperlink

For Each link In ActiveSheet.Hyperlinks()
Cells(link.Range.Row, link.Range.Column).Offset(0, 1) = link.Name
Cells(link.Range.Row, link.Range.Column).Offset(0, 2) = link.Address
Cells(link.Range.Row, link.Range.Column).Offset(0, 3) = link.Range.Address
Cells(link.Range.Row, link.Range.Column).Offset(0, 4) = link.ScreenTip

Next link
End Sub

If you want the cell to show the URL, a similar loop could go through and
change them.
--
Earl Kiosterud
www.smokeylake.com

"Barb Reinhardt" wrote in message
...
I have an issue where I have hyperlinks that do not match the text that's
shown for them. I think what happened is that someone copied the cells
down
and just changed the text, but not the hyperlink.

Here is an example of the problem.

Hyperlink displayed: www.microsoft.com
Link coded: www.yahoo.com

How can we validate which hyperlinks match and which ones don't?

Thanks in advance.

Barb Reinhardt

Can I do this without VBA?

"Earl Kiosterud" wrote:

Barb,

The following code will put the each hyperlink's address next to the cell.
And some other stuff which you can rem out. Insert four columns after your
column of hyperlinks before you run it.

Sub ValidateHyperlinks()
Dim link As Hyperlink

For Each link In ActiveSheet.Hyperlinks()
Cells(link.Range.Row, link.Range.Column).Offset(0, 1) = link.Name
Cells(link.Range.Row, link.Range.Column).Offset(0, 2) = link.Address
Cells(link.Range.Row, link.Range.Column).Offset(0, 3) = link.Range.Address
Cells(link.Range.Row, link.Range.Column).Offset(0, 4) = link.ScreenTip

Next link
End Sub

If you want the cell to show the URL, a similar loop could go through and
change them.
--
Earl Kiosterud
www.smokeylake.com

"Barb Reinhardt" wrote in message
...
I have an issue where I have hyperlinks that do not match the text that's
shown for them. I think what happened is that someone copied the cells
down
and just changed the text, but not the hyperlink.

Here is an example of the problem.

Hyperlink displayed: www.microsoft.com
Link coded: www.yahoo.com

How can we validate which hyperlinks match and which ones don't?

Thanks in advance.

Barb Reinhardt

Barb,

I can't think of an automated way to check them, other than with VBA. You
could right-click each, click Edit Hyperlinks, and examine it manually
(address line).

Or click each, and see where it takes you. Not funny, I guess.
--
Earl Kiosterud
www.smokeylake.com

"Barb Reinhardt" wrote in message
...
Can I do this without VBA?

"Earl Kiosterud" wrote:

Barb,

The following code will put the each hyperlink's address next to the
cell.
And some other stuff which you can rem out. Insert four columns after
your
column of hyperlinks before you run it.

Sub ValidateHyperlinks()
Dim link As Hyperlink

For Each link In ActiveSheet.Hyperlinks()
Cells(link.Range.Row, link.Range.Column).Offset(0, 1) = link.Name
Cells(link.Range.Row, link.Range.Column).Offset(0, 2) = link.Address
Cells(link.Range.Row, link.Range.Column).Offset(0, 3) =
link.Range.Address
Cells(link.Range.Row, link.Range.Column).Offset(0, 4) = link.ScreenTip

Next link
End Sub

If you want the cell to show the URL, a similar loop could go through
and
change them.
--
Earl Kiosterud
www.smokeylake.com

"Barb Reinhardt" wrote in
message
...
I have an issue where I have hyperlinks that do not match the text
that's
shown for them. I think what happened is that someone copied the cells
down
and just changed the text, but not the hyperlink.

Here is an example of the problem.

Hyperlink displayed: www.microsoft.com
Link coded: www.yahoo.com

How can we validate which hyperlinks match and which ones don't?

Thanks in advance.

Barb Reinhardt

I found the getaddr() function and that will get me what I want. Thanks for
the info. You gave me enough info to know what to look for.

"Earl Kiosterud" wrote:

Barb,

I can't think of an automated way to check them, other than with VBA. You
could right-click each, click Edit Hyperlinks, and examine it manually
(address line).

Or click each, and see where it takes you. Not funny, I guess.
--
Earl Kiosterud
www.smokeylake.com

"Barb Reinhardt" wrote in message
...
Can I do this without VBA?

"Earl Kiosterud" wrote:

Barb,

The following code will put the each hyperlink's address next to the
cell.
And some other stuff which you can rem out. Insert four columns after
your
column of hyperlinks before you run it.

Sub ValidateHyperlinks()
Dim link As Hyperlink

For Each link In ActiveSheet.Hyperlinks()
Cells(link.Range.Row, link.Range.Column).Offset(0, 1) = link.Name
Cells(link.Range.Row, link.Range.Column).Offset(0, 2) = link.Address
Cells(link.Range.Row, link.Range.Column).Offset(0, 3) =
link.Range.Address
Cells(link.Range.Row, link.Range.Column).Offset(0, 4) = link.ScreenTip

Next link
End Sub

If you want the cell to show the URL, a similar loop could go through
and
change them.
--
Earl Kiosterud
www.smokeylake.com

"Barb Reinhardt" wrote in
message
...
I have an issue where I have hyperlinks that do not match the text
that's
shown for them. I think what happened is that someone copied the cells
down
and just changed the text, but not the hyperlink.

Here is an example of the problem.

Hyperlink displayed: www.microsoft.com
Link coded: www.yahoo.com

How can we validate which hyperlinks match and which ones don't?

Thanks in advance.

Barb Reinhardt