Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is there a 'Scroll' event for Excel worksheets? (Excel 2000 SR1)

    I'd like to update a label on a worksheet if the user scrolls left or right.

    Is there any event that occurs on scrolling that I can use for this?

    Ian.

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a 'Scroll' event for Excel worksheets? (Excel 2000 SR1)

    I'd say the answer is no. If you go to the VBE and look at the list box for the available events for a worksheet you get:

    Activate
    BeforeDoubleClick
    BeforeRightClick
    Calculate
    Change
    Deactivate
    FollowHyperLink
    SelectionChange

    There are more at the 'workbook' level but still no scroll event.

    They can scroll more than one way; via keyboard or via mouse. You could capture the keyboard scroll but that would slow everything else down since it's monitoring every keystroke for a scroll (PgUp/PgDn keys, arrow keys, mouse wheel, etc.). This would get more into the Windows API calls.

    I'm curious about your reason for wanting this. You said you want to change the text of a label when they scroll away from a certain region. Why is that? You can limit the scroll area of a given worksheet to prevent them from moving around beyond a set xy area. Maybe there's another way to do what you want, but since I don't understand your reason for changing the label, nothing comes to mind. What is the "label" anyway? Is it just a text box made by the drawing tool bar?

    Deb <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a 'Scroll' event for Excel worksheets? (Excel 2000 SR1)

    Thanks, Deb. I suspect that you're right. <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>

    The attached file shows what I want to do, but in a rather kludgy way. Move from Sheet2 to Sheet1 (to activate the Sheet1 Activate event) then scroll horizontally to see what happens.

    I'm actually moving the label (a Text Box) rather than changing the text, but I was hoping there would be a simple way to respond to a scrolling event, so I didn't go into details.

    All the best
    Ian. <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22>
    Attached Files Attached Files

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a 'Scroll' event for Excel worksheets? (Excel 2000 SR1)

    I looked at your solution, and for what you have in that sheet, it works quite well. My only concern is how much impact the timer event has on any other things your project needs to do. If it's all these same type of data entry sheets for daily activity, then it won't be an issue. Visually, I think that since each sheet (at least the one you showed me) is just for one month and probably the sheet name will be the month name and each cell heading already has the numeric month digit in it, the additional work you're doing to make sure the user knows it's September is probably not really necessary. I like your solution though and it does have that cool factor <img src=/S/cool.gif border=0 alt=cool width=15 height=15> . It just concerns me that it's constantly in 'running' mode but in your case it doesn't seem to be a problem (but then I don't know what all your application does).

    What other things are you doing with VBA for this project? if it's only used to move this text field around, then you might want to consider dropping it altogether. I find most people are very leery of anything with macros and will only accept it if the program can't be done anyother way. In your case, it's more a cosmetic reason not that you're performing some calculation or feature that Excel formulas can't do. I write lots of Excel apps and they all need VBA for their complexity and to provide a GUI and/or custom menus but people still hate the idea of enabling macros. I ended up buying a digital certificate so at least they have some extra comfort that they know this project is safe (or at least safer than usual). <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Unless she sheet is protected, the user could set the property of this text box so that it won't move either (select text box, right-click, choose Format Text Box, go to Properties tab, select 'do not move'). This is a bit far fetched as I'm sure most users would never both to do that (or even know you could), but....

    Deb <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Is there a 'Scroll' event for Excel worksheets? (Excel 2000 SR1)

    Try this it does NOT use a text box.
    It puts the date formatted as mmmm yyyy into the cell right after the pane. It gets the date from row 3 of the selected column so will change as the dates change

    Steve

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Range("1:1").ClearContents
    Range(ActiveWindow.VisibleRange.Address).Resize(1, 1).Offset(-3, 0).Value = _
    Format(Cells(3, Target.Column).Value, " mmmm yyyy")

    End Sub
    </pre>


  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a 'Scroll' event for Excel worksheets? (Excel 2000 SR1)

    Thanks, Deb. We seem to be thinking alike in that keeping the routine running all the time is wasteful. While putting in the delay prevents it being too intrusive, it does delay the movement of the text box, which is distracting. I'd like it to look as though the text box was part of the frozen pane.

    In the real version there is lots of other VBA code to set up a report for a month selected from a Calendar object. This includes changing the month name and updating all the numbers (I'm using VBA since formulas in all the cells take forever to calculate). Keeping the month name visible would be nice, but it's not really essential, so given there doesn't seem to be a good solution, I think I'll give up at this point. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>.

    It does seem like something that should be easy though!

    Thanks again <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    Ian.

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a 'Scroll' event for Excel worksheets? (Excel 2000 SR1)

    Thanks, Steve. Yes, using text in a cell would work OK, but it doesn't move when you scroll using the scroll bars - since that doesn't change the selection.

    I don't think there is a good answer, so I'll move on to more important matters (like getting the right data into the cells! <img src=/S/smile.gif border=0 alt=smile width=15 height=15> )

    Thanks again

    Ian.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a 'Scroll' event for Excel worksheets? (Excel 2000 SR1)

    Just curious, but what's wrong with having the month in A1:B3?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a 'Scroll' event for Excel worksheets? (Excel 2000 SR1)

    Nothing really - that's what I've ended up with.
    I just would have liked to have the label above the table of times.
    In the 'real' worksheet there's other information in that corner and it gets a bit cluttered <img src=/S/boxedin.gif border=0 alt=boxedin width=25 height=20>.

    All the best
    Ian.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a 'Scroll' event for Excel worksheets? (Excel 2000 SR1)

    What about this one ?

    ***ADDED***

    Dunno why, but the first time the message window shows it is in the wrong position. After exiting and entering the sheet again, it moves to where I planned: somewhere in the top 3 rows, to the right of cell A1 and B1 (on my system, with a 1024/768 resolution). Try changing the application's screen size and change sheets to and fro.
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a 'Scroll' event for Excel worksheets? (Excel 2000 SR1)

    Very nice!

    I find all the API calls a bit scary, so here's a version based on the same idea, but using a nonmodal userform. Now if I could only get rid of the title bar and borders of the userform!

    Thanks!

    Ian.
    Attached Files Attached Files

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a 'Scroll' event for Excel worksheets? (Excel 2000 SR1)

    <<Now if I could only get rid of the title bar and borders of the userform!>>

    You can. Using some heavy API calls <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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