How To Create A Hotel Booking Application And Protect Unprotected Cells In Excel

hello this is Randy with Excel for freelancers  and welcome to the date range and cell protection   booking system we've got an amazing training here  we're going to show you this really unique cell   based calendar with automatic protection so you  can delete these formulas and have them return   automatically I've got a lot to show you in this  including automated tab order save booking forms   in this amazing room booking system so let's get  started all right thanks so much for joining us   today I got a really unique training we've got  a really a cool calendar that we're gonna show   you in fact I saw some airline websites where you  can automatically enter a start date and end date   and I really like the concept of highlighting  the days within the range so I wanted to create   that automatically so that whatever days are in  the range are automatically highlighted when the   calendar comes up and in this instance I wanted to  try a cell-base to calendar as opposed to a pop-up   calendar pop up calendars work great and I use  them all the time but I don't wanted to show you   something different there's a few advantages we  got a really nice highlighting the range feature   and this works particularly well when you want  to highlight a range between two dates so if we   select a specific date one it's automatically  going to be highlighted on the calendar and we   can also of course change the month if we want  a longer duration we can change it to November   first and you're gonna see that the entire month  is now filled out so I'm going to show you how to   create this calendar I'm gonna show you how to  switch months automatically we're going to show   you how to click on the current day of the day so  it's just a lot of features we're gonna show you   how we did all that of course we're gonna add in  an automated tab stop which we have covered before   so we're gonna automate the tabs and we're also  gonna show you how to create these new bookings   so that's a big promise we've got a whole lot to  cover in this very unique training so we're gonna   get right to it so let's start out with just the  basics of this and we have a really basic form   I'm not gonna go too much into detail on how we  create these new ones but it's very very simple   I will cover it but we're gonna cover quickly  because often in these trainings we do the same   thing over and over again but there's something  unique about these when we create these forms we   we do the same thing almost each and every time  but there's unique features that are not covered   in every other so we want to focus on the knee  features and in this training the neat features   that we're gonna focus on are is this calendar  that's really cool and also the ability to look   in this if I protect this sheet and click OK and  now it's protected you see I am unable to click   on let's say the room this cell d6 is protected  I can't click on it right but what I want to do   is I want to be able to protect certain cells for  example total nights this is a simple formula and   I can't click on it I can't see the formula and  all this formula is simply the checkout date - to   check it so but I don't want users to be able to  delete it I don't want them to be able to click   on it which is nice I only want to be able to  click but look at this calendar this calendar   is cell based we need user to be able to click on  a specific date to select it right so how do we   get them but yet of course there's a formula these  are just formulas in here so how do we get them to   select like if we select the highlighted right you  can see there's all formulas based in but what if   they delete it what if they cut that out and they  delete it how do we get that from coming back look   it came back how do we do that right so this is a  really specific training that's gonna really help   you on protection I'm gonna show you multiple  ways to protect the cell in this instance we   want the user to select some cells but not other  cells but yet we have some cells like this one if   we click the calendar we want them to be able to  select on a specific date or a specific cell now   this cell contains a date right contains a formula  that's made up of those dates so how do we protect   that we want to give them the ability to select  them but not delete them so if we cut this and   we delete it we need that information to come back  so I'm gonna show you exactly how we did that so   you can protect all of your formulas so that users  cannot delete them I'm gonna show you exactly how   to do that and there are many many ways that you  can apply this type of methodology protection when   you want to protect some cells and other cells  not so it's a really great training and so I   want to show you how we do that but let's go into  the basics and show you everything that this made   up this is just a one sheet application and for  our purposes we're using a little booking systems   so that we can enter a room number and we can  inter and we can inter some special requirement   test requirements and then we can just save the  booking so it's very very simple and it's gonna   save it down here when we select it it's gonna  show that booking above those are if you've been   following my videos that's a pretty basic type  of feature that we've done before in form we've   covered that a lot I'll cover it very quickly but  I want to basically get into two elements of this   training that are really important one is this  pop-up calendar that allows us to select the date   and when we select for example it's not going  to show up because the check-in date is before   is after but if soon as we check in date before  it's gonna highlight those cells I want to show   you how we did that how we created this calendar  the formula so we've got a lot to cover specially   when it comes to focusing on this calendar and  pop up calendars work great but I wanted to show   you something different I wanted I always want  to you may find an instance where to apply this   I've used shape baked calendars I've used pop-up  form based calendars and now we have a cell based   calendar so you know there's not necessarily one  best calendar really depends on what your purpose   is right so this particular cell based calendar  would not be good for all instances it takes up   a lot of room in cells you may not have that room  but it shows you the ability to highlight specific   cells and it's currently nice for that purpose so  every calendar every calendar every date picker   sometimes you would call them date pickers have  their own purposes so it might smite up to show   you all of the ways and you find you determine  the best way for your application that you build   or for your customers for the end users so we're  gonna go over that today alright so let's get to   it as with all my trainings we've covered the  first two columns this is for admin and you'll   see now this is protected I'm gonna unprotect  it but it's but basically the idea is we want   VBA to control certain things and hide them from  the users these columns of course will be hidden   so we'll unprotect it and we have the selected  row that's gonna always keep whatever row the   user has selected that's gonna change there we use  conditional formatting home conditional formatting   been over many times and then we highlight the row  we change the color and change the font based on   the grow and I also want to make sure that there's  a value in column D so we've been over that many   many times we also have conditional formatting  for alternate rows here blue and white that gives   us the table but it's only based on the data so  as soon as we add data here you'll see that the   colors based that the colors change the alternate  colors change and along with the borders we've   got a border here so that's based all on the value  in d so as soon as we put that in we can see that   and then selection so that covers that now this  new booking we need to contract if I have a new   booking I want to track that separately this has  to go to true I want to know which bookings are   new and which bookings are existing a new one of  course we'd be saved below an existing one would   be saved based on the role of that booking based  on this row 17 so if we change this in row 17 if   we change this to Davis and then we click update  save the booking it's gonna change it here so that   when we select something else it's gonna be able  to load it so we got to know that row 17 is the   one we're gonna be editing this is based on cell  map and you've seen that before we'll go over it   quickly so I also want to know when the booking  is loading when is it loading its loading when   we click something else so you're gonna see  quickly in b3 that as we loader to change the   true to false and there's some reason we need to  load it because we don't want anything to happen   other than loading the form for example if I make  a change to here I want this that kind of change   I want to pop a lot disk calendar to pop up and  also we're gonna use conditional formatting so   we're gonna go over that on the calendar so we  want to know when the booking loaded and then   the month I want to keep track of the in month  in month we'll be check-in month I guess I could   put a little bit more details CH K check-in  just so you know check-in month is October so   as we change this month of course that number is  gonna go so it's always gonna keep track and that   formula is simply a match formula now I have the  CI months that's the check-in month that's based   on this month here that is in of course j4 so if  we look here we'll see j4 scuse me J sick that is   the month so we need to know what is that so the  check in month is the months what is this month's   well that is this this named range right here if  we look here we highlight all of those we'll see   that that is the month though that's the name  range so all we're going to be doing for the   check in month is determining what row what month  it is and we can use this formula so in this case   September is the ninth month and it's gonna find  it right here so when we click on that we know   if we change that back to October this is gonna  change to ten so we're gonna use math we do the   same thing for the check out month except this one  we're doing CEO month now I've named these named   ranges so if we look at October we'll see that's  the CI month that's the named range check in month   I've done another named range here called for see  old month that's check out month so I've given   these named ranges and I've done the same thing  for the year year would be CI year check in year   that's important because we're gonna use those in  formulas and then another one here for Co years so   this is Co year so I've given those named ranges  so for both the years in a month so that for both   of our calendars that's really important and I  and again I want to know we're using conditional   formatting so as soon as I select here VBA is  gonna mark this b6 to true as soon as I click   it it's gonna change it to true and as soon  as I click out of it's false so if I were to   change this to true it's gonna show those cells  but not the button and let's just say it's very   quick because it goes back back so as soon as we  as soon as we change it but as soon as we select   something else it's gonna go back so I'll show  you that in VBA of course all right so there's   basically our conditional formatting is based  on these two cells this is for as soon as this   goes to true it's gonna show these cells as soon  as goes back to false it's gonna hide them the   same thing for the checkout month this of course  is gonna go to true when I select that calendar   and that is gonna help us with the conditional  formatting here and as soon as we check out of   it it's gonna go back to false hide those and of  course we also have a button set now this button   sets also going to be hidden and displayed of  course there's three buttons one that will you   help us click today a previous month next month  that button set is called the check out group   and I've done pretty much the same thing for the  check-in I've given it aside in the name group   these shapes and giving it a name called check-in  group because I wanted I also not only do I want   to show the cells but I want to show this group I  want to hide and show this group if we select on   the selection pane here and we slide this over  just a little bit we can see here if we scroll   down the existing button group we have here new  button if we like a check out group here so if we   if we select something else we'll see that check  and group is hidden if we select inside we will   see this check in group all visible same thing for  check out if we select here we're gonna see the   check out group as if it becomes visible selecting  outside it becomes hidden so we're gonna show you   how we did that in VBA it's just a very simple  hide and unhide it group so that is what these   two things are for they're used for conditional  formatting to show and hide the calendars and the   calendar fix this is gonna be helpful so when we  fix the calendar I'll show you what that is but   that is based on when a user deletes something  here automatically like so if we were to delete   any cells in here just by highlighting them and  deleting they're automatically gonna come back so   we can't wait to show you exactly how we do that  so I need to mark this between false and true and   I'll show you that prevents some looping and all  we have is basically a year's list and that's just   going to be a drop down list called SC years and  that's gonna help us in our drop-down list right   here that's a data validation and the same list  right here and also months of course is our months   list and that's going to be data validation here  and here so if we can see in data validation we   go to data validation will see that months is  there and of course for the years we will see   under data validation where you see that it's  been assigned to SC e or scheduled years from   breviary okay so we've we've gone through all of  the hidden these things will be hidden let's go   over this form and see just how we did it we've  got a tab order we want to show you we want to   show you how to new booking let's go over real  quick and show you exactly how we did the new   book into the developers tab here Visual Basic  of course you don't have the developers tab you   can go into the options they're going to the  customize ribbon and make sure you select the   developer here that's gonna get you it alt f11 is  also a shortcut that'll get you into the VB a now   let's take a look we've got a few different macros  and coding here on the sheet we've also got some   three different modules called booking Macker the  schedule macros and the tab macros that's gonna   automate the tab and of course the booking but  let's start with the booking now what I want to   happen I want to load this booking so all I want  to do when I select something I want to take all   the details from this table and bring them up  here and I would have used data mapping so what   I want to happen is this when I select a specific  I want whatever's in this I want to know what row   we've selected and then I'm gonna run a loop from  column four all the way to calm nine four five six   seven eight nine I want to go through this loop  and I want to take whatever is in row 17 and on   column D which is would be the fourth column and  I want to whatever is there I want to put it in   a four whatever is in column five otherwise known  as calling II I'm gonna take whatever is there in   this case 1245 and I want to put it in a 6 e six  would be right here so we're gonna just do that   and that's called data mapping I've shown you that  before we'll go over it real quick just so you can   get an idea we're gonna define two variables here  I really only need two variables we're gonna focus   on sheet 1 in this training we're only working on  sheet 1 we don't need sheet 2 or 3 the booking row   is long I need to know that Road in that case it  was 17 and I also need to know the booking column   why do I need to know the column because we're  gonna loop we're gonna go from columns number four   to nine so we're gonna run a loop we need to keep  track of what column and of course if b1 is empty   then exit sub what does b1 b1 is our selected  row I need to know that row if we don't have a   row then we don't have anything too low so we need  to make sure that b1 contains an actual row of all   the data that we're going to load into that so  that's important so back into the code we know   that it contains a value we can assign the booking  road to b1 and I want to set the new booking to   false we're gonna load a specific booking so it's  not going to be an booking b2 controls whether   it's a new or an existing booking so we want to  set that to false it's no longer a new booking   I don't want to set the booking low to true I just  need to know when all these fields are being added   to the form I need to know when it's adding as  soon as it when it's adding I want to make sure   that this goes to true and as soon as it's done  adding I wanted to return back to false so we need   that setting that's gonna help us also b3 we're  gonna set that to load up okay so next up booking   column we're gonna run a loop booking column four  to nine it just went over that with you and then   we're gonna what we're gonna do is we're gonna  do that we're gonna take all the data from the   row that we've selected and then we're gonna bring  it in to the field where is that field it's on row   14 that cell and the booking column again we're  looking for this cell e4 e 6 e 8 that's on row   14 gonna take whatever's there this is going to  equal whatever's here here and here so we're gonna   run that loop this line of code does that and  load all of the details from the table below on   the selected row to the specific form above we're  gonna run that loop so that's really simple loop   next up I want to make sure that there we have two  different shape groups why do we have to terminate   soup well I have one for existing let's take a  look at this shape there's three buttons in that   shape a new booking a say booking and delete  booking those three buttons are only used for   existing bookings if we click a new booking we're  gonna show a different group and this one has two   different buttons also say which is really the  same thing and cancel booking so this type of   group called new booking group is only displayed  for new booking so as soon as we cancel that and   get out of it it's gonna show the old one so we  have two groups two groups one existing booking   and one new booking so those are going to be  hidden or displayed based on whether it is a   new now if we select an existing we know it's no  longer a new one we know it's an existing so we   must display the existing booked and hide the new  group so we do just that but the next two lines of   code the new booking group here this shape group  of shape is gonna be false we don't want that   displayed and then the new one the shapes existing  we do want display and then the last line we're   just going to set be three to false that means  it's no longer loading we set it to true up here   and then we set it back to false here that's going  to help us when we go move forward because we need   to know the difference between the user making  a change and VBA making a change so that's why   we said the next up save and update we're gonna  do almost exactly the same thing just opposite   we're gonna define to the rows of the boot row the  booking row and the booking column and of course   we want to make sure that it is a new booking  save up baby if if it's a new booking we need   to determine the row would be the first available  row right if it's a new booking how do we know if   it's new B 2 is true B 2 will always show true if  it's a new so if it's a new booking I need to set   the row in this case to 21 which would be the  first available row if it's not new we're gonna   set it to this one whatever's in B 1 so we just  need to know whether it's new and determined the   row based on that so we do that with this if-then  statement booking row if it's true is gonna be the   first available row to get that we use D 999 and  Excel up bro plus 1 that is the first available   row else the booking rows based on B 1 so we just  need once we have the booking row we can then load   all the data using the reverse of what we just  did using the mapping this time whatever the   row is whatever the column is going to be equal to  whatever is in this cell how do we know what cell   is that mapping 14 remember column 14 and the  column it's going to equal whatever's in efore   it's gonna equal whatever's in e6 it's gonna you  know and so on and so forth so it's going to take   whatever is in a 4 and it's gonna put it right  here and it's gonna use this data mapping this   data mapping of course will be hidden in yours  when you want to hide data mapping you just change   the font to this and it's automatically hidden but  for our purposes it's kind of important so we're   gonna keep it visible but that's what you want it  you don't want it you don't want that map being   visible you can put it up here as long as it's a  row that you're not using you could pretty much   put it anywhere I just concluded above the table  so it's a little bit easier alright so we bring   that and then of course we reset the shapes it's  no longer a new booking because we just saved it   and so we're gonna hide any new and an existing  group we're also gonna set the new booking b2   to false and also the booking led to false so  that's it for save and update new booking is   pretty simple I'm going thick because we've been  through these forms quickly and it's not the focus   of this particular training but I do want to make  sure you understand what is going on new booking   all the way to do is clear out a bunch of fields  we're gonna set the new booking to true be two   must be true right we want to set the new booking  to true be - that's important right because we   need to know the differentiate so once we have  that true I'm gonna select efore that's the first   available name so I want to select that and then  we're gonna hide our shapes now we're gonna show   the new booking Group shape and we're gonna hide  the existing that's all we need to do so when I   right click here and I assign a master you'll  see that the booking new has been assigned to   that so you see so when we click that all the  fields get cleared out and efore gets selected   remember that's the first one because it's a tab  stop so this gives you user the ability to enter   a name very quickly and then tab over enter a  specific starting to enter an ending date and   now you just have the room and you have the room  type and then you have special requirements and   then all you need to do is save it so it's gonna  loop out automatically we're just easily to save   it and then when we click on it we can see that  the four days have been highlighted if we were   to change this to the fifth we'll see when we  click back on the calendar all the highlights   so that's great alright so now we know how to save  the booking let's continue with our booking macros   cancel new all we need to do in this cancel new is  select the first available row for example when I   click new I want and the user wants to get out of  the screen what do I do well the thing that I can   do is select this assuming it has a value which  is the first one so all we do is select that and   that's all I do you see that so that's the same  thing in the code cancel booking is gonna select   just this and it's gonna do the same thing so all  we need to do is first check to make sure d16 is   not empty and if it is empty then we can select it  of course if it is empty we're gonna keep on this   new booking where nothing we want to keep there's  nothing here so the only thing the user can do in   this case would be enter a new one we doesn't know  existing data so that's just one line of code in   this macro if sheet 1 D 16 value does not equal  empty then XI 1 D 16 select once we selected the   rest of the macros run how does that happen when  we click on bookings this is our on sheet macro so   what we're gonna do is we're gonna do selection  change now there's some macros here but the one   that we're gonna focus on is this one down here on  selection of any booking in the table if not d 16   through V 50 this is the table if the user makes  any selection remember when you see not and you   see nothing to cancel each other out that means  if you have selected or user has selected a cell   anywhere between d 16 and V 50 then do something  then what and D in the target row value does not   equal empty why do I have this because if they  have made a selection on anywhere where D contains   doesn't contain a value I don't want anything to  happen I only want something happened when they've   selected something where D contains a value  in that case do what run the macro called load   bookings in that case run the macro called load  bookings but before we do that we need to know   the target row if we're gonna load that booking I  need to know what row the user has selected and I   need to place that row right and b-24 two reasons  one to use a conditional formatting that I showed   you and two to know which row we are loading we  need to make sure to reload the row put the row   here before we actually run the macro that loads  all the data in here so that is how we get it to   load so back into the booking macros you'll see  that as soon as we select these 16 it's gonna run   that macro the next up is booking Tilley don't  want to be able to delete a booking but I what   I don't want to do is I don't want to delete a  whole row so let's say for example I have Sally   Smith one hour workbook is protected so we don't  want to make sure we can't really delete a row   because that's not gonna help us and I don't want  to delete an entire row because I don't want to   delete this stuff so how do we do that if I want  to delete this the best thing I can do is Laer the   contents clear the contents of this and then take  whatever's in here and move it up move it up ro   so that's just what I did so when we click delete  booking I guess I should add an option here delete   booking it automatically does just that I what  I'm saying is I should probably add on are you   sure you want to delete this booking but you get  the point so that's what I want to do in this I   want to take whatever is here and I want to move  everything up so let me show you just how we did   that so that's an alternative to deleting a row  sometimes we always cannot delete a row because   we have other things going on we don't want to  delete doggerel so we can clear the contents of   a specific bro and then move everything else up  so let's do that so the first thing we want to   do when we book it is we want to make sure we've  dimension what row we're talking about we need   them to make sure that we've actually defined a  specific row and then the last row I also need   to know the last row if we're gonna be moving  up data if I'm gonna be clearing this out and   I'm gonna be moving everything else up I need  to define what that last row is in this case   it's 20 so we need to make sure that we define  it so we can move everything else up so we need   to define the last row here and arrange of course  we're working with sheet 1 d 999 and Excel up row   this is gonna get us the last row of the table  so that's important we need to know that and we   of course we need to know the booking row that's  in B 1 next up we're ready to clear the contents   we know the row that we need to clear because it's  here defined in B once so range D and the booking   growth through V in the book you're it's gonna  clear the contents all that that's going to do   is gonna take everything from D through V and it's  gonna clear the contents to clear everything else   and this is the merge cells so we need to make  sure we cover the entire range of that merge   cell including the last cell next up what I want  to do is I want to check to see if the last row   if the row that we're deleting is this there's  nothing more to do right so if the deleting row   is the last row we don't have anything to move  up so we can just exit out so we need to check   is the current row the last row so that's what we  run our check on if the last row does not equal   the booking row then we can go ahead and move  everything else up if it does there's nothing   else to do alright so assuming that it is not  the last road what I want to do is I want to   take D and the booking row through V in the last  row minus 1 what is that equal that equals D and   the booking row plus 1 through V in the last  row what does that mean exactly so let's say if   we're deleting the second to last row let's say  you're actually let's say we're deleting bro 18   this is the one we're clearing out so we clear out  this row in fact let me just pause the code real   quick so we can go through this without anything  happening so we go through this row here right   what I want to happen is I want to delete that row  clear that out clear the contents then I want to   take this information here basically I want to  copy it for the most part and then what I want   to do is I want to paste the values and then paste  them here and then once the page of all I'm gonna   clear out this last row so that's just what I want  to do in VBA so let's do just that all right so   let's go ahead and reset this and then we'll save  this booking that's going to save it back in the   road you can put it back in this row that we just  deleted okay so let's see how we would do that so   when we delete it we want no want to do so we're  going to cool move the values up and then clear   the last row with these two lines of code so that  just does that so when we delete it it's gonna do   just that and bring everything else up one line so  that's how we delete it and that is all the macker   that we cover so the bookings let's get into  the schedule Macker but first I would quickly   want to show you the tab map cause we've been over  this before but basically when we do at a borders   all you need to really focus on is disk for the  most part e for l4 s for e 6 this is the order   in which my tabs are set so this is the order so  that is how we set the order so how do we do that   so this way tab tab tab tab tab again efore l4 s  4 e 6 e 8 and d 11 so those are the cells we want   but the only thing I want to make sure if I want  to run something which soon as we get to escape   out of here I want to set it to elf where I want  this thing but actually it's not gonna run it's   only gonna run when we exit out of afore so that's  exactly what I want to happen so when we exit if   the cell address is efore and we've clicked tab if  the cell address is e4 and we click tab them what   do I want to happen right efore what happens when  I click tap I want this calendar to show up how do   we get it show up we do two things one we change  this to true and I'll show you that conditional   form in just a moment - I want this button set to  show up so those are the two things that we do so   when we click back in here we see b6 equals true  and the checking group shows otherwise hide it   right otherwise if it's not efore hide it using b6  and hide that group hide that group I show you the   crib shape we're doing exactly the same thing for  l4 why is it l4 when we in l4 when ran l4 here and   we tab out of it if the existing addresses and we  click tab we want the same thing but this one in   this case b7 should go to true I'll show you the  conditional format of course and I want to show   this group so again this group check out group  so those are the two things that we're gonna do   when we when are we tap out of it and our address  was l4 so then through the code tabbing out of it   if the active cell equals l4 then what then b7  equals true that's gonna conditional formatting   to show the calendar and the check out group and  then otherwise if it's not b7 is false and hide   the check out group so this is the tab but how do  we get the tab to activate using this code I've   done this before I've got a video that focuses on  all of this automated tab so if you want to see   how this works completely just go to my video on  automated tab because this is how we're gonna do   that so that gives you an idea of the automatic  tab I've got a video that focuses just on that   but this one I want to show this calendar so let's  highlight this calendar here and if we go to home   can additional formatting and managers we've got a  selection of specific conditional formats that we   cover the first one the one that we're focused on  right now is b6 is false if b6 is false from what   do I want to happen remember I wanted to hide that  calendar so I'm gonna change the background color   to the same as the background color and I'm gonna  change the font color so I'm gonna change the font   color to that light blue I'm gonna clear all the  borders out by clicking none that clears all the   borders out and then I'm going to give it a fill  color of blue that and I'm gonna make sure that   that rule is the high up should be higher up right  then almost all the other rules so that it takes   precedence and we've done the exact same thing  for this calendar here except we're gonna cover   b7 so when we go into home conditional formatting  and manage rules we see b7 is false and it covers   p5 through V that is how we get that calendar to  hide and of course you know the shapes you see   how we get them decide okay but how do we get  this calendar to work oK we've used a formula   let's go ahead and pause this code so we don't  have too many things running on it I want to I   want to show this calendar and then I'm gonna  pause the code here just so we don't have we   can keep it hidden I'm gonna keep it displayed  so we have a formula here how do we get this   formula to work well the first thing I want to do  is I want to show you that formula I want to know   if the first day of the month is on a Sunday this  weekday if the year the year what year is it that   would be the checking year this year here based  on the month what is the month the month is them   one month selected right this is the month so we  want to look I want to look at this day so I'm   gonna say if day one if one day one here cuz this  is a date right anytime we have a date we need to   define the year we need to define the month and  we need to define the day so we're focused on the   first day of the year what I'm saying is if the  first day of the year based on the month selected   based on the year selected if it's equal to one  one meaning if the weekday what is the weekday   in this case it would be Sunday if it equals one  then I want to show that date I want to show that   date all I want to do is display the date but  I don't want to display the full date I want   to display just the one just the number one in  here okay so that means this one in this case   but what if it's not what if it's not one that  I what I want to show is I want to show whatever   the previous month day is so we can use that here  in this part of the formula here so if it's true   it's gonna show the date but what if it's not if  it's not that I want to determine what is the last   Sunday of the month the last Sunday the month so I  can do that by determining the last day using end   of month of what the date the year be 4-1 with b4  which of course is the month let's take a look at   b4 that is the month number right the month number  and one of course that's gonna get us the date the   end of month date and then minus one minus one is  gonna be the date before this is gonna get us the   last day of the month the last day of the previous  month but I don't want that I want the last day of   the previous month - subtraction the weekday of  the end of the month so I want the last one - so   for example if the last day of the month is on  a Sunday I want to subtract the number of days   and it's gonna pull that specific date up this  day it is 29 so for example it's two days before   the last day of the month this is one day of the  month this is one day of the month I've reset the   code and so now you can see so basically this  code I don't want to get too far into the code   into this formulas with you because we're gonna  miss a lot of the great features if I do that so   but of course you can download this free calendar  just use the links in the description of course   if you want to get over 100 of these workbooks  I've got a brand new bonus on that with 20 brand   new workbooks so you're gonna get a total of 120  workbooks for just $37 so keep that in mind I have   that available I'll include the links down below  so continuing on we have these formulas here so   now basically all we're doing is determining  if for example if the first day of the month   is 3 in this case Tuesday then I want to show the  date but the conditional formatting let's look at   it back in the conditional formatting but before  we do that I want to show what the custom I only   want to show the day on this custom cell format  only the day D is the day I don't want to show   the full date I only want to show the D that way  when we select it when I select it I can show the   the full date in here all I got to do is take  the value here and place it right in l4 because   remember in this case the format is the full  date but in this case we're only showing the day   so that's a great way to do that on this calendar  so basically what we're doing is we're testing to   determine if the first day of the week so that's  the first part and then after that all we need to   do is simply add one add one add one to get the  full date of the calendar now what about how do   we get this conditional formatting how is it that  this month is in white but next month is in great   well let me show you that when we highlight that  and go to conditional formatting and manage rules   we need to want to test and it's this test right  here when we edit that we'll see the month of i8   what is that what is I ate well I ate is the month  of whatever the cell that we've selected whatever   cells starting with I ate and it goes all the way  through and notice that I ate is not absolutely   what this means is it's gonna cover for every  cell in the table and the applies to as long as   the applies to also begins with I ate if it does  not equal before what is before before of course   is our month number for the check in before so  that means in this case I ate if we select one   I ate would be November so in this case it would  not equal it's gonna color it gray and we give it   that format we're gonna give it a little bit of  a gray background and we're going to give it a   font of also a darker gray so that's gonna give  us our calendar when it's not the month so when   we look at the applies to the applies to also  begins with I ate so as long as the I ate the   formula begins with I ate and it's not absolute  but it applies to also begins already then it can   apply to the entire your table so that's how we  do that green is for the current day you'll see   when we get to that and of course b6 this is for  another rule distance going to form out the right   border I'm just out of the right border onto that  it's because it's important why is that important   it's important because when when this calendar  is selected I need to make sure let's go ahead   and reset the code here when we select the next  calendar I need to make sure that the right border   here you see notice how this all I've hidden all  the borders here but I still need a right border   on this when disa selector so we need the right  border here I've done the same thing for this here   we need a right border so it basically that's  the conditional formatting when we click home   let's highlight the entire there and click home  and conditional formatting and we manage rules   and we'll see on this one includes a left border  this one contains the left blue border we just   want to make sure that when b7 is true that there  is a left border I want to make sure that there's   a left border all right great so we see how we  get that now when we select a specific when we   select a specific date how do we get the date  here in L well that's all in a selection change   so let's take a look at that back into the VBA  into the bookings and we have selection change   now if the user makes a selection so let's look  under selection change if they make any selection   in side of that table that is going to be here if  not I 8 through 13 and b6 equals true that means I   want to make sure that this is the active calendar  b6 is true and that means if they select anywhere   from I ate all the way over to a 13 I should be  I eight make sure that's by 8 ok good 200 13 then   what l4 equals the target value so let's look it  back anywhere click L 4 equals the target value L   4 equals the target value and then what happens  then something changes when we select there it   automatically goes to s 4 and then it shows this  calendar how do we do that well as soon as we   make a change to elf or something happens right  well that's unchanged worksheet change as soon as   we make it work she change to L 4 and L 4 value  does not equal empty then something happens and   B 3 equals false we want to make sure B 3 equals  false why does that because I want to make sure   that the change isn't loading what kind of change  why when I click here I also make a change to l4   right because we're loading the dates but that  kind of change this kind of change I don't want   anything to happen but the kind of change that I  do want something happens is when the user makes   a change this kind of change the schedule change I  want something to happen what do I want to happen   well I want to happen s4 select s4 select that  will select and then I don't want anything else   to happen so we want to end out of that I'm gonna  and out of that what happens when s4 selected well   let me show you when s4 is selected on selection  change you will then see or u5 or then want to   do thing I'm gonna do a few things I'm gonna said  b7 to true b7 that's going to use our conditional   formatting to show the calendar and I also  want to show the shapes the check out group   I want to show those shapes otherwise I wanna if  it's not if they have not selected one of these   3 y u5 + q6 because I want to make sure that if  they've clicked did u 5 here or Q 6 here I want   to make sure that the calendar doesn't get hidden  because when they select anything else it does get   hidden so when they click here or here I want to  make sure that calendar or here any one of these   three I want to make sure that the calendars is  displayed anything else here is going to be hidden   so keep that in mind so anything else is hidden so  when they select anything else b7 equals true so I   want to make sure now what if they've selected a  specific cell inside this calendar range anywhere   from p8 all the way over to V 13 if they've made  a selection change I want something to happen what   do I want to happen I want to take whatever date  they've selected I'm gonna place it in S four so   if they select here on up take that data and put  it in s4 so again if not intersection also under   selection change if not intersection PA to visit  nothing then b7 is true I want to make sure that   that is the calendar then asks for equals a target  value that takes the data and puts it in there   great all right so now we know how we get to date  we know how we load the form but how about these   various buttons we have the today button we have  the ability to select just a single day we also   have the ability to do previous month next month  how do we get those to work well let me go ahead   and show you that this time for each of these  calendars if we right click inside the group   of shapes here on the today we click the assign  macro we'll see that it's been assigned to a macro   called check in today so let's visit that module  take a look at just how we did that in those three   buttons there if we go into the VBA we see we have  some scheduled macros we have add various buttons   in fact they're pretty much the same we have check  in today previous month and next month that's for   the check in we have the same three macros pretty  much for the check out just different so we're   just going to go over one of them because they're  almost identical so the check in today right if we   know we know we have L 4 equals date now remember  if this for some reason older versions of Excel   date doesn't work you can just use a specific cell  like you can if you want to just equal equal today   right if it doesn't if you have an issue with date  or use older version just do today right and you   could say equals range be 23 so if you want to  do that I'll just leave that in for if you want   it so you can equal to cell that would work just  as well too because sometimes date doesn't always   work now it works also so what I want to do is  in l4 I want to put the current date that's very   important so we need to do that that's the first  thing right we want l4 to contain the current   date but I also want to check on the year I want  to make sure the year for example let's say the   user has selected 2022 and then we click today  again it's going to automatically go back to   the current year so we want to reset the call so  the current year so that's important and also I   want to make sure that the month is also reset to  the current month so it's so that everything gets   so it's currently October so if it if I select  August and I click today and then I look back   it's automatically changed back to October so we  can do that with this on the code j6 that would be   the month with equal range b + 9 + the month how  does that work month now right if it's currently   if it's currently October the 10th month if we  add B + 9 + 10 it's gonna get us B 19 well what   is it B 19 let's take a look at it B 19 is October  so that so we add we started out here 9 if it was   January 9 + 1 would be here B 10 so that's how  we can get the month using our list here to get   the month to appear here so that's all we do  today so there's three components of today one   I put the current date in here I'm gonna put the  current year here and I'll put the current month   here that's all this now how do I go previous  in next month now it's nice if I go to previous   next it's kind of normal all we're really doing is  changing this month number in b4 but what if I get   to the point where I get to January not only do I  need to go to December right check in month of 12   but I need to lower the year 1 so when we do that  go back I need to make sure that the year is less   likewise if we're in December we need to increase  the year 1 and then go back to January so there's   a few conditions on previous and next in check  in previous the first thing I want to do is I   want to check for what the current month is if the  current month is 1 then I need to do two things I   need to make J 6 December and I also need to make  n 5 which is the year which is the current year   minus 1 so I need to decrease the year increment  down let's just say down by one right down by one   and else otherwise all we need to do is just  subtract a month again we're gonna use B for   value which is the month number plus 9 minus 1 we  could do just as easily 8 but I wanted to show you   the difference there could easily do plus 8 but it  might get a little confusing why do I want to do   that because I want to show you that it starts on  here nine right like for example orange February   we're gonna add nine plus the current month minus  one so that's going to decrease so it just shows   you it just shows you how the math works if I was  going to do H it'd be a little more confusing so   that's how that's how we increment the month down  one and we do just the opposite for next month if   the current month is December then we need to then  make the next month January and then increase the   year by one otherwise were then adding one adding  one which is increasing so for example if we're   currently in October then we would add one Currie  increase it one up to November simple math and we   do all the exact same thing for the check out  calendar so each one of those three buttons are   assigned to one of these three Macker so that  is exactly how we get the ability to use those   buttons to quickly navigate wherever we want  to go so it's a really really great feature   I'm glad I got to show that part to you but how do  we get the amazing way to if the user accidentally   deletes it highlights it cells clicks delete how  do we get it all back well that's unchanged event   what I have done is basically taken a copy of  these all these formulas here let's pause the   code so we can see just what's going on make  sure not to delete it pause the code okay so   what I've taken you we know that there's formulas  here starting here these formulas here these date   formulas they're all formulas but what I want to  do is I want to duplicate these formulas I want to   make sure that if there is any deletion I want to  want these formulas to come back right if the user   deletes them I want them to come back so how do  we do that well we need to copy the formulas and   put them somewhere else so if I right click and  I copy those formulas and I put them somewhere   like right here and I paste those formulas in  we're gonna have a duplicate of these formulas   right here now that we have a duplicate of those  formulas what happens if we delete something here   if we delete something here I want to take all  these formulas I want to bring them back right   in here so that's all I need to do is if the user  makes any kind of a change in here let's reset the   code so we're gonna make the change if the user  makes a change and they click here and they click   delete I want that to come back I want these take  these formulas copy them right back in here that's   a backup formula it's a great way for protection  when you need to have the user select a specific   cell but you cannot afford to have them deleting  these formulas so how do we do that well that's   on change if the user makes any change between i  8 + V 13 then take these formulas here and copy   them back in to this and end so let's go over that  and see just how we did that so of course we're on   worksheet chains right where the user is actually  making a change so it's worksheet change on any   accidental change of the calendars copy back the  hidden formulas if not intersection target range   I ate through V 8 V 13 that is the entire range  that we just discussed and b8 value because false   now what I've done is I've set a little bit I've  set to prevent a loop when the user makes a change   when it's being copied back we're gonna set ba to  true and when it's done copying we're gonna send   ba to false if we don't put this in there it's  gonna create an endless loop back back back forth   because if we're making changes to the same cells  so this important this prevents loops so we want   to make sure that if in the process if in this  process of copying and pasting we want to make   sure that we set ba to true so that we don't get  a duplicate so we need to make sure that B it's   false all right so that's just a little check  that we run b8 is right here it just goes so if   you'll see if we do that process we will watch  maybe it's real quick it goes from true back to   false so that's all we we need to know so we  set ba to true and then all we do is we copy   the backup let's put this copy back up formulas  and then what I'm gonna do is I'm gonna paste   the specials into the main section I'm gonna paste  them right back in then I'm gonna cancel the cot   cut copy mode that's gonna remove those dancing  ants then I'm gonna select l4 and I just got just   weed this is not necessary but this is gonna show  that the calendar exists and then be a value goes   back to false we've set this back to false so  that it won't have any issues with the looping   so this way all we have to do is you just you can  users can delete this delete and then a come right   back so that is how we have protection in this  way once we protect the sheet we can have users   still select specific ranges within the sheet even  if they're not protected and still have it come   back and yet also for example total nights this  particular formula we kept trying to select on   and out I cannot select on it we could only select  on those cells that we want them to and we still   have protection so we still have the ability to  specifically have them select cells even when we   need to keep those formulas in there all right so  we've showed a really amazing trick we've showed   you how to protect even unprotected cells we've  shown you how to load and brand-new booking system   we've shown you how to create this really amazing  disk calendar show and hide a calendar date range   and also let me show you before the last thing  how do we get these highlights let's show the   last one the last one conditional formatting  and let's unprotect it here and show you the   conditional formatting and that would be the blue  how do we get the blue so all we need to do is   use conditional formatting and all I want to know  is if the date is between this date and this date   color blue if the date is on or after October  11th or on or before the checkout date in this   case Kember 29th that's not going to work let's  put in some real dates here so we can see how that   would work okay so now we have real dates so now  we can see beef on or before October 11th or on or   on or after October 11th up to on or before that  back out day we wanted to highlight it in blue   so when we highlight this and we go to home and  conditional formatting and manage rules we have   a rule here in to them blue here let's edit that  rule and so we have we want to make sure a few   things I want to make sure before I color blue I  want to make sure that one there's actually dates   in both of these values so that's the one I want  to make sure that and I also want to make sure   that the check-in date is before the checkout date  I want to make sure that that's very important so   l4 must not be blinking s4 must not be blank l4  must be less than or equal to s4 right so in this   case it's not right in this case when we click  here there's nothing why is that nothing hide   it because our checkout date of course is before  as soon as we change our checkout date after it's   going to show the proper cells so that's how we  do that now let's take a look back in here and   can go into conditional formatting and take a look  back in here so we have that we need to make sure   of course that the check-in date is less than or  equal to check-out date and we also want to make   sure that l4 is less than or equal to P 8 what  is that let's take a look at that l4 of course   is the date we need to make sure p8 is why is it  p8 because that is the first day of our calendar   let's look at that P 8 P 8 right is the first day  now P 8 if you'll notice that was not absolute   just the p8 no dollar signs because our range here  our entire range here back into the home applies   to also begins with p8 so when we look here our  range begins with p8 through V 13 so that means   it's gonna apply for every cell in that range as  long as p8 is not absolutely what else I also want   to make sure that s4 is greater than or equal to  P 8s form must be greater than or equal to PA that   means that this checkout date must be greater than  or equal to in order to color it so that's how we   color in the format is just a simple font of bold  and white and the fill is a dark blue so that   is how we do it that's how we get that amazing  date range to show up as soon as we've selected   something larger it's and it's gonna change  automatically based on that so it's a really   great way to see especially if you have like check  in and out or you have bookings of some sort that   land over expand over days it's a great way to  show and you can also probably show both of these   just by selecting and this gives us a way to show  both of these you can have both of them display   at the same time I've chosen to share just one  calendar because it might be a little confusing   but it also works also if you have in another  month so if we have a month of November 30 and   we highlight both we're gonna see that it covers  now from October 12th all the way to November 20th   so that might be a nice if you have long periods  you may want to show both calendars at the same   time so that's easy to program in alright I hope  you have enjoyed this training thank you so much   for the date range and cell protection we've  shown you how this really brand new and very   cool way to show date ranges and protect the cells  in them thanks so much and we'll see you next week

Channel: Excel For Freelancers

Views: 20,569

Keywords: Excel Pop-Up Calendar, Pop-Up Calendar Excel, Excel Date Rnage, Excel Date Range, Date Picker Pop-Up, Excel Date Picker Pop-up, Date Picker Excel, Excel Date Picker, Protecting Unlocked Cells in Excel, How To Protect Unlocked cells, Excel Booking Software, Book Hotel In Excel, Excel Date Pickers, Excel Date Range Calendar

Id: VuH2CuT7LDo

Length: 56min 32sec (3392 seconds)

Published: Tue Oct 29 2019