Scrollable UserForm

How to make a scrollable UserForm. This allows you to put scroll bars onto a form so that it can hold information that is accesible when the user scrolls down or to the right.

Sections:

Make a Scrollable UserForm

Notes

Make a Scrollable UserForm

Make sure you are viewing the form. Alt+F11 to go to the VBA window > Double-click the UserForm from the Project window (Ctrl+R if you don't see that window) and then make sure the form itself is selected and not a control within the form; do this by clicking the title bar at the top of the form.

Also, make sure the properties window is displayed, if it isn't, hit F4 to display it.

1. For now, make the form large enough to display all of the contents on it; then, look to the properties window and take note of the number for the Height property:
   
   The value of 236.25 is what is needed to display everything on the form and this number will be used later in the tutorial.
   If your form is very wide and you need a scroll bar to go left-to-right, also look to the Width property and take note of that number when the form is large enough to display everything.
2. Go down to the ScrollBars option.
   
   Click the option to the right so that a menu appears.
   
   Choose the correct option:
   fmScrollBarsHorizontal means that left-to-right scroll bars will appear.
   fmScrollBarsVertical means that up-and-down scroll bars will appear - most common choice.
   fmScrollBarsBoth means that both horizontal and vertical scroll bars will appear.
3. Take the number that you got from Step 1 and put that into the ScrollHeight property if you are using vertical scroll bars and/or if you are using horizontal scroll bars, put the value from the Width property in for the ScrollWidth property.
   In this example we will only use vertical scroll bars.
   
   You will also notice that once you enter the number for the ScrollHeight or ScrollWidth property, the scroll bar will appear on the form, as you can see in the image above.
4. Make sure that the values for ScrollLeft and ScrollTop are set to 0. These properties allow you to have a form that is part of the way scrolled by default; if that sounds confusing, just wait until you get the scroll bars working and test some values for this field, say 20, and then run the form and you will understand.
5. Resize the form so that it is the size that you want it to be, which should be smaller than it was in step 1. Once you do this, you will see that some of the controls will no longer be visible.
   
   You are now ready to test the form out!

Run the form to see the results:

If you notice too much empty space at the bottom of the form, just go back to the property window and adjust the value for the ScrollHeight property until it looks right to you - do the same for the width if you are using horizontal scroll bars.

Notes

Adding scroll bars to UserForms is not difficult, it's just a little bit annoying since it doesn't work as intuitively as it seems like it should.

Just remember, to adjust these properties:

1. ScrollBars
2. ScrollHeight and/or ScrollWidth
3. ScrollLeft and/or ScrollTop

The ScrollLeft and ScrollTop properties are rarely used because, usually, forms start at the upper-left-most position.

Make sure to download the sample file for this tutorial so you can see this example in Excel.