Excel VBA Macro Programming

As you design your form, you may want to see what it looks like when it is running. You can do this in design mode on the form by selecting Run Sub/UserForm or pressing F5 .

As usual with Visual Basic objects, each form has its own module to deal with events on the form, as shown in Figure 9-4. To access the module, double-click the design form, select View Code from the menu, or press F7 .

Figure 9-4: The code window for the UserForm showing events

You can see a drop-down list on the right-hand side of the module that lists events for the form that you can attach code to. For example, one of the events is Initialize , which is fired off when the form is called. Click Initialize, and header and footer code will automatically appear for that event. Add a message box as follows :

Private Sub UserForm_Initialize() MsgBox "This is my form" End Sub

Now press F5 to run the form. Your message box appears first, and then the form appears after you click OK. By examining the list of events, you will see that there are a number that you could put code into. Try the MouseMove event. Click MouseMove to get the code header and footer and then insert the following code:

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As _ Integer, ByVal X As Single, ByVal Y As Single) If UserForm1.Caption = "UserForm1" Then UserForm1.Caption = "You moved the mouse" Else UserForm1.Caption = "UserForm1" End If End Sub

You will find that when you move the mouse over the form, the caption in the title bar of the form keeps changing.

Note that the MouseMove event also passes you parameters for the X and Y position of the mouse on your form and for any mouse buttons or Shift key being pressed. You will see events for KeyDown , KeyUp , MouseDown , and MouseUp :

• KeyDown ‚   ‚   Fired off when a key is being pressed down by the user .

• KeyUp ‚   ‚  Fired off when the key is released from being pressed down by the user.

• MouseDown ‚   ‚   Fired off when a button on the mouse is pressed down by the user.

• MouseUp ‚   ‚  Fired off when the user releases a mouse button from being pressed down by the user.

• KeyPress ‚   ‚  Fired off when a key on the keyboard is pressed and then released. The parameter in this event will give you the value of the key pressed. This is a combination of both KeyDown and KeyUp .

• Click ‚   ‚  Fired off when the mouse is clicked on a control such as a command button or a list box.

All the events procedures pass different parameters depending on what event they are dealing with. Try placing a message box on each event to display these parameters and show what event is being called. This way you will see very clearly when an event is called and what parameters are passed to it.