Pearson Software Consulting Services

    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 solution.

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.

The CDetectScroll class will raise any of 12 events, depending on how the window was scrolled:

ScrollLineUp
ScrollLineDown

ScrollPageDown
ScrollPageUp

ScrollLeft
ScrollRight

ScrollLineLeft
ScrollLineRight

ScrollPageLeft
ScrollPageRight
ScrollEndHorizontalScroll
ScrollEndVerticalScroll

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.
All you need to do is write code to declare and create the class instance and its event procedures. As noted earlier, you'll need the SSubTmr6.dll file.  The CDetectScroll class has a public property called Enabled that you can use to temporarily suppress scroll event messages. Set this property to True to have scroll event message sent, or False to suppress the messages.

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.
 

 

 

 

     
     

 

Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New   
Links   Legalese And Disclaimers
chip@cpearson.com

Copyright 1997-2007  Charles H. Pearson