Automation with Excel Macros - DIY Project List Tool - Beginners | Steve Tran | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Automation with Excel Macros - DIY Project List Tool - Beginners

teacher avatar Steve Tran

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

9 Lessons (58m)
    • 1. Introduction

      2:50
    • 2. Creating the Table

      5:41
    • 3. Macro to Create Folders

      11:36
    • 4. Folder Already Exists

      7:44
    • 5. Simplify Code with Variable

      2:04
    • 6. Macro Column Filter

      8:40
    • 7. Run When Cell Value Changed

      7:33
    • 8. Applying to All Columns

      8:24
    • 9. Finalizing Features

      3:16
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

39

Students

--

Projects

About This Class

In this class, we will use Excel Macros and VBA to build our own excel file to help keep track of projects you're working on and manage related folders and files for each project.  

It is a great follow up to my first class "Getting Started" and start to apply these concepts into a practical tool that you can use to improve productivity and automation in your job or personal projects.

We will cover the following Macro topics in addition to Excel tips and tricks throughout:
- Filtering columns
- Run code when user changes cell
- Use data based on user's active cell
- Creating folders
- Error catching and handling
- more!

Hope you enjoy.  Please leave a review if you liked it and feel free to contact me for any questions or comments.

Meet Your Teacher

Teacher Profile Image

Steve Tran

Teacher

Hello, I'm Steve.  

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction: Hey everybody. So for this class, we're going to continue learning about Excel macros. What we're gonna do is create this project file over here, which is going to keep track of different projects or tasks that you might be working on. So we're going to introduce some more advanced concepts than before. We're going to create something that actually has a great functional use that you might be able to use daily. What we're gonna do is create this tool that keeps track of different projects and tasks that you might be working on. And every time you work on a new project or task, you can add a new row here. In this case, for example, I can add a new task, making a tutorial. And then when I highlight this row, I can click create slash open folder. It says the folder doesn't exist, that I'm going to create the folder. And now I have a place to store all the files related to this project. And I can create and store files on here. And then when I come back to work on this project, I can click Open Folder and bring me right back to this project. And what this is gonna do is help me keep track of all the different projects I'm working on. And then if I'm using this for say, a year and end up with a 100 or hundreds of projects even or tasks that I've worked on, I can very quickly come here and do a search for Excel, let's say, or any key word, it will filter for the column right underneath it. So I can do the same thing here. If I'm looking for Excel, it'll automatically do a very quick filter for this content and become really quick to search through current stuff that you have active and old stuff that you completed in the past. And the concepts that we're going to go through here are creating an opening folders, of course, with this button detecting which cell I have highlighted. So if I'm working on project three, for example, that I click this button and the macro is going to know that I'm working on project number three because that's the cell I have highlighted. We're going to see how to run a macro whenever a cell is updated. So for example, I update this cell. The macro is gonna run as soon as I hit enter and not require me to do something like clicking a button. And even when I clear this value. And we're gonna see how I lookup code to perform different functions when I don't have it memorized. So things like applying a filter. Here, we're going to see not just how to do that, but also how to do things like that when you need to go online or use different methods to figure out how to do a function that you may have not done before. So that's the project. We'll go ahead and get started in the next video. I hope you guys enjoy it. 2. Creating the Table: Alright, so we're gonna start off with a blank workbook here. So you just see I have a blank Excel sheet. It's still called Book One. And this project is going to be making a tool to help you manage your projects or tasks that you're working on. So the first thing we're gonna do is create a outline of what the table that we're gonna use looks like in order to have ideas for each project that you're working on, I'm going to create a column, is going to end up being a table. The first column is project number. Second column is, we'll call it project name. And we'll have project description. And we'll have status of the project. Will include comments over here. We might add some more as we go. But this is what we're going to start with. And so we're gonna make a table over here to make the table look a little nicer, you can play around with the font. I like to just come to cell styles and use generic cell formats that are provided by Excel. Everything looks nice. It's really quick. You don't have to spend time playing around with all your fonts. The project list we're going to make is going to look something like this, where you have your project number and then each project will be a row in your table here. So it might look something like Project 001. Let's do, we'll do four digits here. You see what happened here is that Excel saw that put 0001 and thought that it was supposed to be a number. And so it converted that to a number for me. I don't want that to be a number. I'm going to make it a text item. We're gonna make that 001 again. And then we were not going to run out of numbers until we run out of four digits here. So I can do that. I'm just gonna do that for now. So I'm copying in the text type here, clear these out, and then we'll have something like project one, whatever it might be here. This is going to be our first project. That project right here you see that we want to word wrap here to improve the formatting. And so this is the word wrap function so that the line just doesn't extend out past the cell, then the status will be something like in-progress comments. We are making the tutorial right now is what will have for the comment. And we have our table here, basically what we're going to do. And so we'll add another data point here just so you guys can see what this will start to look like. And so you'd have different project lines like this of everything that you're working on. And then what I'd like to do is you can come up to the developer and where we don't have a macro set right now. So I'm just going to click cancel. We'll have a button and it will say create slash, open folder. And so a will make this button a little bit bigger. And so what we're gonna do is make this list so that you can keep track of everything that you're working on here. And then if you highlight this line and you click this button, that it will create a folder if it's the first time that you are generating a folder for the project you're working on, or if it already exists, it's going to open the folder for you and that's where you can store all your files related to the project. Another thing I'd like to do is turn this into a quick filter. So what you guys are used to seeing is if you come over here and you go to Data and filter, you can make a filtered list here. And so you can look for key words or you could filter for things in progress. You can hide the things that are not started. And you can start using a filter like this. But if you are using a list that you're going to interact with multiple times a day, maybe multiple times in our, sometimes you don't want to use a filter like this. And instead for extreme convenience, we could create something that will allow you to type in some text up here. And you'd look for the keyword project on automatically do the same thing as doing a contains filter. Per project. We didn't actually filter anything here because there they both contain the word project. But so you guys see what I mean when we go. So we want to do something actually instead of project will say the word going. Not that, that's a real search that we would use.contains. And we're going to make this sort of filtering happen automatically as you just type any words or phrases up in these boxes. So now that we have the framework of the list already, I'm going to add a couple of rows up here. I like to dress this up a little bit. I'll turn this to gray as just a heading. And since you're gonna be able to type words up here as a filter, I'll turn that back to no fill. So you can tell that you're supposed to type in the white cells. Will change, that, will give it a border. Here go now you're getting something that's starting to look like a real projects list. We'll drag this button down a little bit. In here we have the template. 3. Macro to Create Folders: Now that we have a starting template for what our project list is going to look like. We'll get started on writing are macros that are going to work with this table to create folders and will utilize this button right up here that we already created. So the first thing I'm thinking about is what do we need to create a folder? And so what we're going to want to create our folder is to create a folder by our project number. That'll be what we start with. So to create a macro, I'm going to go to my Developer tab. I'll go to Visual Basic. This pulls up our editor here. Right now we don't have any modules. And so I want to put my code in a module. So I'm going to right-click Insert Module. We'll just leave that as module one for now. We could rename this later when we cleaning things up. And I always like to just start off with a very basic function and I change things later as we go and put polish on each of these layers. But I'm just gonna put a subroutine here called test. Then I like to test out the button by putting a message box, which just says something like this. And so now we have a macro. We're not actually going to use it like this. We're gonna make a ton of updates here, but I'd like to just create something to start with and get everything set up. And so I right-click the button, I go assign macro, Go test. And I've assigned this button to the macro named test. I'll split these up onto a screen so we can work on them at the same time. And now if you click this button, this macro called test just ran. Now I'm going to click OK. And now we can change this. We'll call this create open folder. And we can see here is because I renamed this subroutine, it's still looking for the macro name test. And so I have to reassign it now. Create open folder. And there you go. This macro code that I wrote up here is running again. So I want to create a folder. When I click this button, I actually already prepared a folder where we're gonna store this file and also the sub folders for each project and just see Dr. projects and that's the name of our folder. And so if you followed my last tutorial, and if any of this is confusing to you, you should go back and watch that. I'm going to save this Excel file that we're working in into that folder. And we're looking for C Drive projects. And this defaults as an XLS file types. So we'll want to change that to x LSM so that it retains the macros that we're writing right now. And we'll call this project list. And so now we've saved this file. It's not called Book One anymore. And as we save in reopen this file, it will retain the macros because the XML SX, which is the default file type, doesn't retain macros. Now, we want to create a folder based on the project number in this C Drive projects folder. To make a folder, the command is MK DIR and then the path of the folder you want to create. So if I type something like this, I believe it's backslash here. We can run this. Nothing happened, which means we didn't run into any errors. And we can confirm that by going to the folder and see nothing happened here actually. Oh, and you see I see the mistake I made. I made C Drive Test and we're in C Drive projects test. And so if I go back to my C drive, I think I'm going to see a floating folder here called test, which is exactly what I see. Projects tests will run this again. Instead of Hangu button, we can hit the button which we've assigned to this macro, or I can just highlight the macro. Quick my cursor into it and click this Play button. This ran in there. You see our folder test was created. We're going to actually need that, so I'll delete it here. But what we really want is for the sub folder in our project folder to be named after our project number. I'm going to create a variable that's going to contain the number of our project. And I'm going to call that project number as actually we'll call it a string because it's going to be a folder name. We might use letters in it. For example, we'll say project number equals and we're just going to type a project number right into their PR RJ 0001, let say. And this is hard-coded in here. So it's not using our spreadsheet that we created yet. Now instead of the subfolder being called cast, we'll turn the make directory function into a concatenated string. Alright, so now we're going to try to run our macro here that we just created. And when this runs, it's going to assign project number 012, this variable. And then it will create a sub folder in the project folder with the name of this variable, which is project one. And so we'll click Run. We'll go to our projects folder. And there you go, you see our project 0-0. 0-1 was created. Just to show you again, I'll delete that folder and we'll run this macro again. There you go. You see the folders created. Now the project number was hardcoded in here. What we really want is for us to be using the project number that is in our spreadsheet that we pull from our project list. So our goal is that we're going to be able to highlight a cell in our list. And then when we hit this button that it's going to know the project number that we are highlighting. So let's start to write that Logic now. So we're going to write the logic to find a project number before we declare the value of our project number variable here, first thing I wanna do is find out what cell I have active. And how I can do that is with the command active cell. And you see by hitting period, you know, you have a valid object. Whenever you hit period, it'll give you a list of properties which tells you that your VBA editor here is confirming that this is an object that exist so that it can show you its properties. So if I type active cell dot value, for example, I'll just show you this. This is the active cell that I have on my spreadsheet and the value of what's in there. I'm going to comment out these two lines of code that we're not using yet. Type message box. Let's see, active cell dot value, just like we have above. I'll comment this out to you. And what should happen is this message box going to pop up with whatever the value of the highlighted cell on the spreadsheet is. And so right now we should get this text here. And you see this is going to be our first project pops up. If I have project one as the active cell, I'll run this again and it'll say Project one. So that was just to show you what the active cell object is. Actually I'm going to bring this message box back and undo this. So another thing you can use, these other properties related with active cell and so active so has this property called Rho. And so let's try this out now. If I run this, you can see the active cell I have highlighted. It's telling me the property rho is seven. And you can see here we're on row seven, and that's how you can get the row of the highlighted cell. How can we use this, which is detecting our active cell row and get the value of this cell over here, which is our project number. We can do that because there is another object called cells. And you see cells is going to be your grid of your spreadsheet over here and you have a row index and the column index. Our column is going to be column a and r, rho is rho seven for seven for a row, type, one here for your column index. Let's just try this out. We'll type value and see what this returns. If we take this and place it into our message box. When we hit run, you see that your cell object went down to row seven and then took column one, which is r a, and gave us the value of here. If we move the column over to two, you can run again. And now we're on Project one in this cell over here. So right now I coded the number seven in the row. But what we saw before is that you can figure out the row based on the cell that you have highlighted. So we'll go to active cell actually and go row type, bro. We'll set that. And you see, whenever you see these terms get capitalize automatically, that means it's something valid that Excel recognized. So they knew that it's supposed to be capitalized and handle it for you. Now let's type this. Again. It's doing the same thing. Only. It's making this decision of what row you're on based on what cell you have highlighted. In this case it's 01. But if I come down here to the second row, it knows that it is row eight and then brings me over to this cell. And because we're only using a row function, we're always using column one. So I can be all the way over here. In the row is the same and it's going to column one. So now our project number that we did before, we still wanted to start with PR j. But our number here is now based on the function that we're just demoing. And so I can use this ampersand sign to concatenate the string together. It's giving me an error because I'm leaving this line of code while it's invalid, we're gonna come back to it. So we know this function. Actually if this function over here, that gives us the value of the first column project number. And so will take us entirely. And we are going to append that to the letters PR J. And we'll delete this message box. We don't need it anymore. And now this number is going to be PR J plus the number located in the row that we have highlighted. So again, we'll make another message box or see what's going on here. We'll do message box project number. If I run this, you'll see project numbers 0001. If I highlight over here, I'm on project number two. And now that we have our new project number based on our cell highlights, we can remove the message box and we can go back to our Make Directory code here. And this is already set up where project number is this variable. Where now if I'm on project one, Let's go back to our project folder. I can delete this folder cuz we're gonna create a new one. Actually, I'll bring this over here. Is where I'm project to. Let's say we'll go out of order. I can create the folder. If I'm highlighting project one, create a folder. And there I just made the project 14. 4. Folder Already Exists: Alright, so now that we have our button creating folders, what are you going to find if you test this out is because Project one already exists. If I tried to create a new project, I'm gonna get an error. And it tells me I have a file path access air. That's because it is trying to create a folder, but Windows is rejecting it because that folder already exists. And so if you hit the bug, it's telling me it has an error when it goes to create the folder, which makes perfect sense. So I'm going to stop this macro here. And what we wanna do is to detect whether or not a folder already exist. And if a project folder already exists for it, we want to open that folder rather than trying to create a folder that already exists. So I'm going to show you guys how I'd actually go about this in not hide the details from you guys. I know that there's a way to check whether a folder exists, and I've even used it a few dozen times, but I can't remember it off the top of my head. I'll go to Trustee, Google. I'm gonna start by typing VBA, which is the programming language that we write macros in. Then I'm going to write check if folder exists. They go, we have a video. I don't really like videos. I like to go straight to Stack Overflow if I can find it, because the voting system allows the answers, the best answers to float up to the top. I'm just gonna scroll right down to the answer if directory. And then I can tell here that is the path of the folder VB directory equals blank then. And so I recognize this. It looks right. We're going to try it out. I'm going to paste this here. This is an if statement. So let's make this bigger. We had this line of code here. This is an if statement. That's all the website provided. I know that we have to end every if statement with an end. If I'm going to comment this out for now. This is a folder path. And so the folder path that we want to check whether it exists is going to be this same path that we want to create the folder in the first place. And so I can actually grab this URL and grab this, copy it. Over here. This is the first parameter of the directory function project number, and this is going to be whatever our project number highlighted is. If it is blank, then message box, let's say I'm in the if statement. We'll close this out. And so let's see how this runs. We're going to run it on this first line here. I expect that before we are getting an error because I tried to create a project one folder again. Now we're going to run this and nothing happened here. I expected to see this message box. So let's see if we can figure out what's going wrong. I'm going to use F eight, the F8 key. We'll step through each line of code one line at a time. So F8, project number. And you can see here that it's skipped right over this. And that's because this condition was not met. It's saying that if the directory, this project number equals blank. So equals blank means that it does not exist. And so the reason this condition is not true is because the folder does exist. If the folder exists, then this statement over here will not be blank. So if I wanted to say I'm in the if statement, if I want to say check if the folder exists, I want to check out if the folder directory is not blank, is not blank. It exists. So let's try to run this again. And there you go. Folder is not blank, it exists. And to prove that that works, we'll go back to our projects folder, will delete folder one. We'll run this and we don't get a message because the folder does not exist. However, if I make a director, again, we'll run this. The Project one folder that we just deleted has come back. And then now if we run it again, now we get the and then we're hitting the file access error again because it's trying to run this make directory line again. So we'll end it. So here is what happens if the folder exists. Now, if the folder does not exist. And if we're going to make are a folder with this line of code that we've used already, in place it in the else statement. So what you're saying now is if this directory already exists, we're gonna do this code. If the folder doesn't exist, then we're gonna make a folder. So let's try that out. We're going to delete the folder here. We'll run it. Project one folders created here. We're going to run this. Again. The folder is not blank, it exists. And here we don't get an error because as we saw over here, we are not executing this line of code that's attempting to make the folder again. We're going to run this line of code and then skip right out of the if statement. And right now we have this message box over here. But really we want to open the folder and just like checking whether the folder exist. I don't have the notation memorized. I know we can do it. And so I'm going to type VBA open for that Stack Overflow here at the top. It's a good practice if you're learning to read through some of this stuff. But I'm just gonna go straight to the answer here. We're gonna take this line of code. If you want to open a folder in File Explorer, will come over here. It's saying call shell, and you're going to open it up with your explorer application. And then this is the path of the folder that you want to open up. And so this is what we want to replace. And just like this code where we created the folder name, we can take this path and replace it here. And I did not believe that quotation mark a gal, let's try this out. Folder one exist. So if this is working right, the folder is just going to open rather than trying to create a new folder. And here we go. Now we're in the Project one folder. And so you'll see here, let's clear this out. We'll delete. Both of these. Will create a project to folder because it didn't exist. And now when we click this button, again, project to folder opens. And we'll do the same thing with the Project one folder. And if you click it again, it opens. Now one thing I'm noticing here is if you're creating a folder, you then need to click the button again to open that folder. If you're using it, you probably want to open the folder right after you create it. If it's the first time you've ever started this project line. And so we're going to come back to our code here. And just like this line of code, open the folder if it existed, we're just going to do, and again, we're going to place this line of code right after the folder is created. And so now we'll try this again. We'll move our folder over here. Let's delete our folders. We've got a project one. You click the button and degas the folder was created and then immediately opened. 5. Simplify Code with Variable: As we're looking at our code here, where we are creating the folder or opening the folder if it already exists. You see we've used this notation, the path of the folder a couple times here. So we're using it to check whether the folder exists. We are using it to open the folder in these two places and to create the folder for the sake of cleaning things up, we're just going to consolidate that into a variable name. And so we're going to call that variable dimension folder name as a string. We're gonna call the folder name. This string that we've already used and tested many times. We're going to paste this string up here. And now the project name plus the original folder path is going to be the folder name. And so actually it's not actually a folder name. I'm gonna call it the folder path instead, because that's more accurate since it includes all the subfolders in the C Drive address. And so now everywhere where we had this full path written, we can highlight that and exchange it for this folder path variable. So that's here, here, here, and here. So let's go back to our projects, will delete this and we'll try it out again and see if our code still works the same as it did before. Only with much shorter code. And so there you go. The folder was created, it was opened right after the photo is created, will go back here. It won't create the folder again. Instead it will just open the already existing folder. And there, the code is still working the same way it did before, only with less redundancy. And if you ever want to change your file path, you now don't have to go to all four places where you had written it. You can just change it in one place here. It will cascade through the rest of your code. 6. Macro Column Filter: And everybody, where we left off is that we got our Folder function working in its basic sense, which is that you're going to have these projects here. We'll add more Project lines just to have more information to work with here. We can actually set these projects up. We'll drag this number down. Project three, these might have different names, will say there's Excel tutorial. And what we want is for us to be able to type into these cells up here in automatically do a contains filter through the list. Similar to go into Tex filters and contains. So if I typed excel in here, I'm filtering for all my projects that have Excel into description. But instead of doing that, I could just type this here and get the same filter rather than clicking through this menu. Now in the other videos, when I couldn't remember how to exactly do something, I just typed it into Google and pulled a line of code out of there. And that's what we did for checking whether directory exists and the code to open a folder. Now what I wanna do is to be able to do this and I can't remember the notation for it, but I can't just type that into Google. Or at least I know that if I typed that into Google, I'm not going to get a great result. And that's because it's hard to describe what we're doing here. Now we're finally starting to do custom functions that aren't single lines of code. We're getting a little creative and coming out with things where maybe a lot of people haven't created a function like this before, where you type in a cell and then filter the table based on that content. So I'm going to show you guys another trick that I would use here. So let's say I have another project, another more learning. I'm going to test out this filtering, the trick I'm going to use, and I'll make this a little bigger is I'm going to use the Record Macro function. And so if you followed my other class, you'll remember that whatever you do while recording the macro is going to write a macro on its own that you can then play back. This is a great tool for seeing how Excel will write the macro to do certain functions. You don't know the notation or it's not easy to find the answer online. So for example, when I'm going to do is hit Record Macro. I'll just leave it as macro ONE over here. Now, whatever I'm doing, records on the screen, I can actually show you guys by pulling up my editor Here. You see when I recorded the macro, it automatically created a new module. It just call it module too, because I was already using one. Here's macro one which I'm recording. And since this is start recording, we know we're live right now. And so as I click around this cell, you can see I'm selecting different ranges here. As I select a seven. You can see this is being recorded over here. And so what I can do if I type Steve down here, you can see the macro recorded that I clicked into cell C42, typed my name, and then move to cell C5, which is right underneath it from pressing Enter. So I'll clear that out. You see it's recording all of this stuff. We're gonna ignore all that because we're going to skip through it later. But what I really wanna do is create a contains filter. And so I'm going to go to this column here. Text filter contains, i'm going to say contains. Word Excel. And you'll see we filtered all the rows that contain the word excel, which is just one of them. And here's my line of code that the macro recorder generated for me. So without going into Google right here, I know my line of code that's going to create a filter on this table. We also have all this other stuff here. I don't really care about it. I'm just going to ignore it and we can delete it later. I'm going to start recording the macro here. We have what we want already, which is this line of code will copy it. Go back to module one, where we're writing our code. And I'm just going to show you, I'm going to make another subroutine right here. We'll call it the test again. And we'll paste this line of code right here. If I unfiltered this, and then now come back to my macro. And if I run this subroutine test, I'm going to click in here, hit play. We made a filter for Excel. And it has nothing to do with the content that's up here actually, because we didn't code at the b. It just recorded the fact that I went through the drop-down system and type excel. And you see in this line of code, the Word Excel is hard-coded there. And so even with nothing in here, if I run this again, nothing's going to happen because it's already filtered. But if I clear this filter, run this again. There you go. Now that I have this line of code that I know works at least roughly. So now we want this code to run not with the word excel in here, but with whatever is typed into this cell here. And so that is cell C5, my last class. You'll see in one of the videos that I don't recommend hardcoding the cell address C5 in here. And that's because if I change this spreadsheet around and I add new rows, then this cell might no longer be the address C5. And so I'm gonna create a name for this cell by coming up here. We're going to call it, we'll call it Filter description. And so I hit Enter as I come back to the cell, you can see it still has its name filter description, which I just assigned it. We're going to want to find the word that is in this cell. And so we're gonna create a string variable again, dim filter value will call it as string filter value equals. And we're going to use the range of a cell that we named over here called filter description dot value. Alright, so we're going to comment this out here. So we're not looking at it yet. And we're going to use the message box as our test again, and we're going to display filter value. So let's run this because it's blank. In this case, what's run? Excel? Run this again. And there you go, you have the value of Excel. And so as I make this bigger, you can see the word excel here is hardcoded. But if I open this back up and take filter value, I can't just plays this because if you follow what's going on, it's going to filter for whatever is in here. And now I'm not referencing the variable because it's in these quotation marks here. And so if I run this, It's not going to work. It's showing our message box and then we are not showing any value because what I'm really filtering for it isn't Excel, which is the string in this variable within the string filter value, which is written here, but because it's inside the quotation marks. And so I actually have to concatenate this together. And now that we're outside the quotation marks, here's the start of the quotations. Here's the end of it. Now we are back in our regular code so I can use the value of variable where it combine that. And then inside the string here is this asterisk in. And these asterisks are wildcards. Since we're using a contains filter, It's telling the filter that it does not care what is on the left or what's on the right of the value. So whatever's in these columns is going to have to contain the value of the filter, but not care if it's at the beginning or if it's at the end or whatever is on the left or right side of it. And so you can see what's going on here before I run this, let me grab this. This is our full string. So this criteria is taking a string. We'll comment this out again. But you'll see if I type this in here. This is what I really want, which is where when I run this, where at Excel again, because the value of filter value is Excel. We'll comment this out. Since we're not using a message box, will use our filter. Let's clear this here. And we will run this again. And there you go. So will clear this filter again. Let's try a different word here. And so let's look for the word learning instead. Now we'll run this macro and they go the two rows containing the word learning. I've showed up. 7. Run When Cell Value Changed: Now that we have our filter working, where we have our code that is going to filter or column based on the value of the cell up here, will run this again. I don't want to have to run this macro in order to create this filter where I really want is to be able to type this value up here. And then as soon as I hit enter out of the cell that this filter is going to take place. And so, so far all I've shown you guys is how to write these subroutines and then run them with either a button or by hitting the play button. And now what I wanna do is to trigger a macro by updating a cell and hitting Enter, rather than any of these other buttons that I've shown you. Yet. Again, it's something that I've done many times, but I don't remember the notation for it exactly. So I'm going to come back over to Google pet VBA, run macro on cell change. So here we are again. I'm going to go to Stack Overflow just because it's usually the quickest way to the answer, although any of those other links will probably work, it'll just sometimes have a lot of content in there. In addition to what you're looking for. I can come straight here. I can see that there's some code over here. It says Private Sub worksheet change value if not intersect and all this stuff over here, I'll continue reading it. I've done this before, so I know what this all means. You guys could read a little bit more into those other links to get a full explanation of how this works. But I'm just going to grab this code over here. I'm going to come back to our macro. In order for this code to work. I can't put it in module one over here. It's going to be related whether a cell value changed in our worksheet. And so the sheet that we're working in is sheet one right now. And so I'm actually going to come over to Sheet one over here. And I'm going to want to place the code in xij one because that's the object that we are working in. And so I'm gonna take this code, will place them in sheet one. Let's try this out. So this code right here says do something here if the worksheet changes. And so I'm actually going to comment this extra line of code. So we'll take it one line at a time. Will say message box. Hi, just to test it out. This I believe is saying to run whatever's in here whenever the work heat changes. And so if I change any value here, you can see it runs this macro which sends me the high message box. And there it goes again as here, here, even if I'm changing the value of these cells and clearing them out rather than typing something in, it is notifying that the worksheet change and it's still running this. And so now we know this is working. Just to show you guys, I'll add another sheet here. And if I change any cells in here, the macro isn't running because our code isn't sheet one, which is why we need to place it here. If we go to sheet two, there is no code here. And so those same rules don't apply, will go back to sheet one, will delete this sheet. And so now I'll delete this. Now we'll move onto our next line of code that we took from Google. And it says, if not target, and it has this range here, h one. And so what this should mean is its own. Now, it's going to go into this statement after the worksheet changes. But then you're gonna run a macro here. This is your regular if statement. Then run a macro. It doesn't have to end if so, we'll write that there to anchor this. And so it's going to enter this statement, but then it's going to evaluate whether or not the cell that changed is cell H5. So let's try that out. If I type anything in these cells, you see we're entering in here just like we were before, but we're not seeing our high message. And that is because we're not meeting the condition over here where we're seeing if the intersection is the cell H5. But if we go over here to sell h five and type something in there, we meet this top condition because the worksheet changed. Then we meet this condition where it is the range h5 that's being updated. And now we're running our line of code here. So we'll clear that run again. We wanted our code to run whenever we update this cell over here. And so this cell is called filter description. Will change h5 to filter description. And now nothing if we change this cell, but if we change this cell over here. Now our code is running within this statement. So now I'm gonna clear this out. We'll go to this subroutine that we already wrote in new successfully applies the filters. We're going to grab this line of code. I'm just going to copy it for now. Come back to this area where our message box is showing up and replace this with our filter code. And we're going to fix the formatting over here. And so now whenever I change this value, this line of code where there's a group of code will run. And we, as we tested before, it's going to run and take the value of this cell and apply the filter. So let me clear this filter again. And so now I'm going to type Excel when I hit Enter, this should all run, which performs this filtering that we already tested in the last video. If I clear it, it runs again. And so what's happening there is I thought that we might have to write some extra code to make that happen. But it looks like it's all working great. Where when I clear this cell, this becomes blank, but filter value becomes blank. And then Excel is going to know that I am looking for a filter where this is blank. Since there's wildcards on both sides. It's basically saying create a filter where, where anything in this column is valid. And so just by clearing the cell, it's basically saying, does it contain anything which every cell does. And all these show up again. You can tell by the funnel, but it's still thinks there's a filter, but the filter is just allowing for every single condition of these rows. And so if I type going, now we just see this cell. I can type project. And you see how this is working and how we can really quickly filter through our list. Now. Now we want to just apply it to our rest. Now we just want to apply it to the rest of our columns over here. Just like we created a name for these cells. We're gonna create them for these other things. We're gonna create them for these other cells here. Filter number, filter, status, filter comments. I'll just check them as I go through each of these cells, you can see the names were accurately assigned a pap. 8. Applying to All Columns: Hey everybody. So where we left off last time was that we add this filter over here where if we type a word, that's going to automatically apply the filter to this column, right? Oh, up, right underneath it. So that code is located here. If I right-click this sheet and go to View code. Here it is. So we got this working. We didn't add it yet to these columns over here. Let's start creating that. And create the same filter for the rest of the columns. So let's do filter name here. We're going to add it above it just to keep the sequence from left to right. We'll copy and paste. Instead of filter description, this cell is called Filter name. Let's make this bigger. Filter name over here. In this field. Equals three, refers to column three in our table over there. So you see these arrows is 123. So we need to change this to two so that we apply the filter to the second column over here. Let's try this out here. We get this error, Duplicate declaration out of scope. So I'll click OK here. You see it's highlighting this line of code telling me where the error is. That's because we are declaring this variable, filter variable down here. And there's a risk that we are going to declare this variable two times and that's the error. It's telling us. What we wanna do is grab this, declared this variable once up here. Then we don't need to declare it in each of these different statements here. So there's no risk of declaring it twice because we're going to declare twice. Sorry, because we're going to declare once. And then the value is going to be set in each of these if statements. So this is highlighted yellow. So we need to stop this macro. Let's try this out. And there we are. We'll clear this out. Now. We'll do the same thing for these other columns. Will do it for our project number here. Filter number will change it here, which enter here? Will change our column number again. We're going to do a fourth status next. And then the last one is going to be comments. And then we're going to do for our comments over here. And this becomes five. So let's go ahead and test this out. With Project two here will clear it out. We'll try Excel here. You'll see here that it keeps trying to auto-fill this cell because it's fine. It's similar values in this column will just click Delete. That works in progress. That works here. We'll type started over here. That works. And so there's something I just noticed when we cleared this cell out. We had four rows of information. You'll see that it's not showing all four rows. So let's take a look at that. We'll go back over here. The comments field is this last one over here where we're getting filtered comments. The filter we're applying, if you go back over here. So if I go over here and clear this, our two rows come back. What I'm realizing is that if the filter value is blank over here, then we're saying the criteria equals star star. And that must mean when we are filtering for star, star, that isn't Including completely blank cells. It's finding any cell that has any content in there, but it does require content. So now we have to think about how to deal with this. How we'll deal with that is we'll take this variable filter value and we'll create an if statement. So we only run this statement if there's a value in there and we'll run this other statement. If there is no value in there. We'll make sure that formatting is right. We'll type ls. And then if basically, if the filter value is blank, then we will do something else. What that filter value should be is basically the same thing as what happens when you go to this filter and clear it out. Now we want to figure out what code to put over here. And again, we don't want to go to Google and do research for it. Instead, what we're going to do is use the Record Macro function, which I showed you before. Now it will go to the Developer tab. We'll click Record Macro. The macro is going to be macro too, and we'll just leave that for now. Then I'll just click this funnel up, clear the filter. Now I can stop recording because we know it was recording that code back here. And so if we go over here, we'll see the code that was just recording. This should be the line of code that unfiltered that column. So I'm gonna grab this, bring it over to Sheet One, which is where our code lives. We're going to paste it in here. And if I make this bigger, you can actually see that unfiltered column five is really the same as just removing the end of this line of code up here. And we can try this out. So we'll go back over here. And if I delete this, nothing happened because we're already showing all four rows, will do the same thing again. Clear it. There you go. Now when we clear it, you can see that filters, that filter value was blank. And so we're in this statement over here, and you can tell there's actually no filter because there's no funnel icon over here. I'll do it again. There's the funnel icon. There goes the funnel. And so now we're going to do the same thing for these column over this. So we're going to copy this fixed to the rest of the columns. So I'll do that. We'll grab this. Put the same if statement over here. And I'm going to cut that. And I'm going to place that in here. The column we want to clear, it says five here. I'm going to have to update that to four, since we're working with different column here. And I'll do the same thing for these other steps. And there you go. We'll just test this again. The difference here is now, after we clear this out, you should see the Filter icon disappear. So we'll just clear these manually really quick. You can see since we did it that way, we're actually showing these numbers again. Because all of these rows were hidden, because there was no content in these cells. 9. Finalizing Features: Hey everyone, at the end of that last clip, I think we're getting pretty close to finalizing this list here. So the last few things we're gonna do is just a couple coats of polished and make this thing a little nicer. We're gonna add, we're just gonna call it a project list over here. I can use these cell styles, so I don't have to think too hard about it. One thing I might want to do is for something like status, I might not want to be able to write just any text over here. Maybe I have a couple set Status Types and I want to force this cell to be some of those selections so I can highlight these cells. This is pretty simple. I'll go to data over here called Data Validation. I'll click that. I can force these cells to be some values and a list. And so I can type, I can type in progress here. I can type not started. What happened there is I hit one of the arrow keys. So starting to reference other cells, I can type not started and I can type, let's say completed. Something groupies happening there. Completed. And I'll click OK. You can see my screens kinda glitching out here, but I'll click OK, we'll see how it goes. And those cells I selected have now become dropdowns in progress, not started, completed. And now I can't write just anything in here. Cancel. I have to select one of these drop-downs. But our filters from before still apply. So if I type Completed here, you see it automatically applies those filters that are column. Now we'll add a few more things to this list here. So let me clear that out. And the other thing I was thinking is if I have a new project here, I don't have a project for Folder yet. If I click this, I know the folder doesn't exist and created an open the folder, however, I want to get a notification so I can tell the difference between opening an existing folder or if I'm about to create a new folder for the first time. So what I can do here is I'll highlight this button that does that function. I just right-click it and click edit contacts menu. I'm gonna go to Developer view code. That's going to take me directly to the code that runs when I click that button. And here's where I make the folder. And this logic is already programmed here. I'm just going to add a message box right before you make the folder that says Folder does not exist. To create folder at a period there. So we'll come back here. Let's try that out. And now let's see. Let's try project three where I know I don't have a folder yet. Now I get this message box folder that's not exist. Going to create folder. I'll click OK. Let's say I'm working on stuff. I create some files in here. If I go to Open Project three again, there it'll just open without a message box. Alright everybody. So that does it for this project. I hope you guys enjoyed the tutorial. Let me know what you guys think, give me feedback and let me know if there's any other projects that you'd want me to make tutorials on next. Thanks a lot for your time, guys.