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.
How to add a new record, Part 1
Loading...
Avis
4.9 (81 notes)
- 5 stars73 ratings
- 4 stars6 ratings
- 3 stars2 ratings
RR
Jun 06, 2018
Challenging and rewarding. Very useful and complete. Great tutor support. A course i am enjoying very, very much.
DF
Feb 03, 2019
Awesome course for all of us that want develop advanced VBA apps for Excel data analysis and management.
À partir de la leçon
Intermediate Projects: Lesson Choices
Enseigné par
Charlie Nuttelman
Instructor
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.
© 2019 Coursera Inc. Tous droits réservés.
