Some Background on UserForms, Controls and Events

 

Introduction.  In your first course in VisualBasic you undoubtedly worked with forms, controls and developed event code.  Due to this I will present only a brief review with emphasis on features that differ from VisualBasic for databases.  In actuality, there are two major places that you can create forms in Excel.

  • Within a worksheet

  • Within the VisualBasic IDE

We will focus on developing forms in the VB IDE.  We will also make use of controls within a worksheet, but on a much more limited basis.  At first it may be a bit confusing to jump between them, but that shouldn't last long.

What you want to do now is open a new workbook.  Now you want to work through the following steps in order to insert a new UserForm.

  1. Click on the VisualBasic IDE toolbar button.

  2. In the Project Explorer window right click on the VBAProject (Book1) as in the following image.  Though I have rearranged the Project Explorer so that it will show up in the same image as the menu.

  3. Now you want to click on the Insert menu.

  4. Select the UserForm item.

Now you should get a new UserForm like the following.  You should also expand the size of the form slightly.  If your Toolbox doesn't appear you need to click on the View menu and Select the Toolbox item.  You can position it wherever you want.  Notice the following image also contains the Project Explorer and the Properties Window, both of which should be quite familiar.

The Toolbox and Controls.  Before going any further I want to describe the tools in the toolbox.  Most of them should be quite familiar, though the default tools in the Excel VB IDE are somewhat different than in the pure VB IDE.  The following table has a discussion of each.  You can determine the identity of each tool by hovering your mouse pointer over the icon so that the name of the tool will appear in a little window.  Each of these tools is associated with a control.

 

Tool/Control Description
Pointer Allows you to select objects in the form window
Label Use this to display labels or text in your form
TextBox This allows a user to input text or to have text appear from some other source.
ComboBox This is a drop down box that contains a predetermined set of entries for the user to select from.  It usually takes up less space than a ListBox.
ListBox This presents a list of items for the user to select from.
CheckBox This is good for selecting something that is either/or, yes/no.
OptionButton This is useful when the user needs to select from a small number of items.  They are almost always used in groups.
ToggleButton This has only two states, either on or off.
Frame This is used to enclose smaller subsets of controls on a form.  This can be done aesthetically or to logically group sets of controls.
CommandButton Click on one of these to execute some event code such as open another form, find certain data or whatever.
TabStrip A bit of a pain in the large fleshy part of your body that you often sit on.  It is better to use the MultiPage.
MultiPage This allows you to create tabbed dialogues with several pages that are easy to move between.  A good example of one of these is for the format cells dialogue in Excel.
ScrollBar Use this to change the controls value.  It is different from a SpinButton in that you can drag a button to change values in larger increments.  Good for selecting a value that can range over a wide scale of possible values.
SpinButton You can increment or decrement the value in the control with which it is associated.  Usually used with a TextBox or Label.
Image Use this to put images into your form either from a file or pasted from the clipboard.
RefEdit Use this to enable a user to select a range in a worksheet.

 

Putting Some Controls on the Form.  It is necessary to have some review of events and developing event code.  We will develop a simple form with two labels, two textboxes and one command button.  We will then copy event code into several different events associated with these controls.  The following table contains the controls you should add to the UserForm and the properties you should change.

 

Control Property Value
Form Name frmEvents
  Caption Event Occurrences
  Font Tahoma - 12PT - Bold
Label Name lblFirst
  Caption The First TextBox
  TextAlign Right
Label Name lblSecond
  Caption The Second TextBox
  TextAlign Right
TextBox Name txtFirst
TextBox Name txtSecond
CommandButton Name cmdButton
  Caption Click Me

 

You want to put these controls onto the new UserForm so that they look something like the following.

 

 

Events and Event Code.  In order for a user to interact with a form, which is the word we will use in place of dialogue and window, the program needs to monitor when the user does certain things with the form.  There are a large variety of interactions that VisualBasic watches for, from mouse click to moving into certain areas.  These monitored interactions are called events.  When an event occurs it is possible to have written some VB code so that other things happen for the user.

If you double click on the cmdButton control you will bring up a code window that looks like the following.

Notice the  Private Sub cmdButton_Click( ) statement that appears in order for you to start entering the event code for the click event of the command button.

You should notice the combo box on the upper left, called the Controls Combo Box, which lists all of the controls associated with the form.  You should also notice the combo box on the upper right, called the Events Combo Box, which contains the events that VisualBasic monitors for each of the controls.

In the case of the command button, the Click event is the event that a developer is most likely to use so it is the default when the developer double clicks the command button.  Different controls have different default events.  For example, the text boxes will default to a Change event.  In order to make use of a different event the developer needs to select that event from the event combo box.

The following image shows the code window with the control combo box expanded. 

The next image shows the code window with the event combo box expanded.  Notice that both combo boxes can have a scroll bar to allow the developer to scroll through even more controls or events.

Now you want to copy the following code in for the cmdButton_Click event.

Private Sub cmdButton_Click()

MsgBox "You just clicked the command button"

End Sub

This will cause the user to get a message box when they click the command button.

Now the set of events gets more involved.  You should double click on the first text box and copy in the following code.

Private Sub txtFirst_Change()

MsgBox "The CHANGE event on the FIRST text box just occurred"

End Sub

Now you want to use the event combo box to select the MouseDown event.  You should copy the following code into the procedure for this event.  Make sure to ignore the words that are wrapped in the procedure declaration.  There are a lot of arguments to pass for this event.

Private Sub txtFirst_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

MsgBox "You just MOUSE CLICKED into the FIRST text box"

End Sub

Now you want to use the event combo box to select the KeyPress event.  You should copy the following code into the procedure for this event.

Private Sub txtFirst_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

MsgBox "You just PRESSED a key while entering info in the FIRST textbox"

End Sub

We could certainly put in messages for when other events occur, but this should give you the picture.  One thing you might remember from the pure VB IDE is that there were many more events that could occur in the event combo box.

Finally you want to enter similar code for each of these same events for the second text box.  For the Change event.

Private Sub txtSecond_Change()

MsgBox "The CHANGE event on the SECOND text box just occurred"

End Sub

For the MouseDown event in the second textbox.

Private Sub txtSecond_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

MsgBox "You just MOUSE CLICKED into the SECOND text box"

End Sub

And finally for the KeyPress event for the second textbox.

Private Sub txtSecond_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

MsgBox "You just PRESSED a key while entering info in the SECOND textbox"

End Sub

Now you can execute the program by using the Run menu or the Run icon on the toolbar.  This should convey to you a sense of the sequence of events and some of the potential for using event code.

Executing the Event Code.  VisualBasic in Excel is slightly different than the stand-alone VisualBasic.  In order to implement the code you may want some sort of executable code to be invoked from the spreadsheet.  When working in Excel it will be very important to distinguish whether the control is on a spreadsheet or on a UserForm.  Up to now we have worked purely with UserForms.  now we want to put a command button onto Sheet1 in order to execute the code associated with frmEvents that we have just developed.  You need to work through the following steps.

  1. Make Sheet1 the active worksheet.
  2. Click on the View menu.
  3. Select the ToolBars item.
  4. Select the Control ToolBox item on the submenu that appears.
  5. Draw a command button onto the spreadsheet around cells C5 ... D7.
  6. Right click on the button and select the Properties item in the popup menu.
  7. Change the Caption to Run the Form.
  8. Close the Properties window.
  9. Double click on the command button to bring up a code window for the click event.
  10. Copy the following code into the space provided.

Private Sub CommandButton1_Click()

frmEvents.Show

End Sub

This code goes with the spreadsheet rather than with the form.  We will emphasize these sorts of little distinctions all semester.

  1. Close the ToolBox on the spreadsheet.
  2. Click on the Design Mode button on the toolbar near the upper right of the spreadsheet to get out of design mode.
  3. Click on the command button.
  4. Type a letter in the first textbox.  You must click OK on each message box as it appears.
  5. Type a letter in the second textbox.  You may need to use the tab key to move between controls.  Isn't this FUN?!?!
  6. Type another letter in the first textbox.
  7. Click on the command button.
  8. Click on the X in the upper right hand corner of the form to end the program.

Hopefully you now have a better sense of event sequences and event monitoring.  We will be doing these sorts of things for the rest of the semester, though in more useful ways.