Have something occur on scroll

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Suppose I have a worksheet active. At any given time, a certain set of rows
on the sheet are being displayed.

1) How would I have a VBA put the correct values in the variables
FirstRowBeingDisplayed as Long
LastRowBeingDisplayed as Long

2) The program terminates and the user is now in the direct mode. He is
free to move around in the worksheet. How might I set it up so that if his
moving around (with the arrow keys, mouse, or scroll bar, or whatever)
trggers a scrolling event, a subroutine Scroll_Routine is executed. )By
scrolling event I means an event which changes the set of cells displayed).
The first thing I want Scroll_Routine to do is to assign the correct values
to the variables FirstRowBeingDisplayed, LastRowBeingDisplayed,
FirstColBeingDisplayed, LastColBeingDisplayed.

Thank you
 
to answer your first question:

Dim lngFirstRowBeingDisplayed As Long
Dim lngLastRowBeingDisplayed As Long

With ActiveWindow.VisibleRange
lngFirstRowBeingDisplayed = .Rows(1).Row
lngLastRowBeingDisplayed = .Rows(.Rows.Count).Row
End With

MsgBox lngFirstRowBeingDisplayed & ":" & lngLastRowBeingDisplayed

Don't know about the second. XL does not have a scroll event for
worksheets. Depending on what you need the first and last rows for - perhaps
one of the built in worksheet event handlers would be equally suitable.
 
with regards to your second question - this was posted by Chip Pearson to
another poster asking about scroll events in XL:


There is no event that is triggered when you scroll in a window. You can use
a technique called "subclassing" to detect the Windows messages sent during
scrolling. VBA doesn't have the performance horsepower to do the actual
subclassing itself, so I use a free DLL from vbAccellerator,
www.vbaccellerator.com, called SSubTimer6.dll. Once you have that DLL on
your machine, you can use the class module on my web site to detect
scrolling. The class will raise normal VBA events for scrolling up/down
left/right by page/line.

See http://www.cpearson.cm/Excel/DetectScroll.htm details and downloadable
example workbook.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Back
Top