Results 1 to 13 of 13
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    901
    Thanks
    153
    Thanked 11 Times in 10 Posts

    Freeze Panes Scroll Indicator (2003)

    I have some worksheets where I use the Freeze Panes feature to keep the top few rows in view as I scroll down the worksheet. This is very nice, except sometimes I forget that I have scrolled, and I think I'm at the top of my worksheet when I'm not.

    I'd like to a flag somewhere in the top few (non-scrolling) rows to alert me that the window has scrolled. Maybe a cell that turns red, or a word that appears in a cell, or ??? The need is for a visual indication that the window is in a scrolled condition.

    How can I do it?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,308
    Thanks
    3
    Thanked 206 Times in 190 Posts

    Re: Freeze Panes Scroll Indicator (2003)

    Unfortunately scrolling in a worksheet does not trigger any events that you could trap. Perhaps you could just highlight the row immediately below the frozen pane? Then if you can't see the highlights, you know you've scrolled.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 30 Times in 30 Posts

    Re: Freeze Panes Scroll Indicator (2003)

    Another option would be to use the Split Window feature instead of Freeze Panes. With Split Window, each part has its own scroll bars, so you have a visual indicator.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,308
    Thanks
    3
    Thanked 206 Times in 190 Posts

    Re: Freeze Panes Scroll Indicator (2003

    I should add that you might be able to approximate what you want using a selectionchange event but it would not be entirely reliable.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    901
    Thanks
    153
    Thanked 11 Times in 10 Posts

    Re: Freeze Panes Scroll Indicator (2003

    Thanks, guys. I think I'll use the highlight idea.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,308
    Thanks
    3
    Thanked 206 Times in 190 Posts

    Re: Freeze Panes Scroll Indicator (2003

    You can make it a bit neater (IMO) by changing the height of the row immediately below the frozen pane so it's quite narrow, then filling it with a colour.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    901
    Thanks
    153
    Thanked 11 Times in 10 Posts

    Re: Freeze Panes Scroll Indicator (2003

    Good! Thanks.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  8. #8
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    901
    Thanks
    153
    Thanked 11 Times in 10 Posts

    Re: Freeze Panes Scroll Indicator (2003

    I did it one better. I put the narrow green row in just below the non-scrolling area. Then I added two narrow blank columns to the workbook, and filled them with yellow, starting with the row that is just off the screen when the screen is not scrolled, and ending at the bottom of the actual area of the worksheet. (They bracket the active area of the worksheet, so thery're at the left and right edges of my screen.)

    Now when the window is unscrolled, I have a green line just below the non-scrolling labels. When it scrolls, the green line disappears and yellow starts showing in the narrow blank columns. The more it scrolls, the more yellow I see. It all looks pretty good.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Freeze Panes Scroll Indicator (2003

    Hi Lou
    The attached provides an alternative solution to your original requirements,
    Attached Files Attached Files
    Regards
    Don

  10. #10
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, USA
    Posts
    901
    Thanks
    153
    Thanked 11 Times in 10 Posts

    Re: Freeze Panes Scroll Indicator (2003

    What I need is a way to show a visual flag when the bottom window is scrolled. Maybe I'm missing something, but I don't see that in your demo. (BTW, my worksheet doesn't display row and column headings, depriving the user of that usual visual cue.)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,308
    Thanks
    3
    Thanked 206 Times in 190 Posts

    Re: Freeze Panes Scroll Indicator (2003

    Don's works along the lines of the selectionchange event I mentioned. It doesn't work if you simply scroll - you have to change selection or alter a cell.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Freeze Panes Scroll Indicator (2003

    Hi Lou

    Further to Rory's post, the display also upgrades when the sheet is activated. On further consideration, the FreezeHiLite should also be called by the Workbook_Open event.
    Regards
    Don

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,188
    Thanks
    152
    Thanked 591 Times in 561 Posts

    Re: Freeze Panes Scroll Indicator (2003

    What about an ontime macro that runs say, oh, every few minutes, and gets the activewindow scroll row and column positions and..
    ..oh forget it:
    The colour-coding of rows solution looks the simplest.
    Simple is good.

    ., but not my choice of colours.

    zeddy

Posting Permissions

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