Results 1 to 13 of 13
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 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,280
    Thanks
    3
    Thanked 191 Times in 177 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 29 Times in 29 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,280
    Thanks
    3
    Thanked 191 Times in 177 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, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 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,280
    Thanks
    3
    Thanked 191 Times in 177 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, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 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, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 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, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 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,280
    Thanks
    3
    Thanked 191 Times in 177 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
    2,832
    Thanks
    136
    Thanked 484 Times in 461 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
  •