You’ll find that this control doesn’t allow the user to use shortcut range-selection keys (for example, pressing End, followed by Shift + the down arrow will not select cells to the end of the column). Unfortunately, the RefEdit control has a few quirks that still haven’t been fixed. If the user clicks the small button on the right side of the control, the dialog box disappears temporarily, and a small range selector is displayed - which is exactly what happens with Excel’s built-in dialog boxes. The RefEdit control doesn’t look exactly like the range selection control used in Excel’s built-in dialog boxes, but it works in a similar manner. Your UserForms can also provide this type of functionality, thanks to the RefEdit control. The user can either type the range addresses (or names) directly or use the mouse to point and click in a sheet to make a range selection. For example, the Goal Seek dialog box (displayed by choosing Data ➜ Data Tools ➜ What-If Analysis ➜ Goal Seek) asks the user to select two single-cell ranges. Many of Excel’s built-in dialog boxes allow the user to specify a range.
The two examples in this section are available on the book’s website in the userform menus.xlsm file.Ĭhapter 15 shows a similar example in which you can use a UserForm to simulate a toolbar. Double-clicking an item in the ListBox executes the corresponding macro. In addition, this UserForm has a procedure to handle the double-click event for the ListBox. If the ListIndex is -1, nothing is selected in the ListBox, and the user sees a message. The procedure uses a Select Case structure to execute the appropriate macro. This procedure accesses the ListIndex property of the ListBox to determine which item is selected. The Execute button also has a procedure to handle its Click event: This procedure, which follows, uses the AddItem method to add six items to the ListBox: Before the UserForm is displayed, its Initialize event-handler procedure is called. This style is easier to maintain because you can easily add new menu items without adjusting the size of the UserForm. The other buttons have similar event-handler procedures.įigure 14.2 shows another example that uses a ListBox as a menu.įigure 14.2 This dialog box uses a ListBox as a menu. This procedure hides the UserForm, calls Macro1, and then closes the UserForm. For example, the following procedure is executed when CommandButton1 is clicked: Each CommandButton has its own event-handler procedure.
#Excel vba userform examples free download code#
Setting up this sort of UserForm is easy, and the code behind the UserForm is straightforward. This section presents two ways to do this: using CommandButtons or using a ListBox.Ĭhapter 15 contains additional examples of more advanced UserForm techniques.įigure 14.1 shows an example of a UserForm that uses CommandButton controls as a simple menu.įigure 14.1 This dialog box uses CommandButtons as a menu. In other words, the UserForm presents some options, and the user makes a choice. Sometimes, you might want to use a UserForm as a type of menu. Understanding various techniques that involve a ListBox control.Zooming and scrolling a sheet from a UserForm.Changing the size of a UserForm while it’s displayed.Excel 2016 Power Programming with VBA (2016) Part III.
0 kommentar(er)
