Microsoft Office Tutorials and References
In Depth Information
Writing UserForm data to a worksheet
Writing UserForm data to a worksheet
After you’ve created your UserForm, you need to write VBA code to record the controls’
values to a worksheet. You do that by adding a command button to your form and
assigning code to the button’s On_Click event that reads the controls’ values and writes them to a
worksheet.
The process to read and write these values identifies the first empty row in the target
worksheet and then uses the Value property of the Cells object to write the data into the target
cells. As an example, suppose you have a UserForm that collects four pieces of data for a
shipment: the customer’s name, origination postal code, destination postal code, and
shipping method.
Next, create a command button to which you can attach code that writes the values to the
worksheet. To create the command button, display a UserForm and then, in the Toolbox,
click the CommandButton button. Draw the button on the UserForm and, if you want,
change the button’s Caption property so that the button’s text describes its function.
Right-click the button and, from the shortcut menu that appears, click View Code to display
the button’s On_Click event handling code. You could use the following routine to find the
first empty cell in column A of your worksheet, read the values in the four controls, and
write values into the worksheet.
Private Sub CommandButton1_Click()
Dim lngFirstRow As Long
Worksheets(“Sheet1”).Activate
lngFirstRow = Worksheets(“Sheet1”).Range(“A1048576”).End(xlUp).Row + 1
Cells(lngFirstRow, 1) = txtCompName.Value
Cells(lngFirstRow, 2) = txtOrigPostCode.Value
Cells(lngFirstRow, 3) = txtDestPostCode.Value
Cells(lngFirstRow, 4) = lstMethod.Value
Cells(lngFirstRow, 4).Activate
End Sub
12
 
Search JabSto ::




Custom Search