Hyperlink open location

How can i get hyperlinks to locations within the same workbook to open with the linked cell at the top of the window instead of the bottom

I also asked this question on MrExcel because I kind of need https://www.mrexcel.com/forum/excel-...ml#post4829835

.
.
Try pasting this macro into each sheet module you are hyperlinking to.

Please Login or Register  to view this content.

An elegant and simple solution. It works perfectly.
Thanks so much for your help and your expertise.

I tried to use your solution in another workbook I have that I've always wanted the same thing to happen but it didn't work quite the same. I use a Combo box in that workbook and your code disables the combo box for some reason. Can you think of why this would happen and possibly a solution that wouldn't disable my combo box.

Thanks

I don't know why it would affect the Combobox.

Can you post that workbook for review ?

Glad to know the macro works when placed in ThisWorkbook. Saves alot of typing / pasting.

Apologies .. misunderstood.

I have the workbook. Let me look at it and see what can be done.

Which tab is the 'index' tab ?

That was in the other workbook. This workbook doesn't have an index tab.
The sheets I'm trying to use your code in this workbook are the TV Shows Tab and the Mini-Series Tab.
Those two tabs both have a combobox at the top (Cell C1)
Just click in the outline of a box in that cell and a dropdown appears.
If you add your code to the sheet code the combobox will work until you click on one of the links in the index
(Visible when you click the UNHIDE button (At B1)
Then your code takes over and the index works the way your code tells it to meaning the links open at the top of the window but the combobox no longer works.

Give this a go and see what you think :

I notice when entering the code in the TV Shows tab and the Mini-Series tab it shows up like this

Option Explicit
____________________________________________________________________________________________________
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
ActiveWindow.ScrollRow = ActiveCell.Row
End Sub

With the Option Explicit included in the previous script.
Normally the line would appear above the entire new script. That's what's happened with every script I've added to excel from my recollection. Could that be the problem?

Normally it would appear in the code sheet like this
____________________________________________________________________________________________________
Option Explicit

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

But the Option Explicit is being included under End Sub with the prior script.
I don't know if that makes a difference the End Sub should end the prior routine If I'm not mistaken but....

I tried the sheet you linked to and The Combobox is completely disabled it doesn't even drop down when you click on it. It's completely dead.
Also the Hide and unhide buttons no longer work and generate an error message and even your code doesn't work.
If you click on the links in the index starting from the bottom going up the clicked link opens at the top but if you click on the links going down starting from the first one it only works for the first one you click every one after that under the first one you clicked opens at the bottom of the window.

Never mind my last post it works perfectly.
I opened your updated copy it right from the link into excel without saving a local copy first and that may be the reason it didn't work.
I saved a copy and tried it on my main PC and it works perfectly.

Turns out it was a trust issue I opened it from a location that I hadn't trusted in the excel trust settings.

Anyway it works and thank you so much you clever fellow.

Do you know if I could enter this code in my personal.xlsb to use it in every sheet that has links
If you ask me links should always open at the top of the window instead of the bottom.
That should be the default but since it's not I would like to make it so.

Would that work?

I placed my macro in the ThisWorkbook module and disabled the same macro in the two worksheet level modules.
It seems to work 100% from the drop down boxes in both Sheets .. however, clicking on some of the hyperlinks that
are listed in the sheet rows .. some of them won't appear at the top of the sheet as desired.

So I presume you'll always need to place the macro in each sheet level module.

Using the same macro on other projects should not be an issue as the code is generic.

The dropdowns are designed to open at the top of the window on their own.

You've found as I did the script doesn't work at all if it's in the ThisWorkbook module the fact that some links open at the top and the rest at the bottom is the normal behavior of excel.
If you click on the links in the index starting from the bottom going up the clicked link opens at the top but if you click on the links going down starting from the first one it only works for the first one you click every one after that opens at the bottom of the window.

I tried putting your code in personal.xlsb but it doesn't function at all. I don't know why it doesn't work the whole purpose of the personal book is so scripts and macros work across workbooks but whatevs. So I'll do it old school it's not as convenient but I'm thrilled to have this script which I know I'll use many more times in the future.

I've already put it in a bunch of my other workbooks so thank you so much it's so much better opening at the top it makes so much sense I have no idea why it's not the default behavior of excel it really should be.

Thank you so much for your help.
Have a fantastic week.

You are welcome.