How to add a category

Loading...

En provenance du cours de University of Colorado Boulder

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

9 notes

University of Colorado Boulder

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

9 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

I've got a handful of screencast that are going to get

you started with the ultimate organizer project.

There's a lot that I'm not going to be showing you in the screencast,

so you're going to have to connect the dots.

But just to get you started,

I wanted to give you a few screencasts.

The first one is how to add a category to the spreadsheet.

Let me just show you what we're going to be making in the screencast.

We click "Add category",

and it comes up with these user form.

So this itself is a user form that I've made.

And it has new category name,

and I might put something like name,

it's going to add a category.

So you see that has placed it in row 1,

the leftmost empty cell of row 1.

And then I can do something like "Address".

We add that category and it adds it to the next one.

So it's automatically detecting when I do add category,

how many categories already have names or labels in row 1.

And I could do something like phone number and so on.

So that's what we're going to be creating,

just as simple subroutine and user form to do this in this screencast.

So I've already created this pretty simple user form,

and I've got a label here.

I've got a text box.

I've called this, "New Category Name".

I've got an "Add Category" button,

and this is just CommandButton1.

And I have a quick button which is CommandButton2.

The "Quit" button is pretty easy.

That's just Unload UserForm1.

So that will unload UserForm1.

Let's go ahead and, in a module,

I'm going to open up a module and I'm going to put Sub RunForm,

and all this is going to do is UserForm1.Show.

And this is the subroutine that we have associated with the button here.

So I'm going to go ahead and assign macro to that RunForm subroutine.

So then when I press this button,

it'll run the RunForm sub that opens up UserForm1.

And then the last thing we need to do is to code behind "Add category".

Now we need to make it such that it will detect how many labels are already in row 1.

So we are always going to start from the left,

and we're going to keep going to the right.

So let's go ahead and do this.

I'm going to click "Add category" button.

And here we have our CommandButton2.Click.

We're going to be iterating,

and I'll show you what I mean here in a minute.

So I need to dim I as an integer.

I'm going to want to select Range A1, so cell A1.

Now for all of this and a lot of the stuff I'm going to be doing in the screencast,

I'm assuming that there's only one sheet.

If you had multiple sheets,

then you're going to have problems with ambiguous references.

If I had multiple sheets,

I'd have to make sure I'm doing Sheet1.RangeA1.

So I'm just assuming I have one sheet.

First thing I'm going to do is I want the program to

select cell A1 because that's going to be kind of our starting point.

And then what I'm doing is I'm entering into this do loop.

And what I'm doing, and you could actually do this using

a for loop too if you counted the number of rows.

But right now we don't have anything in row 1,

and we don't have any labels.

And so I'm going to do this approach. I'm going to say "Do".

So I'm entering into a do loop,

i = i + 1.

So the first time through,

i will be equal to 1 because i going in is 0.

So we'll set 1 equal to 1.

If IsEmpty, so IsEmpty,

you can use to see if a cell is empty or not.

So I'm going to an ActiveCell.Offset by,

right now, i is equal to 1 so I'm going to active cell offset 00.

And that itself is just cell A1.

So basically what I'm asking on the first time through is,

is cell A1 empty?

If that's true, then I'm going to exit do.

So what I'm doing is I'm trying to find the first empty cell.

If I had labels already in here,

so if I just had things like this,

then I'm going to keep going through.

So if that were the case,

if I had labels in cells A1 through C1,

then the first time through the A1 is not empty.

So I bump i up by 1.

I ActiveCell by offset by one more column and check to see if that's empty, it's not.

So I'll go to the third one, it's not.

And I go to the fourth one and it is empty.

So I would come out of this at being set to four.

So in this example, it would mean that the fourth column of row 1 is empty,

and that's where we're going to put the new category.

But that's now what we have in this case.

So I'm going to go ahead and delete that.

And the last thing we need to do then is in that cell that's empty,

we find that the ith cell is empty.

We're going to then offset, we're going to ActiveCell.Offset,

and we're going to set that first empty cell equal to the new category name.

So UserForm1.NewCategoryName is whatever they enter into this box when it pops up.

So we're going to go ahead and place that into the first empty cell in row 1.

So this is pretty easy.

That's what this does.

So let's go into my Module1. I'm going to press F8.

We're going to show UserForm1.

I'll just put something like name.

We add category.

We bump into the command 2 button click.

I'm going to select A1.

And then I enter into this do loop.

So let me bring up the locals window here.

We see that right now, i is equal to zero.

But now we bump up 1 by 1,

and we're saying If IsEmpty ActiveCell.Offset 0,

which means same row as my active cell,

but I'm going to ActiveCell.Offset, now I subtract 1.

For example, right now 1 is equal to 1 so I don't want to

ActiveCell.Offset anything that's why I subtract the 1.

So If IsEmpty, if that's empty,

which it is, then I'm going to exit the do.

And I'm just going to set then the active cell offset 00 equal

to whatever's input into this new category name text box here.

So when I press F8, we put "Name" here into cell A1.

And now if I wanted to do this again,

I've got the UserForm still open.

I might put something like address,

and let's go ahead and do "Add Category".

We step through this.

Now I'm going to select A1.

Now, i is going to be 1,

but now I'm looking to see if cell A1 is empty.

It's not, so then I bump up i to 2.

And essentially what I'm doing is I'm checking to see if B1 is empty, which it is.

So I'm going to exit the do loop and then I'm going to set

that cell B1 equal to whatever is in our new category name box here.

So I should put address in there.

And then we could keep going and going.

The quick button is just unloading the UserForm and then we end the original subroutine.

So that's how you can add categories to the Excel spreadsheet using a user form.

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