Scroll Excel Window using Userform ScrollBar Control - Problem with Filtered / Hidden Rows

[Default300]

Aim
I want to use a Vertical ScrollBar on a Modeless UserForm to scroll the Excel Workbook / Worksheet window in exactly the same way as the (Windows OS) Application window scrollbars move it. Similar speed. Similar proportion (though control length scaled down 6:1). And especially similar smoothness.

The range that I want to scroll up and down is a Data Table which the user can Filter and Sort at will (Advanced Filter, filtered in place).

Summary of Approaches Tried & Problems
I've tried three approaches so far.

Approach (1)
Use SmallChange & LargeChange properties to increment/decrement automatically. Then set ActiveWindow.ScrollRow = VerticalScrollBar.Value every time the ScrollBar_Change event is triggered.

Approach (2)
Change ActiveWindow.ScrollRow using SmallScroll & LargeScroll methods to increment/decrement ActiveWindow.ScrollRow in code, every time ScrollBar_Change event is triggered.

Problem with Approaches (1) & (2)
Smoothness of scroll is drastically impaired when Filter(s) are applied and rows or groups of rows are hidden at random intervals. This results in random pauses where hidden, and jumps where unhidden, for same user input to control.

Approach (3)
Builds on (1) but before setting ActiveWindow.ScrollRow = VerticalScrollBar.Value, checks If ActiveSheet.Rows(VerticalScrollBar.Value).Hidden = True
If it is hidden, it finds the next unhidden row down, using a loop. (The process is sped up somewhat by setting ScrollBar.Delay = 0 for the duration of loop)

Problem with Approach (3)
Smoothness of scroll is somewhat improved, but it will require further refinements.

(a)
When rows are visible, the SmallChange jump is 5 rows.
Whereas if a batch of rows are hidden, then one is visible, then another batch hidden etc... the loop finds the first unhidden and stops there, so it often appears to move down 1 row. Maybe a simple additional For Next loop 1 to SmallChange would do it. May need to take into account any unhidden rows passed between start position and first hidden row too.

(b)
So far have only coded for SmallChange down. Need to detect direction and whether SmallChange or LargeChange. (Perhaps by comparing ScrollBar.Value before and after ScrollBar_Change event and comparing difference to SmallChange & LargeChange.) Then implement 4 Select Case options (1 for each scenario).

Bottom Line
Refinements probably doable, but messy and potentially slow to execute, which may in turn impair smoothness of scroll.


Question
Does anyone know a simple, reliable, non-head wrecking way for an amateur to hook into the application scrollbar parameters?

Windows and/or the Excel application seem to manage to scroll smoothly ignoring hidden rows and just focussing on the visible.

Two interesting leads I've found are:

http://www.xcelfiles.com/APIIndex.html

http://www.cpearson.com/excel/DetectScroll.htm

The latter will also be useful for updating ScrollBar.Value in response to users scrolling page using methods other than my own control (eg keyboard, mousewheel, or windows/application scrollbars).

Any suggestions would be very welcome.

Happy to experiment and learn, but need safe result in (not too long) long run.

 

[Default300]

Bump!! (No replies so far)

If the original post seems too long, please see headers "Aim" and "Question" for the jist of what I need help with.

Essentially, I would really appreciate pointers (or ideally foolproof recipe) for ?how to control the Excel Application's own Scrollbars using a standard ActiveX ScrollBar Control on a (modeless) Userform?

I'm presuming this involves working with the API.

As you'll see from other posts, I'm a very enthusiastic amateur (who cut his teeth on a ZX Spectrum in the early 80's) but not formally trained, and far from a professional programmer.

In the meantime, I'll proceed with the clunky version as outlined and report back on success or otherwise.

Please let me know if you need further clarification on my aim, setup, etc. (Also see previous posts by me as so far, they are all concerned with this same project.)

Thanks.

 

[Jaafar Tribak]

If i understand propperly you will need to synchronize scrolling of the activeX and worksheet both ways.

I remember trying to capture the worksheet scroll event but turned out to be a real pain as one has to anticipate and handle every possible user scrolling scenario ie : dragging the worksheet scrollbar,moving the MouseWheel, Zooming, pressing the Enter, arrow, PageDown,Tab, Home, End... keys .... etc .

Also,given that there is no such built in Worksheet Scroll event your code will need to subclass the worksheet first in order to catch the WM_VSCROLL,WM_HSCROLL and WM_MOUSEWHEEL messages which makes it even more complicated and unstable.

Have you considered using a Windows Timer ? Maybe it can give you smoother results.

Regards.