0:03
The next step then is going to take whatever they enter.
So if somebody entered the different stuff here and
pressed Add Record, we want that to then be placed on the spreadsheet.
In this case, the next empty row, which is row 4.
So now let's work on doing this last part.
This last part is going to happen when we click Add Record.
So after the user fills in these three fields, we want to click Add Record.
So I'm going to go ahead and click on that.
And this is where we're going to put the code for this.
We're going to Dim nr As an Integer, that's going to be the number of rows.
We determine number of rows using the WorksheetFunction CountA.
0:44
Columns A:A- 1, so that's going to tell us the total number of rows,
just like we did previously.
We're going to make sure that Range ("A1") is selected.
And then we're going to ActiveCell.Offset.Select.
So we're changing the selection of the ActiveCell, but
we're going to say ActiveCell.Offset (number of rows + 1).Select.
What this does is it selects in the same column.
So we're not ActiveCell offsetting any columns with the 0 here,
but we're going to select and choose the first empty row in column A.
So in our example, this would then go down to cell A4.
1:23
And finally with these three lines,
we're taking the items that were input into these three boxes.
We're taking those one at a time and
placing those into the empty row that we are now on.
So after we select the new ActiveCell,
we're going to offset by (0, 0) and place the name.
We're going to offset (0, 1) to place the address.
And we're going to offset by (0, 2) to place the phone number.
So let's go ahead and see if this works.
In the Add Record Sub, I'm going to put a break point here.
And let's go ahead and run it from the beginning.
I'm going to press F5, I'm going to enter some information here.
2:07
Now I'm going to click Add Record.
We bump into this Private Sub, the AddButton Private Sub.
And now I'm going to press F8.
We count the number of rows, which is 3 minus 1 for the first row here.
That's 2, so we have two elements.
We're going to select cell A1.
And now I'm going to ActiveCell.Offset 3 rows and 0 columns.
So that'll go 1, 2, 3, and that'll be A4.
So now we're re-selecting as our ActiveCell, cell A4.
And now what I'm going to do is I'm going to take these three input fields,
input one, input two, and input three, one at a time.
And I'm going to place them in ActiveCell.Offset all on the same row.
But I'm going to ActiveCell.Offset 0 columns, 1 column, and 2 column.
So I press F8 three times, and
we place that information that was in the Add Record form onto the spreadsheet.
And then we end that Sub.
And then if I press Quit, we can quit, or I could add another.
Let's do another one here.
So when I click here, I'm just going to go through this pretty quickly,
we're going to place Sally's information on the spreadsheet.
And then I can go ahead and press Quit.
All right, so that kind of shows you how you can
take the information from the Add Record form and place it onto the spreadsheet.
In the next Screencast, I'm going to show you how you can use kind of a dynamic
number of columns, how we can detect that.
And also how we can reveal or unhide some of these hidden labels and text boxes.
Charlie NuttelmanInstructor
