Page 453 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects

Creating Advanced User Forms
Then you can set the LastRow variable by adding the following line to the UserForm_Initialize
event.
LastRow = FindLastRow

The FindLastRow function can also be used in the event associated with the Last button to
update the LastRow variable as well as set the value for the RowNumber control.

Private Sub CommandButton4_Click()
Chapter 20
LastRow = FindLastRow - 1
RowNumber.Text = FormatNumber(LastRow, 0)

End Sub

Editing Data
At this point, you can view any row of data in the worksheet, but any changes you make in the
data displayed on the form aren’t saved in the worksheet. There are a lot of different techniques
you can use, but here’s one that should work well for you.
In this approach, the data displayed on the form is kept separate from the cells on the work-
sheet until the user explicitly presses either the Save or the Cancel button. Pressing the Save
button should copy the data from the form to the worksheet, whereas pressing Cancel should
reload the data from the worksheet, overwriting any changes in the user form that may have
been made by the user. Both the Save and Cancel buttons should be disabled until the data on
the form is actually changed.
The easiest way to disable these buttons is to set their Enabled property to False. Then change
the Enabled property to True once one of the values in the field changes. You can reduce the
amount of work by creating two subroutines, one named EnableSave and one named
DisableSave, which enable and disable the command buttons associated with Save and
Cancel, respectively. Then, in the Change event associated with the text boxes that contain
data, add a call to the EnableSave subroutine. This setting means that any change to the data
will mark the entire form as dirty, meaning that the data in the form is different from the data
on the worksheet.
Because loading the data directly from the source means that the data is clean, the Save and
Cancel buttons should call the DisableSave routine. This call should be placed only after the
data is loaded onto the form because it’s possible that the user might not have entered a valid
row number and GetData might not actually reload any data.
The PutData routine found in the user form module (shown in the following listing) is sim€
ilar to the GetData routine in that all the validations used to ensure that the value in
RowNumber is valid are included. The main difference between the two routines is that the
GetData routine copies information from the worksheet, whereas the PutData routine copies
data to the worksheet.

427