Microsoft Office Tutorials and References
In Depth Information
14.4 Adding UserForm Code
Generally speaking, a control (or ActiveX control ) can be thought of as a special type of code
component that can be placed within a larger container object (such as a form) and has the
following properties:
Controls generally (but not always) provide a visual interface for communication with the
Controls can have methods that can be invoked by the user.
Controls can have properties that can be read and set by the user.
Controls can have events for which the user can write event code.
We discussed events that are associated with Excel objects (worksheets, workbooks, charts, and so
on) in Chapter 11 . Control events work in precisely the same way, as we will see in the upcoming
14.4 Adding UserForm Code
In general, VBA programmers add two types of code to a UserForm module: event code that
underlies the various controls on the form (and perhaps the form itself) and additional procedures
that perform utility functions needed by the application. The latter code is added to the general
section of the UserForm code module.
To illustrate the point with a very simple example, suppose we want to create an application that
sorts selected columns (treating each column as a single object) using the first row as the sort key.
Our form might look something like the one shown in Figure 14-2 .
Figure 14-2. A Sort dialog box
When the user clicks the Sort button, VBA will ask him or her to confirm the sort operation and
then act accordingly. Now, when the Sort button is selected by the user, VBA fires the Click event
for this button. If the button is named cmdSort , then VBA provides the event code shell:
Private Sub cmdSort_Click()
End Sub
Clearly, we want to perform the sorting operation when this event is fired. However, it would not
be a good idea to place the actual code to perform the sort in this event code shell. Instead, we
write a separate sorting procedure to do the sorting and place it in the General section of the
UserForm module, or perhaps make it a public procedure in a separate standard code module
within the project:
Public Sub SortColumns()
' code here to sort text
End Sub
Search JabSto ::

Custom Search