Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

How to Use Scroll Bar in Userform

Some times we need to use very large userforms in our VBA projects. Size of the form depends on the number of fields and various objects we have in our form.  And if we need to show them in one window, we need to create big userform to put them all. You can create a form of any size by giving appropriate values for Height and width in properties window. But if our userform is bigger than the screen we need to use scroll bars inside our form. It is easier to put a scroll bar to a userform. But I saw that many people have problems with setting scroll bars correctly. Lot of people say that their scroll bars not working at run time. So there are few important things you should know when using scroll bars.

I will explain these important facts using a simple example. I have created a large form which has height of 1200 and width of 420. And there are a lot of text fields inside my userform.

If you look at properties window, you will notice that ScrollBars value is set to 0-fmScrollBarsNone by default. So we need to change this property depending on our requirement. If you have a userform with larger width, then you need to set ScrollBars value to 1-fmScrollBarsHorizontal. If you have a userform which has higher height, then you need to set this value to 2-fmScrollBarsVertical. And if you need scroll bars in both directions you can set the value to 3-fmScrollBarsBoth. You need to set to this value only if both width and height of your form is bigger than the screen.


You should set ScrollTop value to the 0. So then it will always appear at top.

Finally you need to set the ScrollHeight. It is very important to set appropriate value to this. Othewise you may not able to scroll up to the bottom of the form. Also if you set higher value, then user will scroll after the end of the objects. So user will see like your form is extended more than needed. So you should set a optimum value for this property for correct functionality and nice appearance. For example if you have a form having a height of 1200, you should set ScrollHeight value around 1850.

Contact Form

Name

Email *

Message *

Popular Posts