How To Create A Spa & Salon Manager With Drag & Drop Scheduling AND 1-Click Invoicing In Excel

hello this is randy with excel for  freelancers and welcome to the spa   and salon manager in this week i'm going  to show you how to create this incredible   drag and drop scheduling salon manager  complete with editable appointments one click   invoicing and a whole lot more i cannot wait  to share this with you so let's get started alright thanks so much for joining me this  week i've got a really great one the spa   and salon manager so much to cover in this we  are combining scheduling we've got pictures   i've got invoicing i've got a full on setup screen  where you can fully customize an application like   this and create those applications yourself  that's what i ultimately want you to do i hope   you do enjoy these trainings i bring these to  you each and every tuesday of course and this   application is absolutely free using the links  down in the description either with your email or   facebook messenger in fact i've bundled over  200 of my best applications in a single zip file   and made them available to you if  you'd like to pick that up it's just   77 right now and you can use the links down in  the description that helps keep these trainings   free each and every week so i really appreciate  that all right let's get started on this training   because i've got so much to cover this type of  application you can create any type of scheduling   application so you can create this customize it  for your clients sell these either way this is   a really great foundation because we're gonna  not only be able to show you how to scheduling   how to avoid conflicts how to create a drag  and drop scheduler how to add pictures of your   staff in there how to set things up so you  have a dynamic setup screen so you can show   what you want to show on those drag and drop but  simply by selecting or unselecting that having a   dynamic start time intervals a booking type like  chair or bed or room or something like that could   be anything and also we can have dynamic of course  rooms or chairs whatever you want on here so if   i were to change this to a room if you want to  book a room this is going to change room all you   need to do is just change them down here so very  very dynamic application and that is the key here   customizable tax figures a lot to show you  so this is a lot of foundational principles   that i'm going to teach you in here even if you  don't want to create your own spa salon manager   the things that you're going to learn in here the  scheduling the invoicing the formulas that make   the drag and drop the editing everything is going  to be really really foundational so you're able   to create your own applications regardless of  what they might be and then of course sell them   for passive income that is my goal here that is  why not just to teach you excel but to make you   successful with excel this application simply is a  drag and drop scheduler for the drag and drop it's   going to move that appointment update the stats  and everything like that you'll be able to edit   the idea you can change the the primary service  if you have different services and i'll save and   update that colors are dependent based on service  and that means when you have service items you   create different service items we can also assign  a color to that so we can assign a different   color and so for each let's say we style 24 cut  and style whatever service you're providing you   can assign a color to that if we assign a color  to orange that color is going to be reflected   inside the appointments we'll be able to navigate  based on the days and we'll also be able to show   you a lot how to do that also be able to add brand  new appointments over here and i'm going to show   you how to create this custom background we'll  be able to show you then how to edit it just a   one click to edit that appointment unhiding some  rows to allow us to edit that and also creating   a one-click invoice where a single click to  allow us to create an invoice for that so a   lot to cover i cannot wait to share that with you  so we're going to get started right away let's go   over a little bit of an overview of what we have  in this application obviously if we're going to   be working on clients we need a client list right  so we have a client clients customers all the same   thing we're calling clients this time but it's  really the same thing and we just have a list   we've got a client id a name and address city  and state that's going to appear on the invoice   zip phone number and email if you need all right  along with that we also have a setup screen as i   showed you briefly before the setup screen pretty  much it has the ability to create customized   user experiences so we want to know what if their  start time is nine what if it's seven o'clock   right and that's really helpful because we can  start it earlier or later or however you want and   it's gonna be based on those times those intervals  so it's based on whatever times you want to set up   in your application okay so we also have intervals  maybe you want those intervals every 30 minutes   right so we can set that and as soon as we do  that and refresh the schedule it's going to update   automatically on the schedule so now you see those  appointments are all condensed right there every   30 minutes so these are shape based and they work  really really well with that kind of interval so   we can customize the scheduler any way we want it  all right so back in the schedule we'll set this   back to 15 minute intervals this is what we had it  but it's very customizable we have uh then again   our customized right if we were to change this  to let's say room one that's going to go ahead   and change on the schedule so now we see we've  got room these are basically using formulas that   we're going to go over so it's very customizable  in that way and of course also we have now the   room one is available in the drop down list as  we add those things to the appointments right so   whether even if you're using meeting rooms or  anything like that there's a lot of things you can   do with this any type of scheduling application  can benefit from this kind of application   okay so we'll go ahead and set that back to  chair one course you can call it anything um   our sample today is going to be with a salon so we  also have the durations right how long are those   appointments these durations are going to be based  on our intervals so if i set five minute intervals   i may want five minute durations right if i set  it back to 15 we're gonna have 15 minute durations   interval intervals within 15 minutes 15 to 30. so  that's all based on some formulas okay so we also   have an appointments right an appointment stable  now this is simply where all those appointments   are stored we have an appointment id a client  name a date the date scheduled the start time   what time is the appointment started how long  will that appointment go for and along with the   end time i want to know the staff who's been  scheduled on that what we're going to call   this booking type but it's also known as chair  notice this is also based on the booking type   so if your booking type is room or bed or whatever  it is it's going to appear here so we're going to   call this booking type for our purposes okay so  once we have the booking type we also want to make   sure that we have the primary service what is the  service that we're going to offer our customers   right we also want a secondary service maybe they  want two different services so we can put up to   two in this application of course if you could  create this you can add many more if you like   okay also what i want to know is some notes  perhaps some notes we're going to put on that   so that's all based on the information that's  entered here inside for each appointment when we   save an appointment those lines hide and we also  have a nice beautiful schedule that we can see we   can also see that our staff pictures are displayed  so when we go into our service items here we have   an item id these are the service items these  are the service items that we're going to be   choosing when we add a particular appointment it  is this list of service items here and this list   of services items here the same list of service  items that we have here we also have a description   this item description will appear on invoices  the duration what is the default duration right   this is very easy when we create it if we create  add a new appointment and we decide that we're   going to give it a specific let's say a primary  service of wash and dry we want to know what the   duration is of that what is the default duration  of that so that way we can simply add a service   and then we know the default duration so that's  why it's helpful to add that duration i want   to know a color we're going to show you how to  create this little pop-up color that automatically   changes based on you know style the cost of it  we're not going to use this too much but i put   the column in there because it might help you  understand when we create these invoices what is   the overall cost of that and then a price that's  going to get transferred over to the invoice   we also have a staff list here just basically an  employee id a phone number email we're not going   to use too much of this really and but i just  kept it there large and but most importantly we   are going to use this staff picture now that staff  picture have to be i got a lot of questions hey   how come my pictures don't show up right make sure  you watch these trainings of course that's first   and remember this staff picture here this is just  a picture name right the only way that we can   insert a picture as used because when you get this  application you download it you're not going to   be able to see these staff pictures why is that  because you have to set your folder this is where   my picture folder is located right here so when  you combine this folder path for me it is this   location right here when i combine this folder  path and i make that combination with this file   name picture here inside our staff with this page  name the combination of that path will create the   full file path of the picture i can then load  that in so make sure when you watch these videos   and you understand that that's why if you're  not seeing them you know a lot of people get   this and they say hey the pictures aren't showing  up well that's because we have to make sure that   we create that full file path and then of course  on our patreon platform if you want these pictures   on our patreon platform i provide all the  icons i provide all the employee pictures   all the resources associated with every training  is available on our patreon platform so i hope   you'll join us there all right so we have our  staff and we also have the invoice what is an   invoice screen all the service items we have our  client we have our description of that service   item the quantity the cost and the total we can  save new invoices we can load in invoices we can   update it and save and load it so it's really  really helpful we can create an invoice in just   one click based on that schedule so when i  click on a specific appointment here and i   click this little dollar sign right here it is  automatically going to create that invoice if i   save that invoices it's automatically going  to be saved i'm going to walk you through that   we have an invoice list this is the list of the  invoices i just created all these invoices here   the date of that we probably don't need the time  there that's just a format let's go ahead and   update that that format should be a short date  format okay so we've got a date the customer the   staff that's associated the staff is also brought  over from the schedule right so if we have a   staff of greg perkins right and we create an  invoice for that it is that staff that's going   to be brought over into this so it's going to  be brought directly over and the total there we   also have an invoice item these are the individual  items from every particular invoice i just created   invoice number four that invoice had two items  and the invoice item name description we have the   quantity the price and the row associated with  the invoice notice this is nine and ten if we   look back on the invoice we see that this is row  nine and row 10. so we need to know what when we   need to load that up if i want to load let's say  i'm going to load one up and then i want to load   four up that's the one we just created i need  to know that they need to come back to row 9   and come back to row 10. so i'm going to show you  how we do that to make sure and also need to know   what database they were saved on that's 11 and 12.  notice we also have in our invoice item 11 and 12.   that is the row 11 row 12. we need to know what  row they're saved on so great that's pretty much   it that's the foundation of this application  that's everything that all we need to do to   build it so how let's get into the intricacies to  see exactly how we did that of course i want to   take you a little bit through the setup screen we  went over but i just want to go over some of the   named range that we've created those named range  is going to help us go on we have a start time   this is the start time i'll probably probably  create this earlier 11 if we go into the data   validation data validation and we see that this  is based on the times right so this is based on   all of these times associated here every time that  we associate here so we can create that i will   probably make an update so we can add additional  features we can create that based on maybe a time   in the setup screen a list of times so they  can be customized okay so keep that in mind   that's just based on that and also what we have  is we have an interval of 15 minutes five minutes   and but what i really want is not this interval  five minutes or ten minutes what i really want   is i want the number the decimal number that's  associated with 15 minutes which is this   now how do we get that well we're going to use  these intervals i set up this little table now   you've seen this you may have seen this before in  other applications that i've created so basically   i want to associate a decimal with a specific  amount of time how do we get to these well if   we know that in excel one day is one right so  if i want to know how many what is the decimal   associated with one hour all i would need to do  is do equals 1 divided by 24 and that is going to   give us that .04167 now what if i want to know the  30 minutes why would you simply divide that by 2   and that is going to give us let's try that again  equals this divided by two that is going to give   us our 30 minute intervals and i just keep doing  the division you know dividing it by two right and   then this would be six in one hour ten minutes of  six so i divide that by six as you can see right   here one divided by 24 divided by 6. so what i  want is the decimal that's associated this every   5 minutes is 1 divided by and then 12 because  there are 12 5 minute intervals inside a single   hour that is going to give us this time right here  these decimals because times in excel are based on   numbers or decimals anything less than one day is  going to be a decimal so what i want to do is i   want to determine what this 15 minutes is what  is the interval that's associated with this 15   minutes how can i do that well i can use that with  an index so what i'm going to do is i'm going to   index all these intervals here g and then what i'm  going to do is i want to return the match based on   c4 what's in c4 that's what they've selected here  let's go into this one here so you can see c4 is   going to look for c4 it's going to look for here  once it's found it's going to return the decimal   associated bet using a match located in column  f so basically we are going to return point   zero one zero four that is the one associated for  fifteen minutes it is this one that i've called   the name bridge called interval interval that's  the named range so when i create these durations   here all i need to do is say equals interval and  then all i need to do is just equal whatever is   above plus the interval now the difference here in  this list is is the formatting if we look into the   home and we go into the custom which is our we  see that this has been formatted still the same   decimal number but this has been formatted with  this custom h colon mm that is a custom format   that's how we get this duration i don't want times  i just want the duration shown in hours so that's   how we get this but it's also right if we were  to change that to general we would see that it's   still that decimal that's showing up it's just  based on the format that we've set it up okay so   that's going to give us our duration our list of  durations it is this that we created named range   so inside the formula name manager all the way at  the top duration here let's take a look down here   we go duration here that is going to be basically  to set up everything that's going to be based   in this column so that's all the durations we  have so we've created an image called duration   so it is this same duration that we're going to  use directly inside here when we look in the data   validation here and data validation you see it's  duration so that's how we get that duration our   time it's a data validation of course based on  that time so we already saw that that's going   to be called the times that's going to be based on  this time so continuing on the setup screen so we   understand that and then we understand the picture  folder i also want to know inside the invoicing   are we going to be charging sales tax or  not so if we are then we need to make sure   that we are if it's yes or no i want to base  this dynamic and if so what is the percentage   so if it's no on those invoices i don't want to  show any sales tax however if we do say yes and   i want to and we do set a tax on the invoices i  then want to show the tax amount and show the tax   information so that's of course dynamic as well  all right so that's pretty much it for the setup   screen and then we have our dynamic remember this  here is going to be based on the booking type plus   i'm going to add an s to it so if i put room here  or if i put bed here or if i put any or stall or   anything like that it's going to just be plural  here so for example just change room here it's   going to be room so that gives the user a little  bit information on what to put in this column so   that is all your information or that's how we're  going to call that the booking type what type of   room what type of chair are we booking what type  of you know what do we need okay so that's it for   the setup screen relatively simple not much going  on here you can add to that of course there's a   lot more you might want to do with that but that's  all clients is relatively simple just the table   inside the scheduling we have some information  here that's going to help us moving forward   because we've got also some named ranges and some  formulas here located in columns a and b let's go   over some of the basic named ranges so we can get  that out of the way so i want to make sure that   we understand so we have an appointment booking  type now the appointment booking type is based on   the ap so we have appointments we've got several  named ranges and they're all dynamic name drains   using the offset we've got one for booking type  this is going to be book and type remember it says   share again we're using a formula on that header  based on the book and type we have the appointment   date here we have the appointment end right i want  to know the end time we have the appointment id   also very important let's just close this out no  i don't want to save the changes and i'm going to   go back in the appointments let's continue on so  it doesn't keep switching screens here so we also   have the staff that's associated with that that's  going to be really important because i'm going to   need to know are are there conflicts are there  staff columns so i've created a different named   range now a lot of you have asked me randy why  don't you just use tables because the name ranges   are created automatically i really like i'm very  very picky and specific about how i create these   named ranges and what they look like so i really  wanted things to look the way i you know because   when i put these in formulas it's very easy to  read and they're very short formulas and they're   very easy to read and so i just like to have my  own that way but of course if you're comfortable   with using cables and you enjoy using tables and  they're they're good great you know that's that's   as long as it works for you that's fine  right i'm just showing you an alternative way   it may or may not be better for you but i'm  really i really like to work with these ranges   i fi i find that tables are limiting so we  have the appointment star tape and then back   into the booking type we've got booking type  i showed you that already so we've got that   inside there let's go back inside our service  items we've got a few we've got item id also   a named range okay and item name right item  name also in name range we can assume they're   all based on offset and that's going to be based  on this item name right here so we went over that   so it's called item name data validation we've got  two fields that are associated with this called   service name service name we're using okay service  name and that's going to be the same for both the   primary and the secondary and then just some  nodes fields there so what else do we have we   have a few others but we'll get into them as we  need them so we don't go over all the name ranges   so i've got a client appointment date appointment  time duration and i also want to know what is the   selected appointment id remember each individual  appointment has a selected id that's how we can   keep track of them and when we select a specific  appointment i want the id of that to appear right   here i want the id whatever that ideas appear then  i also want to know what row is associated with   that appointment if we appointment uh for example  if it's appointment id number three it's this is   row number six our first one starts in row four so  keep that in mind so to do that what we're going   to use we're going to use a match and we're going  to base it on a named range called appointment id   whatever's in b2 i'm going to run a match and if  there's a matches found i'm going to add 3 onto   that right because our first one starts in 4. if  there's no match we're going to show a blank that   is going to give us the row number and that's  how if we add a new one right let's move over   here maybe we should move that button over it's  a little bit farther over here a little bit too   far to the left here so we can't see it okay so  if we add a new appointment here that's going   to go blank because everything's right there's no  longer we've cleared out the appointment id in b2   now it cannot find an appointment id therefore  it's showing blank right it's a brand new one   we've got some required fields if i try to save  this of course it's going to let us know we've   got required fields i also want to know the next  appointment id the next appointment id if we take   a look inside appointments all of our appointment  ids are numerical the next one would be 15.   we can use that if you see my videos before we  use the max formula max of all the appointment   ids plus one if there's an error why would there  be an error will there be an error if there's no   data at all then i want to revert back to the  first available which would be one the first id   then what i want to know is the end time what is  the end time i want to calculate and if you'll   notice here on a selected employee we've got a  we've got an appointment time and we've got a   duration but there's no end time here but i really  want to save that end time and that's going to be   important when we determine if there's conflicts  is there is the staff occupied at the same time   or is the current booking type in this case chair  is the chair octane right if i try to move this   right over the top of this one it's going to let  me know hey this chair is already occupied for   this time please select another time so we got  to select another time you know we can't do that   right because that chair is occupied so i need to  know that we're going to use a formula for that we   need to know if there's conflicts so again that's  really really important when we can do the same   thing with staffs so what i want to do is i want  to know an end time and i want to save this end   time inside the appointments database so i want  to save it right here but to do that what i want   to make sure is that we don't necessarily need an  end time we don't need the user to enter the end   time all we need is the appointment time and the  duration the end time can be calculated and it is   simply that it is simply the appointment time plus  the duration right plus the duration that's going   to give us our appointment time so to figure  that out we could do equals right 8 a.m plus   one hour just entering that's going to give  us that end time and if we format it based on   the time it's going to be like that that's all we  need to do because what i want to do is i want to   figure out hey is there a conflict right is there  is that chair occupied from the appointment time   from the beginning time to the end time so end  time is a critical component of that okay so then   what i want to know is i want to know uh that have  all of the required fields been filled in when   i click add new i have the six fields that are  required if i try to save it i need to know hey   all these point the fields are not filled in let  the user know that these fields need to be filled   in the best way to do that if we could do if d  is blank or f is blank or h is blank it's a lot   more complex in or just more time consuming more  code writing but however if we can just create a   specific formula that is going to let us know how  many of those required fields are have been filled   in so we can use that right here required fields  well all we need to do is just use count a based   on all of the required fields if that is less  than 6 which then we know that we need to let the   user know that all the required fields have not  been filled in so when we select an appointment   we see that now six have been filled in so we  know that we could save this those required fields   so we will make a check inside the code if b6 is  less than six then let the user know that all the   fields have not been filled in so to put that i  also want to know is there a conflict right is   there a conflict between the chair we'll call it  chair now notice this is dynamic it's booking type   and conflict so if you change it to room it's  going to say room conflict which is kind of   helpful okay so keep that in mind now what i need  to know is i need to simply add up and we're going   to use sum product for that and we're going to use  some named ranges inside that so let's take a look   quick look at these named ranges just to double  check before we go into that i want to remind   you as we use it so we have appointment staff  appointment staff it's all the staff associated   appointment starts all the start times appointment  and here's all the end times this is where that   end time is going to play into it and also we  have the appointment date so we're going to need   all of those things the first conflict that we're  going to check for is we need to know if there's a   conflict and i need to know if i try to take this  appointment here and i try to drag it over right   in the middle of this one it's going to let us  know hey this chair is already occupied for this   time please select another timer chair right and  so we have to then just change that to chair two   or change it to another chair and save those to  make sure that it gets changed back okay so keep   that in mind of course we can move it before  which is not a problem right so that same but   it's just the so we have a chair conflict so when  there is a chair conflict we need to let the user   know we could do that with this formula right here  if this particular number here is greater than one   greater than one that means there's a conflict  how can we do that well we're going to use some   product for that i need to know if there's  more than one count of the following if the   appointment date is equal to f2 right so that's  our appointment date f2 is located right here   inside our appointment date here then also what i  need to know is i need to know if also the start   date is less than or equal to h2 if the start date  and if the end date is greater than or equal to h2   and if the appointment booking type is equal to f3  so if all of those instances so what i basically   want to do is i want to count all of the instances  when all of those conditions are true it's just   one right now right just this appointment but if  it's more than one what i want to do is i want to   let the user know the first thing what i want to  do is i want to add some conditional formatting   onto here to let the user know as you saw there  was a problem right when we moved it down here   take a look at this all of a sudden this  number became two now there's two appointments   that occupy that time slot they're on top of  each other right so we also want to add some   conditional formatting in here so when we go into  the conditional formatting and manage rules it's   a very simple conditional formatting and we're  going to edit the rule when b7 is greater than   one i want to give it this red background with red  font so they'll let the user know that there is a   conflict as soon as they make that change and then  save those changes the conflict is gone so that   is what we call a chair conflict or a booking  type conflict we have another type of conflict   right we also want to know is the same particular  staff does the same staff have a conflict are they   occupied at that time we have a staff conflict  right if the same staff is also going to be doing   the same thing if we take a look in here we see  k hopkins on this one you see there's a conflict   all the way over here on the right here in chair  seven there's also k so take a look at this k   has an overlapping time right so we we see that  there's a an issue and it's going to let us know   if i move this down where she has availability  here we'll see that that conflict now we're going   to click on that and click on the edit there and  we see that that conflict is gone so we see that k   here and here there's no longer conflict and we do  exactly the same thing on the staff conflict the   only difference is appointment date appointment  start and appointment enter all the same the only   difference is this time we're counting the staff  based on what's d3 that's going to let us know if   there's any conflict so if there's any more than  when all those situations are true if there's any   more than one it's going to put two and that's  also going to put a conditional formatting here   just as like one basically the red background  and the red font if this b8 is greater than one   so that's how and also when we run the macro and  we save it we're simply going to check if this is   greater than one then we need to let the user know  that there's a conflict and to let them fix that   okay great so we understand that staff conflicts  we also want to know for drag and drop when i   select on something i want to know if there's been  a move right this is going to tell us if it's been   a move and this will go to false true and then  false and then back to true again okay so that's   going to let us know here i'll show you a little  bit more about that also we have a left position   when i select a shape i have a left position and  i have a top position of that shape right here i   need to know as we select on something is there a  change to this left position or is there a change   to this top position so as we move something i  need to know that timer is going to run and if   it's been moved if the left position or if the top  position has been changed then we need to know the   user has made a move so that's all we have to do  and then also i want to know has an invoice been   an invoice been created for this if so place that  invoice right here so if you see not all of them   for example this one this particular appointment  appointment id10 no invoice has been created so   therefore the invoice has been row if an invoices  has been created when i select on this icon i want   to go directly to that invoice right which is  right here invoice number one if it has not been   created i want to create a brand new invoice so  either we're going to go to an existing invoice   or we're going to go to a new invoice and it's all  going to be based on this right here that's how we   know and all we're going to do in this formula is  simply we're going to imagine invoice appointment   id now that invoice appointment id if we go to  this invoice list we have invoice id and then we   have the appointment id that's associated i'll  make sure this is updated we don't want to have   different make sure that's set that correct i  think it's correct there i want to make sure that   we know the appointment id that's been associated  with each one of these okay so we'll be going over   that code i'll double check that they shouldn't  all be ones all right so as we move through that   what we want to do is we want to make sure that we  know the difference between something that's been   invoiced and not okay so that's that's the reason  we have this that is it that's all for the admin   then here we have a sample shape when we create  these shapes we need to start out with a sample   and that's this sample shape right here and it is  called there's a name for that that's called right   here sample app back sample app back i also have  a sample shape for the picture if we're going to   be displaying staff pictures that's going to be  called staff sample staff picture so we're going   to duplicate that and then inside this we're going  to fill it with a picture just as if we right   click let's move that over here so you can see it  just as if we right click that then we format the   shape and then what we do is we want to give it  a solid fill but in this case it's going to be a   picture fill right a picture so we click picture  not solid but picture text and then we click file   and we would browse for the file so that's how  we're doing except we're going to do that with vba   vba we're going to use that full file path that we  discussed before we're going to fill that circle   with that so for now we'll just have no fill which  is fine on the sample and so that's going to allow   us to put the staff picture directly in there  so we have those as a sample these get created   automatically okay so let's get into vba and  let's start focus on these appointments how do we   save these appointments how do we load them how do  we clear it out for a brand new appointment and we   do that with just these three buttons here and of  course we have delete button delete appointment   as well so let's go over how we do that now  all right inside the vba here and i've got uh   alt f11 the shortcut will give you there i've got  a module called appointment macros applying macros   we'll start up at the top of this we've got  some variables that we're going to define an   appointment row appointment column the last row  and the last results role we're going to use these   throughout this module first one is going to be  the save and update right the save and update   that is the macro that's going to run whether  we're saving an existing one or whether we're   whether we're updating existing one or whether  we're saving a new appointment this is the same   macro that we're going to go through both now to  differentiate between those two we know that if   there's no appointment id if we click add new and  there is no id and there is no row associated with   it we know it's going to be a new one so we can  use that to differentiate but first before we do   that we need to make sure that when they're saving  it that we have at least all the requireds filled   out if this b6 is not six or is less than six we  let the user know we can do that with some code   here if b6 is less than six then please make sure  to fill in all the required fields before saving   an appointment and so if i try to save this we're  going to get that message saying please make sure   to fill all the required fields okay so we want  to make sure that we have a client we want to   make sure that we have an appointment day we'll do  the same date that we're working on we always want   to have an appointment time we see the chair one  is available and we can put an appointment time   here all the way up here probably 8 30 and then a  duration we can set our duration here we can set   the staff we want to make sure that is the staff  that is not currently busy so let's say hank is   not busy and we have chair one that we're going to  set up and we'll set up a primary service so when   i say that i want to re save that information  it is brand new we know it because there's no   row associated so inside the appointments we  want to create a brand new one appointment id   all that information gets saved here now the best  way to do that is to use data mapping and so if we   take a look inside this upper row this row number  one we see that we have specific fields cells that   are matched as d2 is our client so we look back  on here on our scheduling screen we see that d2 is   associated with the client the appointment date  is f2 so back in our appointments f2 so we've   matched all the data that way all we need to do  is run a loop from 2 to 11 appointment id will   already be there and we're going to bring in all  the information from the scheduling and put it on   the first available row for new appointments  and this is what we're going to do here and   it's going to be all based on that but the first  thing we need to do is get that next available   appointment id that next available appointment  is going to come directly from b4 using the max   we're going to take this next experiment  and we're going to place it directly in b2   and we're going to place it directly inside a18  and we're going to do that right here so if b3   is empty then it's a new appointment right we're  going to take the appointment row it's going to be   that first available row inside the database using  end excel plus one that is our first available row   again you can take what is in b4 and place it  directly in b2 that is going to take that next   appointment id which is currently in b4 and place  it in b2 we're going to take that same one and   place it directly inside the appointments  and that's what the next line of code is   appointment database column a and the appointment  row is going to take on that next appointment id   but what if it's an existing well if in existing  it's very simple all we need to do in this case is   determine the appointment row that's based on b3  once we have all that we can run our loop for data   mapping this is our data mapping our appointment  id in column one has already been taken care of   here or an existing is already there so all we  need to run a loop from 2 to 11. and all we're   going to do is the appointments database this is  this appointment database is the code name right   here so if we take a look inside our appointment  database right here it's the appointment database   that is the code name of sheet code name we're  using cells we're using cells because both the row   and the column are dynamic variables value equals  now we need to get that range where is that range   located that d2 or f2 it's located here in row one  and the column that column is moving from two to   eleven so we're gonna take that range whatever's  in dot range remember we're on the schedule   dot range whatever's in d2 we're going to place  it directly in here whatever's in f2 as we loop   through all the columns this is going to map and  bring all that data in there then what i want to   do is i want to set the appointment move b9 to  true we'll go over why that's important a little   bit later on but that prevents loops right when  i save an appointment if there's a loop going on   the loop happens as soon as we select it there's  a loop that's kind of waiting for us to move it's   waiting and waiting if we move it great it's going  to end that loop there but as soon as it doesn't   right so we want to make sure if we take a look  inside b9 we see that that's going to go from true   to false and then back to true all right so let's  continue on with our code so now that we have so   we have check for conflicts now what i want to do  is i want to check for conflicts right so let's   go back in here and we see if there's a conflict  i want to know if b7 is more than one then there   is a conflict right if i take chair two here and  i and i change it to chair three there's going   to be a conflict if i save it we're going to all  of a sudden we're going to have a conflict right   now b7 is more than one now there's a conflict now  we've got two appointments on top of each other   so that's really if b7 value is greater than one  there's a booking type conflict let the user know   this is this and then booking type right this is  the dynamic field is already occupied for this   time please select another time or booking type  right so instead of saying booking type when we   try to save it we're going to see here this chair  notice now it says chair because it's that dynamic   variable using is already occupied for this time  please select another time or chair so as soon as   we save one to a different time right let's click  on here this one here as soon as we save that to   let's say back to chair one we are going to be  able to save it there we go so now it's going   to save back to there it's going to the conflict  is gone okay we're going to do exactly the same   thing for staff conflict if we have two staff as  i mentioned before we're going to check that here   b8 is greater than one then we have a staff  conflict we're going to let them know that d3   the staff name is already occupied for this time  please select another timer stuff and then what i   want to do is once i've saved it we want to hide  the rows perhaps right once we save it we don't   we no longer need to show these rows  we're done saving this appointment so   when i save it i want those rows from row 2  2 4 rows two to four to be hidden and i also   want to make sure that a group of shapes this  group of shapes right here the save appointment   delete i want this group to be hidden too and  the name of this shape here is called existing   appointment group existing appointment group so it  is that that i also want to hide so we're going to   hide those rows from 2 to 4 entire row hidden  equals true and that existing appointment group   hidden false i want to hide that group of  shapes and i'm going to select just another cell   okay so that's it that's all we have to do when  it comes to a new appointment all i need to do   is clear out a bunch of associated fields  right including b2 especially b2 we want to   make sure that when we select new b2 that selected  appointment id is automatically cleared so when we   click add appointment all those associated fields  are cleared along with b2 that's going to make   sure that there's no longer a row associated with  it because it's been cleared out and i also want   to make sure that we're going to select d2 that is  going to make sure that the user can now enter a   client and we'll want to make sure that the entire  row from two to four those rows are no longer   hidden and i want to make sure those button shapes  the existing appointment group is now visible so   that's all we have to do when we click the add  new button so we're going to make sure that   those rows are visible we're going to make sure  that this group of shapes is displayed and then   we're going to make sure that we've selected d2  that's all we have to do for the new appointments   now when we load an appointment how do we load an  appointment well there's a macro that's associated   with this when i select here or i select edit here  i want to load an appointment right and that's the   best way to do it selecting here it's going to  load that appointment loading all those details   that is a macro that has been assigned to this  group here if we take a look at the button here   and we right-click when we click assign macro we  see that it's called appointment load that is the   macro that we're going to go over now when we do  that macro we need to make sure that we also clear   any check for any loops we want to make sure that  there's no loops that's going to come in so we're   going to clear out that's just what it's going to  do is going to basically stop the timer there's a   timer that's running right now it's going to stop  that timer and this is going to go true as soon as   it goes to true it stops the timer we'll go into  that macro but keep that in mind it simply stops   waiting for users it's waiting for you to see if  they're going to move it if they're going to drag   and drop it that timer gets stopped when we're  ready to edit those appointments that's what we   bring b9 to true okay i want to make sure that the  rows are visible hidden equals false and i want to   make sure that existing group is changed when i'm  loading it i want to make sure that's displayed   if b3 is empty we want to make sure that we want  to select b3 is required b3 is the row that's   associated with this so we can't do anything  unless we have a row so that just ensures that   we have the row okay so assuming that we  do have it we want to let the user know to   if it's empty let the user know to please select  an appointment to view all right so assuming that   it's not what we want to do is appointment load  display appointment row is equal to whatever's in   b3 i need that row so i know what row to load  from our database that row is located in b3   once i have that row i'm going to load from 2 to  11 but not every single column keep that in mind   if when i load that data remember i'm loading an  end time that end time is going to go directly   inside this column here however i do not want  this end time to come back into the schedule   why is that because that's a formula you saw that  that was gone for a moment earlier on and that was   simply because i forgot to do something and i'll  show you what it is because you'll probably forget   too i forgot to make sure that column 6 does not  get into why column 6. if we take a look at all   these columns 1 this is column six so when we're  loading if i'm bringing in the the client name the   date the start time the duration i don't want to  bring in the end time why because the end times   automatic it's automatically created based on the  start time based on the duration so it's a formula   i don't want to erase that formula this formula  here i don't want to erase i want to keep it there   because this formula is based on so as soon  as we have an appointment time and a duration   this is going to show the end time so that is  column six so inside our code we want to load   all of the information from the database here  from whatever row whatever the cell is remember   the cell d2 f2 is going to be located in row one  so we're going to take that and we're going to say   this range is equal to whatever the appointed row  of appointment column is i want to bring it all in   except for when we get to column 6. if the  appointment column does not equal 6 then   load it in if it's 6 don't do anything because  that's a formula we don't want to erase this so   it's going to load all the data and then all i  want to do is simply select a cell that's it for   load okay well what about appointment delete if  i want to delete it i didn't assign this mac to   the button but we'll do that now so first of all  we want to give the user are you sure you want to   delete this appointment yes no delete appointment  if it's no then exit the sub and that basically   gives the user an option to exit out focusing on  the schedule if b3 is empty remember we need a row   associated with that appointment if it's empty  then we're going to let the user know to select   on a point of view we're exiting out we cannot  delete any row if we don't have it so assuming   that we do we're going to set that appointment  row based on b3 and then what we're going to do   is simply delete that row from the appointment  database using appointment database range   appointment row and the colon and appointment row  the entire row delete then we're going to run the   macro that's going to new that's going to clear  everything out so when we go ahead and assign this   here right click here and i actually have to run  to refresh the schedule too so we want to click on   both of those icons right clicking on here assign  that macro and of course we can do appointment   delete or paste in clicking ok now when i select  an appointment here and also what i want to do is   i want to add one more thing i want to refresh  that schedule right not only appointment new   but i want to remove it if it's visible on  the schedule we want to refresh the schedule   so that's the macro we're going to be going over  next but it is going to be based on the schedule   macros and it's called schedule refresh so i want  to refresh that schedule be going over this macro   and i'm going to copy this and then what i'm  going to do inside the appointment and then   appointment after the new i'm going to paste that  in here which is going to refresh the schedule   so now if i take an appointment here and i want  to delete it first we're going to get the option   are you sure you want to delete this appointment  if yes it's going to refresh the schedule and now   i'm going to make sure please make sure to fill  in the required okay we'll update that so that's   it so now you see it's gone and we're good to go  on that so that's all we have to do there okay   on the schedule refresh so appointment new and  then schedule refresh great so we've covered all   of the appointment macros now let's go over we're  going to skip the invoice we'll be going over that   and i want to focus on let's do service  macros there's just a few that's pretty much   pretty simple there so we'll go over that now so  if you'll notice if you remember correctly on the   service we have just a small macro when we create  a style i should probably have this only when we   have a value here in other words we really don't  need this appearing here so i'm going to update   that that's going to be on selection change so  let's take a look inside our service items sheet   under the selection change so inside service items  selection change if the user makes a selection   change i'm going to add something to this right  notice how this appeared even when we have no data   which really probably isn't very good right what i  really want to do is i want to check to make sure   that a does not equal empty you know or b does not  equal empty only then do we want to display that   so let's do that right now so if e3 if user makes  a selection from e3 the first thing we'll start at   the top if we're going to select a large amount  of cells we want to exit out of the sub so we're   really focused on selecting one cell at a time  also if for any reason we make a selection on any   cell i want that group this group of shapes all it  is is simply a group of shapes now this looks very   if you follow one of my prior trainings we used  this shape just a few weeks ago and so it's the   same shape all we're doing is giving it a  specific name as a group of rectangles each   with its own format each with its own name this  one's called style 22 this one's called style 23   and so on and so forth now these styles are very  specific to the styles that go so when we click on   format and we have a shape these are the styles  this is style 22. if we take a look at this one   we see that this one is actually style there's  specific styles that are associated with this   okay so it is those styles that we're going to be  using and so they're the name so it's really the   number that we really want associated 22 23 and  i'll show you what that is when we get into the   code but basically i've signed a name i've grouped  all these shapes and i've given a name called   color group if i select something i want that  group to be hidden only if i select associated   column in column e do i want it to show up so  if it's currently visible then hide it using   shapes color group visible equals false so if  the user makes a selection on e and i'm going   to add something here and range okay we also want  to make sure let's say a and the target dot row   dot value does not equal empty then do we want to  display that color group so now when i select here   nothing's going to happen only when a contains  a value is something going to happen so what do   we want to do we're going to focus on this color  shape i want to make the top position the active   cell offset i mean one row down and no columns  to the left or right one row down that's where   i want to place it i want to place it directly not  in there but one row down notice it's one row down   and as far as the left position i want to make  sure it's on the active cell left and then the   last thing is i want to make sure it's displayed  that is all we have to do to display that color   grip okay so inside i guess you probably don't  need an entire mod we don't probably don't need   an entire module just for this code but if you  want to build out your codes probably necessary   okay what i've done is i've assigned a macro to  that every shape a single macro to every shape   in this group if you right click the glue  group and you click assign macro you're not   going to be able to find it however if you right  click here and you say you if you assign a macro   let's go ahead and assign it assign that macro  and we see down here called service set color   when you assign that macro and you go back in  and you look it's not there why is that the   case why is it not showing up because it's been  assigned to every shape inside the group so when   i right click a specific shape in the group you  can then see the macro now when we click start   macro we see that service set color so it is  this side macro all i need to do is determine   the active cell value equals the application  collar that's it basically what i want to do   is i want to take the name of that shape the  name of that shape and i want to put it directly   inside here it's all it's very very simple all i  want to do is take the name of that shape so the   name of this shape is called style 25 when i click  that all i want to do is put style 25 directly in   here just like that that's it and the last thing i  want to do is just make sure that that group is no   longer visible so act shape and so false that's it  that's all i have to do probably not warranted an   entire module for just four lines of code but okay  fair enough okay so let's go into the schedule   macros now okay so now all of the things that  we've been working on the schedule and how we do   it now of course once we get into that once we  understand these colors how they will actually   apply to the individual shapes for our schedule  based on those service types so that color   wash and dry if we look at wash and dry the one i  just clicked here it is based on that blue color   so when we go into service items and we see wash  and dry here that is style 23 that is this one   right here you see this blue here so that's how  we get that color if i were to change it to red   wash and dry and we refresh the schedule here  let's go back to previous day right now wash   and dry is that red color see now it's that red  color so it's very very easy to change that color   simply in the service items let's change it back  to that nice blue it looks better so it's very   very simple very very quick and very easy and fast  all right so how are we going to do that so let's   go ahead and go start with all these variables  and basically what we'll do is i'll define as i go   through the code as opposed to reading you all  these variables just a whole bunch of variables   we'll go through and i'll show you those variables  so first thing what we have is appointment shape   when i refresh that schedule when i run that  macro fresh schedule i need to make sure that   all of these shapes are deleted deleted i've got  my sample shapes to create new ones first thing   i want to do is delete them so the best way to  do that if you follow my videos is give each one   a specific name notice each one this is called  appointment one that group of shapes right this   is called appointment four so basically it's the  word a point plus the id of that that's all it is   so all i need to do is check for every single  shape inside the sheet that starts with that   contains the word a point and delete it and that's  what we're going to do here so appointment shape   that's already been defined add as a shape  right here appointment group we have that an   appointment shape here okay we got two shapes  here we can update that let's bring that down   i'm going to bring it down here where i like the  shapes all to be and then i think that'll be a   little bit easier okay so we've got two shapes  appointment group and appointment shape so for   each appointment shape in schedule we're calling  out the sheet and the shapes for every single if   using the in string based on the name of that  shape if it contains the word appointment if   that's greater than zero that means it's been  found in the name of the shape then what we want   to do is delete it simply delete it and then  we're just going to loop through every sheet   make sure when you're doing that other shapes  that you want to keep don't contain the word   because they're going to be deleted okay then what  i want to do is i want to select hide the selected   appointment group what is that the selected  appointment group that's based on the schedule   so if we take a look inside here this particular  these two little icons here oops i clicked one   by accident this icon here this two little  icon set is called selected appointment group   it's the edit and it is the invoice icon here  these two icons i have grouped them together   and we'll call it select appointment group i  want that hidden right i want to keep it right   we only have one i want to keep it but i want  to hide it so we're going to hide it so we're   going to make mso visible equals false hide  the selected appointment group also i want to   clear there's a string called appointment details  this is a string variable i want to make sure that   it is cleared so we're going to do that also i  want to set the picture folder now remember this   is where we how we get that full file path for  our pictures that picture folder is located in c19   c19 is going to hold that so what i want to do  is i want to put that now you notice there's no   backslash on to get into that but i'm going to  add that within the code so the picture folder   is going to be based on the setup c19 and the  backslash so c19 along with the backslash that   is going to be our picture folder we're placing  that inside this variable that is our staff   name picture established called staff picture  folder okay so that's where you're going to   put so when you get this make sure in c19 you  put your pictures there okay i also want to set   the start time what is the start time based on  the schedule start time this is a named range   inside the setup screen that is the official start  time start okay start time now we also have the   intervals remember we we focused on this interval  this named range is decimal value and we're going   to put that into a long variable schedule remember  we've got some long variables here duration   schedule interval and schedule start time those  are all long variables as excuse me they're double   variables double they're not long they're double  long as a whole number doubles decimal so we use   double for the decimal okay so now we can focus on  the appointment database and what i want to do is   pretty much determine all of the appointments for  a specific date that date here is located inside   d5 so i want to know basically i want to run a  filter all the appointments on this sheet filtered   by a very specific date and what i'm going to do  is i'm going to set a criteria inside an advanced   filter it's going to be based on this d5 now keep  in mind that this is a formula this is a number   that is fine in fact it's better i would have  preferred not to show the date format i'd like   it just in the numbers because excel will read it  always as a date regardless of your date format so   we want to use a formula we like to keep it  in this format that way there's no date format   issues if we place the date using vba in this  cell we can create issues based on date format so   that this is a sure way to make sure that your  filters work properly then what i want to do is   i want to determine the last row filter all of  those appointments based on that one single date   in this case december 1st and then run a loop  adding each appointment individually so that's   what we're going to do the first thing we want  to do is determine clear out any prior results   so i'm going to clear out all the way from  aaa to all the way am now keep in mind i need   additional things on this right i've got all  of my information but what i want is the item   color but our item color that color is located  here in the service items right it's not located   we don't have inside this database we don't have  the color right but i need to extract that color   but i if i know my primary service if it exists  then i can extract the color so if i have it all i   need to do is run a formula and then when my data  is brought in here then i just need to bring that   formula down that's going to bring in the color  to get that we can use a formula what we're going   to do is we're going to index the service color  this is the named range based on all the colors   in our service we're going to match it based on  the service name that service name is going to   be located right here and then what i want is a  single column and if there's any error show blank   so that way once i get my results i'm going to  determine the last row of results then all i   have to do is take this formula and bring it down  here i also want to know the staff picture right   if i'm going to be loading that staff picture if  i'm going to be putting that staff picture right   here in the circle i need to extract that staff  circle again that staff that staff picture is not   located in our appointments database our that  staff picture is located in our staff database   and it's located right here so i'm going to do the  same thing i created a named range for the staff   picture and i'm going to create a formula and that  formula i'm going to place directly in am and the   reason i don't put form is i don't necessarily  want these formulas where there's no data exists   right i want to basically do calculation on  demand only where i need it right so we can have   vba bring down those formulas that keeps your  light your workbook light and fast by using   formulas only so all we need to do is just clear  out these formulas so again i'm going to use an   index match on this we're indexing the staff  picture in this case matching the based on the   staff name that's located in ag3 and then we're  going to run that based on that and determining   if there is a match if it is a match then we're  going to place that name of that picture and i   want to bring it down here and the reason that  that's important is because when i create these   shapes for these appointments i want to give  it a specific color and i want to give it a   picture it's nice to have it's not absolutely  necessary but it's nice to have all this data into   one table so we can work with just this one table  so that's what we have done right here so moving   along now we're going to get into code so the  first thing what i want to do is i want to clear   out all the data from a all the way to am clearing  all that data out 8 am 99 clearing any prior   results determining the last row of our original  data located here based on a99 so that's going to   be based on the appointment database that's what  we're focused on here we want the last row of data   if it's less than four we're going to exit out of  the sub then we're going to run an advanced filter   advanced filter is going to be based on your  header row starting in a3 all the way through k   we're going to run that criteria that criteria is  going to be based on that date p2 through p3 then   the results are going to come all the way from a  a through ak our results are only here right our   formulas come after we get our results so our  result says so that's just we have inside our   advanced filter which is here a3 through k in the  last row copying the criteria range is p2 to p3   and our results are going to come from aa2 all  the way into ak then what i want to do is i   want to determine the last results row is it in  this case 13 if that last results was less than   three we can exit that means we have no data  so we can check that here the last results row   based on column a a if it's less than three  then we're going to exit the sub nothing we   can do more right we have no data no scheduling  data we don't need to move forward assuming that   we do have scheduling data then it's time to bring  down those formulas now we know the last row so i   know in just one line of code i can bring in all  of these formulas that we can do that with here   al3 through am and the last results row formula  remember it's not value formula is equal to   whatever formula is in al1 through am1 formula  this brings down the service type and color   formula and the staff picture let's put and staff  picture into that here so now we have all that so   now that we have all of our data we're ready  to run our loop now what i want to do is simply   duplicate this appointment shape here now  notice there's been a macro that's already   been assigned to this so we don't need to use  vba if we right click and click assign macro   we see that the appointment select that macro  has already been added and if we click here we   see the picture of the same macro also right  because we don't know where the user is going   to click so it's the same macro sorry slightly off  the screen here the same macro that's been already   assigned to our sample background shape and our  picture circle here so what we do is ready to run   our results so for the results row because three  to last results row i want to run a loop based on   all of our results starting at three all the way  to the last row and getting all the information   so the first thing what i want to do is determine  the appointment id in this case a string variable   it's going to be based on a a and the results row  we also want to put in the start time based on   ad duration based on ae and then i want to know  how many rows that's really important if i need to   know for example this is a one-hour appointment i  need to know that this appointment is going to be   based on four rows how do i know that it's forced  i know it's one hour right if i know it's one hour   and i know that that one hour if i divide that one  hour by our intervals i'm going to get four right   this is based on 15 minutes so if i know it's  one hour if i know our duration is one hour and   we divide it by our interval we are going to get  four so that's all i have to do inside the code   so our schedule row is our duration which been  defined here divided by our scheduled interval our   schedule interval's already been defined up here  based on our interval right here so both of us so   now we have the number of rows our scheduled rows  number this is all here this is a long variable   duration and schedule are both double variables so  that's how we determine the number of rows that's   how it's dynamic that means if you change the  duration the schedule integral is always going to   be one hour so let's say we decide oh we want our  schedule interval is going to be 30 minutes right   now our duration is still one hour right so that  way if you decide you want to oh you know what i   want to change this to 30 minutes it doesn't mess  things up how does that happen because now our   duration is still one hour our schedule interval  is 30 so now our schedule rose is going to be two   right duration one hour divided by 30 minutes our  results gonna be two so now we know based on this   30 minutes right all of a sudden when we refresh  this schedule this is going to be two rows right   so we go previous and next we see now that is back  one more day so we need to see now it's based on   two rows right so that's how we can do it so  it's very very dynamic we'll bring it back to 15   here but that's how we can do it so that's how  things stay really really fluid and flexible   simply by using just a little bit of math so  we've got our duration rows i need to know   how many rows now i want to know the background  type that background type is going to be located   inside our appointments located right inside  here al so our booking type based on account   that service is going to come a little bit later  on so our booking type not background type booking   type that's the chair or the bed or whatever  here that is going to be based on ah right   here so this is our booking type not background  type we'll get the background type in just a bit   ah booking type chair room bed etc i just  don't read my own notes i should probably   remember that okay our service column what is  our service column now we're going to get into   our color right i want to know service color  not column color i think i need more coffee   service color is going to be located in a  l on the result row right i want to know   what color that right style 27 23 so that  we're going to put inside the variable here   now also one of the picture name is going to  come from am so that's our employee picture name   now we are ready we've defined all of the  variables to get all the information that we   need but now what i want to do is i want to build  a string if we take a look in our schedule we have   this string here we've got all this information  client name start date and date how do we know   what to put in there well that's going to be based  on users right if we decide and we only want a few   things i only want the client and the start date  then we want to select there so now if i decide   i want to refresh the schedule here now we've only  got two details in here so it's dynamic based on   that so what i want to do is i want to run a  loop based on all the check marks if there's   a check mark then i want to have that information  there if not then don't so that's all we need to   do so let's go previous day and next day i should  probably add a refresh icon that might be helpful   so so now we have all that information so  what i want to do is i want to run a loop   i'm going to run a loop from row 8 all the way to  row 17 looking to see if this checkbox now how do   i know if it's this checkbox well this checkbox  is a specific character so if we go into insert   and we click symbol what we're going to look for  is this checkbox right here this is the checkbox   this is called wingdings character 252. so i'm  going to be looking for this character 252 if it's   found so how do i know that well let's go into the  code so first of all we're going to have a setup   our setup row is going to be from 8 to 17. and  so we're going to run that loop if the setup   focus on column c and the setup row value  equals character 252 selected okay if it's   been selected i want to display and i want to  build up a string that string is going to be   used inside that's going to be inside that text  range that's that shape is going to fill with that   text range with all this text we got to build that  up so basically what do i want to do i want to put   the basically the label and i want to put the  value i'm going to separate it by a colon so   where is that label going to come that label is  going to come from here the value is going to   come from the variable the value is going to come  directly from that so how do we know that well the   where is it so if we take a look at this  client everything here everything here   client date start number in order is exactly the  same order as our results client date start time   everything we have here so how do we get that well  the first thing what we want to do is determine   how do we know what column we know what row right  we're looping through the result rows from result   3 so we know the row but what i need to get the  column so how do i what column is this located on   this is located on column 28. so now let's take  a look inside our setup right this is located   on row 8 so if i'm looping from 8 to 17 i need to  do i need to get from row 8 i need to get directly   to column 28 so i can do that simply by adding 20  20 will give us the column that this located on   so we can do that so if if it's been selected  the first thing what i want to do is i want to   add that label that label is located inside b  column b and whatever our row is or our setup   row here so b and the setup row that's going  to get our label then i want to add in a colon   then i want to add in a space now what i want  to do is i want to find the variable so that   variable is located here we're focused again on  the appointment database we're focused on that so   we're going to use cells and then what i want to  do is i want to know we know the result row we're   looping through all the result rows so we know the  result again we don't know that column that column   is going to be based on the setup row starting at  8 plus 20. so that's going to get us our column   right because everything is exactly shape right so  that's going to give us the value so whatever that   value is but now what i want to do is i want to  give it a specific format right if we take a look   inside here on our appointments right look some  have this format some have this format you know   there's different formats a date right how do i  differentiate between these formats right because   i wanted to have a specific format a date should  have this format a time should have this format   well what i can do is i can use the format of this  cell the format of this cell and i can match it so   all i need to do is not only take the value from  this cell but the format if we look in the home   we see that this is the time format if we look in  the home we see that this is a date format so not   only do i want to extract the value but i want to  extract the format as well so we can do that here   so notice here it says format format here and  we're formatting what format are we using we're   using the format of this let's scroll over keep  scrolling back the format of the same exact cell   that's how we do it so we're going to take  the number format of the same cell and now   we've been able to highlight it successfully the  format of whatever cell we're going to use that   as the format and then what i want to do is i  want to add a brand new line right i want to   add a carriage return or basically the next line  of data so we're going to use carriage return vb   crlf and that's going to be best next line of code  so what we're going to do is we're going to keep   building up this string that's why we're adding  whatever is existing here so we're going to   loop through that basically adding all those  labels all those data and the specific format in   to this string and we can do that with just all  of that with just three lines of code in a really   simple loop so this is going to build up all of  the appointment details that we want displayed   based on whatever is selected here inside the  display and that's a great way to really condense   that into just a few lines of code because we've  organized it in a pretty good way because we've   made sure that our call our rows here are exactly  like our columns here exactly the same way we've   made we've ensured that they're exactly the same  okay great so now that we have that we can move   on with the code now we've got our string set up  so now what we want to do is i want to determine   if we're going to be placing it directly on the  schedule what column and what row are we placing   well a column is going to be based on whatever  chair our row is going to be based on whatever   time so we need to extract both of those so  the schedule row is going to be based based   on the start time i need to know the start  time we've got the appointment start time   minus the scheduling start time divided by the  interval plus seven what is that crazy formula   if i know this starts at 8 15 right how do i  get column eight right so i know if i know our   start time is eight right i know our start time  is eight o'clock always based on that but if   if our appointment time is 8 15 if i subtract  8 15 minus 8 it's going to give me 15 minutes   if i then divide that by our interval i'm going  to get 1 divided by 1 is 1. so if i add on 7   right we're starting it's the reason we want to  add if i add 7 plus 1 it's going to give me 8.   that's just what i've done right here so inside  our appointment start time the start time this   was 8 15 minus 8 o'clock this is going to give  us 15 minutes if i divide this 15 minutes by the   schedule interval which is also 15 minutes that  is going to give us 1. if i add 1 to 7 it's going   to give us 8 and 8 is our scheduled row great so  that's how we get our schedule roll but how do we   determine what column well what we know is we know  our booking type we've already defined our booking   type here not background type randy our booking  type here so we have that if i know right if i've   got our book in a named range i've created a named  range for all of our booking types here formulas   name manager into booking types here under booking  types here we've got them all here so what i want   to do is i want to search for them if it's found  i want to know where it's found if it's found then   i know where to place it right if it's found on  four let's say it's the first one it's found on   row four where is it going to be placed on the  schedule well it's gonna be based in column d   also in column four right so that means our column  column four chair one is also exactly the same   as our row so it's simple again using good good  programming we can see that row 4 is the same   thing as column for so that's all we need to do is  use the find so our our servers column here excuse   me let's go back down here schedule column here we  go schedule column based on the setup based on the   booking types this name just we're going to use  find i want to find the booking type excel values   xl and i want to re-extract the row because that  row is exactly the same as the column row 4 is the   same as column d now i know the row now i know the  column now i know exactly where to place it just   in case if for some reason it's not found then  if schedule column equals 0 then go to the next   appointment we're going to skip everything else  right because we don't have a column to place it   in assuming that we do have a correct column we  can then move on now we're ready to create the   shape the background for our appointment so we can  do that with our current sample background we have   a sample right here this one's called sample at  back what i want to do is i want to duplicate that   and then i want to give it a unique name so we  can do that with schedule shapes sample app back   duplicate it give it a name that unique name  is going to be the appointment back and the   appointment id so that appointment id is unique  remember we have that name of that background   unique to that appointment id so appointment  back appointment id okay once we get a name i   can then work with it first thing i want to do is  with schedule shapes appointment back permitting   so we're going to work with that specific shape if  the service color serve color does not equal empty   then we want to set the background remember i've  put that in a variable it's coming directly from   here style 27 25 but what i don't need is i don't  need this the word style i only need 27 27 is   all i need to set it up so how do we do that  well so what i want to do is i want to take that service color and i want to remove the word  style from it i only want the number i want to   extract that number so to do that we're going  to use the replace the replace service style   removing the style replacing with nothing and  we're going to do the dot shape style so now when   we do dot shape let's do equals right let's take  a look you see we've got all these styles here   right so this is what i wanted to show you here  so we've got different style different presets so   we're going to use basically 23 24 25 so they're  already in here preset 23 24. now we don't need   that entire text all we need is the number that's  sufficient enough so 23 24 so all i want to do   is put that number in the shape style and it's  going to automatically give it that shape style   so that's what i wanted to show you there so all i  need to do is put in the number there so if i were   to put in 23 here it would be 23 every time so  that's all we need to do is make sure we extract   the name we don't need a name just the numbers all  we're looking for so we're going to put in extract   number for the shape style all right so that  colors the background that gives us that unique   now what i want to do is i want to add the text  remember everything's got some unique text how   do i add the text to that shape well see it says  appointment back one so we've got the back plus   we have the id now we want to give it a text so  we can use the text frame for that so text frame   to text range dot text equals the appointment  details that is that string variable that we   built up based on our loop up here so that's going  to set all the appointment text now i want to do   is i want to set the left position where's the  left it's going to be based on the column what   column we set the row doesn't matter when we're  setting the left position so we can use row one   that's fine the schedule cells one is fine the  schedule column the left position of that schedule   i also want to set the top position again in this  case it is the column that doesn't matter we can   use column one that is the row that matters so  we're going to base it on that row and i want to   place it just slightly off the top edge of that  so i'm going to add one to that now what i want   to do is i want to add the height now the height  of this shape is based directly on the number of   rows we have the scheduled rows right so if the if  we have the scheduled rows is based on that if i   know it's going to be four rows what is the height  of that shape the height of that shape is going to   be basically be the height of a single row times  four and that's just what we've done here so all   i want to do is i want to know the height of  that row what is the height of that row and i   want to multiply it times four and then i want to  just make it a little bit smaller because i don't   want it directly up that so that's all we're going  to do is so simply the multiply the number of rows   times the height of one row that will get a  shape and then subtract a little bit now i also   want the width of that right i want those width  dynamic if i bring those width in and i want to   update the schedule i want to make sure that the  width is automated too so the width of that the   width of that shape is going to be based on the  schedules the column width so whatever the column   width is is going to be this width of the shape  okay that's it now place our background our shape   now it's time for the employee picture okay so  what i want to do now is i want to duplicate   now regardless what i want to do is i want to  put this here regardless if a picture is found   or not so i'm going to place that there so all  we need to do is take our sample staff picture   and we need to duplicate that and give it a unique  name also so sample stat picture duplicate this   one's going to be called staff pick an appointment  id now what we want to do is check if the picture   name does not equal empty that means there is a  value the picture name has directly been extracted   from am here so we know that if it is not empty  then we can move forward now what i want is i want   to set the path of that picture this is another  string variable the path of that picture is based   on the picture folder that we've already added  in right that picture folder was already added   all the way up here right based on the setup c19  so we have the picture path in the picture folder   combining the picture name of the picture folder  this is the full file picture path full file   picture path but i do want to run one more check  i want to make sure that that is accurate right so   to do that if the directory of the picture path vb  does not equal empty then it is accurate then what   i want to do is i want to fill i'm going to take  that circle that circle that we just created with   this name here put it in here and i want to give  it a fill i'm going to fill it based on the user   picture that user picture file path is located  here that's going to fill that little shape with   our staff picture and that's all we have to do to  add the picture okay now what i want to do is i   want to place i want to position all we've done is  duplicated it and possibly added the picture but   we haven't placed it anywhere yet i want to place  it basically on the lower right of whatever the   shape is so based on wherever the shape is i want  to place it on the lower right of our background   shape to do that we can say with the schedule  based on the staff picture we're going to put   the left position it's going to be based on the  left position of our background the left position   plus the width of our background minus 17. so  basically i want to know the left position of this   plus the width of this if it's plus the width is  going to end up on the outside but if i subtract   the width of that it's going to move it back  over to the left so in this case we're using   17. that's going to be in the back i'm going to do  the same thing for the top the top is going to be   basically the top of this shape plus the height  of this shape minus a little bit about 17 again   so we're going to do that with the top so the top  is going to be the top position of this background   plus we're going to add the height of the  background minus 17. that's going to place that   picture or that circle with the picture directly  in the lower right hand corner of the shape   all right now what i want to do is i want to  take both of this notice that this is a group   i want to take this background shape i want  to take this picture and i want to group them   together and i want to give it a specific name  i want to call it a point and then i want to   put the id in so we do that with just a few lines  of code here with the schedule shapes range where   you're going to use an array that array is going  to be based on the two shapes that shape is the   background shape name and the staff picture shape  name and i'm going to group those and then what i   want to do is i want to give it a specific name  the name is going to be the appointment id point   the text appointment and appointment id this text  is very important because when we remove all the   shapes we're looking exactly for that way up here  we're looking for a point so we want to make sure   that we name that group something that we can  remove it all right so we've given it that name   and that's it that we loop through every single  picture to do that we want to clear out the string   and then we want to go into the next result row  that's all we have to do to create that refresh   schedule so automatically it is that that we have  to do to refresh that schedule is that one that   when we save an appointment when we go to previous  and next day i'll show you those macros that's how   we refresh the schedule okay so again previous day  it's very very simple remember our this particular   one d5 is based on the data we selected this  criteria inside appointments is also based on d5   so if i reduce day five by one and then i  refresh the schedule that's all we need to do   so that's just what i've done inside the previous  day macro d5 is equal to d5 minus one refresh the   schedule the next day same thing except for  d5 equals d5 plus 1 and then refreshing the   schedule today very simple we're going to take d5  we're going to place the current date current date   here and then refreshing the schedule very very  simple okay that's it that's also we have to do   so that way we do previous day today and previous  day there's no appointments for today or next day   very easy on that all right great but what about  the macro that we run when we select this when i   select it i want a few things i want to edit  that or i want to create an invoice i want   some things to happen when i select it that  is the macro called appointment select and   that macro is right here so we're going to  focus primarily on the schedule with this i   want to get an appointment id now this is very  important that appointment id i need to extract   now let's take a look remember in the in the  macro we just went over we created a name called   whether it was for the shape or the background  shape or the picture they have a specific name   this one's called appointment back so notice that  there's eight characters here and then the number   look at this this is also called this is called  staff pick this is also eight characters and then   the number so if i want to extract when i select  this i want to extract the name i know i want   to get the name of the shape but i what i really  want is the id i want to take that id and i want   to place it directly inside b2 so if it's one i  want to place it so notice that this shape here is   let's right click let's click something else here  this shape here is called appointment back right   one appointment back one there we go appointment  back one that's one so what i want to do is i   want to remove the first a characters whatever's  left is going to be that appointment no matter how   many characters that idea is it could be id 123  that's not going to be a problem so we can do   that extracting that appointment id we're going  to do that what we're going to do is i'm going to   get the take the left of the application collar  this is the name of the shape that called the   macro again if we went over the report if we try  to run this macro from there it is going to give   us the debug why is that because the name there  is no name of the shape that called this macro i   called it from right here this button there's  no name so that's going to of course provide   a bug so what i want to do is i want to take the  first eight characters and i want to replace them   with nothing the first a characters of this we're  going to use the replace what am i replacing with   nothing what's that going to leave me with that's  going to leave me with exactly the appointment id   i'm going to take that id and i'm going to place  it directly inside of b2 then i'm going to run the   macro to load the appointment then what i want to  do is i want to set the appointment group to equal   the appointment id in a group this is that group  right remember the group is the combination of   those two shapes i want to select it why do i want  to select it because the user might want to move   it so if they want to move it i need to select  it so i need to know what that group is so that   we can select it and move back and forth so to do  that oops i moved it too far to the left i'll be   showing you that in a minute that's important we  want to make sure we move it within the schedule   so to do that we need to make sure we identify the  group name and that group name we're going to set   it as a group this is the shape remember we've  defined that appointment group as a shape right   here as a shape so we're going to set that as a  group so to do that because i need to get some   information from it so we can do that right here  set the appointment group equal to shapes a point   and the appointment id now that we know the id  we've already extracted it we know the name of   the group because it's always going to be the  word appointed group so we're going to set it   now what i want to do is i want to set the  appointment change to false b9 is going to   go to false this will you'll understand this  very soon we go to the next macro but also   b10 i want to set the left position and when i  click here i need to know the left position of   that shape i need to know the top position of that  shape and notice how these things change so the   b10 is going to take on the left position  b11 is going to take on the top position   when i move one of these it's going to change and  i need to know so basically when i move this over   it's going to say hey you've moved that  over we know that there's a difference now   so we can go ahead and make the update of that  appointment based on the differences in that left   position or the differences in that top position  and so that's just what we have here so what i   want to do is when i select it i first want to set  that so i want to take that left position of that   group and i want to place it in b10 i want to take  the top position of that group and i want to place   it in b11 now what i want to do is i want to focus  on that with the shape to select the selected   grip remember when i select it i also want to  have the ability to edit it or which is going   to open this up and also i want to have the  ability to also create an invoice from it so   this is going to be based on this group so i need  to display this group and i want to position this   group directly on the top and i want to display  it directly on the right of the shape so we do   that with here so with the selected group first  thing is the left position is going to be based   the left position of that group plus the width of  that group that's going to place it directly to   the right of that shape and also i want to match  the top position i want to make sure it's visible   and i want to make sure that that those two icons  are on top of anything else right i don't want   them if i select here i i want to make sure that  these icons are on the top of this shape i don't   want them the under i want them to all the way on  the top above every other shape to do that we can   set the z order so we want the z order to be to  mso bring to front so it's going to bring it all   make sure that that group is not under any other  shapes okay and then what i want to do is i want   to actually select that group you notice that  when i select it each time it you see that it's   now selected so we can do that with this line  of code group select we're going to select that   again b9 to false setting b9 to false making sure  that it is false here and then we don't probably   don't need that twice in here but just in case  it's probably good just in case and then we're   going to run a macro called check for move it  is that macro that we're going to go over next   basically this macro is going to run a loop it's  going to wait and see if we're making a move wait   for a certain number of seconds or minutes and see  if the user is going to move it so what is that   what we're going to cover right now so first of  all if b2 is empty then we're going to enter this   up right we can't if we don't have any kind of an  appointment id selected there's no point in moving   forward we need to make sure that we actually have  a selected appointment id okay assuming that we do   we're going to set that into a variable point id  this is where we're going to run our delay right   calculate this is a whole number we've defined  we're going to run a delay right we need to wait   a certain period a number so to see if the users  change it right so we're going to run that delay   using a for next loop so we're going to do 1  to 100 000. then what we want is we want to be   able to do other things while this is happening  so to do that we're going to add in do events   then what i want to do is i will as soon as b9 i  want to be able to exit this loop out at any point   as soon as a user makes a change notice it's false  right now but as soon as a user makes a change to   the location that's going to go to true i want to  make sure that we exit out of that loop as soon   as b9 goes to true so we do that with this line  of code here so as soon as b9 equals true we're   going to end we're exiting out the loop right  there's nothing left we want to make sure we   exit out of that okay so now what we do is check  to see if the user has made a change by basing it   as it continues to loop and loop through we're  going to continue to look for changes i'm going   to look for a change in that left position i'm  going to look for a change in that top position   so with we're going to focus on this group of  shape that group if the left position right does   not equal b10 right that's where our left position  if there's been a change to the left position   or there's been a change to the top position in  b11 then i know the user has changed or the top   position of that group is different from what's in  b11 that a move has been detected now we want to   make sure that the move is a correct move right  if we take this shape and we move it way too   far over i need to let the user know but please  make sure to move the appointment to a correct   time and open right i also want to make sure they  don't move it too high up if it's moved too high   up off the schedule please make sure to move the  appointment to correct just let them know so the   first thing we need to check for have they move  it to a correct position so we do that with these   lines of code if the left position is less than  the left position of column d right we cannot   go less than column d so if it's less than the  column d then it's been moved too far to the left   how or if the schedule row 6 and the top left  column doesn't equal empty that means that here   row 6 there's nothing there right if there's  nothing here here in m then we've moved it too far   over to the right so we need to make sure that row  six and whatever column they moved over contains a   value and so we make sure that it does contain a  value so we can do that or rho six equals empty or   maybe the top position is less than row seven  if the top position they moved it too high up   right then let the user know please make sure to  move the appointments to a correct time and open   based on the booking type right and open all  right all right so then we're going to refresh   the schedule so that their changes come back  we're refreshing the schedule without doing else   okay but what if they've made a correct move  what if they made a move in a good position   then what i want to do is i want to get all the  information and save those changes that make   first of all i want to know the booking type  right that booking type's going to be based   where have they moved it to for example if  chair six if they've moved it to chair seven   i need to know what they've moved it to it's going  to be based on row 6 and whatever column they've   moved it to so it's going to be equal to row 6 and  whatever the top left column of the column where   have they moved it to what column that's coming  in 6. so we're going to put that into a variable i   also want to know well i want to take then put it  then i want to do it i want to take it and put it   into f3 i want to take that once they've moved it  here i want to put that new chair right into f3 so   if i've moved it over here it moved over chariot  i want to take that i want to put chair 8 directly   in into f3 so that's how we do it right here  so f3 is going to take on that new booking type   also i want to know the start type what is the  what is the new start time have they moved it   up or down where is that start time going to be  located that start time is located in in column c   so if they've decided they don't want to know more  they don't want it eight o'clock maybe they want   an 8 15 and they've moved it down i need to find  out based on the row based on the top row of that   it has they moved it to 8 15. so to get that what  we're going to do is we're going to base it on   column c and the top left cell the row of that  shape what is the top left cell row what bro is   that and that's going to be our start time based  on column c that's going to set our new start time   and also what i want to do is i want to take  that new start time and i want to put it directly   inside h2 h2 is going to take on that new time  once i have that new time right i also want to   set the move to true right move is to true that's  going to exit out of that loop right as soon now   we're done right as soon as b9 becomes true it's  going to exit out so setting this to true is going   to exit out of loop and then all i want to do is  i want to save those changes we've just made those   changes here we've changed the chair we've changed  the possible start time and now what i want to do   is save those so basically inside here just click  save appointment and then refresh the schedule   which will automatically refresh all right that's  it that's all we have to do so we just keep   running that loop until the user makes changes  and if the loop runs out before we go then we're   going to make sure that b9 is true that's going  to exit out of any loop automatically that is how   we automatically save and make changes based on  a drag and drop schedule simply by dragging and   dropping it to a new location okay great i'm glad  i've got to show that to you but what about how do   we create the invoice now we have an invoice here  how do we do that well as i explained before when   i selected we've got an appointment id i need to  know if there's an invoice that's been selected if   we notice appointment 6 there's no invoice that's  been created for that if we go into the invoice   list we see that we have appointment id and i'm  going to double check this there's nothing that's   been created for that so we know that it's going  to be a new one so what do i want to do well in   this case basically what i want to do is i want to  go through the invoice i want to clear everything   out here by clicking on new invoices then what  i want to do is i want to add in the client name   i want to add in the next invoice number i  want to add in service item one and possibly   surface item two where are those going to come  from it's going to come directly from here   h3 or here j3 it's going to have those secondary  items so that's what we want to do in here so with   the schedule b9 first of all i want to make sure  the move appointment setting that move a point   all right mint to true and to exit any loops  i want to exit i still spelled that wrong i'll   probably spell it wrong three more times uh move  point to true that's going to exit any loops right   so basically regard if they selected here and then  they decide to create an invoice automatically by   clicking this is the this is the one we're going  over this is the macro that's been assigned to   this dollar sign right that's the one we're going  over here called create invoice so first thing we   want to do is make sure that b9 is true right if  b3 equals empty please select we have to make sure   that we at least have an appointment id right have  to have we also have to have a row and we have to   appointment id to make sure it's been saved if  it's not we need to let the user know to please   select an appointment or view to view an invoice  or two let's say to create let's call it to create   and invoice that makes better english okay we're  exiting the step out so now what i want to do is   i want to run the macro we'll go over these  macros in a minute but basically this macro   simply clears out the invoice screen and then  also what i want to do is i need to determine   is this an existing invoice if it's an existing  invoice like this one isn't existing right so we   know that deployment id for has been created on  invoice row 6. so if i take a look at the invoice   list i know that on row 6 appointment at e4 has  been created so all i need to do is take that id   in this case and load it put it in here this is  a search invoice so when i do that it's going to   automatically load that so let's change that to  1. let's differ let's change that so now when i   go into schedule and i decide okay i want to load  this invoice i'm going to click here it's going to   do just that it's going to load automatically  for and load in those two so it's loading an   existing invoice because an invoice has already  been created for that appointment if this is   blank we know it's not so we need to differentiate  whether it's been created or not we can use b12 to   differentiate existing invoice lovely spelling  here existing invoice so if it isn't existing   it does not equal empty that means that it is an  existing invoice on the invoice screen j1 value is   going to be based on whatever row here's the row  of the invoice what is an a in the invoice list   let's take a look at that here inside this invoice  list here what's located in a is the invoice id   i know the row is 6. i want to extract this i want  to take this 4 and i want to place it directly   inside j j1 because our change event which i'll  go over in a moment is automatically going to load   that invoice okay so we have that there so that's  it all we need to do that's going to automatically   trigger to load the invoice any change on j1  assuming it is the correct invoice number is   going to load that input but what if it's a new  invoice well if it's a new invoice then i want   to run this invoice new we don't need two of these  we've already done it here so we don't need to do   it again here so all right so first thing i want  to do is i want to determine the appointment row   based on b3 that's the row i want to know the  row that appointment the row that's been saved   already so previously saved located in b3 because  we're going to need that information that's going   to be employment row then what i want to do is  i want it inside the invoice i want to save some   e is going to take on our client name where's our  client name going to come from it's going to come   could come from two locations it could come from  here right or it could come from our appointments   we've already got the appointment row or it could  come directly from column b and the appointment   row so that's what we've done here appointment  database b in the appointment rows our client   name we're going to extract our invoice date we're  going to set up our invoice date based on d5 i'm   going to default the invoice date based on our  scheduling date right here which is located in   default that's going to set that invoice date  now invoice date is going to go directly inside   h2 all right so once we have that i also want to  set the staff that staff's going to locate it in   g7 g7 is going to take on our staff here's g7 and  it's going to come directly from our appointments   located right here inside g the column g and  our staffer all right so once we have that d9   is going to take on our primary service now our  primary service is going to be located inside   i right our primary service and our secondary  service is going to be j if there is nei so our   primary service is going to go directly inside d9  our secondary surface is going to go directly in   d10 if there is any and that's it that's all we  have to do there's one more thing that i needed   to do which i forgot remember there was a missing  so what i also want to do is i want to take that   i've got a special place directly inside our  appointment id right here b5 b5 is going to take   on our appointment so i want to do that i want to  take our appointment id directly from our schedule   our appointment is located in b2 so that's the  last thing that was the last thing we had to do   so invoice dollar range b5 dot value is equal  to right our schedule dot range and it's b3   b2 excuse me b2 is going to take our appointment  id so that's the last thing to do appointment id   i want to save that appointment id and make sure  that that's saved alright so b5 is going to take   on our appointment id and our product is going  to come directly here so that 4 is going to be   brought directly inside for new directly inside  our invoices right here so i'm going to put it   directly here once it gets saved it is then going  to get saved directly inside here okay great so   that's it so then all we need to do is save then  we just activate the invoice screen so let's take   a look at that so let's find one that has not been  created this one right here let's take a look here   this one right here has not been created notice  that there's no invoice row associated with that   so if i select on invoice here everything's going  to be brought over so let's just double check that   because it was kind of quick so i've selected this  one we have our hair color it is our appointment   idea is five our client is dolores so we look back  over here our client is dolores our appointment   id five which is working correctly where i  wanted our haircut standard our hair color two   and that creates our invoice okay great so we know  how we brought everything over our date here has   come over our invoice number four has been brought  over here so that's very good so how do we do that   so actually i probably want to do one more thing  and that that is going to i think it's on save but   i want to do one more thing and our next invoice  number is going to be based on the max of all   the invoice i want to bring that over let's say  h1 is going to be equal to b7 so let's add that   while we're in here so dollar invoice.range h1  dot value it's equal to dot range and we're going   to take it i want to know the next one b7 is our  next invoice id let's set that up next invoice id   good so we've got our next invoice id is going to  come directly from b7 it's going to be placed here   that's going to set that up i like that better  let's do that one more time select on this one   here and then we'll set the invoice make sure that  we have our next one oops invoice okay there we go   let's try that one more time invoice we gotta  set the invoice okay there we go that's gonna   be correct so now we do that and now we've got  our invoice number here that's what i want brought   directly from here we have our correct appointment  id we have our information great so now we   understand how to get that but how do first of all  how do we get this address to automate how did we   are we going to be able to save it and those are  going to be inside our invoice macros so let's   take a look inside first inside the actions on the  invoice screen here the invoice screen we've got   some worksheet changes here so when a user makes  a change on a customer name i want that address to   appear if we decide to change that customer here  if anyway i want to make sure that that address   changes accordingly inside e5 and e6 so basically  i want to look up to see if this customer is found   and then place the address well we have a customer  row right i've got a client name here we can use   the mask to determine the customer row as long  as b3 is not blank i can then load the address 1   and the address 2 directly inside that and that's  going to come directly from our clients right so   we want in from column c and then i want d e and  f to appear in the second row so to do that we're   going to if the user makes a change to e4 and the  target value doesn't equal empty i want to make   sure that there is a customer row that customer  row is based on the invoice screen i want to make   sure it's not empty b2 must contain a value inside  our invoice right if it is it's not that we don't   have a correct customer assuming that those things  are true i'm going to dimension the client row is   long and the client row is going to be equal to  whatever's in b2 that's our customer or client row   e5 is going to take on whatever's in c which is  that main address inside our from our clients row   and then inside the row below which is e6 i want  to combine both the city the state and the zip   code coming from columns d column e and column f  it's going to combine all that and of course we're   going to separate it by comment and space so that  is going to bring the address in here our city our   state and then if there's a zip code we're going  to put it in here mobile alabama 35 so that's all   we have that's all we need you probably should  put the comma after the city i think that makes   more sense don't you so let's add a comma in here  and then remove the comma here i like that better   okay so just some small details to make it look a  little bit better so now when we click betty white   here okay so now mobile that looks much better so  we understand how we're going to get that address   in here so that's all we really need to do to  automate the name change but what about if i   want to add more items right if i want to add  like a washing dryer i want to make sure that   the description automatically appears the quantity  and the cost now if this looks familiar i do have   an invoice from scratch that this was modeled from  so if you want to see this completely from scratch   check out youtube invoice from scratch this  thing was built from scratch for that video   this video obviously we're going long on it so  but basically on any change to d although i've   made some changes on this one usually we have the  row but that's not necessary anymore if there's   any change to d i want to find this item if this  item inside our service items has been found   i'm going to extract the row what row is it found  on and then i want to put in the item description   and then i want to put in the price and i  want to default the quantity to 1. so that's   just what we're going to do in here on invoice  item but not on invoice load right i want to   differentiate between those two changes if i  make a change if i load this invoice right i   want to make a change that's all these are going  to be automated but the kind of change that i want   when a user makes a change or when the invoice is  loading those are two different types of change   so i need to differentiate that because the change  event is going to trigger no matter what so if i   differentiate between those it won't change so  i can do that with invoice load when the invoice   load when i search for an invoice it's going to  load automatically this will go to true so i need   to differentiate between that so there's another  change but if i do this that's another change   so i want to make sure that b6 is false so we can  do that here if the user makes a change to any   item name and b6 is false then what we want to  do is make sure that d in the target row is not   empty okay but if it is then what we want to do is  do something else and i'll fill that in right now   so dimension the item row i need to find that item  row as long okay i'm going to set that item row   it's going to be based on the service items it can  be based on the service names i'm looking for that   target value i'm looking in the values and whole  and i want to look for the row if it is found if   the item row does not equal empty or not equals  zero then i want to take the information from c   which is the item description in the service items  and place it in column e i want to set the default   quantity to 1 and column f and i want to take the  sales price which is in column g in our server   items and i'll place it directly in column e all  right that's skinny but what if it's empty well if   it's empty what i want to do is i want to delete  everything right but it's not happening yet why is   that well because i haven't programmed it yet so  if the target value is empty i want to clear out e   f and g so that's what we're going to do here  if it's empty then range e and the target row   and through and then g and the target dot  row clear the contents clear those contents   okay so we're clearing it out that way if it's  done and i also want to delete the any database   row that's been associated with or clearing out  the database if there's if there's a row in the   database this is where our database row is located  if this is existing then i want to make sure to   clear it so now it's fine that's great but what  if i decide i want to delete this i need to it's   nothing's going to happen if i don't delete this  row so if our database row is existing column   i i also need to delete it inside the database  row so we can do that basically it is this row   here that i want to delete right so i want to  make sure it's deleted inside our invoice items   so that it doesn't come back so we need to check  an i so we can do that through here if dot range i and the target will scoot and and the target dot  row value does not equal empty does not equal   empty then we need to delete it right so then  how do we do that so we could do our invoice   items invoice items actually we can dimension it  probably a little bit easier so we'll just say   dimension that's the invoice item call this  invoice item database row as long a little   bit easier and what we're going to do is i'm just  going to call this one here it's going to be our   database row so copy this so and then we'll say  invoice item database rows equal to that okay   so that's the row that's been associated so now  we're just ready to delete so invoice items dot   range and what is it it's that row invoice item  database row and colon and and then the database   and again invoice item database row and then we're  going to entire row dot delete okay so dot entire   row dot delete okay so that's going to delete this  entire row so now we save it our work of course   and now let's give it a try so now when i delete  this let's just do delete so now oh i'm going to   fix that dot range we don't need dot not dot we're  on the sheet so we don't need dot so that's fine   if we're on the sheet we don't need that okay so  continuing on okay so let's take a look so let's   do that and delete it and also what we want to  do so now six has been cleared up we also need   to delete it here under hair so let's do the  last thing the last thing here we want to do   is i and the target row clear those contents  out so clearing those contents out dot clear   contents so now we load again it's not going to  be there clearing the contents out clear contents   okay so saving our work so now that's no longer  going to be here so if i decide i'm going to load   and automatically load it without that last row  so just three items now okay so that's how we both   add items here and we clear items there we go so  now what about if we change the invoice so now as   you notice here i just changed remember we spoke  of j1 when i added that invoice that little search   here when i add an invoice here it's automatically  going to load here so how do we do that so that's   very very simple that's going to be just with a  little macro all we need to do is run a change   event based on j1 and of course if j1 is not empty  then what i want to do is i want to make sure   that we have certain row based on that so if i  put a number in here i want that number to be   associated with the search row so based on j1 here  search row matching j1 i want to match the invoice   id found if it's been found add to otherwise  blank right so i know if b4 contains a value   i know what database row that's associated with  so if it's empty let the user know please not   found so if i enter something that doesn't exist  it's going to be a message box saying invoice not   found please enter but if it does exist then all  i need to do is just enter one or whatever it is   and it's going to be found okay so it is that same  formula that we're using inside here except we're   just using a different so this way we're using h1  based on the existing invoice this one we're using   j1 based on the search for invoice numbers same  formula just different cell okay so now that we   have that all we all we need to do is assuming  that we do have a correct value in b4 all i need   to do is take that id located in j1 and place it  directly in h1 so once i place whatever's here   inside h1 this should be invoice number and so  once we have that we can then place it directly   inside here invoice there we go cleared it back up  must have cleared those out i'll better fix that   that was based on a clear contents here okay so  now we have that so what i want to do is i want to   make sure that we have it so once we have those  values all we need to do is just then run the   macro to load the invoice and that macro is what  we're going to be covering next inside our invoice   macros we'll go over some of the macros that are  associated with this invoice first one is going   to be save and update running a bit long today  or we're always long but we're even longer than   normally long so what i want to do is i want to  determine is it a new or existing invoice right   so again our invoice row if that's blank it's  going to be a new invoice if it is not blank   it is going to be an existing invoice so we  determine that if b3 is empty it is a new invoice   we're going to determine the invoice row based  on the first available of our invoice list items   we're going to set our brand new invoice number  h1 based on b7 we're going to add remember   this is that max formula that's going to determine  the next invoice number we're going to place that   directly in h1 i'm going to also take that brand  new invoice number placed in column a and i want   to take whatever appointment id is located in  b5 and place that right we want a place only for   new ones we're going to be replacing placing that  invoice id and the appointment id here so let's do   let's go back to the schedule creating an invoice  clicking here creating that invoice we're going   to show that that invoice now we'll go ahead  and save and update that invoice all right and   taking a look inside the invoice list we have our  brand new invoice our appointment id is showed up   everything is here that exactly the way we want it  okay and back into our invoice macros here so all   we're scrolling up here so what i want to do is  saving an update we are going to do all this only   for new invoices if an existing invoice all i need  to do is extract the row from b3 so invoicing and   then all we need to do is just simply add in the  information we're not using data mapping it's only   about four rows so it's fine small amount of data  the date is going to go in column c column d is   going to take on the customer name science staff  is going to go into e and f those are going to all   come from the invoice so basically it's just going  to go inside date customer staff in total and   they're all going to come directly from here date  staff the total is going to come directly inside   from h and then it's going to bring all that of  course the appointment id is coming from here   so that's going to bring in all information okay  but now what i want to do is i want to update all   these items and i want to go i want to basically  run through all the edits determine the last row   i want to know if it's been added to the database  the invoice item database row if there's a number   here 12 and 13 it's been's been added already  we would need to make the update if necessary   that is going to be here notice row 12  and 13 this is where those items get saved   invoice row 9 and 10 database row with a formula  9 and 10. so that's what we're going to do inside   our add update invoice so first we're going to  determine the last row the item is going to be   based on d28 which is the last possible row here  we're going to determine the last row this case 10   so i want to loop through those so if it's less  than 9 we're going to go to no items if it is   not we're going to loop starting in row 9 going to  the last item and then what i want to do is i want   to know is it an existing row or not has the has  this item been saved to the database or not i if i   is blank it's going to tell us if it's not so if i  does not equal empty then we know that the invoice   item row is going to be equal to whatever is in  the row this is the invoice item database row   otherwise it is a new we have to assign it a  new one that's going to be the first available   row based on that inside our invoice items row  i'm going to take that invoice number whatever's   in h1 i'm going to place it in the first column  i'm going to place the invoice item row whatever   row that is i'm going to place it in i right  i need to know place it right in here so that   means basically if we loop through these and  we decide we're going to add a new item i want   to place whatever the next database row is  this will be 14. now i want to place that 14   directly inside here so when i save it it is  that 14 that's going to get placed here okay   so now that we know where that inverse row i  also want to add a formula notice a formula   inside column g and that formula is going  to dictate the row why is it a formula why   is that important because if i decide to delete  a row i need to make sure the rest of these get   automatically updated that's why they're all  formulas in column g based on the row so that way   if we decide to delete one they automatically get  updated they're always accurate so now the rest   we're going to do regardless if it is the new or  an existing item we're going to do this i'm going   to take whatever's inside a d through g and place  it in b through e what do i mean by that b through   g right all the way b through e excuse me there i  want to know the item the description the quantity   and the price i want to bring all of that directly  from whatever's here from all the way from d   through g all that information is going to come  and go directly into the database and i also want   to put in the item row in f f is going to take so  this item row 11. i want to take that item row 11   and i want to place it directly inside f so we're  bringing all this information into our database   great that's all we need to do just loop through  that and then what i want to do is i want to run a   fade out message do you notice every time i click  save on that invoice you see this little message   pops up well this message is simply a shape if i  click on our selection and we show all we see that   this is a shape it's called invoice saved this  is called the invoice saved message shape so this   shape basically is going to fade out i did this  in the training before a few of them so this is   with the invoice save message i'm going to run  this mac it's a macro that fades out the message   so we're going to invoice shape save message we're  focused on this shape on the sheet i'm going to   dimension i is long delay has a double and a start  time is double basically just going to run a loop   i'm going to make sure that it's perfectly visible  and then we're just going to simply run a loop   and dim the transparency as it grows the  transparency grows and grows and grows until it   becomes almost uh you know invisible then the last  thing we're going to do is just making that shape   um basically visible equals false so if we want to  make it faster we can lower the delay or increase   the delay to make it slower or we can change  the transparency but basically this time or   this delay can make it slower or faster all right  great so what about invoice new invoice new again   all i'm going to do is just set the invoice  load to true and then return it back to false   then we're going to clear out a bunch of cells  and then we're going to set the current date i   want to default the current date so when i  do new invoice i want to make sure that the   current date is always on the default okay all  right so that's it just clears out a bunch of   the cells and make sure we're clearing out the  columns row right if we save it that invoice   is going to be automatically gone okay so what  about if i want to load the invoice right i want   to load it we certainly need to make sure that we  have a row if there's no row associated with this   invoice we can't load it so first thing we want to  do is check to make sure that b3 contains a value   if it's not let the user know to please select  the correct inventory b6 first of all we're going   to set b6 load to true and then before the macro  ends we're going to set it to false first thing i   want to do then is i want to set the invoice row  based on what's in b3 i want to clear everything   out clearing all the fields out and then what i  want to do is i want to set the appointment id   based on whatever's in b5 whatever's in column b  put that in b5 whatever's inside column c is our   date put that at h2 and our customer is going to  go on e4 now keep in mind as soon as we place it   in e4 that customer address is automatically going  to populate assuming the customer has been found   then what i want to do is i want to load the  items right i want to know i've already added   as soon as i make a change here that address is  going to be loaded so if i delete that and i just   double click on here no matter what the address  is going to automatically populate now we need   to load in our items but i need to know what items  are only associated with this invoice number it's   in h1 so we have our invoice items here i want  to run an advanced filter based on all this data   that criteria is going to be based on invoice h1  so we're going to run we're going to have those   results come directly in here then all we need to  do is loop through the results i need to determine   what row in the invoice to place it once i know  the row we can place those items directly in the   invoice so that's what we do here the last row  based on the invoice items if the last row is   less than three the new under no items we're going  to run that advanced filter based on all the data   on those invoice items we're going to have our  criteria based on that invoice id i2 to i3 here's   our criteria and our results are going to come  from k into k2 through q2 those are our results   k2 so we're going to copy those results from  k2 into q2 then what we're going to do is we're   going to determine the last results row based on  column k and if that last results row is less than   3 then there's no item so we can go to no items  it's going to skip everything and go down to here   there are results we're going to then loop through  the results four starting at row three going to   the last little throw first thing i need to know  is that invoice item row what row in the invoice   are we placing are replacing on row 9 10 or 11.  that row is going to come directly located in   column p 9 10 or 11. so to get that we need  to put that into a variable based on column p   once we have that i can bring all the information  over d through g is simply going to equal   l through o d through g on the invoice here  d through g is going to simply equal whatever   is located on our invoice from l through oh bring  in all the information lastly i want to in column   i i want to bring in the database that database  is going to come from column q right that database   that row 3 4 5 must go into column i 3 4 5 here  so we know it's associated with the specific row   all right that's it all we need to do there and  then i want to set low to false we're also going   to be printing that invoice very easily printed to  the default printer clicking on print it's going   to set it to the default printer i've already set  the print range snagit is my default printer here   i've already set that print range based on  this very very easily notice that we have a tax   right now this tax is variable we have a setup  screen here based on this so we know if bc22   is no then show nothing if it's yes then we need  to show it so inside the invoice we have here   if the tax option that's called the tax option  equals yes that's the name range i set for that   then what we do is we're going to show the tax i  want to show text and the taxed and the percentage   i want to format that will be the percentage  and i want to show that percentage so that's   going to show that tax five percent right so  we take a look here this is the tax option   this is what's called tax name so this is the  tax name so i've given it a name that's it then   what i want to do is i want to calculate it if g30  equals empty does not equal empty right if there's   nothing here then show nothing otherwise what i  want to do is i want to take that tax whatever   that radius and multiply it by the subtotal that's  going to add in our tax and then we have our total   that is pretty much it so printing new invoice  saving invoices and also if you notice lastly   there's a background here i've got a really  cool picture background if you weren't aware of   it excel has that feature we can inside the page  layout you can see delete background it's going to   clear that background i've got a custom background  i'll include this picture inside of patreon if you   like that patreon background and i just have  a picture here so we can click on the picture   and then we can just click insert and it's going  to insert that background there had an incredible   scheduling training where we've shown you how to  create this incredible drag and drop spa and salon   manager complete with scheduling complete with  employee pictures drag and drop navigation on   the display employee pictures one click invoicing  and it's been an incredible training thank you so   much for joining me don't forget check out our  patreon we've got a lot more head in there if   you've got any ideas you want me to add to this or  anything you want me to fix or maybe an area you   want me to focus on i'm doing that every single  week for every single video on our patreon so i   hope you'll join us there on patreon we'll see  you there and thanks so much see you next week you

Channel: Excel For Freelancers

Views: 102,242

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Spa Scheduling, Salon Scheduling, Massage Scheduling, Spa Scheduler, Massage Scheduler, Salon Application, Massage Application, Spa Application, Excel Drag Drop Scheduling, Spa & Salon App, Spa & Salon Application, Spa Schedule App, Massage Scheduler App, Service Scheduler App, Salon

Id: AzFLnDplS7k

Length: 122min 33sec (7353 seconds)

Published: Tue Dec 07 2021