Excel 2016 Power Programming with VBA

14 UserForm Examples

In This Chapter

• Using a UserForm for a simple menu
• Selecting ranges from a UserForm
• Using a UserForm as a splash screen
• Changing the size of a UserForm while it’s displayed
• Zooming and scrolling a sheet from a UserForm
• Understanding various techniques that involve a ListBox control
• Using an external control
• Using the MultiPage control
• Animating a Label control

Creating a UserForm “Menu”

Sometimes, you might want to use a UserForm as a type of menu. In other words, the UserForm presents some options, and the user makes a choice. This section presents two ways to do this: using CommandButtons or using a ListBox.

Cross-Ref

Chapter 15 contains additional examples of more advanced UserForm techniques.

Using CommandButtons in a UserForm

Figure 14.1 shows an example of a UserForm that uses CommandButton controls as a simple menu.

Figure 14.1 This dialog box uses CommandButtons as a menu.

Setting up this sort of UserForm is easy, and the code behind the UserForm is straightforward. Each CommandButton has its own event-handler procedure. For example, the following procedure is executed when CommandButton1 is clicked:

Private Sub CommandButton1_Click()
    Me.Hide
    Macro1
    Unload Me
End Sub

This procedure hides the UserForm, calls Macro1, and then closes ...