A Worksheet OnScroll Event ! - How cool is that ?

[Jaafar Tribak]

Hi all,

There is no such event in excel. Using a timer to constantly monitor any change of the ScrollRow/ScrollColumn of the activewindow is feasible but it is not worth it because of the strain that such a running timer would put on the application.

While playing around with some Controls via the "More Controls" icon on the ToolBox Toolbar, I came accross this ActiveX control named: InkPicture which has an interesting event called InkPicture_Painted and which basically fires every time it receives a repaint message.

I thought , maybe if i place one on the worksheet , the Control paint event would fire when scrolling the worksheet. Guess what: It worked

I embeeded one into the worksheet and reduced its width to a minimum so that it's almost invisible. I streched its height over 1000 rows so it covers a reasonable down/scrolling region.

Ok. Now, before I get too excited about this, there is a problem: Does this InkPicture Control come with all or most Office standard installations like the OWC controls? if so then great. if not, i should just forget about this whole thing. (i am running Office XP XL 2003)

Notice that there is also an annoying prompt upon opening a workbook containing an embeeded ActiveX control. Fortunatly, this can be avoided by adding a reference to the Control library ( MS Tablet PC Type Lib) Programatically , adding the Control at run time and hooking its events in a Class module.

here is a a workbook demo that shows an implementation of this custom Worksheet Scroll Event (it captures the event to prevent the user from scrolling down beyond the visible range ) : http://www.savefile.com/files/1158486

I would appreciate any feedback on this as i would like to know if it works on different machines/XL versions.

Regards.

 

[Logan_Traceur]

Ahh yes! I assumed that Start() was a form of Workbook_Open() Event and was meant to run automatically.

Thank you! This code is ecellent

Did you run the Start Routine ? What version of excel and windows are you testing the code on ?

Here is a workbook demo