Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Shape Text | Excel Worksheet Functions | |||
Shape error | Charts and Charting in Excel | |||
Run-Time Error: You must select a shape | Excel Worksheet Functions | |||
Name of shape giving VB run-time error | Excel Programming | |||
Hyperlink within the TextFrame of Shape Object..? | Excel Programming |