• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Force Hyperlink Results to Top of Page

C

Crystal

New Member
Hello, I am working on a workbook that I would like to use for our concept to market process. My goal is to have a single workbook and use hyperlinks to navigate to required pages.

BACKGROUND: I have a document I am calling a concept summary, after each evaluation againt the criteria, the user needs to navigate to the next section of the worksheet. It is a single page that will print as multiple. I want it to be formatted so that the page can be viewed in Excel like a powerpoint (because everyone will want to use ppt. I want them to just use Excel instead of having so many documents)

PROBLEM: The hyperlink result is always viewable only at the bottom of the page. I would like the result of each navigation to result in what "looks like" a new page.

QUESTION: I found a suggested solution on Technet but it does not work. The answer was to go to Advanced Options and click on the box for transitional navigation under "Lotus Compatibility". This is supposed to work for MS 2013 but it is not working for me.
Any suggestions?
 
Crystal said:
BACKGROUND:
...
It is a single page that will print as multiple. I want it to be formatted so that the page can be viewed in Excel like a powerpoint (because everyone will want to use ppt.
...
PROBLEM: The hyperlink result is always viewable only at the bottom of the page. I would like the result of each navigation to result in what "looks like" a new page.
Click to expand...
Hi, Crystal!
I'm afraid I don't understand those 2 paragraphs, would you please elaborate or explain it in other words?
Besides, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!
 
thank you SirJB7 for your prompt response. See the attachment. I just want each page of the worksheet to show at the top when you click the hyperlink at the right to navigate the user through to each step in the process.

The first link navigates to the top because it is already in the top (i.e., Row 10). The second hyperlink result is at the bottom of the screen so the user has to scroll up to the top for the content to show. I want them to be able to navigate using the hyperlink and have the entire page showing already.
 

Attachments

Hi, Crystal!

You have 2 hyperlinks: one at cells R5:S5 which leads you to cell B10 and another at cells R14:S14 which leads you to cell B31.

With formulas only (HYPERLINK) you can't achieve what you want, since Excel just gets satisfied if the linked cell is displayed, but no matter where. So even if pointing to the cell in the middle of the range or to any cell anywhere you wouldn't get the part of the worksheet to be displayed at the top.

Give a look at this file:
https://dl.dropboxusercontent.com/u...Top of Page (for Crystal at chandoo.org).xlsm
and check a solution using VBA. This is the code for the worksheet selection change event:
Code: 
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' constants
    ' declarations
    Static bLink As Boolean
    ' start
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    ' process
    If bLink Then
        With ActiveWindow
            .ScrollRow = Target.Row
            .ScrollColumn = 1
        End With
    End If
    bLink = (Target.Hyperlinks.Count = 1)
    ' end
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Just advise if any issue.

Regards!
 
Hi Crystal,

I usually point the Hyperlink to the bottom of the "page" and in that way I get the whole page on my screen.

cheers

kanti
 
@kchiba
Hi!
That's a good technique, unless the whole page/part doesn't fully fit the screen, and with today available devices with very different screen resolutions (PC, note/netbooks, tablets, phablets and so) it's hard to assure it'll work in all cases.
Regards!
PS: Oh, the easier older times of 640x480 and 1024x768... :)
 
Ok. thank you so much SirJB7. Sorry it took so long to get back but I was pulled onto something else. I will take a look at VBA. I have not actually used this before. Oh and Kanti . . . now there's a smart solution. Yes. I think that might work because I woudl want the entire screen to show on one page.
 
Aw Snap. I have to look at this file at home as our security software will not let me access outside storage media.
 
You must log in or register to reply here.
Back
Top