Microsoft Office Tutorials and References
In Depth Information
Part 5: Manipulating Excel Objects
Microsoft Office Excel 2003 Programming Inside Out
6 Add three more CommandButton controls. Change the Caption property of the first
one to Save , the second one to Cancel , and the last one to Add .
7 Change the Enabled property on the Save and Cancel CommandButton controls to False .
Once all of the controls have been added, you can tweak their sizes and exact place€
ment on the form until you find a pleasing arrangement. (See Figure 20-3.)
Finishing the form layout.
With the form constructed, it’s time to copy data from the worksheet to the form. The
RowNumber text box contains the number of the row that should be displayed on the form,
so the real trick is to convert the value in the RowNumber text box into a value that can be
used to extract the data from the worksheet using the Cells method.
The following program listing shows the GetData routine, which is located in the module
associated with the user form. GetData copies the data from the currently active worksheet to
the user form. After declaring a temporary variable r to hold the current row, the routine ver€
ifies that the value in the RowNumber control is numeric. This step is important because the
user could type any value into this field.
Private Sub GetData()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)