Do like these trainings i do a lot of vba but it's often intermediate to advanced and a lot of people have been asking me how do i get the basic vba or how do i get intermediate vba or my skills i have no skills in vba or they're just intermediate and i want to help how do you do that now that's just not something that i've trained before and i'm really focused on application development so it's always intermediate to advanced so what i've done is i've partnered up with one of my mentors daniel strong daniel strong is an incredible excel vba guru and he's got an incredible course.
Out called the ultimate excel vba course i saw this course i was really really impressed with it and so i spoke to daniel and i really prided and thought of him saying can you give us a discount you know i've got some really great uh followers who love but many of them need help with vba what can you do for them so he's done go ahead and give us us incredible discount on this 30 hour course it's 12 sections 30 hours well an amazing course that goes into every bit part of vba even if you have no experience at all with vba this is the course for you so i've partnered up with him he's given a great discount a ton of bonuses i'm going to include the link down below so let's go ahead and support daniel he's one of my mentors and uh he helped me getting started he.
Had courses way before i ever did so i'd really like to show him some love and get on this course so that's daniel's corsa the ultimate excel vba course so i hope you'll pick that up all right so much let's get started on this training because i've got a lot to share with you we'll go over a high level overview of why this application are important and how you can make it your own and how you can leverage this and start creating your own applications and sell them of course my goal here is not just to teach you excel but to make you a successful with excel and it's with applications just like this right we need to track our leads we need to track our prospect now leads are those contacts who you don't necessarily know very much they could be cold leads they could be warm leads now a cold lead is somebody that you don't know and they don't know you right.
So we're going to call that a cold lead right and so what our idea is to bring them through a sales funnel starting out with cold leads and then as they get to know you like you and trust you they become warm leads and maybe they're going to become prospects as you they go through the funnel and then maybe you'll get the opportunity to make a proposal to them they'll approve it hopefully and then you'll be able to schedule that then that job becomes in progress then it becomes complete completed and then it becomes paid so that is the sales funnel process and we can you do all of that every single step with excel in a really incredible application as we take in contacts and.
We put them in our contacts list we have a status right we can have status we can have an active if they're no longer a cold lead a warm lead prospect so as they go through your process it is automatic now once it becomes once they say hey please send me a proposal what you can do is you can then create a brand new project for them and if you'll take a look at this this looks very familiar this is something called invoice with profit that we have created before in a prior training i won't go into a ton of detail because it's such a massive application this week because this particular screen called invoice from profit i've changed the name change the look a little bit but otherwise it's the same fundamentals i have an exclusive training on that i'm also going to.
Include that down in invoice with profit invoice with profit i'll include the link down below for you to get that okay so basically what we want to do is we have a new project we can select a contact right and we can set a default right what is that are we proposing it is it scheduled so we do a proposal right we call a proposal and then we can pros let's say we have some kind of an auto mechanic right and we want to give them a proposal for an engine clean we can give them a proposal on that date and then we can say a technician's going to do it so 100 right so we can save that project and then let's say the customer says oh you know what that's a great idea why don't we go ahead and schedule that so you just need to change the status to schedule and it becomes scheduled.
And then of course as you do the work it becomes in progress completed and then when they pay it then they can just mark the amount paid so they go ahead and pay it and then it becomes paid and we save that save and update that project and then our kanban is what we're going to do when we refresh the command it's going to show all that information it's going to show which projects are paid which are not paid how much is the total and it's going to show a lot of that information there as we go ahead and update so the cold leads don't have anything they become warm then they become prospects then we propose make a proposal to them and then of course we do the work we'll schedule.
It we make it in progress if it comes in progress and then of course we can show it completed and then as we do as they pay it it'll show paid and we get a total paid so the total completed total paid will show up here so everything here is just set up automatically and i'm going to show you how to do that of course inside this application now it's a fully dynamic admin screen what i like that is if we decide that we want to change the color of the warm leaves or something it is a fully dynamic maybe we want to change the warm at least to this darker yellow or something like that or light green we can do whatever we want here let's say we want to change warmly to this light yellow all we need to do is just go into the kanban refresh that and it's automatically going to.
Be fresh so now warm leads are that neat so it's fully dynamic i'm going to show you how to convert that background color to these shape colors very very easily it's not a lot of coding on that one and then of course we've got a dashboard we'll be able to show the sales funnel we're going to be able to show this particular dashboard of course we're going to have some slices in here a timeline and we'll show you that actually timeline here and then project status summary how many projects are proposed scheduled this is just for projects and then of course we'll want to know the ever so important the customer acquisition cost what is the actual cost of the acquisition right we're going to be able to track expenses as well i didn't mention that but that's just a little bit of a table it's not a huge big deal there but all i do have here is a table of expenses so i want.
To know what our expenses are what is our actual cost of bringing those customers in so we're going to go over that that's a very important metric in applications like that we want to know what the cost of these customers are and basically if we have uh you know 1500 in sales we have 34 new customers we've got to tell our customer acquisition cost is 42 per uh customer or 4233 cac customer acquisition cost and the project summary all right so we've got a lot of this if i do move fast and i tend to do move fast i know that i can see that got some pivot data here i'll show you all you need to do is just slow down the video change the video of course i'll try to slow down i do want to slow down as well and i'll do my best to do that but you can also slow the video down to 0.75 or 0.5 and that way i'll talk slower automatically.
RELATED TOPICS:
It'll force me to talk slower so that might help too because i know i get excited i love sharing
These applications with you just so much i get excited and i tend to talk fast okay so let's get into it let's just see how we do that we also have a really cool search by we want to search by our contacts we don't know we maybe want to know what our contacts are so maybe we want to know which jobs are in progress so we can do a search by in progress and it'll show us these four contacts are have in progress we can clear the filter we may want to search by contact name so if we search by.Fr we can search by fr by contact name so i'm gonna show you how to do this dynamic search by very very easily and yes just a few lines of code on this so it's really really a great feature lots to show you so let's get into that okay so we've got this feature and of course we're going to be able to add a new contact we'll be adding name information here save the contact and of course delete a contact when i select a contact from this list it is going to load up automatically contacts will be able to add documents for those contacts let's pick a peter parker's got some documents we'll be able to review that document we'll be able to see a specific thumbnail of that document.
If it's available and also we are going to have the ability to add additional communications onto this there's so many features onto this so if we add a communication maybe we want a phone conversation maybe we sent an email maybe we had an online meeting or an in-person meeting we can do that so all we need to do i should probably add a date a calendar date picker there on that so we can simply add one there like a phone meeting and it's checking it's automatically going to be saved right if we just want to add a message here phone message left a phone message we can just simply select it and then save it and it's going to update that communication so we can keep track of the communications that we've had with that contact the full history it's automatically.
Going to be supported by date and what that means if we enter something at a later date right and we decide to add that when we re-add that contact is automatically going to be refreshed so when we see it it's automatically going to be set to the top so that means the newest is going to be at the top we'll be able to add documents adding any type of document even pictures that's from prior training so we can add pictures onto that so we know we're going to have that but to add these pictures now these documents we need a central folder to keep track of it and what i want to do is i want to also be able to track them on a per customer basis so if i take a look inside my desktop here right we're going to take a look we've got two different folders we've got one for david davidson one for peter parker now peter parker we just added so we added that picture now every time we browse for a.
Picture we're going to create a brand new folder if it has not been created for that customers that way whatever folder that we've selected to keep track of our contacts is going to automatically copy those documents into a folder for that customer or create one if it's not yet been created so that we're going to be able to do and i'll show you how to do that so documents whatever they are are going to be are going to be handled here now whether it's pdf or word documents we can also do that too so if we want to add a word document we can add a word document and then we can get a thumbnail and i'm going to show you how to do that as well so let me just browse here so we've got a little bit of a word document here and this word document it's going to automatically summarize and browse it'll be kind of contorted but we'll it'll you'll get an idea of a thumbnail.
Of that document even if it is a word document so previewing pictures previewing pngs previewing pdf we can do that all with just a little bit of code and a single click is going to do that okay great so let's get started on this and we'll go into exactly how we did it and then we'll go into the overview now we're going to go over every step however this project we're going to skim over this because this is already detailed it is a big application and i want to contain this in within just a few hours before my voice goes okay so what do we want to do well why don't we start out with this filter here how do we create this filter let's go into some of the databases and see what kind of data makes up this and now as i mentioned before we've really got two folders right we've.
Got a document folder and that's where remember that's where our documents are kept and i've got an icon folder so those are really important now the reason that we have icon folders is because with these individual statuses we can also assign an icon to that and if you saw previously in that previous folder right about here i had these icons so i've got individual icons and they're inside this folder now if you like these icons and pictures and you want to set it up just like i did i'll make these all available through our patreon account so i hope you'll join us on patreon all the resources that go into the training along with the updated workbook will be available on our patreon platform so we have that we've got we need to know what the names of those icons here right.
Those names match exactly the names of these icons here so we can browse that we need that because we're going to be showing those icons here if you'll notice our kanban contains those icons so we can when we refresh it it's automatically going to show those icons and that's really important so we need that we also may want to not show cold leads right so we may want to unselect these forgot to show that to you too i always forget to show you so many features so maybe we only want to show a few different uh types of statuses we can do that here maybe we want to show the inactive or not show we can do that with just a simple selection change so so much really cool things to show you in this application alright so our admin screen is getting made up of that we have.
Our communication types this is a dynamic list we have our our project status is here we have our advertising expenses this is something we can build out more in other words i did not add a form to keep expenses however that's certainly something that you could add or i could add on our patreon platform if you wanted all we have here is just simply a table with our expenses relatively just some basic data on that it's going to cover it and that's sufficient for this training we also want to know the project details are we charging sales tax what is the default sales tax rate and what is the default status if we create a brand new project what is the default status of that.
Right is it proposed scheduled so we can set that up automatically project types this is something i might add in the future but i didn't really go into too much detail on this really i just
Kind of put it there as an idea we're not really using that in this training okay we can browse for the individual document folders here just with the button either with for the icon it's just a relative macro for that okay so what else do we have i've got some pivot data we'll be going into that before we get into the dashboard i've got obviously a contact database we're going to have data mapping here we'll be going over a little bit into that although we've done it before if you haven't seen my video we'll go ahead and we'll go over that and of course we've got our contact documents database remember we need to keep track of documents on a per contact basis i need to know.The contact id that that document was assigned to the date that is assigned the name of the document the type of the document and i also need to know where's the location of that document where is it right we've created a brand new location and the associated database row and also what i need to know is i need to know the contact communication remember we have communications the contact id the date of that communication the type of that communication some notes and the associated row.
Relatively simple expenses we saw that already and i've got a project list this is our project id the date of the project the contact id what is the you know each of our contacts has our own id and our contact database right here contacts have their own id so that's important and also the contact name the status of that project the total if it was paid what is that total paid and also what i do is i have a project item so these are the individual items associated with a project.
Project number one contains these items here so if i were to show you a search for a particular project we want to see a project let's say project number one i'm going to search for that and load that project project number one has little four items right so that has to be stored inside another table here and we have that available to us inside that project item list here so that keeps track of those four items on the project list the technician the description the quantity of the amount the cost of that item the row on the invoice is really invoice or project row right 9 10 11 that's the rows associated with that and then the row of the database okay so that's.
All very important what about the items right we want to add items to our project right we need an item id we need the type is it a service or an item the name the description what is the default quantity the cost and the price and also we have technicians right that's not something this is just available within our projects right so we're able to assign technicians to individual projects here by adding editor so if we add a service type item here like a tire change that's a service type item so we want to be able to add technician who's going to do that work.
Setting the amount rate and the cost of that labor all right great so that's kind of an overview of this application let's start out within the context because i want to go over this filter this is really cool so basically what we want to do is create a dynamic filter based on whatever that is not only that our dynamics is going to have a dynamic criteria that criteria could be any one of these right so all we have to do is create that and that of course original data is going to come from the contact database here so our contact we're going to filter by one of these so what i need to do is i need to create a link in order to do criteria so this criteria here we're going to run an advanced filter from all of our context we have a dynamic criteria notice it's context a e3 that is the same cell as this one so as i change e3 for searching by status right it is.
Also going to change inside that context database it is now e3 since it is linked and as soon as we put it in we have noticed we have two stars that means we can search any type if we want to these two asterisks on either side of e4 are going to are going to change that so if i search for a particular status such as paid what i want to do is or maybe i just want to do pay right let's say pa right i can search pa right and what that's going to do even if i don't fully do the word paid what that's going to do inside our contact is going to search any status that contains p a.
And so that way the results this criteria will result in these results these particular contacts have a status that contains the word pa which i think is just paid right so that's paid is the only one so it's definitely going to look here and search for pa now of course if we did just p it would go down propose in progress it would be a lot more so we can do that as well we don't need to put in the full name that is why we put the wild card before and after so that our search simply contains the word being contains p and a okay our p a okay great so that's going to be a dynamic that all we need to do is have v2 through v3 inside our criteria our results are.
Going to come here from x through another result then all we need to do is determine the last row bring our results over into the contacts here and bring them over here now that contact id is important because when i select it it is that contact id that i need to place directly inside b but where is it located well it's located right here but how come we can't see it right that contact id is brought in here but we can't see it why can't we see it because we've used.
Us very very special custom format if we look in the more number format and we use three different semicolons here we are not going to be able to see it if i were to take that out and click ok we'd be able to see it you see now we can see it right so that's a great way of hiding data if we don't want the end user to see it but we know it is there if i do control z it is going to be hidden again okay so that's a great way to do that three semicolons to hide it sometimes i used to do background you know you can do it same color as a background that works too that's just fine or we can do that type of formatting both work well so that's how we do it but this one i'm using a custom background if we look in the page layout and we delete the background you see it's just white right and all.
We have to do to add it back in just simply go into the background here and then add it again working offline that's fine and then just clicking insert and that's going to insert the background here i'll include that picture in our patreon account of course great so how do we get this really cool search feature right all we need to do is just simply enter some values and it's going to automatically think i've also tied a clear search to this so we can clear the search out so how do we do that right it's very very clear how we do that and just in a few lines of code so let's go into that and i'm going to show you exactly how we do that of course it's all going to be on change event based on e4 when we make a change to e4 that's when we want something to happen.
All right and that's going to happen inside the worksheet so we go into the developer and visual basic we're going to take a look at the worksheet event that's going to be based on the context so when we take a look inside here inside e4 right we're focused on our worksheet change event on our contact sheet when a user makes a change to e4 then we want to run a macro that macro is called contact list load when we right click here let's go ahead and go move this up here all right this is the macro that we're going to run and when i right click and then go to the definition we're going to see that we have inside our module called contact macros the first macro.
That we're going to focus on is the contact list load now the first thing we want to do of course when we do that is we want to make sure to clear out the data right we want to clear out any data that's shown in here and also i've got some hidden data here i've got some hidden data right here located inside column c if we click on here we can see in the formula bar we've got these contact ids here now how do we hide those well we can do very simply with some formatting and how do we do that with some custom formatting if we go into the more number formats we see that we're going to use three semicolons three semicolons will hide whatever there so if we clear that out we can see that now it is one contact id one if i control z and undo that it's going to.
Automatically be thing but now i'm not going to do that so let's go back in and set that we've already reset that using the selection change so custom formatting right all we need to do is just three semicolons right here one two three and then enter that's gonna be i will hide it automatically okay great but how do i get this really cool dynamic filter where i can search by any name here let's go into that and of course that's going to be on that change event right here so when i make a change we're going to go into this is the macro that's called contact list load the first thing i want to do is of course clear out some fields when i select a specific contact that's going.
To be highlighted and what i want to do is i want to make sure that the selected row gets cleared out that's going to use conditional formatting if we highlight these i'm going to the home we go into conditional formatting we're going to see three different conditional formats right the first of which is we want that highlighted row that's going to be based on whatever row number is located inside b4 so when we move that over here we scroll up we see that b4 is 7. so we know that row 7 is going to be highlighted and then of course we've got also some additional rules and that's going to be for the alternating rows so notice this formula mod row equals 1 this.
Is going to be for the odd rows and this one is going to be for the even rows we also want to make sure that that highlighted row is always on top if we were to move that to the bottom and then apply we would see that we would not get the result that we want because this rule this odd rule is taking precedence over it so that's why we don't see it we want to make sure that the selected row the highlighted row is always on top so we're going to move that there as soon as we do it will show up great so what i want to do is when i make a clear clear that search result i want to make sure that that highlighted row is automatically cured out to do that i want to make sure that b4 gets cleared out i also want to clear out the contact id in c6 all the way through e and then down we can.
Do that that's the first thing that we're going to do so with the contact sheet b4 we're clearing out that highlighted row selected row plus c6 e9 we're going to clear all those contacts out then we're going to focus on the contacts database it is this database where we're going to run our advanced filter and it is this database where we're going to use our criteria so that we're going to determine the last row right if the last row is less than four we're going to exit the sub out we need to make sure there's data we're also going to run our criteria going to be based on v2 through v3 this is where our dynamic criteria comes in right i've got a link notice we've got a dynamic header context e3 based on the header whatever's in the context e3 here it's going to come so if.
I change that to status and i move that to paid it's only going to show those paid jobs and it is this criteria here that we're going to show status paid so we're wrapping that with the aster so that that means anything that contains the word pa id if i were to do just pa that would work as well so it'll also result in that so pa doesn't have to be exact and that's because we've added the asterisk before and after that so this is going to be our criteria we want our results to come here.
From x2 to x3 and then we're going to bring in assuming that there are results we're going to bring those results in to our contacts okay so let's take a look inside that inside the vba as we move through the vba we're going to determine the last row based on our context database if it's less than four we're going to exit the sub out we're then ready to run our advanced filter we're going to be from a3 through n using those header rows and we're going to use that criteria as i mentioned v2 through v3 we're going to copy that range into x2 that's where we want our results to go and we want those to be unique then what we're going to do is we're going to determine the last results row based on column x if that that's less than 3 that means we have no results data but however if we do we can then bring that directly inside our contact stream so the context.
C6 through d in the last results row plus three why because our results start on row six however our original data starts on row three so we need to compensate for that difference so we're going to add three and we're going to bring over the data that is it that is it all right so that's how we do it but what about clearing the filter how do we quickly clear the filter i've tied a macro to this clearing the filter is just as simple because when we have it all we need to do because i've got the trigger on e4 all i need to do is simply delete or clear whatever is located in e4 and automatically it's going to run the macro clear filter and we do that with just a single line of code here that clearing the filter all we need to do is clear the contents of e4 all right.
Very very cool next up how do we save and update this it's this button that's tied to this button here when i run that whether it is a new contact or whether it is an existing content we want it updated but we do need to differentiate between a new contact and an existing context so that's an important distinction so we've got some additional features here inside our hidden columns a and b what i want to know is i want to know the row that is associated with this contact and how do we know the contact is going to be located here in b3 as we load it what we're going to be doing and i'll go over the section change event i'm going to take whatever whatever contact id appears and i'm going to place it directly in b1 then what this b2 is going to do is going to calculate.
The row that that contact is located on inside the contact database so we see that contact id number five mark mason is located in contact row eight if we look in our contact database here and we look at five mark mason we see that it is on row eight so that's what i want to extract we can use a named range to do that now i've got several named ranges we'll go over them very briefly but we've been over there and then also what we have is a contact id here that's based on all the context using offset that's a dynamic named range if you want to learn the basics of.
That of course we've got that great vba course by daniel strong the ultimate excel vba course i'm including the links down below so that will help you with these types of name ranges i've also got contact name and a few other things so you know if we know the contact name we know the contact id then we can determine the row of it directly inside here so we're going to wrap it on iferror we're going to match whatever's in b1 base sitting on the name range contact id we're adding three because our first contact starts on row four and we're going to return empty if there's an error so we know that it's row eight so if we know that if there's a row here then we know it's.
A new existing context however if we click new contact we're going to clear out b1 if we clear out b1 it's automatically going to result in an error which is going to create a blank space and we know that this is a new context so we're going to use that also we're going to determine the next contact id using the max formula plus 1. that's all the contacts the maximum of all the contacts which is 34 plus 1 which is 35. if there's no date at all it will create an error so we want that to result in 1 and that way the next the first contact id will always be 1. excellent so that's how we do that so that's how we're going to differentiate so when we take a look inside this.
First thing i want to do is make sure that there's a contact name if i try to save a contact that doesn't have a name i do want to let the user know to make sure to add a contact name before saving right so if i add let's see i like fred fretters ranger so what i want to make sure is that we save that content and assign a brand new row so as soon as we say that we want to make sure the contact rail and we want that brand new contact id down here so we have to differentiate between a new contact and an existing contact and we're going to use b2 to do just that b2 equals empty we know it's a new context we're going to determine the new contact row using the first available row inside the context database we're going to take that net contact id as we mentioned that next.
Contact d and place it directly inside b1 i also want to take that next contact id and place it directly in column a here okay great so once we've done that a can take on that brand new contact id we're going to focus on the existing existing all we need to do is extract the row from b2 for everything else we're going to use data mapping right regardless if it is a new contact or an existing contact what i want to do is i want to save all of the data inside here all the data all these cells i want to save it directly to that so that way if we decide to add a specific cold lead here what i want to do is i think this is the default when i click new contact i'm going to update that it should say status right i need to make sure that the default brand new context will fix that up new context we want to make sure the default.
Status goes into n5 not m so i'll make sure when we get to that macro i'll make that update so i want that status to show here prospect okay so what i want to do is i want to map those fields n 5 or i want to map h5 inside the database so i've done that here inside the contact database h5 here for the contact name j5 for the office phone number l5 and so on and 5 for the status data mapping we're simply going to loop for the first all the way to the last and then whatever is in those cells we're going to place them in the selected row that's how we do data mapping and.
This particular will handle that and then we're going to run a fade-out message for save that message all that does is simply loop through a timer it takes this particular shape contact save message that's the shape that you saw the same shape when i click save contact let's select it when i click save contact it's automatically going to be uploaded so it's going to get that little let's take a look at that in so you see that fade out message it's going to loop through that timer and slowly it's going to change the transparency and then it's going to turn it off that's what this macro does you can just copy and paste that macro contact new here's where we need to make that fixed m n 5 is where we want that default status and five and that's going to come from.
Our new admin screen but not before what we want to do if there's any document preview right if i've worked on a specific and peter's got some documents here if there's a preview that preview name is always going to be called document preview so when i click new contract i want to make sure that any preview i also should make sure that this is hidden too doc group i want to make sure that that is hidden as well okay so we'll focus on that too okay so i want to make sure the document preview is done so i want that document preview we're going to delete that okay i also want to that shape that you saw i want to clear a bunch of cells so we're going to do that l11 i want to put the current date l11 is actually our created on date and again we're going to set.