How to add a new record, Part 1

Loading...

En provenance du cours de University of Colorado Boulder

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

13 notes

University of Colorado Boulder

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

13 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

For the second project, choose one of two possible intermediate projects.

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

In the previous screencast,

I showed you how you could add categories.

For example, you could add three categories: name, address and phone number.

In this screencast, I'm going to show you how you can add records.

Records are essentially the rows of the spreadsheet.

Records, in my example,

they are going to be people names.

They are going to be names of people.

So let me show you first what we're going to be

making and then I'll show you how to make this.

We click this button, "Add Record".

It brings up an Add Record form.

Now, the important thing about

your ultimate organizer project is it's going to have to detect,

when I click that,

it's going to have to detect how many columns are on the spreadsheet.

Because the number of columns is dynamic,

it's going to be changing over time.

So you can't just like hard code this such that it just always has three different boxes.

You're going to actually have to make this Add Record box adapt to the number of columns.

So if there's three, it's only going to unhide three sets of labels and text boxes.

But if you have something like the maximum in this project,

which is 12 categories,

you're going to have to unhide

12 different pairs of labels and text boxes or input boxes.

So in this example, I'm just going through a fix number.

We have three categories and each of those categories,

we've taken the label and placed it next to an input box here.

So if I want to add a new record,

I could do something like, Charlie,

I live in Boulder,

and my phone number is 333-333-3333.

And when I click "Add Record",

it placed that information up here on the spreadsheet.

And then I could go ahead and add another one.

So maybe, somebody else,

lives in Moab, 123-456-7890, Add Record.

When you add a record, it has to

detect how many records previously are on the spreadsheet.

So it's going to have to count the number of items in column A.

And then we can go ahead and "Quit".

So that's what I'm going to show you how to do in this screencast.

I've got this starter file.

It's called AddRecord-STARTER.xlsm.

And I've already prepared some stuff here so you can practice.

I've got a button here called the Add button.

I've got a Quit button.

Let's go ahead and quickly code the Quit button.

That's pretty easy to do.

It's just UserForm1, and we're going to unload that.

So unload UserForm1.

You can't see this but I've got three hidden labels here.

So this is actually called Label1, Label2 and Label3.

I've got input1, input2 and input3.

So I'm just working with a fix number of three here.

So we have three input text boxes and three labels.

In the next screencast,

I'm going to show you how we can have more of these,

and how we can dynamically hide or show

them depending on how many categories exist on the spreadsheet.

Before I forget, let's go ahead in module one.

In a module, I'm going to just make a subroutine here called RunForm.

And I'm going to do UserForm1.show.

So that's going to show the user form.

Now we're going to have to do something in this RunForm before we show the user form.

Remember, the user form is going to have

six different items here that we have to account for.

Before we open up the user form,

we have to change the labels here.

So we want to change this first one to name,

the second one to address and the third one to email.

So let's go ahead and do that first.

So I'm going to go back to my module and we have to do that before we show UserForm1.

That's why we have to do it in the module here before we open up the user form.

And what I'm going to do is I'm going to make a PrepareForm subroutine.

And up here in our RunForm,

we're just going to prepare forms so we can call that subroutine.

This subroutine is just going to be kind of on its own.

So this prepares the form before opening.

Now, the first thing we have to do is we have to

detect how many items are currently on the spreadsheet.

So right now I've got two items plus the label here.

So I'm going to have to somehow detect how many rows we have.

And to do that, I'm defining nr As an Integer.

We're going to have an index of iteration, i.

We can then count the number of rows,

which is countA function of column A minus one.

We subtract one because that top row,

row one, has labels in it.

Another thing I'm going to do is I'm going to dim this vector called categories,

it's going to be a variant.

And because I'm working with vectors,

I'm going to put Option Base 1,

which I like to do up there at the top.

Categories is just going to be a vector consisting of the three categories: name,

address and phone number.

In this example, that's just going to be size

three but you'll see in the next screen cast we have to

adapt that because we are going to have a dynamic or varying number of category names.

So I've dimmed categories vector as a variant.

After we count the number of rows,

we're going to go ahead and select Range A1.

So that will just make sure that cell A1 is selected.

And then what we need to do is we need to go one at a time through

the first row and we need to take cell A1 and put that into our categories vector.

We need to take the next one, B1,

put that in our categories vector, and so on.

So that's what we're going to be doing with this For loop.

So this For loop is going to iterate for i equals one to three.

Again if you had a different number of columns,

you would change this three to something like number of columns,

and we'll do that in the next screencast.

But basically, we create this categories' vector

of all of the category labels that are in row one.

And finally, the goal is to take those category names and to place them on

UserForm1 here into Label1, Label2 and Label3.

So I can just do three lines of UserForm1.Label1 equals categories(1) and so on.

We can do that for the second item, the third item.

Unfortunately, you can't do this inside of a For loop because you

can't concatenate like Label1 here,

you can't split that up and use an i.

So we can't really easily use a For loop for that, unfortunately.

So this should prepare the form.

Let's go ahead and make sure that this is running.

So I'm going to step through.

We're going to prepare form.

So we jumped down into the PrepareForm sub,

we count the number of rows,

and that's down here in the Locals window, is two,

which makes sense because we have

three rows but we want to subtract one for the first row.

Then we're going to select range A1 which we make sure is selected.

We're going to enter into our For loop categories

of the ith element is going to be ActiveCell.Offset.

So let me go through the three of these

and we can open up the categories vector down here.

So we've taken up name,

address and phone number,

and those are the three category names in row one.

And then what I'm doing is I'm going to set UserForm1.Label1 equal categories(1).

We do that for the second item and the third item.

So this, even though you don't have UserForm1 open yet,

you can still populate it.

So we've changed those labels.

We end the PrepareForm sub,

which bumps us back to where we were in the RunForm,

and now we show UserForm1.

So what we've done, in essence,

is taking these three category names in row one and we've placed

that here as labels and then we've got the input that we need.

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.

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

Télécharger dans l'App Store

Disponible sur Google Play