NotesĪdding 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. 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. Once you do this, you will see that some of the controls will no longer be visible.
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. 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. Make sure that the values for ScrollLeft and ScrollTop are set to 0. 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. In this example we will only use vertical scroll bars. 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. 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.Ĭlick the option to the right so that a menu appears.įmScrollBarsHorizontal means that left-to-right scroll bars will appear.įmScrollBarsVertical means that up-and-down scroll bars will appear - most common choice.įmScrollBarsBoth means that both horizontal and vertical scroll bars will appear. 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. 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:. 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.Īlso, make sure the properties window is displayed, if it isn't, hit F4 to display it. 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. Bill’s next webcast on CFO.com is “Using Excel for Advanced Financial Data Analysis,” airing on Tuesday, October 11.How to make a scrollable UserForm. His latest book, Learn Excel 2007-2010 from MrExcel, contains solutions to 512 mysteries like this one. In past editions of Excel, copying the worksheet was enough, but in Excel 2007, the scroll bar will not resize until you save the workbook.ĬFO contributor and founder of Bill Jelen is the author of 33 books about Microsoft Excel. Even after you delete the extra rows, Excel will not restore the size of the workbook. The scroll-bar slider will return to full size. Delete all rows from the bottom of your data set to the rogue last row.This row is usually way beyond the row that you believe to be the last row. Excel will jump to the intersection of the last active row and the last active column.
Press the End key and then press the Home key.
Note the last row that you believe to contain data. Perhaps someone pressed the space bar or applied text formatting, etc. 
If this does not work, then there is one rogue cell way below your data that has become activated. You can often restore the size of the slider by moving it completely to the top of the spreadsheet. This can happen if someone presses End+Down Arrow key to move to row 1038718 (see below).
0 kommentar(er)
