Detecting Scrolling In A Worksheet Window
The Excel object model does not provide any method or
event that allows you to detect when the user scrolls in a worksheet window.
If you need to detect scrolling, for example to reposition some controls on
a worksheet, you have few options. The closest you can get is perhaps
using the SelectionChange event and comparing the active window's
VisibleRange property to some stored value. This is cumbersome and not very
reliable. Using only the built in objects and methods, there is no good
Using a technique called "subclassing", you can detect when the user scrolls in a worksheet window. Subclassing is a complicated topic, and will be described here only in the most general, oversimplified, terms. In summary, subclassing is as follows: When the user initiates an action in a window or control, Windows sends a message to that window or control indicating what the user did so the window can properly respond. For example, when you click on a vertical scrollbar in a window, Windows sends a message called WM_VSCROLL to that window, telling the window that it should scroll vertically. A windows message consists of four Long Integer variables: the "window handle" or identifier(HWnd) of the window receiving the message, the message number itself (e.g, WM_VSCROLL = 277), and two additional values, called wParam and lParam. The meanings of the wParam and lParam values depend on the message number being sent. For a WM_VSCROLL message, for example, wParam indicates how the window is to be scrolled (up or down, and by line or by page). The lParam value indicates what scrollbar was used to scroll the window. Subclassing allows you to intercept these window messages, examine their contents, and act accordingly. These messages are coming from directly Windows itself, not filtered through VBA methods or procedures or objects.
The workbook ScrollEvents.xls contains a class module called CDetectScroll that subclasses the Active Window in Excel and intercepts the WM_VSCROLL message, which occurs when the user scrolls vertically, and the WM_HSCROLL message, which occurs when the user scrolls horizontally. While it is technically possible to write the subclassing code itself in VBA, that isn't really a feasible solution. VBA simply isn't fast enough to cope with the flood of messages sent by Windows. Instead, the ScrollEvents project uses a third-party subclassing component called SSubTimer6, implemented in a DLL file called SSubTmr6.dll. This DLL is available for free on the vbAccelerator web site. You can download the zip file here, which contains the DLL and some VB6 example code, and read much more about how it works here. See this page for information about downloading and installing SSubTmr6 on your PC and referencing it in your VBA project. This DLL is required in order to use the code in the CDetectScroll class.
CDetectScroll is always working with the ActiveWindow
and only with scrolling initiated by the user by clicking on a scrollbar. It
will not respond to scrolling caused by VBA code.
ScrollLineUp ScrollLineDown ScrollPageDown ScrollPageUp ScrollLeft ScrollRight ScrollLineLeft ScrollLineRight ScrollPageLeft ScrollPageRight
Each of these events passes a reference to the top-left cell in the VisibleRange of the window and a reference to the window being scrolled. To use CDetectScroll and its events, you must declare a variable WithEvents of the class CDetectScroll in an object module, such as a separate class module (as it is in the ScrollEvents.xls workbook), the ThisWorkbook module, a Userform's code module, or one of the worksheet's code modules. You declare the variable as follows:
Public WithEvents ScrollEvents As CDetectScroll
Once you've declared the variable in zan object module, you'll have access to all the events. Just declare the event procedures and in those procedures take whatever action your application requires. For example,
Private Sub ScrollEvents_ScrollPageLeft(ByVal TopLeftCell As Range, ByVal Wnd As Window) ' do something when the user scrolls one page to the left End Sub
The CDetectScroll class is entirely self-contained.
It contains all the constants, variables, function definitions and
procedures needed to detect scrolling. Aside from the SSubTmr6.dll
file, it doesn't require any other code (other than the declaration and
event procedures). It will automatically detect
when you switch windows, so it will always be intercepting events for
whatever window happens to be active. This includes all windows of all open
workbooks. It does not restrict itself to the windows of the workbook
containing the code.
The ScrollEvents.xls workbook contains a Userform that will display the result of the scroll event messages as you scroll in any window. This is for demonstration purposes only, and is not necessary in order to use the CDetectScroll class. The userform is shown below. It will be displayed when you click on either the vertical or horizontal scroll bar.
If you scroll by clicking on the scroll bar, you will receive two events: the normal Up/Down/Left/Right by Page/Line event followed by and EndScrollHorizontalScroll or a EndScrollVerticalScroll event. If you scroll by dragging the button in the scrollbar, you will receive only the EndScrollHorizontalScroll or EndScrollVerticalScroll event. Your code should be prepared to receive both the normal scroll events (Up/Down/Left/Right) followed by an EndScroll event, and to receive just the EndScroll event. You can this by storing the last event received, and if that is an EndScroll and the next event is also an EndScroll, you'll know that the user scrolled by sliding the scrollbar buttons.
It should be noted that the SSubTmr6.dll can on
occasion crash Excel. This can happen when you are currently subclassing an
Excel window AND you are editing the subclassing project code. As long
as you don't edit the code while you are subclassing an Excel window, you
should have no problems.