Scrollable UserForm

Add to Favorites
Author: don

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:
    24e46d50f704459f463b452a7a2c1cb6.png
    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.
    f1ad10181dc8047fff3db25400a350ce.png
    Click the option to the right so that a menu appears.
    970906a409cb04f3462badc0cd1aaf44.jpg
    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.
    550ee752c4d061d17865d65b1ca3e819.png
    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.
    85d17298d41f5d4c2829fff016658093.png
    You are now ready to test the form out!

Run the form to see the results:

a25a28471fd35403daf39c6dabc99f6f.png

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.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
Make a UserForm in Excel
Tutorial: Let's create a working UserForm in Excel. This is a step-by-step tutorial that shows you e...
UserForm Events
Tutorial: Explanation of UserForm Events in Excel. This includes what they are, how to use them, and...
UserForm Controls
Tutorial: This tutorial gives you an overview of what a UserForm Control is and how to add them to y...
Use Macros with UserForms
Tutorial: This tutorial explains how macros interact with UserForms. This includes an explanation of...
Showing a UserForm
Tutorial: How to display a UserForm in Excel and some common things you might want to do when you di...
UserForm Properties List and Explanation
Tutorial: This is a listing of all properties for the UserForm in Excel. Each property includes an e...
Tutorial Details
Downloadable Files: Excel File
Similar Content
Make a UserForm in Excel
Tutorial: Let's create a working UserForm in Excel. This is a step-by-step tutorial that shows you e...
UserForm Events
Tutorial: Explanation of UserForm Events in Excel. This includes what they are, how to use them, and...
UserForm Controls
Tutorial: This tutorial gives you an overview of what a UserForm Control is and how to add them to y...
Excel Forum