Figure A Sort dialog box

f Sort Columns



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

There are several reasons why it is better to place the sorting code in a separate procedure. This code modularity makes it easier to:

• Use the code in other locations in the application

• Move the code to other applications

• Find and repair bugs in the code

• Make improvements or additions to the code

• Just plain read the code

Once the sorting procedure is complete, we can add the following code to the Click event:

Private Sub cmdSort Click()

If MsgBox("Sort currently selected columns?", vbQuestion + vbYesNo) = vbYes Then SortColumns End Sub

Incidentally, the Click event for the Cancel button is often just the following:

Private Sub cmdCancel Click()

Unload Me End Sub

All this does is unload the form.

While on the subject of unloading a form, it is important to understand the distinction between unloading a form and hiding a form. We can hide a form by setting the form's Visible property to False. This makes the form invisible, but it still consumes resources, such as memory. When we unload a form, it no longer consumes resources. (Well, this is not quite true. We need to not only unload the form, but also to set any variables that reference the form to Nothing.)

0 0

Post a comment