O'Reilly logo

Excel 2013 Power Programming with VBA by John Walkenbach

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 12: 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.eps

Chapter 13 contains additional examples of more advanced UserForm techniques.

Using CommandButtons in a UserForm

Figure 12-1 shows an example of a UserForm that uses CommandButton controls as a simple menu.

9781118490396-fg1201.eps

Figure 12-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

    Call Macro1

    Unload Me

End Sub

This procedure hides the UserForm, calls Macro1 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required