How to add a new record, Part 2

Loading...

En provenance du cours de University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 3 (Projects)

70 notes

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 3 (Projects)

70 notes

Cours 3 sur 3 dans Specialization Excel/VBA for Creative Problem Solving

In this course, learners will complete several VBA projects. It is highly recommended that learners first take "Excel/VBA for Creative Problem Solving, Part 1" and "Excel/VBA for Creative Problem Solving, Part 2". This course builds off of skills learned in those two courses. This is a project-based course. Therefore, the projects are quite open-ended and there are multiple ways to solve the problems. Through the use of Peer Review, other learners will grade learners' projects based on a grading rubric.

À partir de la leçon

Intermediate Projects: Lesson Choices

Introduction to the Ultimate Organizer project7:58

A demonstration of the Ultimate Organizer7:38

How to add a category7:21

How to add a new record, Part 18:00

How to add a new record, Part 23:50

How to reveal hidden labels and text boxes9:22

How to add an icon to the Quick Access Toolbar to run your user form3:26

Rencontrer les enseignants

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

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.

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.

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.

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.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2019 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play