Automatically jumping to top row with hyperlink function 2

[shawnmandel]

Hi,

I posted this question in the VBA forum, but I feel I need a non-vba answer to my question:

Is there a way, using the hyperlink function, to jump to the top of another sheet?

What I need is to be able to jump from the current sheet to the destination sheet at the same column location in the destination sheet, then automatically to jump to the top row from this cell location (NOT to the TOP LEFT cell).

In other words, is there some type of parameter in the Hyperlink function that tells the cursor to go to the top?


I hope I am being clear enough?

Thanks very much,
Shawn

 

[TonyJollans]

Hi Shawn,

This should do it ..

[blue][tt]=HYPERLINK("[Book1.xls]Sheet2!"&CELL("ADDRESS",OFFSET($A$1,0,COLUMN()-1)),"Top of this column on sheet Sheet2")[/tt][/blue]

This has a hard coded workbook name and sheet name. Obviously you know what sheet you want and it must be hard coded. If you're happy to hard code the workbook name as well, then fine; if not, it can be obtained with functions but it is a bit convoluted - this is one way ..

[blue][tt]=MID(CELL("filename"),LEN(INFO("directory"))+1,FIND("]",CELL("filename"),LEN(INFO("directory")))-LEN(INFO("directory")))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[mbarron]

If I'm interpretting correctly you want to click on a cell using =hyperlink() function and get sent to the same column on another sheet, but at the top of the column. Ie - click on cell Sheet1!F23 and go to Sheet2!F1.

If this is the case the following will work:

=HYPERLINK("[book1.xls]sheet2!"&LEFT(CELL("address"),2)&1,"Your Friendly Text Goes Here")

One note - this formula will work in columns A through Z. If you need to go past column Z you can use:
=HYPERLINK("[book1.xls]sheet2!"&MID(CELL("address"),FIND("$",CELL("address"))+1,FIND("$",CELL("address"),2)-2)&1,"Your Friendly Text Goes Here")

Mike

 

[shawnmandel]

Hi,

Thanks so much for the feedback. Since, I'm a bit unfamiliar with the hypertext function, can you elaborate on what the "address" means?

Can you get me a more concrete example such as the following:

I'm hyperlinking from cell H10 is sheet1 to cell J23 in sheet 2. From cell J32, I need to automatically jump to cell J1.

You feedback is highly appreciated.

Best regards,
Shawn

 

[shawnmandel]

The popup errors that I'm getting is:

'The address of this site is not valid. Check the address ann try again.'


Thanks again,
Shawn

 

[TonyJollans]

Mike,

You cannot use the CELL function like that to get the address of the current cell - it gives the address of the last cell changed.

Shawn,

You want to go to the top of a column AFTER a successful hyperlink. That is not what I (or Mike) understood from your post and I don't think it's possible. Why do you hyperlink to J23 if you want to go to J1? Why not hyperlink directly to J1?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[shawnmandel]

Tony,

You're correct. I'll try to figure something else out. Thanks so much for the efforts!

Regards,
Shawn

 

[mbarron]

Tony,
What do you mean I can't use CELL() in this manner. I'll agree that it gives the last changed cell, but since I used "friendly text" the change is never evident. It always sent me to the correct location.


Mike

 

[TonyJollans]

Hi Mike,

My apologies.

I really should learn to try things before I open my big mouth.

According to my understanding of changed the hyperlink should have gone to the wrong place. It does, however, appear to work correctly.

I copied your formula into cell A22 and E22 of Sheet1, and they do link, respectively, to A1 and E1 on Sheet2.

Clicking on Sheet1!A22, hyperlinks to Sheet2!A1.
Returning to Sheet1 and hovering the mouse over cell Sheet1!E22 shows the hyperlink as Sheet2!A1
Changing the contents of cell Sheet1!B22 and then hovering the mouse over either of the hyperliks shows the hyperlink as Sheet2!B1
Clicking on Sheet1!E22 still hyperlinks (correctly) to Sheet2!E1.

Without the friendly text, you can have the hyperlink show as one cell, the 'hover tip' show as another and the actual target be a third.

No cells have changed, the worksheet change event doesn't fire, and I have to say I don't understand. But as it does all work I have to eat some humble pie.



Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[shawnmandel]

Another question along the same lines:

Is there any way to be able to HYPERLINK to a cell in another sheet, say, Sheet1!H23 hyperlink to Sheet2!D187; after the hyperlink is done, is there a way to have D187 be at the top of the sheet? In other words, to be able to SCROLL to the hyperlinked cell and have it appear AS the top cell in the row (even though it is on Row 187).

I hope I'm clearer this time.


Thanks,
Shawn

 

[TonyJollans]

Hi Shawn,

You should be able to do this in the FollowHyperlink Event. In the code module for the worksheet with the hyperlink source, enter ..

[blue]Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    ActiveWindow.ScrollRow = ActiveCell.Row
End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[shawnmandel]

Tony,

That did it! Thanks so much for you help.

Regards,
Shawn

 

[shawnmandel]

Tony,

How does this code module get trigerred? Do I need to call it? If so, how?

Thanks,
Shawn

 

[TonyJollans]

Hi Shawn,

The code, in the (source) Worksheet's code module, should run automatically.

The Follow_Hyperlink Event was introduced in Excel 2K, so is not available in 97.

What problems are you having?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[shawnmandel]

Hi Tony,

Where exactly does the code need to reside in order for any activated hyperlink on the sheet to trigger the function?

Thanks,
Shawn

 

[TonyJollans]

Hi Shawn,

It needs to be in the code module for the worksheet with the hyperlink in (NOT the target).

Assuming it's in Sheet1, then in the VBE Project Explorer you'll see something like:

- VBAProject (Book1)
- Microsoft Excel Objects
Sheet1 (Sheet1)
Sheet2 (Sheet2)
Sheet3 (Sheet3)
ThisWorkbook
+ Modules

Double click on Sheet1. In the main window there are two dropdowns at the top. From the one on the left (which defaults to [purple](General)[/purple]), select [blue]Worksheet[/blue] - this will immediately create an empty procedure for the default event ([purple]SelectionChange[/purple] - you can delete this. From the dropdown on the right select FollowHyperlink and you will get an empty procedure - just drop your code in it.


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.