Hyperlink problem to cell in same workbook

[stainless]

I have a hyperlink created by a VB program that should link to a cell
on a different worksheet within the same book.


The hyperlink code currently is as follows:


=HYPERLINK(ADDRESS(4,2,1,FALSE,"MultipleAliases"),"MULTIPLE DP
ALIASES")


MultipleAliases is a separate worksheet and I want the link to jump to
row 4 cell 2 on this sheet.


I realise that normally you need a spreadsheet identifier as part of
the worksheet definition, thus the "MultipleAliases" would be
"[FILE1.XLS]MultipleAliases" if this was saved as FILE1.XLS.


If I do save this file with the altered hyperlink it works. The problem

is when the spreadsheet is first created, it is not yet saved (I guess
it is held in the temporary folder). Thus the hyperlink does not work,
even without [FILE1.XLS] in the text.


While this is still a temporary unsaved file, how do I define the sheet

identifier part of my ADDRESS statement so that it finds the
MultipleAliases sheet in the current document?


You can easily try this out by opening an excel spreadsheet, rename
Sheet2 to MultipleAliases and trying variations of the hyperlink text
in a Sheet1 cell.


Please note, I cannot define the cell to jump to in terms of a named
cell and can only use co-ordinates to identify it (due to this text
being created by VB), hence the use of ADDRESS to locate the cell.


Cheers


Mark

 

[Dave Peterson]

You have replies at your other posts.

I have a hyperlink created by a VB program that should link to a cell
on a different worksheet within the same book.

The hyperlink code currently is as follows:

=HYPERLINK(ADDRESS(4,2,1,FALSE,"MultipleAliases"),"MULTIPLE DP
ALIASES")

MultipleAliases is a separate worksheet and I want the link to jump to
row 4 cell 2 on this sheet.

I realise that normally you need a spreadsheet identifier as part of
the worksheet definition, thus the "MultipleAliases" would be
"[FILE1.XLS]MultipleAliases" if this was saved as FILE1.XLS.

If I do save this file with the altered hyperlink it works. The problem

is when the spreadsheet is first created, it is not yet saved (I guess
it is held in the temporary folder). Thus the hyperlink does not work,
even without [FILE1.XLS] in the text.

While this is still a temporary unsaved file, how do I define the sheet

identifier part of my ADDRESS statement so that it finds the
MultipleAliases sheet in the current document?

You can easily try this out by opening an excel spreadsheet, rename
Sheet2 to MultipleAliases and trying variations of the hyperlink text
in a Sheet1 cell.

Please note, I cannot define the cell to jump to in terms of a named
cell and can only use co-ordinates to identify it (due to this text
being created by VB), hence the use of ADDRESS to locate the cell.

Cheers

Mark

 

[Guest]

One option is to use the following code instead of the code used by your VB
program to create the hyperlink...

Dim strWksht As String
strWksht = "Sheet1"
Application.Worksheets(strWksht).Hyperlinks.Add _
Anchor:=Selection.Offset(0, 0), _
Address:="", SubAddress:=Chr(39) & _
strWksht & Chr(39) & "!B4"

The above code will create a hyperlink whereever your cursor is with a
hyperlink to Sheet1!B4. Adjust the code to your specifics.

HTH,

 

[Guest]

You wasted my time.
NOT APPRECIATED!!!!!!!!!!!!!!!!

 

[stainless]

Many apologies if you believe I wasted your time. I simply posted the
same question in more than one group in the hope I would get an answer.
I do appreciate everybodies help.

Thanks Gary