Your challenge for today is to come up with a real-life use for this nifty (do the kids still say nifty?) Excel scrolling tool.
Here’s how the tool works:
- Click a button to select a group of cells that you want to work with, like this list of months.
- Then, click the up and down arrows, to scroll the list through the selected cells.
- When you’re done, click the Exit button, to “release” the scroll area.
In this example, the scrolling list is in cells D8:E19 – months and numbers. The original list had January at the top, and February in the second row. I clicked the Up arrow twice, and now March is at the top of the list.
Cautions Before Using
In his sample file, Jim lists a few things to consider, before you use the code in one of your own files.
- The Custom Scroll Area program may alter...
- Cell interior colors
- Conditional Formatting
- Data Validation
- Merged cells
- Undo (on the edit menu) will not undo custom scrolling.
Jim Cone sent me this file, because he thought it was a unique tool, but he couldn’t think of a practical use for it. He wondered if an Excel slot machine was possible.
I don’t have any ideas, but maybe the Blue Jays could use it to rotate their starting lineup, now that baseball season is underway.
Do you have any suggestions, brilliant or otherwise?
Download the Sample File
To see the scrolling tool, and the code that runs it, you can download Jim’s sample file from my Contextures website. On the Sample Files page, go to the “UserForms, VBA, Add-Ins” section, and look for UF0021 – Scroll Items Up and Down in List
The VBA code is protected, and the password is: s
And if you have any ideas or suggestions, please share them in the comments, or contact Jim at the address in the sample file. Thanks!