Hi,

I'm hoping someone on this forum can help me. I'm trying to figure
out how I can use VBE in Microsoft Excel (2003) to convert hyperlinked
GIF images in a spreadsheet. I already know how to convert
hyperlinked words into the original text, but I can't figure out how
to do the same for pictures.

The GIF images paste into a spreadsheet fine, but I get an error
message when I use the following macro (found on
http://www.developersdex.com/vb/mess...77&r=6138512):

Option Explicit

Sub RemoveLinks()
Dim prngCell As Range

For Each prngCell In Selection
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")

Next prngCell
End Sub

The error message reads "Subscript out of range". Any help on this
would be greatly appreciated. I don't necessarily need to use VBE, so
I'm open to any other suggestions.

CB

We just need to test if a cell has a hyperlink before trying to delete it:

Sub RemoveLinks()
Dim prngCell As Range
For Each prngCell In Selection
If prngCell.Hyperlinks.Count 0 Then
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")
End If
Next prngCell
End Sub


--
Gary''s Student - gsnu2007k


" wrote:

Hi,

I'm hoping someone on this forum can help me. I'm trying to figure
out how I can use VBE in Microsoft Excel (2003) to convert hyperlinked
GIF images in a spreadsheet. I already know how to convert
hyperlinked words into the original text, but I can't figure out how
to do the same for pictures.

The GIF images paste into a spreadsheet fine, but I get an error
message when I use the following macro (found on
http://www.developersdex.com/vb/mess...77&r=6138512):

Option Explicit

Sub RemoveLinks()
Dim prngCell As Range

For Each prngCell In Selection
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")

Next prngCell
End Sub

The error message reads "Subscript out of range". Any help on this
would be greatly appreciated. I don't necessarily need to use VBE, so
I'm open to any other suggestions.

CB

On Dec 1, 11:19*am, Gary''s Student
wrote:
We just need to test if a cell has a hyperlink before trying to delete it:

Sub RemoveLinks()
* * Dim prngCell As Range
* * For Each prngCell In Selection
* * * * If prngCell.Hyperlinks.Count 0 Then
* * * * * * prngCell = prngCell.Hyperlinks(1).Address
* * * * * * prngCell.Hyperlinks.Delete
* * * * * * prngCell = Replace(prngCell, "mailto:", "")
* * * * End If
* * Next prngCell
End Sub

--
Gary''s Student - gsnu2007k



" wrote:
Hi,

I'm hoping someone on this forum can help me. *I'm trying to figure
out how I can use VBE in Microsoft Excel (2003) to convert hyperlinked
GIF images in a spreadsheet. *I already know how to convert
hyperlinked words into the original text, but I can't figure out how
to do the same for pictures.

The GIF images paste into a spreadsheet fine, but I get an error
message when I use the following macro (found on
http://www.developersdex.com/vb/mess...77&r=6138512):

Option Explicit

Sub RemoveLinks()
* * Dim prngCell As Range

* * For Each prngCell In Selection
* * * * prngCell = prngCell.Hyperlinks(1).Address
* * * * prngCell.Hyperlinks.Delete
* * * * prngCell = Replace(prngCell, "mailto:", "")

* * Next prngCell
End Sub

The error message reads "Subscript out of range". *Any help on this
would be greatly appreciated. *I don't necessarily need to use VBE, so
I'm open to any other suggestions.

CB- Hide quoted text -

- Show quoted text -

Gary"s Student,

Thanks for responding, but I'm not sure I understand your answer.
Perhaps I should clarify my question.

I'm working with a database that includes a quantity of email
addresses. However, each address must be accessed by clicking on a
hyperlinked image (GIF format, I think). I know this is a common
security feature. I have authorization to use these email addresses,
but am trying to avoid the tedium of clicking, cutting and pasting one
at a time.

I've used VBE/VBA on a similar project with hyperlinked text (using
the macro above)--i.e. the name of the person is clicked to open up a
new outgoing mail. I'm trying to find a shortcut that works for
images.

Thanks,
CB