Error when using VBA to change hyperlink on a shape
 

Hello!

1. When I execute code:


Code
-------------------
Workbooks(1).Worksheets(i).Shapes.Item(s).Hyperlin k.SubAddress = "'" & sSheetName &"'!A1
-------------------


where 's' is a number of shape (rectangle) in which I want to change o
add a hyperlink
'i' is a number of a worksheet
sSheetName is a string variable with target sheet

I get a run-time error 1004: Application-defined or object-define
error

2.What is more, I managed to execute properly this code once (don'
know how). It is executed for different shapes in different worksheet
(using a loop) and the link is changing to point the previous and th
next sheet from the sheet that is being linked. (like arrows <- an
-)
As a result hyperlinks in all shapes linking to the last sheet (-
shapes) or to the third one (<-).
It is worth mentioning that when I was debuging macro all variable
were good.
It seems to me that when sSheetName variable is changing hyperlink
that has been pasted before are also changing (and shouldn't)

Could you tell me why and how to solve it?

Mateus

--
matfie
-----------------------------------------------------------------------
matfiej's Profile: http://www.excelforum.com/member.php...fo&userid=1666
View this thread: http://www.excelforum.com/showthread.php?threadid=31874

Error when using VBA to change hyperlink on a shape
 

I think that if your shape already had the hyperlink, then your code works ok.

If the shape didn't have the link, then kablewie!

This seemed to work whether or not the link was the

Option Explicit

Sub testme()

Dim i As Long
Dim s As Long
Dim sSheetName As String

s = 1
For i = 2 To Worksheets.Count
sSheetName = Worksheets(i - 1).Name
With Worksheets(i)
.Hyperlinks.Add anchor:=.Shapes.Item(s), _
Address:="", _
SubAddress:="'" & sSheetName & "'!A1"
End With
Next i

End Sub



matfiej wrote:

Hello!

1. When I execute code:

Code:
--------------------
Workbooks(1).Worksheets(i).Shapes.Item(s).Hyperlin k.SubAddress = "'" & sSheetName &"'!A1"
--------------------

where 's' is a number of shape (rectangle) in which I want to change or
add a hyperlink
'i' is a number of a worksheet
sSheetName is a string variable with target sheet

I get a run-time error 1004: Application-defined or object-defined
error

2.What is more, I managed to execute properly this code once (don't
know how). It is executed for different shapes in different worksheets
(using a loop) and the link is changing to point the previous and the
next sheet from the sheet that is being linked. (like arrows <- and
-)
As a result hyperlinks in all shapes linking to the last sheet (-
shapes) or to the third one (<-).
It is worth mentioning that when I was debuging macro all variables
were good.
It seems to me that when sSheetName variable is changing hyperlinks
that has been pasted before are also changing (and shouldn't)

Could you tell me why and how to solve it?

Mateusz

--
matfiej
------------------------------------------------------------------------
matfiej's Profile: http://www.excelforum.com/member.php...o&userid=16664
View this thread: http://www.excelforum.com/showthread...hreadid=318748

--

Dave Peterson