Discover the Perfect Rent Invoice Format in Excel for Public Relations

Rent invoice format in excel for Public Relations

hello and welcome this is Randy with Excel for  freelancer and in this week's we're gonna be   developing an amazing equipment rental application  complete with pictures of equipment check in check   out date we've also gonna have brand new buttons  and an amazing pop-up [ __ ] calendar all from   scratch all from the beginning so I can't wait  to show that to you so let's get started all   right thanks so much for joining me today I've got  another amazing application I want to create for   you right in front of you today and that's gonna  be the equipment rental application as you can see   we're starting out with an absolutely blank sheet  I do have some other sheets with some information   on it we're gonna go over that soon just before we  start if you have not yet subscribed I would love   for you to do so we've got an amazing over 100,000  subscribers trying to get to 200,000 subscribers   with your help so if you have not done so yet I  do create these videos each and every week for you   for free so I'll always appreciate your patronage  your subscription so if you want to click that   subscription and don't forget the notifications  icon bells well that way you'll get alerted to   each and every brand new training that I'm going  to create for you and make sure you download the   application always free and I'll always include  the links in the description below with your   email or your facebook Messenger and we'll get  that right over to you well thanks so much I   really appreciate you joining me the subscriptions  has gone through the roof so I really appreciate   that if you do like these trainings of course  I've got an amazing accel mentorship program   or gonna be creating an amazing application and  a huge accounting application where I teach you   to create your own applications in fact here is  a little preview of the invoice screen that we   just started working on here in week 17 which is  where we're at but you should start anytime and   be great we'll bring you week to week and how to  build up amazing applications in this particular   application this invoice screen is just taking  a look at this here we just started working on   it but users and users are gonna have the right  to customize their own invoices so when you click   this customize it's gonna open up this brand new  panel it's gonna show you be able to select any   label any data just by simply lightening it it's  gonna display that and then you can you just can   drag and drop those fields anywhere on the invoice  they want men cludes labels and of course they're   going to be able to change the colors change the  look change the borders and everything they want   on those invoice whether it's invoice details  they'll also be able to control the width of the   columns show the columns hide the columns display  footers show tax high tax pretty much anything you   want and a fully customizable invoices that's just  one of the amazing things that we have going on   in this accounting application that I'm teaching  you in the mentorship program we've got an amazing   chart of accounts feel here we're going to be able  to showing pop-up quick release features a ton of   amazing features in this application so I hope  that you will join us and I'll click the links   down below for the mentorship program alright  let's get started on this week's application I'm   gonna be creating equipment rental applications  can allow you to use this application or sell   this application or do whatever you like with this  application customize it the first thing I want to   do of course is drop this down and we're gonna  save those first two columns for admin you'll   eventually want to hide them so we're gonna cover  them great this is where our details are gonna go   and then you'll hide it all include this top row  for the title if I will go all the way over to Jay   and I'm gonna put up just a fade out cause we've  done in the past will create a new car I'm gonna   do a fill and I'm gonna do a fill effects create  just something kind of a nice color to give it   a good look we'll go for this blue and then a  lighter blue and then I'm on the road down what   I'm going to do is I am going to bring it a little  bit of lighter color here in just this little mini   row here because I've I've spaced the rows out to  help us move things along I know these trainings   can be long and I know there's a lot of work so I  try to do as much as I can in advance as possible   so we're gonna color that just a little bit and  then I'll give the background the color and I'll   probably go down to row let's say just thirty or  something like that so basically what I want to   do is I want to create a form in this entire  form is gonna take care of all of our rental   units here it's gonna we're allowed to be able to  rent equipment and what we're gonna do is we're   gonna create a list of orders those orders are  gonna go on here every time we create a new order   it's going to go here we'll be able to try the  customer the order date created by date the red   dot date expect a return date so this is where the  tables gonna go I've already pre filled it with a   list of equipment and I've got some pictures in  a folder here so we can see some pictures I'm   gonna have these pictures appear on the forum too  because I want to show pictures and show what kind   of equipment people are gonna be renting out and  vac you can if you combine other trainings we can   create a receipt or an invoice with these pictures  if you want so we've got that I've got a list of   customers just prefilled out a few customers  with basic information nothing too important   their order items this is where our orders are  going to go so all the items that will work and   then based on order ID so that means every time  users can check out multiple equipment right so   you can have one order with an unlimited number  of equipments that they can rent out in return   and we want to pull a rate and then I also have  a calendar pop-up this is something that I've   used in the past this is just a calendar that  I've created we're gonna use this on the page   but what you can do is I'm gonna show you how to  use this calendar on any type of application you   want so it's great but we do need to start out  with this calendar page this is the shape based   calendar so you can change the colors and change  all kinds of things on it so make sure this sheet   changed you can drag this sheet to any other  application if you want we'll get into that and   I'll show you exactly how we're gonna use it but  our order sheets gonna be blank so let's give it a   start and let's give it a title and some icons of  course let's pull it up we'll call this equipment   rental application equipment rental application  make sure I spelled that right because often if   I don't I won't catch it and then you'll have  to look at my misspelling for the next hour okay   looks good so at all creased to font something  to probably about 24 and then give it a bold and   italicize and then we're gonna merge it across  let's just say we'll go here to I just so we can   get a good idea of it alright so what I wanna  do is I want to create some fields here we'll   start and let's say for I'm gonna put the customer  here and then I want the expected rent out date here and then expected return day one we want an  expected wanna know when they expect to return   back to return date so that we can price out at  least give them an estimated price and I also   want the actual return date our actual returned  on that's fine either way is good so this way we   can have a customer here or the returned on and  then I also I want some more fields I want the   order ordered number we gotta track each order  should have its own order number than the order   date what's the date that they ordered it on  this will be automatically created and then I   want it created by we can put in whoever created  by we can put in whoever created the application   whoever created the order I should say duration  what is the duration it's going to be automated   how long are they checked it out for pricing  can be priced on this duration so that's really   important and then the next field I want another  field something like total rented price total   rented rental I guess with a rental price because  this is gonna be all of the items all the items   combined like if they have multiple equipment we  want the total here and then I want to know how   many payments they've made in case they've made  a deposit or they paid on full we need to know   the payment so payments will go here and then  probably total amount due based on the payments   so that'll be pretty easy all right so this is  are gonna be automated but payments is gonna be   injured by the user so I'm gonna call it this  white just the fields that they can enter here   so create a by and then customer rent it out  these gates these are all date field so we're   gonna put those in white because they're gonna  be form and I'm gonna left justify those left   and then I'm gonna put these are gonna be right  justified I'm gonna put a border around those as   well just to give them some kind of uniform of yet  I'm gonna put a table down below too so I'm gonna   right justify those format those and I'll put  a border around those because I want it to be a   nice clean look and I'll use a color consistent  with my theme so I'm gonna use this darker blue   here and then I'm gonna put it all the way around  but I'm put a dotted line on the right I'm gonna   do the same thing except the dotted lines gonna be  on the left as I hold down the control select all   the fields in which where user is going to enter  in fact I'll add these to format those cells and   I'm going to put the borders just a solid border  on the left and the right the top and the rocket   the bottom all right so the same thing with  this one here I want to do actually I want   to put the number of days here I don't want to  put the formula here so let's put that in there   format cells and I'm gonna do the same thing with  the border here just added this color border okay   so our forms starting to come together now we can  see how it's going to look and it gives us a good   idea of what we're gonna be having and I also  want a table down here let's just format those   cells that's where we're going to be putting in  our formula for the calculated number of days   now the calculated number of days be based on two  factors it can be based on the expected readout   date and the return date or if there's an actual  return on it can be based on the rent out date   and the return on so we need two different so the  duration can be based on either one of those then   in 12 I want a table so I'm gonna put in equipment  I want to know the equipment that was rented out   equipment and probably name and then description  we have equipment description and then the status   I want to know if it's been rented or if it's  available that's important the rate how much is   the rate the daily rate we can put in daily rate  and then duration how many days of course and then   the total this will be calculated okay so that's  gonna be the top of our table so let's color it   ingly we're gonna Center it in there I'm  gonna put borders all the way around it form   out the cells it's a little bit off the screen  sorry about that I'll drag it up I'll use the   same border color and I'll color the outside and  inside and I'm gonna give it a fill again a fill   effects so that it it looks a little bit different  than everything else and then I'll use this fade   out color here okay so now our tables starting  to come together and I'm going to create a very   large table because I want to make sure it's  on the screen for you I don't want to go off   the screen too much so we'll just let's go down  to say 26 and then what we'll do is we'll format   those cells just create a little bit of a table  here again using the same colored border border   all the way around I'll use a just a lighter maybe  a lighter dotted line in here a little bit lighter   in the center vertically okay and then I also  want to alternate to color row so we're gonna   add conditional formatting to color alternating  rows Co the even rows how do we do that I'm gonna   use a formula for that and then something that's  automated for me because I used autohotkey you can   download that for free and it just automatically  types in this formula will color even rows two   equals zero means even row so I'm gonna give  it a format and a fill use this but a little   bit lighter so I'm gonna drop it a little bit  lighter here and that's key gave us alternating   rows that we can easily view our table contents  much easier okay it looks really nice but I'm   gonna color it does look kind of nice iein colored  white though the rest of the cells were white so   we can get a nice table okay well add make these  bolt control beads gonna do a quick way to bolt   it's coming together nicely this is going to be  a formula this is going to be the customer order   number looks good now let's add in some buttons  I'm gonna put some buttons I also want to put   a picture so let's actually drag this over here  and I want to increase this table I think I want   a picture here as well so let's do that let's put  a picture in here and I want a picture of that so   we can also put a border around this too because  once we select an item I'm gonna let me just go   down so you can see it I'm just right clicking and  formatting the cells with its office screen here   adding the border so that pictures gonna go there  so when we select an item equipment item from this   table the picture of the items gonna show up there  which is kind of a nice feature okay that looks   good we can bring this over color and just call  it the outside a little bit so it looks a little   bit nicer and I colored the outside a little bit  so it looks nicer I'm just gonna copy paste that   down there and bring this down here so that we get  all the way around the table okay so now we've got   a nice all the way around it looks nice okay so  the look and feel of it is correct and I want to   add some information in we're gonna need I need to  know whether this is a new order or not new order   I also want to know the row of the order order row  and I need to know from the next order number next   or because we've got to keep track of all the  orders and if the order is loading or not order   load that means one is load what I want to do is  when a user puts in an order number here like one   I want that order to load automatically I'm gonna  show you exactly how to do that so everything's   gonna come up and I also want to know the selected  row that means the selected row in this table when   you use selects row I want a few things to happen  that row to be highlighted and I want to show the   picture if it's available of the item right here  makes it for really really nice and really easily   to look at so we've got that and let's get let's  color these ingly just so they stand out   something different like this green now we've  got that already set up so what I want to do   is I want to add some information I also want to  add some buttons on here so let's add some buttons   let's put in some nice big buttons we're gonna  use a square button something within the theme   of the table and I'm gonna put it in the buttons  right here all right and let's size them ion   looks a little bit too big point forward let's say  and then one point six we've got to put about four   buttons in there total but let's get that all  let's put the shape let's use a probably this   one here looks nice and then what I'm gonna do  is I'm going to duplicate that control D control   D control D four buttons I want four buttons and  really only three buttons are gonna show at any   given time so we can drag these down here and then  I'm going to put this one over here just for now   so we're gonna have about four of those buttons  all right now I've got some buttons on wanting   interest so let's enter I've got some saved up I'm  gonna use all of these here I've just saved them   so we're gonna preach and then we're gonna let's  just adjust height two point three or something   like that something a little bit smaller and  manageable and one of them is actually the icon   that I'm gonna be using for the application  which is here so let's bring this up and I'm   going to increase this little bit so we can see a  nice okay good that looks very good and now let's   change the font to something a little bit bigger  and the color here so it looks a little bit nicer   okay so now we have a nice look and now let's go  ahead and place these on our buttons so what do I   want I want them delete button here I also want a  save and update button I want to cancel new button   and I want a new order button good I also want  to use a calendar pop-up it's can being really   nice so I'm gonna reduce this down and I want this  basically to display inside this then we can make   it a little bit bigger let's go at point one seven  and I want basically when you user clicks on this   I want the calendar to pop up whether we're here  here or here so it's gonna be in either one of   those threes so that's gonna be really helpful so  let's get these buttons done and see what we can   do the first one let's call this new order and on  all of the buttons I'm going to click them all and   I make them all right justified although they're  not long to me and in the middle and then let's   increase the font until we get something that  we like make sure we got a white font perfectly   white and maybe make this bold there we go  that looks kind of nice alright so we've got   that and then maybe the fonts a little bit too  big there and drop that down a little bit okay   and then we can Center this actually all right so  individually will have to space them out a little   bit okay so we got the new order and then I want  this we're gonna call this one saver update save   or update and then this one's got a little bit  text so we'll write justify that and then I want   delete let's say call this delete order and we can  write justify that one - it's a little bit bigger   text and then the last one is gonna be cancelled  new and I only want this to appear up on new one   so cancel new I only want this to appear on the  new mode okay and this is only going to peer so   basically it's either to be canceled new or new  order so not both of those at the same time so   I'm gonna hold down the control and then I'm gonna  do is I'm gonna group both of those and I'm gonna   call this cancel new button and I want this to  be called new order button so let's do the same   thing here control and then we can also format  and align those up to make sure they're aligned   in the middle and then I'm gonna group them and  give this a name we're gonna call this at will   call this add new button and then I also want  the save update here so again let's drop move   this over here Center it control group it and name  it we'll call this save update button and then the   last one we're gonna call this just the delete  order button again here and then we're gonna head   and line it up here align the middle and then  group it and then name it delete order button   okay we've got that done now we got everything  nicely let's just spaced out ingly holding   down the control I'm going to align the left and  then I'm going to distribute it vertically here   now all the buttons and I also want to place this  cancel new we're not gonna show cancel new and new   at the same time so I'm gonna place it on top of  each other I'm gonna send it to the back okay so   now we've got our button set up we got a new order  same order perfect if things were looking really   good now what we want to do is I want to add some  named ranges for those really important especially   with customer so let's go ahead and do that in  the customer name I've got different customers   so I want to add a dynamic name range so to do  that I'm gonna go into the formulas name manager   and you'll see two already those are based on  the calendar so those are pop-up calendar and   we use those so what I don't worry about those for  now just focus on this customer name okay and so   what is it I want to use an offset farmer because  it's dynamic so I'm gonna start out with off set   and I'm starting out in row 2 because that's the  header button right we don't want to include that   but the reason we and start out with the headers  because when there is noted no data it's gonna   not prevent an error so start at an 8 2 comma 1  1 means one row down comma comma count a but I   want to count all of the one so what I want count  I want to count all including let's say a through   a large number like 999 that's gonna count all  the ones with text but I want to subtract 1 again   because we're using the first header row which  we don't want to include so we're gonna subtract   1 comma 1 means just one column all right tap out  tab back in make sure the dance accounts cover all   the data as they do here all the way from a 3 to  a 7 we're good to go so now we've got a customer   name but let's add more I also want equipment  name we've got equipment I wanna do the same   thing for equipment so let's just highlight this  again actually we could probably highlight this is   gonna be a little bit quicker go into the name  manager new and we'll call this one equipment   name okay and then again we're gonna start it off  with offset just like it as we did before and then   open parentheses and then what we're gonna do is  going to go to comma and then 1 again comma comma   count hey what are we counting we're counting all  the equipment with names in this list again large   number 999 starting at the header comma minus 1  actually minus 1 comma and then 1 1 column tab   out tab back and looks like we're good okay don't  forget to download this you want to see what I've   done you always have free downloads click ok  alright good now one more let's do one more I   want to do order numbers that's really important  even though we don't have any order numbers order   list but we're going to so let's just say 1 2 3  putting some sample data right now will control   this and then name manager again new one more and  we're gonna call this order numbers same exact   formula using offset offset is a great formula but  we're using very very large ranges you might want   to use something like index which is non-volatile  we're using smaller ranges so it's okay there's a   small application keep in mind there's more than  one way to do this depending upon how much data   you want how big your application is offset okay  comma 1 comma comma count a what are we counting   or counting all the orders in this list all the  way up to a large number 9 9 9 close parentheses   minus 1 not including the header comma 1 okay tap  out tap in great I know I move fast over these we   got a lot to cover so you can always download this  and see the formula exactly for yourself you want   to make sure the dancing ants cover the data ok  good alright great so here's the idea let's go   back into some formulas and start working on that  we've got a customer I want this to be a drop-down   list so let's put that in a data validation data  validation and then we're going to make it a   list and we want to equal the named range we just  created called equals customer name okay great so   that's going to give us a drop-down list of all  the customers but we also need the order to row   order number so let's put in a row now what is the  row of water number one it's gonna be row four why   is that because our row starts on our first one  starts on four so I also need to know the role of   that so let's put that in equals always if air in  case there's an error what do I want to do I want   to find a match I wanna match the formula what  am I looking up I'm looking at the order number   what is the array it's gonna be order numbers and  I don't want an exact match so it's gonna be zero   but I don't want the first value I want the first  row so our first row starts on four so we need to   add three right because our first value starts  in row four so comma why are we coming because   if there's an air what are we going to do if  narrowing a double close that's gonna get us   four if we change that to it's gonna get us five  perfect that's what we want because order number   one is in row four perfect and if there's no  orders then I just want to be blank see no air   it's just blank perfect that's exactly what I want  what I want the next order could be the next order   I want that to be using a max so we can use the  max and if there are no orders like in this case   there are no orders I want to show up one so how  do we do that we use if air equals if air we're   gonna use the max this time max of what max at  the ordered numbers but I ought not only want   the maximum I want one beyond that so plus 1 so  plus one beyond that but what if there are no   numbers in that case I wanted to return 1 which  would be the first available in case there's an   error so it's gonna do one now as soon as I change  this to the order list as soon as I count one two   three that next order numbers gonna automatically  go to four which exactly what I want so it's the   maximum which is 3 plus 1 which is 4 okay this is  gonna be a true or false so a boolean so we can   select that and I also want another selected row  and add conditional formatting so for example if   this likely rows 13 I want to highlight that row  based on this row we can do that a few conditional   formatting if we highlight all the rows and add a  conditional formatting rule and create a new rule   and then use a formula to create that we can  do equals what does it can be equal whatever   seen B 7 and then equals row open and close  parentheses row open and close parenthesis now   I want to format that very different than what it  is so I'll just use a contrasting color let's go   into fill effects and use a very contrasting color  we'll use this and then what I'll do is I'll use a   bold font so like this and I'll use a white font  that'll stand out bold and white and it's gonna   stand out a lot okay good so now we've got that  so now if we type an equipment name good alright   but let's do that let's type in some equipment  in fact I want a drop-down list here just as we   did for the customer so we'll drop that down and  I'm going to data validation data validation here   I want to create a list and the list based on  the equipment names so equals we just created   equipment named click okay and that's gonna give  us a drop-down list perfect okay so now we in but   when I enter this equipment am I actually want to  show the description the status and the daily rate   we have all that in the we have the description  we have the status and we have the daily rate so   I want that information to appear but the best  way to get that information appears we need to   know the row number it's on in fact this is on  row three and four so how do we get that well   we can use the same type of a match formula using  or if air so I'm gonna put them formula right here   and eventually you can hide the results just by  changing the font color equals if air we're gonna   use a match what do we match it I'm gonna match  up whatever's in d13 d13 and then I'm gonna match   it based on the equipment name comma zero on an  exact match and so again I want the row what is   the row it's gonna be plus 2 in this case plus  two plus two because the first one's gonna be on   a row for I think I'll check that a second comma  what if it's an air and need parentheses okay so   if air compressor is on row four let's take a  look at compressors on row four cuz our first   row starts in three in this case that's why we  added two perfect okay that's what I want let's   go back so we've got that and we can just drag  that all the way down there and it's gonna provide   a blank because there's an air but as soon as we  add something on the first row it's gonna provide   the air now if you want to hide these which you  probably do on yours just go into home and then   change the font to the existing color to the  background color that's gonna hide it which is   great but for our purposes I want you to see it  so we can continue on and we're going to write   some additional code very soon that's gonna put  in the description status in the daily rate all   right very nice I also want to pop up calendar  we use the selects here I want this little icon   to appear here so let's give this icon a name  and what do we call this cow icon so now it's   got a little name so we can refer to it within  the code and I want to format these based on the   currency so we're gonna click currency symbol  that is what I want to format these as a long   date so we can format those long date here and  this one's going to be a short date all right   it's coming together we've got a customer list now  we've got an order number we've got the format so   if we put in a format here perfect what do I want  this to be I want this to be the total of what is   in here we're gonna put a formula in here so let's  write that now equals sum of what and sum of all   the totals here all right great so I'm gonna have  the sum and what is this this is the amount of   dues gonna be equal whatever the total is so if  we add let's just say 150 and we're gonna also   format this as a currency good so now we have  this so now I want to know the total amount due   is equal to the total rental price minus the  payments all right great so soon as we enter   payment in here it's gonna show them out perfect  so now we're getting they're getting a lot closer   now all right let's get on to that counter changes  to return on make it a little more clear so let's   get into that calendar I have a pop-up calendar  here that I created this is a shape based calendar   it's really cool I'm gonna copy this control see  I'm gonna place it on this order sheet here I only   wanted to really appear when they user selected  I'm gonna change the color several colors share   anything that we like we could probably go with  this blue here that's kind of close to what we   want okay so basically what I want this calendar  to do is when I click this icon I want it to pop   up and I want this calendar to hide otherwise so  let's write some code to do that right now it's a   great little calendar and let's let's work on that  so we're gonna go into the VBA to start working on   our V being get get this application created it's  going to be amazing so into the developers tab if   you don't have the developers tab you can go into  the file and the options and you can find that   developers tab and then customize clicking right  here on that checkbox you can also use a shortcut   alt f11 to get you in there I do have some code  this is a pop-up calendar this is the code that   runs the calendar that's the only code that I have  on there and you can take a look at that but we're   gonna focus on something different today we're  gonna focus code on sheet and I'm gonna creating a   lot of other macros but this pop-up is just to run  the calendar ok so let's take a look at it what do   we want to do well the first thing what I want  to do is when I select select on certain cells   I want this calendar this icon to pop up what  cells are those those are gonna be cells e6 e8   tense let's write some code to get that calendar  to display that little icon here display and I   want to display all you menu click on those cells  otherwise I want it hidden so we can use selection   chain so we're gonna click on worksheet selection  change so we're gonna write code in here now if   the user makes it change to one of those we want  to we want to happen so let's also write some   code to prevent some air so I'm gonna write if  target dot count large is greater than let's use   just ten then exit so what that's going to do is  gonna prevent airs when user selects on a large   large range of cell so that helps remove some  bugs that's just something I write quite often   okay so we're gonna focus on selection change so  first thing I want to do is I want to write if not   intersection now I wrote that code automatically  and very quickly because it's part of autohotkey   that's a software that helps me write code very  fast and it's also free on the internet called   autohotkey you can download it yourself e6 of  course is what we're gonna be focusing on along   with e8 and eat end when user makes a selection  on one of these three cells I want something to   happen what do I want to happen I want to have  that pop up calendar show up then did I want to   show up so what do I want to do let's just write  some notes so we know exactly what we're doing on   selection of date field display calendar icon okay  so we're gonna know what we're doing there so when   user slice up then what do I want to do I want to  say with shapes right with shapes we've already   named it cow icon we've given it that name so  let's refer to it cow icon what do we want to   do with that well the first thing I want to do  is I want to display it based on the I'm gonna   position it so dot left to position it and I don't  want to place it inside the song I want to place   it actually what I want to do is I want to place  it on the cell to the right so I really want to   place it right here and then what I want to do is  I want to move it over a little bit here so place   him to the cell the right ensures that it's always  gonna be on the don't worry about that it's always   going to be on the inside just on the inside of  it okay so we want to first place it on the cell   to the right so we can do that by continuing with  our code dot left with reset the code that wasn't   finished what is it it's equals the target is  the cell that they're selecting but not I don't   want the exact target don't want the cell to the  right so we can use offset for that I don't want   any row offset it's not going to go row above  or row below it's gonna stay in the same row so   we're gonna keep that zero but I do want to call  them I'll call them offset call them to the right   one plus one is going to be a column to the right  native one would be a column to the left dot left   I'm gonna do the same thing with the top property  top again equals target dot offset and no rows up   or down zero comma one one column and then dot  top they don't want it exactly on the right I'll   actually want to bring it over a little bit to  the left so we can do dock increment left and   then minus sixteen about 16 because the icons  about 16 pixels give or take so I wanted to   move to left and I don't want it exactly on the  top so I'm gonna move it down a little bit using   increment top to move it down we go one so in this  case down would be top let's just say one the last   thing I do I've used those four lines to position  it exactly where I want but I've actually haven't   made it visible yet so we can make it visible with  this line of code dot visible equals MSO true okay   so great so if the user selects on anything it's  gonna make it visible but what if they don't want   if they select outside they select outside in that  case else what I want to do else I want to hide it   shapes again cow icon dot visible equals Emma so  false so this is gonna hide it so if they select   anything else is going to hide it and then I also  want to run the Mac and I have a macro called   calendar hide and calendar show this is always  here so calendar hide this is gonna hide I've run   a macro cuz it clears it so we can do that that  hides that pop up counter so I'm gonna copy that   macro then I'm gonna go right in here I'm just  gonna write in calendar hide so perfect remember   we only want two I'm gonna run the macro this  macro calendar show which is gonna display the   calendar but I only want to run this macro when  they click on that icon so I'm gonna copy that   calendar show that's a complicated macro we're not  going to go into today because we're just gonna   focus on the or but I do have a training on that  as well you can find out calendars on my youtube   channel you can find a few on that one called  three calendars Russia show you three different   calendars so got a lot so I want to sign a macro  to this little pop-up click assign macro I'm gonna   paste in that macro called calendar show now let's  see when I click on here let's find out and show   it's not showing up let's see what we did ok so  when we select the order sheet and weeks when you   make a selection cow icon dot visible we need  to make this true see true ok there we got it   wrong one okay so now it's gonna show up that's  exactly what I want to shows up exactly where I   want to show up and when I click it I want to show  that calendar perfect and when I click off it if   I click in it outside of it it's gonna hide the  calendar show it that's exactly what I want when   I click the date it's gonna show a date when I  click it it's gonna we'll be able to show a date   perfect that's exactly what I want okay just a  review we want to make sure we're showing visible   it was true not false obviously and then false  would be to hide it great so now we've got our   calendar feature let's write additional code so I  also want to load the item remember remember when   we change this I want to load this item here  so that's unchanged event so let's write some   code for that meaning we're actually gonna make a  change not a selection but we're actually making   a change I want the description status and the  daily rate to show up and I want it to pull it   from this equipment here in the description the  status in the day they're gonna pull that out   so how do we do that well okay let's take a look  let's format this I think we didn't format this as   a currency yet so we can do that alright let's do  that and that's unchanged events so what do I want   to do I'm going to we've got worksheet but this  one's changed we're going to click change when a   user makes a change to a cell inside a worksheet  something's going to happen well what do we want   to happen I only want it to happen on specific  changes one what are the changes few things what   I wanted to only show up when we make a change to  anywhere between d13 and d26 so let's write that   code d13 if not range is nothing then what do we  want to do again let's change this to D 13 through   D 26 that's the change so if they make a change  then that's what I want to happen but I also want   to make sure a few other things for sure I want to  load it only on a few other things what is those   other things I want to make sure that they're not  loading the order remember when we're loading the   order all the information is gonna come from here  the equipment name and description it's gonna come   from here so I only want to load it when the when  we're not loading what do you mean what I mean by   that so if I've saved this order if I've already  saved this order all this information is going   to come from all of our order items here but if  it's new order or if it's an existing order and   we're adding on to it in that case the order load  would be false so I want to make sure b6 is false   so that means as we add as we added on existing  not when we're loading it I'm we're looking a   previous one two different kinds of changed we  need to specify and differentiate between those   two types of changes so how can we do that we just  want to make sure that b6 is false so we're gonna   add an and and range b6 dot value equals false and  I also want to make sure that the kind of change   is there's actually a value I wanna make sure that  the target there's a actual value so and range D   and the target dot Rho dot value does not equal  empty right if the kind of changes were they're   deleting something that we don't we can't load it  so we have to make sure that that equals and we   want to make sure all right so then what do I want  to do then I want to run a macro that's gonna load   the equipment so we're gonna write a macro right  now then equipment load equipment though that's   gonna be a brand new macro that we're gonna write  right now so I'm gonna copy this to make sure we   have the name so we're gonna do all that the macro  doesn't exist yet but we're gonna write it right   now so that's going to load load our equipment  let's bring this over here and open this up a   little bit so you can see more of it okay so now  you can see all the codes so then we're going   to load equipment so there's three conditions the  user makes it change to D 26 it's not on the load   meaning you're not loading the order from a nut  from a previously saved and we want to make sure   that they're actually entering a value it does not  equal empty so when those three conditions are met   we're going to run a macro so let's create that  macro now but we to do that we need to actually   create a module so we're gonna right click  anywhere insert a module and what I want to do   is I want to create a load module let's just call  this load order sort of load anything we want so   I'm gonna hold down the properties here click on  that and then just call this load order max okay   and to do that let's go ahead and write something  sub and I don't have paste in that macro name that   I just created so here's the macro so now let's  start writing this macro and this macro really   gonna need two variables what I mean I need to  know the selected Road the rope they've selected I   also need to know the equipment row the equipment  row is based on the selected row is it row he's   the equipment roof 3 or 5 or where's the equipment  because I've got to know if I'm gonna load in the   information if I'm gonna bring in the description  the status and the rate I need to know what rows   it on three four five or six but I also need to  know the road the user has selected what row are   they currently on that's important so we're gonna  define both of those in two variables so we can do   this dimension the selected row as long and also  the equipment row that's good and also of course   I want to actually display the picture - because  that's nice so I'm gonna dimension the picture   path as a string that's the path of the picture  now I have that picture path and it's saved   right here based on my desktop based on decent  information right here so it's all here so make   sure you have your pictures - and make sure this  path is correct you want me to make sure it's the   correct path for the pictures all right so I also  wanted that so that I can load in the picture as   soon as I load it in I want the picture to display  right here in this box right here so how do we get   that let's continue on with our macro of course  we're focused really with XI one so with she and   then we can continue on I want to make sure that  b7 does not equal value if we have to of course   we need really two things are in order to load  our ger we need the selected row that can't be   blank and of course we also need the equipment  row we need to run checks to make sure both of   those so we can start out with the selected row  if dock range b7 equals empty equals empty then   exit sub nothing we can do we can't load anything  unless they've actually selected a row also now   we can define the Select of those selected because  it's not empty so now we can continue on selected   row and what does it equal N equals b7 right  here so we can just copy and paste that save a   little time equals b7 I'm just call that selected  we're always good to comment out once we have the   selected row we can then define the equipment row  why is that because the equipment a rose located   in column C equipment Row is equal to dot range  C and the selected row I'll show you that just   second selected row and that's the equipment row  equipment okay so we've got that of course it's C   in the equipment row which is right here seen in  this Quitman row that's gonna be our that's gonna   be our row so we know that now we can define  that we also want to make sure that that's not   blank because if it is we're gonna we can't load  anything so we can do if equipment row equals 0   then exit so just in case you're wrong we can't do  anything on that just in case alright so now that   we have that now what we can do is we can bring in  over the data so dot range e is the first column   we've already loaded D cuz that's the name he is  our description E and what he and the selected row   and what else and parenthesis : all the way over  to G and the selected row equals what is it equal   let's just go over that e3 G equals equipment  B through D so that's on the equipment sheet   so what is the equipment sheet well that equipment  sheet is located right here if we can see that the   equipment sheet is sheet 2 here so we know that  so we can move on equals let's close this we don't   need this anymore sheet 2 dot range B this B is  the SEC we have the name and call them a and B is   the description B and colon all the way through in  this case it's going to be D and they quit B and   the equipment grow all the way through and call  it and D and equipment Rome ok let's just take a   quick look at that E and the selected row through  G in the selected row equals sheet to be an   equipment row and D and the equipment of row dot  value it's gonna bring over all the information   all of you ok so that would be the description  that would be the status and the rate ok let's   take a quick look at that to confirm double check  we're on the right page here description status   and the rate bring that over there B through D all  right ok so now we're good to go and let's take a   look what else do we need to right now we're gonna  write the picture but let's just take a look at   that and see where we are now to make sure we're  in the right place so let's just double click on   that we don't need the end if because we have the  macro right here alright we don't need this cuz   we have it right here so we don't need end if it's  all in one line we could just remove that if it's   all in one line you don't need to know but if we  are going to be doing something we would use it we   could do this as well let me just show you what it  would look like if we didn't have it if we wanted   to keep that end if we would have to do enter and  then we would do and if that would work also ok   so we can do either one of those let's keep it  this it stays a little more space both are the   same ok so let's double click on this and then now  we have the description the status and the daily   rate perfect ok so but I also need the duration  that's really important what is the duration well   the duration is going to be basically whatever is  equal to what's in G so and what is the duration   let's put a formula in the duration the duration  is it going to basically be if we have a returned   on date if we have returned on then duration is  going to be the return on date - the rented out   date but what if there's no return date in that  case of what I wanted to be the duration is going   to be the expected date - the red dot date so we  can write some code here so equals if and then   I wanted to do it and and why and because I want  to make sure that two of these have values if and   returned on date does not equal blank and what  else and our readout date does not equal blank   let's write that up here so it's a little easier  so if aunt eat n is not equal blanket be six now   you could blink then what I want to do then what  I want to do is I want to just basically say e ten   minus e six right its case are no blank otherwise  what otherwise what I want to do another one I   want to make sure if another and and expected  return on date does not equal empty and what   else and also again rent out date does not equal  NP if those two does not equal empty if those   two are okay in that case then I want to do our  return on date - or rent out date otherwise blank   right otherwise if case one of those is blank so  let's take a look at that and Neri we just added   an additional prints so it's too great what if  focus are expected returned on date is going to be   but what if we come back and we return it on the  ninth then I want this to go to three you see how   that works so basically when this is empty it's  gonna assume it that's gonna be on two days but   what if this is empty if they're both empty that  I wanted to blank that's exactly what I want and   then I'm just put in something like actually let's  let's write some code let's write add to here what   I want to do is let's say it's on one day don't  want to make this actually one because if we're   renting an out on the same day I do want to add  one to this so let's just do that plus one because   generally in rental if you rent it out for one day  from the same day it's gonna be considered one so   let's add one to both of those formulas here both  those I'm going to add one because generally both   the day you rent it on and the day return on is  considered one so in this case if we rent it out   on the same day cuz shouldn't be zero should be  at least one okay great so we have that but what   about the days equals on all right eagles is this  is less than 2 then what I want day otherwise put   in days there we go now I have two so one day or  if we can change this to two days perfect okay and   if it changes this three days four days whatever  okay great so now what I want the duration to be   always on this so it equals duration that's what  I want and of course we're gonna use f4 because   I want the absolute when I drag it down so now  it's going to be four so now but I only want   the duration to show up if we have a daily rate  otherwise I don't want to chump equals if then I'm   gonna drag this this daily rate does not equal  empty it does not equal empty then what I want   to do then I want to show g10 otherwise empty  so we understand so that we were only showing   the duration for the rows that have a value so  right click copy and then I paste this all the   way down paste and paste formulas nice okay and  then this one we want to do this one as well this   one equals I want to make sure this one I had a  formula equals there's two conditions in this if   there's a daily rate and a duration then in that  case I'm going to show evils if and two conditions   daily rate does not equal empty and the duration  does not equal empty and that would be H thirteen   H thirteen does not equal and E in that case  what I want to do that case I want to do G 13   x HH 13 the rate daily rate times the duration  times duration H 13 perfect otherwise if those   conditions are not meant to leave it blank okay  great so now we have four times two now I'm gonna   copy this formula down I'm gonna paste it all the  way down paste formula okay great so our form is   working but now what I want to do is I also want  to update B 13 based on the selected row so let's   write some code so as we select a row I want B 7  to change so let's go back into the code and this   back into the selection change right here and  what I want to do and I want to write some good   if not the user makes it then what I want to do  is I want to show that row they make a selection   change to what is this selection change in this  case it's going to be anywhere from d13 all the   way through i 26 so we can write some code d 13  through i 26 then what i want to do well the first   thing i want to do is i want to make sure that  b7 is updated range b7 dot value equals target   bro okay so let's select on different rows here  now we got now alright so now we've got it now we   understand that we're selecting on different rows  so let's select on this and let's load this and   let's try another one we got that alright so now  our loading our duration is automatically let's   enter this so it looks a little bit better and now  we've got our totals and our totals are already   updating perfect it's looking really really good  what i want to do now i want to save this order   i want to make sure everything is saved exactly 1  and i also want to load the picture so let's load   the picture and let's continue with the load and  load the picture in because i think we want to   do that so we're gonna continue we have the work  order loading but i still when we add a specific   equipment i want to add the picture of that and  display that so it looks really nice so how do   we do that well we know that the picture path  we're gonna define it where do we know it we   know we know the equipment row we know what row  is but now all we need to do is get the picture   path and it's gonna locate it in ease so let's do  that right now let's but we can write a different   macro Kazak what i want to write a different macro  i want to write a different macro because i want   this picture to load in a few different ways one  we're adding a new it and one when we select on   it so for example if i select if i select I want  that picture to load here on the right side if I   add a new item I also want the picture to load so  in those two instances I want the picture loaded   so I'm gonna write a separate macro for that so  let's go ahead and write that right now and then   doos I'm gonna add that macro to right here so we  can do that here so let's write in sub equipment   equipment load pick okay so again dimension the  we can actually copy this because it's gonna be   pretty much we don't need to pitch a path here I  don't know we need it actually it's copy this and   I'm bringing it down here and then I'm gonna  get rid of the pitch of that we don't need it   here we're gonna use it down here though so we do  need it down here so that's important alright so   let's get into that with sheet one always going  to focus on sheet one for the most part for this   training and then I want to do I want to remove  any picture first of all because if we're loading   it we could have an old picture there so the first  thing we want to do is remove any picture but if   the picture doesn't exist it could create an error  so the best thing to do is start out with on-air   resume next and then dot shapes because we're  already focused on sheet 1 I'm gonna call it the   equipment pick and then dot the leaf so if there's  any existing equipment picture from any other   equipment want to delete it first and then on air  go to 0 so that's gonna go so that way it'll end   up that would just delete the picture if it exists  so that's a good way to start all right now that   we've deleted any picture now we can do want to  make sure that of course we want to again we want   to make sure that b7 does have a selector so I'm  going to copy this here since I want to redefine   the equipment I'm going to actually copy all of  this here and bring it down here we're gonna do   the same things again I want to define all that  equipment row so we've got that just quick easier   so now what I want to do is I want to define the  picture path so the picture path is where we know   it's on sheet equals sheet 2 dot range e in the  equipment row E and the equipment row dot value   and that's the picture path picture pal so we  have the picture path to find out but we do need   to make sure that it actually is going to lead to  the correct path right because it could be an old   it could be from another computer could be issues  if there's not a specific and proper path so let's   write some additional code so on-air resume next  I want to check I want to check that picture path   if we can do that with directory if directory of  the pic path and then VB directory equals every   good use does not equal that's pretty good does  not equal does not equal empty then then what   then we know the picture path exists and if then  we know it exists so picture path existence so   now we know it exists now we can write some code  to display that picture so with dot pictures dot   insert and what are we inserting pics your path  gonna int path okay so we're gonna insert that   picture we want to insert it but what do I want  to do with it then once it's inserted I want to   define it so with dot shape range shape range  that's the picture itself that we're focused   on I want to do a few things I'm gonna lock the  aspect range lock aspect ratio because I want to   show up equals MSO true and what else I want to  set the height dot height equals about 150 right   so I want to make sure we're probably about 150  and then I want to set in a specific name that   name is very important so we know it's always  gonna have the same name dot name is equal to   again equipment path equipment path equipment and  I want to set a name make sure we have a specific   name equipment picture that's the same name that  we delete up there it's always gonna be the same   name so we're gonna set that name okay great  so now that we're done with that we can move on   okay so we're done with the patience so now we  can refer to it by name so we can do something   like with dot shapes and then we know the shape  name equipment because we've already named an   equipment pig now I want to display it right and  I want to display it in a specific location so dot   left what I want to display it I want to display  it right about here right in j13 so let's write   some code to do that dot left equals sheet one we  have to specify the sheet again because we're on   shape so that's really remember one with shape so  it was needed specified again shapes range j13 dot   left it want to do the same thing for top so we  can just copy that again to make it a little bit   quicker and then dot top equals sheet ones you  through deduct top okay so good now it's gonna   be the top left but we want to bring it down and  b