Error when using VBA to change hyperlink on a shape

[matfiej]

Hello!

1. When I execute code:


Code
-------------------
Workbooks(1).Worksheets(i).Shapes.Item(s).Hyperlink.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

 

[Dave Peterson]

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 there:

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]

Thanks Dave!
I do think you're right. I will test it
thank you once again

And do you know anything about the point 2?

 

[Dave Peterson]

I was kind of hoping that #1 would fix #2.

Did it?