Excel ScrollBar - Don't Move or Size with Cells ...

[tkt_tang]

1. Enters an Excel worksheet.

2. A (target) ScrollBar (be it the genre of Control or Form Toolbar)
is located (approximately mid-stream) in column-K.

3. Select the ScrollBar and activate short-cut menu, Format Control >
Properties > Check : Don't Move or Size with Cells.

4. Then, scroll the ActiveWindow to the left by a column (after
clicking once the Right-Pointing Arrow of the Horizontal ScrollBar).

5. Consequently, column-L scrolls into view ; repetitive clicking as
in step-4 follows-suit.

6. As it appears, the (target) ScrollBar continues to shift sideways
(to the left hand side of the ActiveWindow) in the midst of column-K
until it drops out of the LHS of the screen ; although during the
interim, one would have expected that the (target) ScrollBar might be
floating over column-L, then Column-M and beyond, it has not happened
like that.

7. This query is intent upon a means of keeping the (target) ScrollBar
relatively stationary (since its Debut) with reference to the
ActiveWindow (despite that the underlying GridLines and hence the
Ranges thereof may be wound to the Left or Right and similarly,
unwound Up or Down).

But, how to accomplish that something just like a Floating Toolbar ?

8. Please share your experience. Regards.

 

[Greg Wilson]

The scrollbars you describe are worksheet based and don't float. You need
either a userform based scrollbar, or perhaps can suffice with a toolbar;
although they don't support scrollbar controls. Short of subclassing, you
can't capture the scroll event and thereby programmatically move the
scrollbar when scrolling. Rarely is subclassing worth it (IMO).

The best I can offer is to create a userform and put a scrollbar on it. Make
the UF as small as possible, just big enough to hold the scroolbar. The
scrollbar's top and left properties should both be zero. You need to be
familar with UF programming. When you call the UF, use vbModless. For example:
UserForm1.Show vbModeless

If you don't need the slider control, you can go with a toolbar. Clicking
buttons with up/down arrow faceid's perhaps may suffice. I once rigged it so
that clicking the same button, say faceid 447, would cause scrolling in
either direction, depending on what part of the button is clicked. Just a
thought.

Greg

 

[tkt_tang]

Mr. Greg Wilson,

Thank you very much indeed for responding to my query.

Will study in details.

Regards.