Microsoft Office Tutorials and References
In Depth Information
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
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()
LastRow = FindLastRow - 1
RowNumber.Text = FormatNumber(LastRow, 0)
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
worksheet 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 Tr ue 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€
filar 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.