A demonstration of the Ultimate Organizer

Loading...

From the course by University of Colorado Boulder

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

9 оценки

University of Colorado Boulder

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

9 оценки

Course 3 of 3 in the 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.

From the lesson

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

Meet the Instructors

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

All right. Just going to demonstrate my ultimate organizer project,

this is the one that I made.

Now, you're welcome to use whatever information you want.

You can feel free to use these data here,

I will post this on the course website.

But you can use any names,

or items, or categories,

anything that you want for this project.

So I've got names here and I'm going assume

that column A will never be deleted or never change.

You can delete rows from this but you're never going to delete the names column.

You need something that sort of stays with all

of the items so name of something that you would probably never delete.

And we've got other categories city,

phone number, email, years.

And let me show you what this ultimate organizer does.

So, I've got up here on that,

it's called the quick access bar at the very top I've got

this icon and I'll show you in one of the screencast how to do this.

But it turns out if you put a button on the spreadsheet,

then when you delete columns or rows that kind of overlap with that button,

then the button shrinks really small and it changes the size.

So I've added this button up here to make it kind of easier to learn this.

You could also run it from the developer tab.

You could run macros here and select it but this will always be on this file.

So I'm just going to click up there,

and it opens up the ultimate organizer.

We can add a category.

So I'm going to click on "Add category".

My new name might be something like favorite color,

add category and that you see that up here on cell F1,

we added favorite color.

So that's adding the category.

We can also delete a category.

So maybe I wanted to,

let's just go ahead and delete that favorite color.

You noticed that before this user form is brought up,

it populated this with all of the different categories other than name.

So I just added favorite color and I populated combo box with favorite color.

So I'm actually going to delete favorite color. Let's go ahead and delete that.

It says, "Are you sure you want to permanently delete this category?"

And I click, "Yes" and it eliminated that category or that column of data.

We can add a record,

so a record is a row so this would be like a new person.

And I told you in the previous screencast how it's going to detect the number of columns.

So right here, I've only got five columns,

A through E, but in general,

you should allow up to 12.

And what I've done here is I've put a little conditional statement in here and I've said,

if there's six or fewer categories,

then you can have the size,

the width of this user form be set to

something and I think it's set to something like 250 pixels.

Otherwise, if the number of categories is greater than six,

what happens is the width expands to I think around 450,

and there's another six of these hidden labels and input boxes over here to the right.

So I can show you that here in a minute.

But I'm going to add in maybe Paul,

he lives in Ohio.

That's not a city but that's a state but we'll just go with it.

And he's been with the company for five years.

When I click "add record" you see that down here in line 13,

we've add that information to the spreadsheet.

Another thing you need to do is have the capability to delete a record.

When I click "delete record" its going to populate to form that pops up

with all of the names in rows two through the last one which is 13.

So we see that those names have been populated into this combo box,

and I'm just going to delete Paul who I just added.

So I can do delete record,

it asks us to confirm and it deletes that.

Again, if I do add record right now,

there's only five because there's five categories.

If I quit and I add a couple more categories so,

let's add a couple more,

I'll add favorite color, so I've added that.

Let me add one more so this will be a total of seven.

So I'll say I don't know,

shoe size or something like that.

And let's do one more, height.

We can add category. Now when I do add record,

it's going to detect that we have one, two, three, four,

five, six, seven, eight categories.

Now what is done, first of all,

it's revealed a couple more hidden input boxes and labels;

favorite color, shoe size, and height.

And it's also expanded the size,

so you can actually on the fly in your code,

you can change the dimensions of these different user forms.

So now we could add in a new record if we wanted to.

The most complicated thing is going to be this search and replace tool.

So let's go ahead and click that and what it has done,

is it's gone through and populated

this first combo box with all of the names here in column A,

I see that drop down menu here.

So I'm going to go ahead and select like Jimmy,

"Which category do you want to display?"

So maybe you want to do what's his email address and then

you search and it populates this search result with the email address.

It also took the text here in that second combo box and change this label here.

So let's change that to email.

If I change this to a phone number and search,

this label here has changed to phone number.

So this matches whatever the text is of this combo box.

If you want, you can replace,

so a phone number with the years and search,

it has three, but maybe I want to replace that with something else,

it says, "Are you sure you want to replace that?"

I'll click, "Yes" and then I'll enter maybe he's worked now for

seven years and now it has replaced that.

So it says seven here,

but it's also replaced that on the spreadsheet.

I can't see it because it's hidden behind this box but it's

replaced permanently on the spreadsheet.

The last thing that you're going to have to do is if something is currently blank,

for example Jimmy, if I went to his shoe size and I searched, it's blank.

So Jimmy is in row six,

and shoe size is in column G, which is blank.

So your program needs to detect if it's blank,

if it's blank you got to have some sort of message box popping up,

"Shoe size is missing for Jimmy.

Would you like to add this data?"

And I click "Yes. And he might wear a 10.5,

and I can click OK, and then it permanently writes

that to the spreadsheet and then we can quit.

So that's the search and replace tool.

And finally, you should have a quit button on your user form.

So that's kind of the basics of this.

And in the next couple of screencast,

I'm going to go through smaller elements of this.

I'm not going to give you a whole lot of ideas related to this project.

One of the best ways to learn is just to actually get in there and

and start spinning your wheels and get stuck.

And when learning is hard,

when things are hard, that's when you tend to learn the most.

So, going through an actual project and getting to stumbling blocks and hurdles

is really a good way to learn things and then you can go back and try to

find some different things in parts one and two of the course.

You can also do some internet searches,

take part in the discussion forums and so on.

So good luck and hope you enjoyed this screencast.

Ознакомьтесь с нашим каталогом

Присоединяйтесь бесплатно и получайте персонализированные рекомендации, обновления и предложения.

Coursera делает лучшее в мире образование доступным каждому, предлагая онлайн-курсы от ведущих университетов и организаций.

© Coursera Inc., 2018 Все права защищены.

Загрузить из App Store

Загрузить в Google Play