Hyperlink Code only works when file saved on desktop
 

Effectively teh code loops though sheets then cells and finds information.
It then prints the cell address of the cell that has the the information its
looking for on a summary sheet within the same file.

The variable rng is where it creates/pastes the hyperlink. Variables a and
b are what are used for the hyperlink information. This is nothing more than
cell address and file path information from the cell in question.

This only works when the file that its searching is saved on my desktop. If
I save the file anywhere else, the hyperlink gets created with the correct
cell adderss as text. But when you click on it, it creates an error saying
"Reference is not valid". When I right click on the hyperlink and go to Edit
Hyperlinks the problem seems to be that the Cell Reference. When clicked
upon it says A1 (which is nto the value I passed to it in the code - E2) and
the wrong source sheet is highlighted in the shee director tree. If I simply
click OK, the hyperlink then starts to work albeit with the wrong Cell
Reference.

Now this is odd because when I run the code and watch the immediate window I
get the exact same result whether the file is saved on my Desktop or My
Documents. But when the code is finished, the data for variables a and b
show up differently in the actual hyperlink itself.

Saved to My Document
?a
Colour Legend!E2
?b
'C:\Documents and Settings\Me\My Documents\Excel Documents\[Test
File.xls]Colour Legend'!E2

Saved to Desktop
?a
Colour Legend!E2
?b
'C:\Documents and Settings\Me\Desktop\[Test File.xls]Colour Legend'!E2


Private Sub CellAddressPass(a As String, b As String, rng As Range)
a = Cell.Parent.Name & "!" & _
Cell.Address(0, 0)

b = "'" & Workbooks(OriginalWorkbook).Path & "\[" &
Workbooks(OriginalWorkbook).Name & "]" & Cell.Parent.Name & "'!" & _
Cell.Address(0, 0)

'Pass variable values to Hyperlink Creation
rng.Parent.Hyperlinks.Add Anchor:=rng, Address:="", _
SubAddress:=b, _
TextToDisplay:=a

End Sub

Any thoughts - Thanks

Hyperlink Code only works when file saved on desktop
 

I should also let you know that this is only happening in Excel 2003. I just
ran the macro in XL 2000 and it is creating the hyperlink properly and the
Cell Referece is correct. What would be the issue in XL 2003?

"ExcelMonkey" wrote:

Effectively teh code loops though sheets then cells and finds information.
It then prints the cell address of the cell that has the the information its
looking for on a summary sheet within the same file.

The variable rng is where it creates/pastes the hyperlink. Variables a and
b are what are used for the hyperlink information. This is nothing more than
cell address and file path information from the cell in question.

This only works when the file that its searching is saved on my desktop. If
I save the file anywhere else, the hyperlink gets created with the correct
cell adderss as text. But when you click on it, it creates an error saying
"Reference is not valid". When I right click on the hyperlink and go to Edit
Hyperlinks the problem seems to be that the Cell Reference. When clicked
upon it says A1 (which is nto the value I passed to it in the code - E2) and
the wrong source sheet is highlighted in the shee director tree. If I simply
click OK, the hyperlink then starts to work albeit with the wrong Cell
Reference.

Now this is odd because when I run the code and watch the immediate window I
get the exact same result whether the file is saved on my Desktop or My
Documents. But when the code is finished, the data for variables a and b
show up differently in the actual hyperlink itself.

Saved to My Document
?a
Colour Legend!E2
?b
'C:\Documents and Settings\Me\My Documents\Excel Documents\[Test
File.xls]Colour Legend'!E2

Saved to Desktop
?a
Colour Legend!E2
?b
'C:\Documents and Settings\Me\Desktop\[Test File.xls]Colour Legend'!E2


Private Sub CellAddressPass(a As String, b As String, rng As Range)
a = Cell.Parent.Name & "!" & _
Cell.Address(0, 0)

b = "'" & Workbooks(OriginalWorkbook).Path & "\[" &
Workbooks(OriginalWorkbook).Name & "]" & Cell.Parent.Name & "'!" & _
Cell.Address(0, 0)

'Pass variable values to Hyperlink Creation
rng.Parent.Hyperlinks.Add Anchor:=rng, Address:="", _
SubAddress:=b, _
TextToDisplay:=a

End Sub

Any thoughts - Thanks