O'Reilly logo

Excel 2016 Power Programming with VBA by Richard Kusleika, Michael Alexander

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

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.

Using CommandButtons in a UserForm

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

Screenshot shows Button Menu Demo dialog box which displays Execute Macros 1 to 6 and selects Execute Macro 1. Bottom page indicates a Cancel button.

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 ...

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