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

Default300

Board Regular
Joined
Jul 13, 2009
Messages
83
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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Default300

Board Regular
Joined
Jul 13, 2009
Messages
83
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,939
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,336
Messages
5,528,101
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top