+ Reply to Thread
Results 1 to 12 of 12

How do I get a hyperlink target cell in the same workbook to show half way down screen?

  1. #1
    Registered User
    Join Date
    03-17-2019
    Location
    Wales
    MS-Off Ver
    professional pro 2010
    Posts
    5

    How do I get a hyperlink target cell in the same workbook to show half way down screen?

    I have created a genealogy spreadsheet with the data from censuses entered (one census per sheet). I am using hyperlinks to follow the same person across the censuses, sheet to sheet. When I click on a name to follow it to the next sheet there's no telling where the row containing the target cell appears on the screen. It can be on the top, the bottom or anywhere in between. I would like the row containing the target cell to appear half way down the screen every time. I have tried various solutions suggested on forum sites using VBA but none have worked so far. Can anyone help? I am using Excel Professional Plus 2010.
    I am a complete newbie at this so please be kind :}
    Last edited by chesmo; 03-17-2019 at 07:16 PM.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How do I get a hyperlink target cell in the same workbook to show half way down screen

    Without seeing an example of what you have it is difficult to provide a definitive solution.

    Rather than using a hyperlink perhaps search for the target name in the next sheet instead? Something that would mimic copying the name from the cell with the link, go to the next sheet, then search using ctrl + f?

    Would that do what you need?

  3. #3
    Registered User
    Join Date
    03-17-2019
    Location
    Wales
    MS-Off Ver
    professional pro 2010
    Posts
    5

    Re: How do I get a hyperlink target cell in the same workbook to show half way down screen

    Thanks for responding. I could do it that way but the point of the hyperlinking is to speed up navigating the sheets. As I need to do a lot of navigating to constantly add information on the people in the lists, often pasting the same information to each of the sheets in turn, using the search function for each person would take ages. I just need a bit of code to tell the target cell row to scroll to the vertical mid-point of the screen each time I click a hyperlink.

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How do I get a hyperlink target cell in the same workbook to show half way down screen

    Sorry, I think that you might have misunderstood my suggestion. I wasn't intending for you to search each time, but for excel to automatically searchfor the 'user friendly text' of the hyperlink and go directly to the cell that contains the same text on the next sheet.

    The way I read your question was that you just wanted the link to go to the midle of the list, for you to find the match visually. Did I misread somthing?

  5. #5
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: How do I get a hyperlink target cell in the same workbook to show half way down screen

    Without knowing the full function of your spreadsheet, it's difficult to provide a comprehensive solution. However, it sounds like you already have links that take you to the cell you want, you just need a way to scroll that row to the middle of the screen. If that's the case, this may be of use:
    Please Login or Register  to view this content.
    It may not be ideal, as it will trigger every time a particular sheet is activated, but it may be a good starting point. This should be put in the code for any sheet you want to auto-scroll. You can get to the VB Editor using alt+F11. Let us know if you need more assistance.

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How do I get a hyperlink target cell in the same workbook to show half way down screen

    I just figured out what I was misreading in your question.

    Try this code in the 'ThisWorkBook' module.

    Please Login or Register  to view this content.
    I've set the row counts to 13 beacuse I can see 26 rows with my settings. Noticing Melvosh's suggestion, you could replace the 13's with the variable i as defined in post 5.

    Hopefully one of these suggestions will help.

  7. #7
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: How do I get a hyperlink target cell in the same workbook to show half way down screen

    Quote Originally Posted by jason.b75 View Post
    Workbook_SheetFollowHyperlink
    I did not even know that was a thing, that's fantastic. That's why I love this forum, always something new to learn, even when you're not expecting it!

  8. #8
    Registered User
    Join Date
    03-17-2019
    Location
    Wales
    MS-Off Ver
    professional pro 2010
    Posts
    5

    Re: How do I get a hyperlink target cell in the same workbook to show half way down screen

    jason.b75 and Melvosh. Both your solutions worked perfectly. The difference I note between the two is that the Melvosh code also scrolls the the target cell to the left of the screen hiding the columns to the left of the target cell and the jason.b75 code only scrolls vertically leaving the other columns visible. Many thanks to both of you. While we're here can I impose on you a little more with one other problem ?
    I am trying to add fill colour to the target cell to make it stand out and then go back to no fill when I click a different hyperlinked cell. I've tried a few suggestions that add the fill colour but in doing I lose all the fill colours that I've added to other cells in the workbook. How can I achieve this without losing all the colour formatting in the workbook?

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How do I get a hyperlink target cell in the same workbook to show half way down screen

    Trying to change, then change back could be a bit of a pain, it would be easy enough with no existing fill colours, but trying to get the colour pre change, then reverting it back after will be an absolute nightmare. You could combine conditional formatting with vba to make it work, but that would be extremely slow and inefficient.

    As an alternative, see if this works well enough for what you need. I've taken bits from both previous suggestions and mashed them together. This will scroll to the column, the same as Melvosh's code did previously, if you want it without that then simply delete the line in red.

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    03-17-2019
    Location
    Wales
    MS-Off Ver
    professional pro 2010
    Posts
    5

    Re: How do I get a hyperlink target cell in the same workbook to show half way down screen

    jason.b75. The previous solution you gave me for centering the target cell was perfect so that's sorted. It's the colour thing that I'm now struggling with. What about changing the border colour or the font colour in the target cell instead of the fill colour ?. Could that be done without losing all the fill colours in the book?

  11. #11
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How do I get a hyperlink target cell in the same workbook to show half way down screen

    Did you try the code in my last post?

    Rather than changing the colour of anything, I used a bit of creative thinking to draw your attention to the cell in a different way.

    If you do anything to change colour of fill, font or border then you will have problems changing it back. There is no command in vba that identifies the location of the previously selected cell. i.e the one that needs to revert back to the previous colour / state.

  12. #12
    Registered User
    Join Date
    03-17-2019
    Location
    Wales
    MS-Off Ver
    professional pro 2010
    Posts
    5

    Re: How do I get a hyperlink target cell in the same workbook to show half way down screen

    jason.b75
    I've dealt with the problem by replacing the fill colour in formatted cells with a different colour font instead. I then got it to add fill colour to the target cell and change back again using this code :

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
    'Clear the color of all cells
    Cells.Interior.ColorIndex = 0
    ' Highlight the active cell
    Target.Interior.ColorIndex = 19
    Application.ScreenUpdating = True
    End Sub

    So everything is now fine. Many thanks for sticking with me on this. It's been really appreciated. (You're now in my will. I've got nothing to leave anyone but you'll get a mention )
    Cheers!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need half of 1 to show in cell as 50%
    By thursday140 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-11-2019, 04:02 PM
  2. Make hyperlink target show in the first (ie left most) column
    By alreadytaken in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-08-2019, 09:35 AM
  3. Replies: 8
    Last Post: 06-27-2017, 10:36 AM
  4. hyperlink and temporary highlight target cell
    By dayz in forum Excel General
    Replies: 2
    Last Post: 12-18-2015, 11:24 AM
  5. Show zeros on half, but don't show on the other half
    By AstToTheRegionalMGR in forum Excel General
    Replies: 4
    Last Post: 04-13-2015, 02:41 PM
  6. Hyperlink target showing on top left corner of screen (Using image not cell)
    By Mo T in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-29-2013, 03:23 AM
  7. Half of Screen Turn Grey
    By rjwilcox in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1