Create This Hotel Booking Application With Drag & Drop Gantt Chart in Excel Today [Free Download]

hello this is randy with excel for freelancers and welcome to the hotel booking application in this week's training i'm going to teach you how to create an amazing drag and drop gantt chart where we're going to use it with hotel but you can use it for any type of application it's going to be an incredible unique training i can't wait let's get started alright thanks so much for joining us today i've got a really fantastic training we're going to be using the incredible drag and dop functionality that maybe you have seen in other trainings we're going to go over it in a really amazing gantt chart we're going to create you can use this for projects renting out spaces you can use this for almost any type of application we're going to be creating a hotel application but you can use this for almost any type of industry so we're going to show you just how to do that i'm going to walk you step by step through all the code and every part i've designed just a bit of this on the screen that's going to help move this training faster but don't worry i'm going to go over every part of it a lot of the applications we do the designs from scratch but they tend to be much longer trainings and i want to get to really the focus i want to go through the scheduling i want to go through the application the creating the units the hotel the bookings and all the information that's going to help you create those applications for the design purposes i've already done that for a lot of applications so i just want to let you know if you do like these trainings and i hope you do i do my best i work really hard to bring you something unique each and every week the best thing you can do is just three things like the video below make sure you share this application of course comment below i create these every week for you and i always respond to each and every comment so if you have any questions concerned or just want to say hello comment down below i'll make sure to respond within a day or two as i make sure to make that a part of every my day you're important to me you're following your subscriptions really means a lot so i appreciate that i want to make sure that you get this application to do that all you need to do is click the links down in the description i always make these applications free for you you can use your facebook or email to get that delivered right to you within just a minute or so if you want over 150 applications i've got a whole zip file you don't need to download them and it includes an application library so that means you can click single click to open the application single click to view the associated training videos i put all that into compiled it for just 37 cents a workbook that means 56 dollars for us dollars to get this 150 my best applications so if you like this and you want to support our training each and every week you can go ahead and pick that up i'll include the links down below for that as well all right let's get started we've got a lot to cover on this i'm going to try to let's see if we can get this within an hour it might go a little over this time let's see how we do helping taking care of the design elements is going to save us a lot of time on this so let's go over what i have and then we're going to go over what we want to do and then we're going to get right into it and show you exactly what we're going to do so the focus of this is to basically allow a user to create additional bookings for a hotel so we'll have a list of rooms here and then the associated dates will have a month obviously month and year here using previous and next buttons can allow us to navigate previous or next months users will be able to create a new book and clicking on that that's going to clear all of these fields i set up these fields before and i've got also got a pop-up calendar that's already associated with this pop-up calendar we've gone over before but that's okay if you download this application you can get it for free of course in this application you can use that in any application as well okay so the idea is really simple the form basically now all the bookings are saved in a database here and a booking database is pretty relatively simple i've kept it really simple i've got a booking id a booking date the guest name the room room type status check in check out number of guests and special questions something pretty basic but it should be sufficient and the only other thing is we have a list of rooms now the list of rooms are also we have a room type and the idea we want to room type is maybe we only want to show in our list here certain room types maybe a guest is looking for a deluxe or city or penthouse or something we can filter it that way we only see the availability based on the room type so i thought that would be helpful so if you don't you can see all rooms or just a filter so let's take a look so that's all we have is room now we also have some named ranges that i've set up before so basically all we're going to do as far as the room type is we're going to set up an advanced filter here and then the results of all of that here so we're going to go through that let's take a look at some named ranges that i've created already so we can go through those there's only a few of them so not too much we're going to go to the name manager and now we have booking checking so we have some bookings based on the bookings here booking check-in is a list of all the check-ins based on all the check-ins and that's going to be a dynamic named range and that means as this list grows so does that named range and that's going to be based on just the check-ins pretty much the same thing for the check out we have all the checkout dates in a named range called booking checkout and the reason we want these in a name range is because we have to check for conflicts so later on i'm going to show you a formula a sum if formula it's going to allow us to figure out if there's conflicts so if there's conflicts we're going to make sure to pay special attention to those so the user knows that there's a conflict schedule booking id is just that it's a list of dynamic booking ids based on that and then of course the rooms also so i have four named ranges for a booking database booking id room check in check out most of that's going to help us the room the check-in checkouts can help us look for conflicts so that's the reason what else do we have we have uh some named ranges associated with the pop-up we're not going to go over this the ex criteria in the ra the criteria and the extract are based on uh created by vba when we create the advanced filters so if you see these in your workbook you don't know where they came from every time an advanced filter is created one of these are created on a per sheet basis notice we have one for rooms and one for bookings criteria for each and extract for each so that's where that came from we'll go over that a little bit now the room numbers i've also got a dynamic list of room numbers and that's based on the room database here so just three databases excuse me just two databases rooms and bookings so that's it rooms is and we have room types also so rooms and room types pretty simple stuff nothing too complex as far as the database is concerned and i have a drop down list we want to drop down let's have room types with all and what does this mean i want a list of all the room types and i want to include the words all right why do we want that we want that as a data validation i'm going to place that data validation right in here so that we know we can select between those and i also have a status let's take a look at this the status i want to know the status pending book to check in checked out so we can assign a status to every single booking and why is that important it's important to know and then we can also color code those statuses and that means the shapes those drag and drop shapes that we're going to use for scheduling can have different colors based on the booking status and they're dynamic what does that mean that means as a user or you as a developer change these colors so will the colors on the sheets very very simple so instead of programming it and this is really great because theoretically if you had a pop-up color picker which they're pretty common in vba you can add your end users to create their own colors so that means that your end users would select a specific color and then that's color would be in the cell and then the shapes will take those cells and i'm going to show you that within vba we've gone over that in a task scheduler this is going to be really amazing because this is dynamic drag and drop gantt chart so basically this type of training you can use this for projects you could use this for tasks you can use this for not only room booking but let's say you're renting out equipment or let's say you're have a meeting room that you want to rent out so basically any kind of asset that you want to rent out this is perfect for on a daily basis on an hourly basis you probably want to make some more changes but on a daily basis this is perfect this can be customized for any type of asset rental a hotel room is an asset that you're renting out based on a per night basis so whether it's a meeting room or anything or even a event hall or something like that scheduling is great and the ability to change dates or change length of dates is super simple we even use drag and drop so you can extend to stay simply by dragging the shape very cool stuff very advanced excel so we're going to get into that and i'm going to walk you step by step so don't worry about that so we have colors associated with that and then we have a start date that start date is going to be based on right here based on the august that's the start date of that's going to be dynamic based on the month okay so that's pretty much it for our named ranges in the manager so we're going to go through step by step so what do we want to happen well basically here's what i want to happen i want to do a lot of things when i select on a shape and the shapes are going to be extended across here as long as the booking i want that information to load the booking when i create a new booking i want all the fields to be cleared out and then i want the user to enter the room number which is of course a dynamic named range already said based on our rooms number room numbers here as we went over before check-in is the pop-up calendar check-outs will be a pop-up calendar so we can enter a date number of guess is just a field not too important on this one just basic status is based off course on our data validation that we just went over located on statuses which is right here and then we have some special instructions or special notes that's going to get saved in the database the save booking this booking here is only going to be visible when when on a new booking everything else so this group here this group of buttons here that i've set up these are just shapes and icons nothing else there's no macros assigned to it we've created these in the past and all just if you want to create your own buttons all you need to do is just insert shape here all i did was use in this case i'm using a square shape and then i've set the format here to just this one that's all i did i added an icon and group pretty simple stuff so nothing too complex so i don't want to take too much time going over that so the idea is this group here this group of buttons has been called existing group and it's used for existing bookings bookings that have already been saved this button here this button is associated with a new booking group this button will only be displayed for new groups the reason we have two groups so that we can actually place them in the same location just like that because only one of those is going to show up at the same time either the existing or the new so we can place those over because only one will show up so we can do that i just wanted to be able to show you both no macros have been assigned to them yet so we want to make sure bring it up a little bit so we can actually assign a macro to that and then we'll place it in the lower so the idea is basically once we have all the information we do have some required fields like guest name select room check in those are required fields and probably status all those are required once we assign those i want to take that brand new one and put it right in here in a brand new row into the bookings if it's an existing if it's already existing we want to then update whatever the existing is and then once it's updated i want to update the schedule here so every time we save or update a hotel booking we want to make sure that that schedule gets updated all right so we've got a little bit different information very very simple here not a whole lot of things going on i want to differentiate between a new booking and an existing booking when we click on a booking here it's going to load that information up here that would be an existing booking is so we want to differentiate we can use true or false to know whether it's also i want to know what booking id we selected if we select a new booking booking id is going to clear out because there is no booking but if we select any kind of a booking here when it's here i want that booking id i want to know was the booking ideas one two three or four we only have four right now i want to know which one because if i'm going to load all of this information i need to know what the id is not only do i need to know the idea i need to know that booking number four is on row number seven so how do we do that we use just a match for that and it is located right here i'll go over that in a second let's go in order selected shape when i select a specific booking i want the name of that shape to show up right here that's so we know we can work with that shape once it's displayed there we can work with it okay again i want to know the booking row what rows it if row number booking number four is located on row seven we can use match for that we wrap that on iferror always we don't want any errors to show up in these cells because once vba takes a look at the cell if there's an error vba will return a bug so we want to make sure that we always wrap that on if air so what are we going to do we're going to use match and we're going to match based on the booking id booking id is located in b4 and we're going to match it within the named range called booking id that's the named range i just showed you and we want an exact match so we're going to use zero we're going to add three why are we adding three i'm adding three because the first booking starts in row four right here so if if i get one it's going to return if i just use match and i put booking number one in it's gonna return one because it's the first match but i don't want one i want four i want the row number so all we need to do is add three to that so then we're set up okay so assuming that we've added three if there's an error we're just gonna blank spaces so that's going to get us our row number that's very important because we need to load all the details in here and we need to pull all the details include the booking date and everything else from that so how do we pull it we know the row number but how do we get all the information in we're going to use mapping for that you've seen this before and we're going to map just a certain number of cells if you've followed my videos but i'll go over with you and basically this cell j2 is mapped to guest name and i mean map what do i mean by that j2 is right here j2 guest name so all i need to do is determine the row then let's say we found row 5 booking id 2. terry tester where am i going to put terry tester i want to put that in j2 right here so what about the next one if i want the room 105 where am i going to put room 105 i want to put it in ae2 and this helps us out a lot in coding because all i need to do is loop from 2 to 10 or whatever or three to ten in this case and i just need to pull every time i get the row i've got the row i go through the row i find what's here and i place it in here i find what's here and i place it here so that's data mapping that saves us a ton of time we can do the same thing we use the same data mapping whether we're loading information from the database into our form or we are saving information from our form into the database whether it's a new or existing the reason we don't have mapping here is because we manually put these in i only want to put these in manually these are fine because booking date's not going to get loaded and i just want to know what data was booked that's all i want to do there's no place for a booking date here i just i didn't put that in i guess you could put it up here some other place but i didn't put it in i just wanted to save in the database it seemed unnecessary but i wanted to put it in just in case if you guys used it and so basically what we're going to do is i'm just going to bring all this information in here based on whatever information and bring it back into the form so it works both ways sending saving the form data into the data into the database and bringing the database form so that's what we're going to do we're going to start writing code in just a moment i wanted to go over everything that we need to do so basically what do i want to do i want to have the rooms the bookings show up in a thing but the first way we need to do is we need to get this information into the database that is really important so let's do that let's write some code to get in some information inside the database so i'll let's call this save i'm going to save it i save or update we're going to use the same macro to both save and update and what does that mean that means this update button is going to be tied or this saved booking both those are going to be tied to the same macros called save update let's put in some information first so we can see that lisa leslie and we'll put in some information let's say we already have rooms let's say we're on room 106 and we're going to give her a check in of keeping the month august 3rd all the way check out to let's just say august 6. and we'll put number of guests to and then their status we can assign any status i haven't automated these status but it would be nice to automate them and then we can just put in special requests so let's say um big bed okay so now we have that so basically what i want to do is now i want to write a macro it's a relatively simple map to take all this data assuming it's a new hotel booking and place it inside this table so how do we do that well let's go into vba and write a small macro that's going to allow us to do that how do we do that well into the developers tab we can go into visual basic to get there if you don't have this alt f11 will get you there but you can also get that developers tab if it's not visible for you into the customized ribbon just make sure developers checked right here okay so once we have it there you can go back into the developers and i've got just some basic framework about i didn't write any macros for you because i want to make sure to write those along with you so you can watch me as i create this and as i bring life into this application right now it's just a design phase but we're going to bring it to life and give it functionality and features every step with you so i've got pop-up calendar that's the only information that i've written this is a large large macro all you need to do is pretty much drag and drop this module into your application we're not going to go over that i do have specific trainings on some of the features on this just type in pop calendar date picker on my youtube channel you can find more information that this training is not focused on that pop-up calendar but just one these macros allow us to create this really beautiful shape-based pop-up calendar which you may like i created that a few years ago myself all right so the scheduling max we have scheduling max we're going to get to that second but basically what we want to focus on right now is the booking macros so i want to write just four macros we're going to write one macro to load the information one to save it or update it one to new and one to delete let's focus on the save and update and then we'll get to the booking load so let's bring this down here because i want to get to that next and then we'll focus on the save and update now i've dimensioned a few variables the row booking row as long and the booking column is long and the selected booking is the shape so those are the variables that we're going to use within these macros it's not so many again just a few of them so the first thing i want to do is focus on save and update the first thing we have to do is we have to make sure that we have some required fields that those get required we want to make sure the users entered at least the basic ones concerning like j2 which is our guest name a room number that's required and we need check in and check out dates without that we can't save a booking so we want to make sure that we least have that the minimal information so we're going to write some and just call this check for required fields so that's the first thing we want to do and what fields are we have yeah we'll go we're going to focus on which sheet one so that's the first thing first thing we want to do sheet1 we want to make sure we're setting that so if dot range j2 that's the name we want to make sure j2 has a value j2 value equals empty or there's more what other fields let's just copy and paste this it's a little bit quicker or what we also have af2 what is af2 that is the room nut select room we need that so a f2 we gotta can't have that empty we also have what is this one this is called ax2 we need that that is our check-in date so let's do ax2 x2 and then at least one more we do need at least two more bi 2 this one here bi2 we need to check out date so we have to make sure that that is required so let's put in b i2 that can't be empty and also i do want to make sure that um that there is a status because the status are based on color so i don't want that to be blank as well so let's make sure r4 is or let's put in that once more and write in then so that's going to automate so then we'll change that we're going to update that to r4 instead of j2 okay so now we've checked for all so what happens if if any one of those are empty we need to let the user know to make sure that they have actually entered wrong so let's do that message box let the user know please please fill in all required fields before before saving this booking okay so we just let them know and then we're going to exit the sub nothing we can do unless those fields have been required now assuming that they have been filled and we can continue on the first thing what i want to do is i need to make sure again we need to run another check i can't have a checkout date that's on or before the check-in date we need to make sure it's actually after so let's do that right now if dot range b i 2 right bi 2 that is our checkout date dot value is less than or equal to is less than or equal to dot range ax that of course our check in date a x 2 if it's less than or equal to there's a problem then we need to let these know then what do we want to do message box please make sure the check out date is after after the check in date okay that's important just let the user know and exit the sub can't have we can't have a check in date that's on or we can't have a checkout date that's on or after the check-in date okay so now that we've covered and then let's let maybe we'll select one cell so we can do dot range bi2 dot select so what we're going to do is we're going to select that for them so that they can put in quickly the pop calendar will show up automatically as soon as they select it and i'll walk you through the code on that okay so then we're going to exit nothing we could do now what i want to do is i want to determine is it a new booking or not right so how do we know if it's a new booking this b2 is going to let us know if it's a new booking because as soon as we click the word as soon as we click the button new book inc when we write that macro we're going to change this to true so b2 we need to know is b2 true or not so if dot range b2 dot value equals true then we know it's a new booking what else can we do there's something else that's even better than that if booking id is empty then we know the booking row remember there's an error so why don't we use b8 if b8 is empty that's even better so b is empty then we know it's a new booking so we're going to do that b8 equals empty then we know it's in then then new booking okay and if and then we'll write an else in here okay so what if it is a new booking if it's a new booking we need to assign a specific row what row are we going to do booking row remember we've already dimensioned the booking row up here as long so the booking row is going to be equal to that's the auto hotkey that helps me automate that text in case you saw me type it in it's going to be sheet 2 a and then that's the that's the last row of the value but i need the first available row so we're going to do plus run that's the booking row that's the first available row in sheet 2. that's where bookings are stored so once we know the booking row what i want to do is i want to take the next booking id how do we know what the next booking id and that's going to be right here we're going to use a max formula what i'm going to do is i'm going to take all the booking ids make sure if you're going to use this we need to make absolutely sure that these numeric okay they can't be alphanumeric they can't contain any letters they must be numeric the next idea the max of these plus one is going to be the five so that's exactly where you we're using the max functionality and if there's an error if there's no data there would be an error right there's max of nothing so if there's an error in this case i want to put one that would be the first booking id just in case there's no data at all so that's going to set up right in this case our next booking id is 5. so we can take that i want to put that in two places i want to put that right here under booking id and i want to place that right here in our first available row and i want to put it in column a so we're going to do that right now within vba i'm going to place in both of those locations so dot range b4 value equals dot range b9 that's going to set the booking id b9 value set booking id that's the first place i want but what's the second place dot range a in fact this should be sheet 2 dot range a and booking row which we just created equals in this case of course b9 value so we can just put b9 we can also copy this pic copy and paste this right here we're going to place it right in here so that's it so we're going to place it in both of those locations once on sheet two and one on sheet one so that's if it's a new so we've done that so what else do we need to do i want to set up the booking date i want to know when it was originally booked and so we're going to place that in b in this case but only for new bookings so in this case we're going to copy and paste this i'm going to update the a to a b then i'll put in now equals now and that's going to put in the current date and time so just put in current current i'm just going to put in current date and time okay so that's it that's pretty much that's all i want to do for a new booking what if it is an existing booking then we already know the row so we got to set the row if it's an existing booking we know the row is going to be located right here into bh so we can set that right now booking row equals dot range b8 in this case b8 that's the booking row existing booking row we need that to save the data because everything else we're going to add automatically so now that we've set the booking route now what we can do is take use our mapping and run a loop in this case it's going to be 3 to 10. column 3 all the way to column 10 how do we know this is column 10 equals column 10. we know that's column 10 so i want to take that and i want to take all the information from whatever we have added here and i want to put it all into here including the standard view this what is this let's take a look at that before we go over that is a formula i need to know based on room 106 what i want to do is i want to look up room 106 and i want to determine what the room type is i want to take that room type i want to display it right here using this formula and i want to take that room type and i want to add it into the bookings right into here now keep in mind that this this particular field only because it's based on a formula we are going to bring it one direction and what i mean by that from the form into the table but not but not from the table into the form why is that because it's based on a formula i don't want to replace this formula because that way if the room changes so does the view so does the information the details about that the room type so how do we get that what we're going to do is we're going to index room types that's the named range and what is the row we're going to use based on the room numbers so we're going to be indexing all those room types we're going to be basing it on the room number whatever room number has been selected in ae2 and we're going to use just column number one if there's an error we're going to keep it blank so that's how we determine the actual room type and that's going to be helpful when we want to show only specific room type this is a dynamic drop down list based on this list right here all room types it is simply based on this information right here including all room types this is a drop down list basically here so that's it so that's all we have there okay so let's get back into our code and show where we were so now we have the row number now we can set our columns so we're going to create we've already created a name branch called booking column here so let's write in that so 4 booking column equals 3 to 10. we know we have to loop make sure we close our loop next booking column okay so once we have our loop it's really quite simple what do we want to do i want to take the information from our form and place it into our table so how do we do that well we can do that just very simply sheet to that's our booking dot cells we're using cells because both the column and the row are dynamic what is our row the rows booking row what is our column booking column dot value so what is that going to equal equals based on our dot range wise we've already said dot sheet one so where we don't need we're already specifying sheet one so we just need to use dot range now what is the range where are we gonna find that range i need to find that range the range is located right here in row one and in the variable column three four five six so we know the range so all i need to do is extract that range from row one right here in sheet two so how do we do that we do that just like this range equals sheet to dot cells using cells again rows1 column is booking column dot value so this right here this value is the range so that range so dot value here again is going to place that value place whatever is in this range and place it inside this row as we move through all the columns so that's it that's all we need to do and then all we need to do again is update the shape so now that we have all of the data from here we know it's not we've we got to make sure that our new booking group is hidden and that our existing booking group is displayed those two groups we need to update to make sure that the user knows that now they can update it but it's no longer a new booking so dot shapes new booking group dot visible equals mso false it's no longer a new one so we need to put in false what about the other one the other one we need to make sure it's visible dot shapes existing booking group dot visible equals and so true we want to show that show existing button group and then hide the new button group hide new button group okay so we've got those that's very important and again we also want to make sure it's no longer a new so we want to certainly make sure that b2 is set to false so we can do that here dot range b2 dot value equals false okay so that's it let's set up set new booking to false no longer new booking so we could set that up okay that's it that's all we need to do and then what we want to do is we want to actually run the macro that we have not created yet called scheduler fest so after after i created i want to update the schedule right after i add i want to make sure that that booking assuming that it's in this month appears on this schedule so we need to make sure we run that macro we're going to create a macro called schedule refresh and so let's just write that in here so we don't have to come back and do it schedule refresh now i've already written just the type of the macro here i've just written the header so that's going to automatically update and it's going to be right here but there's nothing in that macro i'm going to write it with you there's nothing here yet so just keep that in mind that's what we want to do okay that's it that's all we need to do let's of course save our work and let's assign that macro i want to assign that macro to 1 save booking so i'm going to right click assign the macro booking save or update and i'm going to do the same thing for our update button so our update is two parts it's the icon and the button so holding down the control right clicking and click assign macro we're gonna do the same exact thing booking save or update okay so it doesn't matter what button we click and that actually let's just move this down we want to make sure it's in the right place before it gets hidden and so all we need to do in this case is just click that button please fill in all the required fields and that means one of the fields we have we'd better double check that one of the fields that we have is not correct this should be a e2 not f 8e2 okay so then we're good let's go ahead and update that there we go let's take a look in the bookings now it's all the information the date the name the room this looks good eight three to eight six two guess and bigger okay it's all been saved table perfect so now we have it let's write additional macros i want to write a macro that says new so as soon as we click this button all the information gets cleared out and the brand new one appears so let's do that right now that's under new booking so we're going to scroll down here and here's my new booking and let's take a look at that and just write in a few macros it's not going to take very long in fact i've written it before because there's a lot of fields to clear out so i just wanted to write one of the lines so first thing we want to do is set the new book into true so with sheet one we're always focused on she wanted this dot range b2 b2 is our one that lets us know whether it's a new or not equals true set set new booking to true and i want to clear out a bunch of fields a lot of fields so that let me go ahead and copy this it's going to be i've got it here just to save us some time so you don't have to sit and watch me type in all these fields so basically here is it here's all the fields that we need to clear out it's a lot and i usually get it wrong so i wanted to make sure i get it right so basically what's going to now keep in mind that when you clear notice that these are merged fields so when we clear this out we need to make sure j 2 through x is cleared so j 2. you want to clear out not just the cell and individual cell but make sure each individual j2 through x2 a e2 through ai ax to b size so i've cleared out all the fields within that that's all we need to do and then again we want to update the shape sets in this case what we're going to do is we're going to copy these shapes then we're going to just change over we're going to alternate the true the false so this one is going to be true in this case we want to show the new button group and in this case we want to hide the existing because it's a new one so hide the existing and this one's going to become false okay great so that's pretty much it a very very simple very very easy macro and all we need to do is assign this to the button so we're going to click on the button holding down the control clicking on the individual shape and the individual icon right click assign macro and then just make sure it says booking new click ok now click the button and that's perfect that's exactly all the fields got cleared out this is for filtering the schedule so this is not part of it and the word saved booking is perfect that's it okay what about when i want to load a booking now let's write in that when i load a booking i want to make sure when i click something it loads but there's nothing to click we haven't created a schedule so what we're going to do is we're going to update this schedule we're going to create the buttons on the all of the shapes on this schedule before we do the load because that we can't click and there's nothing to load because there are no bookings here displayed yet so let's get those bookings in let's create that first and then we're going to show you exactly how we do that now let's update a little bit here we have a shape here now basically this shape is called sample shape all it is is simply a square shape that i've created it is this shape that's going to be the foundation for all of our shapes in our bookings every time we refresh the schedule i want to delete all the shapes within this within this region here and i want to create brand new ones and it's going to be based on this so what i'm going to do is i'm going to take this shape called sample shape and i'm going to duplicate it once i duplicate it within vba i then want to position it color it and show it up and assign a macro to it and this macro is going to be shine the macro's already been assigned to make it a little bit easier what macro is that there is no there is no code inside the macro but i wanted to show you now there's a macro that we're going to use called select shapes when we go into the schedule and we look down here we have something called select booking that is the macro that i want to sign because when i select that what do i want to happen let's duplicate it again and bring it up here so i can show you once i duplicate i'm going to have a booking and you'll have all the information here you'll put their name in when i select it i want that information to load up inside here i want it all to load so how do we do that we're going to show you the first thing we need to do is get these shapes displayed with just a little bit of a macro to do that we're going to call that schedule refresh and it's going to put take all of this information all these bookings here and place them right on our schedule here based on shapes it's going to be really really great that way the reason we're using shape is because when we have a booking we can drag and drop that shape anywhere we can change rooms we can change dates we can change durations really amazing and it's not that much code to do it so i can't wait to share that with you so let's get into it let's focus on the schedule refresh because that's the one that's going to build out all the shapes so i'm going to take you step by step through that so you can see exactly how we are going to do that so the first thing we want to do is to make sure we're going to focus on with sheet 1 as we're dealing mostly with g1 with sheet one now we've defined some variables let's go over just some of these briefly the last booking row we need to know the last row that's the last row here we're going to run it through an advanced filter now i need to know the advanced filter it's going to be based on what it's going to be based on this date august right august as we click previous this is going to change for the previous month or next to the next month but basically it's all going to be based on this called the start date this is the named range i created based on this and this is basically august 1st but it's been formatted for just the month and just the year but it's actually august 1st how do i know that well if i just take another cell let's just take let's just take this one equals and then i change this to august 1st you're going to see that it's automatically august 1st you see how that is based so it's just a formatting it's been and how do i get a formatting that look like that it's a full month it's a custom format if i go into more numbers we're going to see that it's a full month which means 4 m's and then the space and then the four y's which means they're all four digits so it's a custom format but it's actually august first and so that's important because that's going to help us so what i want to do now is basically i want to take all this data here and i want to determine if there's any check out date that is less than or equal to inside the month or check in date that's less than or equal to then i need to know that so basically any booking inside the month even if it's a checking out even if we're checking in before the end of the month but checking out inside the month or likewise we're checking in in the month and checking out the next month even that i need to know so how do we do that well that's an or condition or condition and basically when we have dates we're going to create a criteria in advanced filters so i'm going to take all of this data and i'm going to run it through this filter here and basically based on dates but these are numbers right how are these dates this is the what i want start date notice it says start date that's the date that we've defined automatically so if it's great if the check in in this case is greater than or equal to the start date and these are and and or so or is here or is the second line and an and so if there's a check-in date that's greater than the start date and a check-in date that's less than the end of the month how do i get the end of the month all we're going to do is we're going to take eo month based on the start date and no months i've had in no months before that means the current month 0 means the current month so the end of that month in that case it's of course it's august 31st so any checking date within that month that's fine i want that or any check out date within the month any check out date notice we're focused on checkout or checkout date check in so remember this is and right here and this is or or and keep that in mind or check out and to check out here so what is this a checkout date that's greater than or equal to start date or a check out date that is less than that means basically all i'm saying is a checking a checkout date within the month or a check-in day within a month that's a simple way of saying it this criteria is going to handle that then what i want to do is set the criteria advanced filter that criteria in fact if there's a filter on this i need to know what if we want to show only certain room types not only do i want to show but in this case we're going to show all rooms but what if i only want to show deluxity for our standard view if i only want to show one of those in that case i need to have additional criteria so how do we do that we can do that with a formula because it's not going to change so let's take a look at that if or the schedule ai6 that is that is the cell that we're focused on the schedule equals all rooms or it's empty in either one of those cases if it's empty then what i want to do is just double close i want to show empty under the room types any other condition i want to show the actual room type so that way when a user changes might give me a bug so when a user changes this i want to show that room but i want to show it both right because it's both this and this anytime we have multiple criteria in an or scenario both of them must take on this room type so notice i have the same formula in both cells that way they both respond this way only when i run our advanced filter from this data from n 2 through r 4 all that information is going to be person and the results are going to come here then what i'm going to do is i'm going to take all this data i'm going to convert it into shapes and i'm going to bring it right back into the scheduling based on shapes okay so you understand what's going on let's bring this back to all so that's going to show no room so we can show all the data all rooms here and there we go so now it's blank again so that's what we're going to do inside the macro so i want to run that so that is exactly why we have the last booking row we run the advanced filter i want to know the results row so we need the last results row in this case the last results row is six but of course we need to loop through starting at three going all the way to the last results row looping through those we also need to know the result row as we loop through each row we need to keep track three four five six so i need to keep track that's why i've got a variable called results row i need to know the schedule row that we're going to be placing it on this is the schedule row starting in row nine where is it going to be placed i also need to know the column notice that with day one these are days i should have told you that earlier these are days one i guess it's kind of obvious as we go through the months so each day has two columns why is that because theoretically a check out will appear here a check in will appear here because it takes half a day so if you have a guest checking out on two and another guest checking in in the same room we can have the same room on the same day but so the check out contains the second half of the day the check in contains the first half of the day okay so that's why we have that and that these are also based on dates notice it's based on the start date again and this is just the start date plus one but what about when we get to february we get to february we want to show 8 28 but maybe not 29 maybe not 30 maybe not 31 so those are special instances so i gotta write additional formula to display that i don't want to display 29 you know if it unless it's a leap year or less in february or unless it's a month i don't want to display 31 if the month only has 30 days so how do we check for that well the best way to do that is just to determine if we could say if the month of this is the same as the month of this plus one in other words 28 plus one is it the same month or not if we do this 31 plus 30 is it the same month or not so we can do that if the month of bka plus 1 is the same month if they're in the same month number this is going to determine the month number then what we want to do is just simply add one but if it's not in the same month then keep it blank then put it blank because there's no date that's all we have to do so i've written the same formula here for all the three the last days so that we can then for the months that don't have those end dates it can just show blank spot so that's how we got that and let's continue back into the variables so we need to know the schedule column and the duration columns how many columns is it based on the booking so we need all that i need to know the booking row the row that we're booking all of course in the booking call and that's important and the last room row and i want to know the last room why do we want to know the last room also i want to know a list of rooms right i've got to know what the room type is i only either i'm going to show all the rooms here or i'm going to show just rooms based on a specific only deluxe city or only standard view rooms so i need to run an advanced filter based on rooms because i only want to show those certain rooms that are contained within this filter so again it's inside our rooms database it's that exact same formula here exact same one so the same formula so as soon as i change to deluxe city view in rooms we see the deluxe city view this way we're going to run our advanced filter for the room number and it's going to be based on this room type here here's our criteria this way in vba only those rooms that meet this specific criteria are going to show up here once we have the list of rooms i'm going to take all these rooms and bring it right back into the schedule here showing those rooms then all i need to do is match up the rooms with the bookings and then create the schedule here so let's do that let's continue inside now that you understand that we also have a few more room number booking that's detailed strings for based on status and we'll get that found room i need to determine if we find the room we're going to we need to look for that room if we find it on the list we know what row to place that book ink and also a check in date and check out data's date and the booking shape this shape is important because we're going to define that shape and we need to create that shape and then we're going to set it as a shape okay so let's get into let's write some macros now that we have a understanding of the application let's go ahead back into schedule refresh and continue with our macro so the first thing we want to do is clear b6 b6 is the selected shape row so just in case there's any selected shape if i want to make sure that this gets cleared out so let's do that right now that's always going to keep track of whatever shape we've selected but when we're refreshing the schedule i want to clear out so dot range b6 dot clear contents okay so that's going to clear out clear the selected shape because when we select a shape the shape name is going to go into b6 so you'll see that shortly okay so then what i want to do is i want to delete all of the shapes on the sheet we're refreshing the schedule basically any shapes here i want to delete them automatically so how do we do that how do we delay well if we set a specific name for those shapes when we create those shapes if we assign a specific name let's just call it let's say we say booking and then the id so booking 1 or booking 2 or booking 3. if i give each shape a name and that name contains the word booking then i know that to delete it this name of course doesn't contain the word booking none of our none of our other shapes contain the word booking so we're pretty much safe to delete any shape with the word booking on it so we can do that with this one these lines of code right here for each booking shape we've already defined as a shape above in dot shapes that we've already defined sheet one this morgan is writing notes clear all booking shapes on sheet one those are booking shapes not all shapes just booking shapes next booking shape so what do we want to do then if the first thing i want to do is i want to just check to make sure that the type is an auto shape type we want to make sure that so booking shape dot type equals mso autoshape then okay so let's close that end if now we're going to run one more check if in string i'm checking for the name in this case booking shape dot name dot if it contains the word booking booking and how do we know if it contains if this if the in string is greater than zero then we know it is then booking shape dot delete then we can delete it because it contains the name booking that's it that's all we need to do so that's going to delete all the shapes that contain the booking in that contain the word booking inside this sheet now what do we want to do now we want to clear all the previous rooms any rooms that are here maybe they've cleared so let's clear d9 all the way through a high row let's just clear that dot range d nine through d let's just say nine nine nine that gives us a lot of rooms dot clear contents and that's gonna clear the rooms clear rooms from schedule now that we have the rooms cleared and now we're ready now that's pretty much it i want to focus on sheet 2 for now so let's move ahead on sheet 2 what do i want to do on sheet 2 i want to run an advanced filter i want to determine the last row our criteria is set based on formula so we don't need to add any criteria here it's already based on they're all based on formulas when it comes to dates you really want to use a formula it's much easier dates can create a real problem when you add them through vba but when you create them through formula and remember we really want them unformatted we just want that then it works with any type of regional settings or date settings so really bad we know our criteria set from n2 through r4 then we just want the results here so we're ready to go with that so let's do that with focus on we're going to focus on wish sheet 2 now with sheet 2. what do we want to do with sheet 2 i want to run the advanced filter for the current month run advanced filter for current month and so let's do for again we need to know the last row the last booking row equals again we want to make sure it's dot range we're going to use we've already specified that was auto hotkey again so that's y type dot range a that's gonna get us the last booking row last booking row once we have that we can run our advanced filter just in case there's no data if the last looking row is less than three then exit the sub okay we can't if there's no data there's nothing we can do so once we've checked that we can run our advanced filter.range of course we're focused on we need the headers in this case it's going to be a3 all the way through j and the last and the last looking row not not that value but advanced filter i'm going to run that advanced filter and what do we want to copy those to another location and i want to know the criteria range what the criterions criteria range equals dot range just the n 2 through r and 2 through r 4. that is a criteria range not that value of course it's just the range that we're focused on now that we have the criteria range we're ready to get the copy turn so copy to range colon equals what is the colon equals what is the range dot range in this case we're copying it through t2 just the headers all we need to focus on all the way to aa2 all the way through a2 let's just double check that to take a look all the way through a2 just the headers make sure these headers are exactly the same in the criteria and the results they should be exactly the same as they are in our original data that's very important okay so once we have that a2 and we're going to focus on unique now unique uh colon equals and then false not so concerned about whether they're unique or not in this case okay so we've got our advanced filter and now what we want to do is we want to determine the last row of our advanced filter and make sure that there actually is data so let's check our last results row last result row is equal to in this case dot range it's going to be t column t is the one we're focused on t this is our last results row and again we want to make run a check to make sure if the last result row is less than three then go to no data and no data is just going to be at the bottom of the fingering so let's just write down here no data because there's nothing else we can do if there's no data okay so moving on assuming that there is data that we have results the first thing what i want to do is i'd like to sort the results i want to sort them by the room i think that would be really helpful this way we have a nice organized list so let's sort the sort the data by the by the room number so how do we do that well the first thing we want to do is clear the story field so dot sort sort fields dot clear clear all any concern sorts and now we're going to focus on with sort so with dot sort what do i want to do i want to sort based on what based on w3 based on the room so we can do that with dots sort fields dot add we're going to add a key to that what is that key key is going to be equal to sheet 2 dot why am i writing sheet 2 because i'm focused on with sort so i want to make sure we specify the sheet in this case dot range w3 that is our key that is the first one that we're going to focus on and i want to sort on what is the sort on in this case it's going to be equal to xl sort on values sort on values i'm going to sort those on the values and then order what is the order that i want to focus on it's going to be equal excel ascending i want them ascending because i want the first room first and then growing ascending and then the last i want the data option in this case the data option we're just going to store it on normal so this option equals sort on normal okay so that's it once we have that we can set our range so the range in this case is going to be sheet to dot range we know it already the first row of data in this case is going to be t3 through all the way aaa through the last results row and the last result row dot applied so we're going to apply that filter and that's it dot apply so that's going to apply this sort so now that they're all sorted based on rooms okay now that we've got everything sorted what i want to do is i also want to run an advanced filter on rooms i want to take all this data here i wanted based on the room type that the user selected i want a list of rooms i want to bring those rooms over into the schedule right here so let's do that right now so we're going to write that place rooms on schedule so we want to place those rooms the last room row is going to be equal sheet three is where they are sheet three and then it's going to be let's just update that and get rid of that okay so sheet three range that's gonna be our last brooms row last room row once i have that i can run an advanced filter on that so let's let's take a look at sheet 3 so we're on the same page here sheet 3 again we're going to focus on probably just a and b notes don't matter criteria is going to be e2 through e3 and then the results are going to go inside g2 so that's just a quick advanced filter so sheet 3 dot range a 2 through b and the last room row dot dot advanced filter and then again we're going to copy over to place and we're going to criteria range is equal to again sheet3 sheet three dot range what is the criteria criteria's e2 through e3 e 2 through e3 that's our criteria now we're going to copy this range copy to range is going to be equal to sheet 3 dot range and it's very simple c3 is going to be g2 g2 that's where we're going to copy it colon equals false is fine okay now that we have our list of rooms i can just simply bring it over here so let's take a look at that d 9 all the way through something in the last row is going to be equal to basically all this g3 and down so let's write some code to bring that information into the schedule so sheet 1 dot range d 9 we can just use a large through d999 dot value equals in this case sheet three dot range g3 starting at g3 g3 and we can just use the large 993 right same number i want to make sure it's the same number dot value so it's going to bring over all rooms in case this is going to bring over all the rooms in the schedule okay so now that at this point we have all the rooms here what i want to do i want to run through our results here i want to look for the room i want to run through our results in this case three to the last row check the room number if it's found here in the schedule here look for it within here if it's found then place it in that row so that's just what we're going to do inside the code so we're going to write a loop so 4 the result row is equal to 3 to the last result row and that's the result row that we got right up here here right here the last result row through our results because we're back on sheet 2 now next result row okay so based on that we're going to focus on this these are the results that we're focusing on taking these results and basically converting them into shapes that's what we're going to do right now but i want to define some information from those results first is the room number room number is going to be equal to dot range because we're focused on sheet 2 w and what and the result row and the result row that's the room number okay so now that we've got the room number into a variable i also want the booking id booking id and what's that that's going to be basically just this but it's a different different letter different column that's going to be located in column t so we can change that to t and call this booking id book id that's going to be important what else i need to know the status because that's going to help us determine the color so the color is going to be dependent based on whatever status that is located in column y that's where our status is coming from right here called the booking status booking status okay because our colors right are based on the status so what i want to do is basically find the status in here we've got a named range called status i want to find that whatever status it is and then determine the color on the column to the right that's going to help us determine the color of the shape that we're going to create and that's that shape that's going to be placed on the schedule okay so the status is very important but let's find the room now we've already placed all the rooms here so what i want to do is take whatever room number 101 or 103 i want to find the row that it's on here i want to find that row so we can set that as a row so let's do that set the found room which you've already defined as a range equal to sheet dot range located starting at d 9 all the way to d 99 d 999 okay so what i want i want to find it dot find and i want to find the room number looking for the room number so once we've set it now we can check to see if it's found if not found room is nothing then what does that mean that means it's been found room found okay so once it's been found we can determine then the schedule row we've already defined that as long is equal to the found room dot row that's the row that it's on that's the schedule row and then we'll put row room found okay we know it's been found what if it's not found that we better add it it's probably better be found but if it's not for some reason then we just want to make sure then the schedule row is going to be equals g1 in this case we're going to use d the first available row it's going to be the last row of the value and column d plus one so if it's not found we're going to add it to the first available row at least we have a schedule room i'm going to put in add room if it does not exist that will prevent us from errors okay so now we've got the schedule row but we also need the column we also want to we also need the column now let's check for status okay if the status does not equal empty just in case then we need to set the found set the found status which has already been determined as a range remember we have ranges up here found room remember we have ranges up here found room and found status those are ranges so we can set them and i'm going to continue that status will set the found status equal to what is it going to be sheet 1 dot range our named range that we've already created called status that's going to set the status of it dot find what am i looking for i'm looking for the status that we've just defined above status and i'm going to basically look in excel values in excel hold i'm going to make sure to find that just in case that helps but it's not necessary this part because we didn't use it above instead okay so now we're going to do now we've set the found status let's just see if it's found if not found status is nothing then then what then i want to set the status color that's already set what is that the status color which is a string color is equal to let's take a quick look just to remind ourselves in this case bt and the color of this whatever the row is whatever the row has been found column bt and the color of that row so how do we write that up into vba status color equals sheet one dot range b t we know the column and what's the row the row is the found status dot row that's the row now what do i want from that i want the interior color how do we get that well we can do that with this dot display format dot interior color interior dot color that's going to give us the color of that because i want to put that into a variable dot color that's going to get us the status color status color okay so now we have the status color very good so perfect we put that in because that's gonna we're gonna need that for our shape let's focus on the check in now i need to find the column we've already determined check in check out define them as dates so check in where's our check in locate our check in is located right here check in is located in z and the result row so let's put that into a variable check in equals dot range z and the result row and the result row and we'll call that check in check in okay so what if the check-in is like you need to check what if the check-in is less than here less than that because i got to make some special circumstances so let's run a check because the checkout could be within the day but the check-in might be before the first of the month so let's run a check to see that if check in is less than sheet one dot range e8 that's the first month we could also use start date as as as well that named range that we've created e8 dot value then what then i need to make some some changes the first thing i want to do is schedule columns equal five right because the first the check in is going to be the previous month so we need to make sure that the first column is in this case five which is column e what else do i need to do i need to set the check in check in is going to be equal need to update the check in i want to update that check in because it's actually before the first of the month so i want to update it to the first of the month so we can do that with checking is going to equal the sheet one dot sheet one dot range e8 value so that's gonna automatically set the current check in to e8 that value set to first because that way because the actual check-ins before the month said first day of the month okay so month okay else what if it else then i want to set the check in based on a specific column what if the check in is on the first the column is going to be column five in this case it's actually column six because i want the check in to show up on the second right i want to check i don't want to check in to show up on the first column i want to show the checking call on the second column of the day because check outs are associated with the first column check ins are associated with the second column so how do we find that well we know it's either going to be 6 8 10 based on the data so we can write a little bit of a formula to figure that out so in this case the schedule column is going to be equal to what i want to do is i want to extract the day of what the day of the check-in and then i want to multiply times 2 because for each day we have two columns times two and i'm going to add four why am i adding four i'm adding four because our first columns start of course on e right so we need to add four because the first four columns we're not actually associating with the schedule so that's we're gonna and that's going to be a check in date column okay that's going to be the column associated with that checking date okay so we focused on the checking date and we've covered for if check-ins are actually before the first of the month or we've set the date that's going to set our scheduling column the first column now let's focus a little bit on checkout where's going to be checkout of course we're going to check out located we're just going to copy this here but we knows our check-ins on a a not z right checkouts are located right here that's where our data is coming from in column a so we can write that up right here check out in this case i just copy and paste is going to be equal to based on a a and that's going to be the check out so now we set that as a variable now i need to know what if the checkout is after the last day of the month what if they've checked in on the 28th they're checking out after i want to compensate for that as well with inside the code so let's write some if then statements to see for that if check if the checkout is greater than what i want to i want to know if it's greater than the last day of the month how do i get the last day of the month again i can use end of month formula just as i do inside so let's write that application worksheet function i'm going to use end of month eo month that's going to give us the last day of the month what's the what's any day of the month here well the first thing we can use to get the last day of the month we're going to use the check in plus zero so what i want to know is if the last day of the month of the checkout is at the current month or not right so i want to notice is the last day of the checkout date the same as the last day of the current month so we do that if the check out date of the last day of the month is greater so basically if the checkout date is greater than the last day of the check in month then we know it's after the next month so check in then the check out is going to be equal to the last day of the month in this case all i need to do is just write this again eo month then now it's going to set automatically to check out the last day of the month so we do that just like this then the checkout equals the application worksheet function end of the month so basically what we're doing is we're automatically forcing the checkout at least on the schedule to appear the last day of the month because in actuality it is the next month perhaps so that's going to set the checkout automatically to the last day of the month just in case they're checking out the next month all right great so now we've set the check in now we've set the check out we've compensated for check-ins before the month we've compensated for checkouts after the end of the month now let's update the duration so we're going to set the duration set set duration because we need to know how big to make that shape it's based on the duration if the check out is less then again i want to know if it's going to be less than the first day of the month less then if the checkout i wonder was less than again dot so now what we want to do is we've reset the check i've reset the check mark possibly if it's less so what i want to do is i want to see if it's different okay so now what i want to do is we just updated possibly we've updated the checkout to be actually less than the checkout if they're checking out next month we've reset the checkout possibly to be the end of the day of the the end the last day of the month which is possibly less than the actual checkout day so i want to check that out right now and see if we did that because in that case what i want to do is i want to set that checkout column to the last day of the month let's say they're checking out the third of next month i want that shape to go all the way to the end but i need to check is the checkout date did we just set it less than the actual checkout date let's check that out if the checkout date is less than again what is the checkout date this is the one that we've set all the way up here check out i want to check it right here is it less than this so if the checkout date is currently that means do we just reset it then what do we want to do then we want to schedule the duration value or or when the other condition or dot range z the check in and the result or z which is the check end date and the result row is less than e8 so that means basically we're going to check for those two conditions is the check in date before the first of the month or is the check out date after the last day of the month i just want to know that means basically there's some type of a check-in that's straddling the end of the month or the beginning of the month so z which is the check-in date is that less than sheet one dot range e8 is it less than the beginning of the first of the month if it is then we need to update the call the rows right because we're going to we need to update the number of columns that we're going to be setting the shape to so if in those two then what do we want to do in that case i want to set the duration column then in this case let's just write some code check in date before month or check out date after month out out date after month in those two conditions what i want to do i want to update the duration columns and basically what i want to do here is if it's going to be the last row i want to make sure to fill in in other words i want to fill in both of these dates if the checkout is after i want to fill in this one make sure that this one is filled in if it's before so we need to adjust the columns we need to adjust the number of columns for check-ins before the first of the month or checkouts after the month so we do that with this the duration columns is equal to check out minus check in times two times two that's it for that else times two else what else do we wanna do in this case we wanna subtract one i want one less column if it falls inside the month to check in and check out fall within month i'm going to subtract one it's one less column why is that because i don't need to go to the end i would just let's say the last day was on the 31st i would just want to go to here but if the last day falls on the first i want to go all the way i want to include the entire date so i want to make sure that shape extends all the way to the end but if the checkout is here on the 31st i want to make sure it ends right here that's why we want to either subtract one column if it falls within the month that's all we're doing there it's just adjusting the columns based on whether it falls inside the month completely or before or after the end of the month so that's all we're doing here all right so now we're set now i just want to put now what we're going to do is i want to determine what is the text that's going to go in the shape in that shape i want to put the name of the person so that text is going to come here from v i want to place the guest name right there inside our shape so we can have that so how do we do that well we do that with just this line's code we already have the booking text is equal to dot range v in this field we're focused on v in the resolver result row and this is going to be the guest name guest guest name so that's going to be the guest name so that we're gonna put in there okay so now we've got all the information we have the starting row we have the number of rows we've got it all set up we have the name all i need to do now is take this shape duplicate it and place it in the schedule appropriately so we can do that with this line of code sheet 1 dot shapes sample that's the name of sample shape that's the name of that shape as you can see above sample shape dot duplicate dot i want to set the name of it i want to set a unique name and of course that name if you remember correctly it must include the word booking booking it's got it and what else and the booking id we've already defined the booking id above so we could just add it on now we've got the shape so now we can focus on just this shape now we've duplicated it but we haven't done anything else we haven't sized it we haven't placed it we haven't done anything with it we haven't colored it so we're going to do all that now with sheet 1 dot shapes now we can sign a name booking and what of course and the booking id that's the name of the shape we just assigned booking id so that shape what are we going to do with that shape well i want to do a lot of things i'm going to assign the left dot left position is going to equal to sheet one dot cells right dynamic dot so we know the scheduled columns we've already defined the schedule column and then the schedule row we know the row dot left so that's the left position dot left so that's the left position what else dot top what is the top position again in this case it's going to be the same sheet1 dot cells schedule column dot comma schedule row dot top but i'm going to add just one i want to don't right on the top it's a little better i'm going to add one just so it drops down slightly from the top so we've got the top position we've got the left position but now we need the width right how long it should be dot width is going to be equal to sheet 1 dot range equal to sheet 1 how long i need to know the width of the columns what's the total width of the columns that's going to be the width of our shape so we're getting we can define that by cells the schedule row so we're going to the range i want to know the width of the range so for what it is that means if our bookings from all the way from 8 to let's say 13 i want to know the width of all of those columns whatever the width is going to be the same width of our shape so we can define that here dot cell schedule row comma schedule column that's our first row but what is our second that's our first cell in the range but what is the last cell how can we determine that sheet one again dot cells same thing schedule row schedule row this time schedule column plus though plus the duration rows duration plus the duration columns duration columns we've already set the duration columns up there so now we know the duration columns and i want to know the width of those cells what is the width if i know the width of those cells that's going to be the same width of our shape so we can set the width automatically to that perfect okay so what about the height the height i want to double check dude the height is going to be equal to sheet one i want to know basically just the height of one of those rows it's fine we can set that dot cells again we can just use schedule row dot comma schedule column dot height and that's the height of that specific cell that's going to set it automatically okay so we now we know we've got the left position we've got the top position we've got the width and we've got the height but now let's set the text i want to know the text what's the text that's going to go inside that shape dot text frame 2 dot text range dot text is going to be equal to the booking text booking text we've already defined that above with the name of the guest okay so now we've got the text now we want to set the color so i want to make sure if it's not if the status color which we've already defined color does not equal empty then we can set it then dot fill dot four color what is the four color dot rgb is going to be equal to the status color it's going to set the color of the shape automatically based on the found color that we found okay great that's it that's all we need now we just need to finish our loop right we have the next results row so we're going to do that for each row each row so we can bring that up because we've completed that once we've completed all the next rows we have our node data in here here well all i want to do is basically once it's finished i want to select something else so we can do that sheet 1 dot range j2 dot select just want to select something out of that in case we selected anything else so that's it let's go ahead and test that we'll save our code of course save what we do and then just go ahead and run that macro and then let's see sort on normal all right x should be excel sort normal not x so no excel okay let's continue on it should be xl there's an x l in there okay and continue on this should be g booking not book d booking id just updating those variables here okay continuing on fix that misspelling all right you get the the fun watching me fix these bugs all right let's take a look extract my now when you have this extract range is missing usually either your range or your variables so let's just check that t2 through aa2 okay yeah this should be row three our data is located our data headers are no row three in this case three so we want to make sure to have row three updated and let's continue on and okay we can't select that's fine that bug we can't select if we're not on sheet one we can't select it as soon as we are on sheet one we can then rerun this code take a look and take a look perfect that's just what i see let's set all rooms in fact i've already got this based on change on based on the change on ai 16 let's just take a look at that some code that i added previously it's very simple when we click on the schedule when we make a change to this ai6 we're not going to run the schedule refresh so any change to our room type is automatically going to refresh the schedule now we have our room so now we've got it notice that this particular booking this one if we click or we're not going to click we haven't updated that memo but we go to the next month you're going to see let's program in previous and next month now so we can actually navigate these so let's just put that in right now okay back into the scheduling macros i've got up here previous month and schedule month so let's add in some additional code for these right now okay so what do we have all focused on the previously scheduled month we're going to say in this case sheet sheet1 dot range now i'm focused on in this case i really want to focus on this here this is m6 right here i want to focus on this because our first day is scheduled based on our start date so we're going to focus on m6 and what do i want to do with m6 in this case m6 dot dot value equals i'm going to do use date add date adds a great feature date add and we're going to add the month i want to use the interval as month that would be m and i'm going to subtract 1 minus 1 and then based on what based on the date what is the date it's currently sheet 1 dot range m6 based on that that's all we're doing based on that specific date we're going to subtract one month dot value that's all we have to do and then of course run the macro schedule refresh once we subtract it everything else should be and the basically the same thing we're going to do in the next so we're going to copy and paste this the only difference in this but it's not going to be negative one we're going to add one that's all i need to do very simple for previous and schedule now what i do is just going to sign those two buttons right click on those assign the macro here under let's go all the way down to schedule refresh and just type in schedule just type in next in this case next this would be the previous month so we'll go into previous scheduled month here and then we're going to assign this to the next scheduled month so assign macro just type in next and it's going to come up automatically i have a lot of macros associated with the calendar there next schedule month notice next month that's for our calendar a pop-up calendar got a macro so okay click okay all right so let's click next month and you see now this particular one's actually scheduled until the 10th previous previous nothing on this okay great that's just what i wanted to see that's exactly what i'd like to see only those rooms excellent okay good so when i select something i want to assign a macro that on the selection of this now let's take a look at this let's create a macro now i've assigned a macro to this there's nothing in the macro so we click assign macro we see that something called select booking now there's nothing in here now but we're going to create a macro that's automatically assigned and since we assigned that macro to the sample it is that same macro that is already assigned to every single shape we select we create so that's really great so we only have to assign it to the sample it gets automatically so what are we going to do inside this macro well that macro is very very simple so let's write some macro with sheet 1 first of all dot range i want to know the application color and i want to put the name of that that's the name of the shape that we're actually clicking so it's the name of the shape and what is the name of this shape if we take a look inside our schedule we see that we've already assigned these this is called booking four this is called booking two booking one so what do i want to do well i want to take the shape name i want to put it into b6 i want to take only the number only the booking id in this case it would be 4 in this case so i want to extract just the number by taking out the word booking i don't want to put that right here so the id will go here and the whole name will go here so how do we do that we can do it with just a few lines of code right here so dot range b6 equal to the application dot color and that means the name of the shape name of the shape that called the macro okay and what do i want in b4 i want to put just the id so we need to take that name but only but pull out replace the word booking with nothing so dot range b4 in this case b4 we just want the id so in this case it's going to be equal to replace what are we replacing the application dot caller but i'm looking for the word repl looking for the word booking and i'm going to replace it with nothing there we go so that's all we need to do let's just take a look at that and that macro's already been assigned so now as we select the macros notice that booking two showed up and two so that's what i want booking for now notice because of our formula that's based on b4 our row our booking row automatically comes up once we know the row we can load everything in okay so the last thing what i want to do is i also want to get some information i want to place it because if the user moves it i'm going to show you how to drag and drop but what i need to know is the position of all this information i want to put it here notice i'll just clear that out from previous what i want to do is i want to put the left position the top position and the width of this shape and i want to place them right here this way if the user makes changes to any of those if they change the top position if they change the left position or they change the width we'll see the difference so let's put that information in here right now so back into the code we go into the schedule max and inside this let's go ahead and add that information and so also what i want to do is i want to select the shape so dot shapes application dot collar first thing i'll do is select it so that way it'll shape and also i want to put the information in so we're going to be focused on this shape here so let's copy and paste this information here and then what i want to do is i want to put the left position of it so how do i get the left position of it i want to put that where do i want to put that let's take a look i'm going to put that in b12 so let's bring this down we don't need this i'm going to put that in b12 so dot range b12 dot value equals application color dot left position what do i want i want to put the top position in b13 so all i need to do is just pretty much copy and paste this here and in this case it's going to change it to b13 and in this case i want the top position and of course you can get it the width is going to be the last one the width is going to go inside b14 okay let's take a look at that and make sure we've got it and with we don't need this extra end width that came all right so let's continue on now we have the information here very good so as we selected this is going to help us later on for drag and drop so now what we need to do is pretty much when we selected i want to load all that information in our load macro which we have and we're going to write so the last thing that we're going to do is we're going to run for booking load which is going to load that that's the macro that we're going to write right now booking load that's the macro it's going to load it let's take a look at that into our booking macros and it's empty right now but let's add information for that booking load so how do we do that let's focus with sheet1 okay the first thing we want to do is we want to make sure there's a booking row inside b8 if there's no row there's nothing we can do we need to let the user know so how do we do that if dot range b a dot value equals empty then let the user know message box please select a booking to load okay exit sub and end if nothing we can do if there's not actually a row to load if there's no roto load nothing we can do okay so assuming that there is a load we can now set the row so we can do that the booking row is going to be equal to of course b8 right we know that that's located there because it's automatically calculated because we saw the booking row is equal to booking row that's in b8 now all we need to do is set the column we know we're going to loop through the map data as we did when we saved it we're going to do the same thing 4 booking columns equal again 3 to 10 3 2 10. there's one small difference in this next booking let's focus that booking call let's complete the loop first the only thing is what i don't want to do is i i don't want to load what's in here this is automatically based on the room type so in this case this one room type i don't want to do so let's take a look at each column this is column 5 so if it's column 5 i don't want to load it so let's write something in there if booking column does not equal 5 then what do we want to do then we want to load the information in then dot range focused on sheet 1 where what is the range the range is located in sheet 2 dot cells row 1 column booking column dot value that's the range so that range is going to be equal to what equals whatever's in the row equal to sheet to dot cells booking row and then booking we can increase this now booking column so booking column that's the column dot value so that's the column that we want that's going to bring it all in that's all going to be other than column 5 it's going to bring in all the data from here starting in 3 going to 10 bringing it all in and bringing it in to this form of course based on right the cells right here 82 based on all those cells so it's going to bring it all in so that's going to load it up so what else do we need to do once we have that completes our loop here all we need to do is update our shapes because it's no longer an existing right we just want to make sure that it's not a new shape so i'm going to copy these shapes here and we're going to set the new book into false just in case going to do the same thing as we did when we saved it and i'm going to do it right down here so that's going to set our new booking hide our new booking group show our existing and make sure to set our new booking to false that's all we have to do let's save our code and then click in here and see if we have new year's perfect that's just what i want now you see it loads up accordingly all the information loads up as we do of course we can make this faster but it looks good i really like the way that it looks now we're set okay great so all we have to do left pretty much is just take care of delete and move check for move so now what i want to do is when i move this here when i move this shape i want the scheduling to update automatically how do we do that well let me show you so let's write that right now so back into the vba and we're going to go up here and i'm going to call this something called check for move so inside the scheduled macros we scroll down here we're going to look we're going to create a macro called check for move and so as we select the booking we're going to run this macro so basically this macro is going to be running all the time behind the scenes checking for a move so i'm going to copy and paste this macro right here that way as soon as we select it it starts checking so let's write that now now keep in mind that we're going to really use one variable here and it's going to be called count number for the most part just a few other variables count numbers the one that we're going to we're going to be running a loop so i've already defined that up here so let's go ahead and set that up right now of course with sheet 1 we're going to focus on that that's the one we're going to be primarily focused what i want to do is i want to make sure that we need values we need to make sure that we've actually selected a group so if b6 or b8 is empty we need we really need b6 and we need b8 we need a selected shape and we need a row we need to focus on a specific selected one if either one of those things are empty there's nothing we can do we should exit out of it so let's check that right now if dot range b a dot value equals empty or dot range b6 equals empty then exit out okay so there's nothing we can do then exit sub nothing we can do unless we have value so we're going to keep that in mind we're going to make sure we have that all right we're going to set the booking row we know the booking row of course that's always going to be located be it booking row is going to be equal to b8 we just found that b8 that's our booking row where our database is located so once we have that booking row we need to set the booking shape where is that located set the booking shape is going to be equal to dot shapes and where's the name of that shape we know the name of that shape because as we select it's going to be located right here in b6 so we can set that shape to be dot range b6 that's the name of the shape that we're going to focus on that's the name of the book and shape so that's what i want to set now we've set those two variables the booking row and the booking shape now what we could do is run account so basically i'm going to run a loop and that loop's going to run behind the scenes and going to continue to check for any movements that the user may have made so how do we write that for count number equals one to let's just say one hundred thousand a large number okay next count number so inside that loop we're going to continue to check for possible movements but in order for us to do that and do other things while it's running we need to add do events that's going to allow the user to do other things while this macro is running so now we can check for differences so what do i want to check for i want to check for different shape i want to check for a different top position i want to know if this shape has been moved has it been moved to top to the left or the width so i want to check is the left position different than this is the top position different than this or is the width different than this if any of those things are different then what we want to do is update the update the current the database if any of those things are different i want to update the associated database for that booking then i want to refresh the schedule so let's write that it's if if again we've already defined the booking shape booking shape dot left does not equal let's move this over again so we can see the variables that's going to be located into b12 right dot range b12 that's our left position or right or what our booking shape we could figure out that top position does not equal the top position located in b13 or one other the width the width dot value or the width of it booking shape dot width does not equal the width of the current shape which is located in b14.range b14 if any of those things have changed then i want to do something what do i want to do okay so that's our loop so let's just go over it again once more if the left has changed based on the volume b3 b12 when we selected it or the top position has changed based on b13 or the width has been changed based on b14 if any of those things are changed we need to do something we need to update that okay so let's just take a look at that right now okay so the first thing we want to do is we want to make sure to see where they have moved it and if there's any issues with that so let's check for anchor because we can't have i can't have them dragging this thing off the screen anywhere i can't have them dragging it up here so we need to put some caveats to see if if they've dragged it to improper places so check for incorrect positions so what would an incorrect position i would say that a top position above row nine or a left position above a b less than column five we gotta update that so those those positions so let's update that and write some checks if booking shape top left cell dot row is less than nine or because there's or what or booking booking shape dot top left cell dot top left cell dot column is less than five can't have that less than five then what do we want to add then message box please move booking to a proper location but now that it's moved they've messed it up so we really need to run the macro that's going to refresh the schedule so schedule refresh and then exit the sub okay that way they can start over again okay so that's it so now we've checked in case they've moved it off into the wrong place so let's check for a date change okay let's take a look at that and see if there's been any date change check let's check for a check-in day chain check-in date change so we're going to check for that to see if there's been any check-in date change if booking shape dot top left cell dot left does not equal b12 dot range b12 that means they've moved the left position b12 value then they've moved less than then we want to do something but i need to check first of all i want to check to make sure that they've moved it when they move it i need to make sure that the check-in is always the second column of the day the second column so we can check the second column is an even column right the first column this is five the second column is six so the second column is where i want to place it except if it's before so i want to make sure that they place it that it's going to be placed on the even row so let's set that up right now how do we do that well we can do that here if booking shape dot top left again we're using top left cell in this case dot column that's what i focus on mod of 2 equals 0 that means it's even then that means it's an even even column they've put positioned it on even column then booking column is our variable that we're going to set is equal to automatically this we already know that the column is right we want to set that equal to this column equals zero should be zero equals zero then looking so two the booking column that's going to get us that means that there's no remainder it means already on an even column which is what we want then we're just going to set up else what is it else i want to add one right because i want to make sure if it landed on an odd column let's say it landed on 5 right here i want to make sure it moves over to 6 because i want it on that even column else let's just copy this here else booking column equals we can call plus one just to want to move it over one column to the right i want to make sure it's always on that even so that's how we check for an even column now we can update the check in so check in is equal to dot cells of course we're on row 8 right row 8 here booking column minus 1 why is that dot value i want to get the date right i need to get the date now we know the date's not in the dates in the first column here the dates not in the second column even though we wanted position here on the second column but i want to get the date from the first column so if our booking column is even i want to make sure to subtract one and row eight that's where our date is located so eight plus the column before is gonna get us our date that's gonna reset our check-in date so our check-in date is going to be equal to the booking column minus one check call this updated check in date okay so now we've got our check-in date now all i need to do is update our database right i know the row booking grows here now all we need to do is update our data our check-in date is located in column g so we know the row we know the column we know the sheet we can update the check-in date inside the database so let's do that so sheet 2 dot range g and of course the booking row dot value equals check in that's going to update the check in date okay great now that we have that that's all we need on that so that's basically if there's been a change to the top left so we've got in so that's any change in the checking date we're going to make that change what about if there's an update to the duration i wonder if there's an update to the duration so let's check that update duration on any change so we need to update the duration for any possible change so we can do that if the booking shape focused on the shape again shape dot bottom right cell located on the bottom right so column i'm looking for the column if they've extended that mod 2 i need to know again mod of 2 equals 0 that would be the even column is it even then booking column equals right i want again let me show i'll type it out and then i'll show you booking just change it right here then the booking column this time we're going to set the booking column is going to be equal to this minus 1 and i'll show you why that is equals this minus one why is that because i want to check out notice i want to check out right here i don't want the checkout to be at the end i want to check out on the on the odd row in this case i want the on the odd column this column here which is the odd column i don't want to check out here i want to check i heard so if for some reason they've dragged it over to an even column i want to back it up subtract one that's all if it's even i want to make it odd just as it was in the in the check in in this case i wanted to make it even that's how we can straddle those days so all right so update update check out call okay so we've updated that accordingly else else what else is basically going to be the right column else we can just we don't need subtract one else in this case else we just need to copy and paste no need to subtract because they've already placed in the right column all right now we're ready to actually set reset the checkout date so let's take a look and of course the checkout date is going to be located right here up there also in row 8 so let's update that if dot cells focused on row 8 in this case and the booking column dot value does not equal amp as long as it doesn't equal empty then check out is equal to just that value equal to this value right here located whatever's in that row that's going to be our new checkout date so we can set that up else what if it is empty what if it else in that case i want to make sure i want to reset the check out equals the check in plus i need to determine the width of the shape right basically if it's empty why would it be empty well it's empty what if they've dragged it over what if they've dragged it over all the what if they've extended it all the way beyond here i need to update this i want to update the days based on the width of that they've dragged if they've increased it so much i need to determine what's the width based on the width of it so we can do that with this formula determining the checkout date based on the check in plus the total number the width of the column so how do we do that it would be in this case the check in plus i want the whole number so i'm going to use integer what is the whole number what is the integer i want to use the background shape width the background shape the width of that divided by dot range basically the the width of one single cell basically the total width width of what is that in this case we can use e1 through f1 that's the width of basically an entire day which is actually two columns so the width of that so basically what i want to do is i want to determine the number of days so basically the number of days here in this case if it strands two days one i would need to figure out the width of one day the width of one day how do we know the width of one day in this case we'll just use this e1 right e1 through f1 is the width in this case we could use any row it doesn't matter e if we use e9 through f9 or whatever what is the width of that divide that by the total width of the shape and that's going to determine the number of days for the checkout so we could do that e1 dot width so the width of that that's it so the other way is the check in basically we're going to add the total width of the shape determine the number of days using the width based on the width of the columns that's going to determine the total number of days that they're going to be staying in our lovely hotel okay great so now that we have that we can update so we've got the check out date now based on this orbit this so all i need to do is go into our database here and now update the checkout date based in column 8 so we can do that with this line of code sheet 2 dot range h and the booking row dot value equals check out update check out date okay we're almost done i appreciate you sticking with me on this long training we need to run a few checks on this okay so what about a room change check for room change need to check for that too how do we know if there's been a room change well that's based on the row right if they change this from one room if they drag it to a different room we need to compensate for that too and i need to make the associated update into column d so let's check for the current column and see so what i want to do is i want to check does d whatever's in the database is that equal to the current row based on d here let's run that check so right now so if dot range d the background shape dot top left cell dot row all right that's going to tell us what row they've actually moved it to we're going to check on that if it does not equal we can check for we can actually check for this we can check for the room right here ae2 if it doesn't equal this then we need to make the update so let's write that down value does not equal dot range dot range ae2 which is the current room that we've selected ae2 then we need to make the update then we need to make then sheet 2 dot range now we need to update the database we know basically we're looking at the row here we're looking if they've moved it to this row here we know there's been a change right so if there's been a change i need to we're going to compare this room to this room if we see that it's different then i want to go into the database and i want to update d here inside the database so we do that with just this line of code then sheet2d and the booking row dot value equals equals what equals this right here whatever is in right here whatever's in row d this right here that's all we need to update though that's it okay so that update update the room in the database all right great so that updates the room basically all we're going to do there is basically look at the room here if it's different than the current room here we're going to take this room put it right in here put it inside the database that way when we refresh the schedule it'll be updated automatically okay let's take a look here to see what else we have now we need to do is run the macro for schedule refresh i'm going to run that macro and then what i want to do is i want to load the booking booking load i want to load the updated run the macro that loads the the updated information into that looking i also want to clear b6 why do i want to do that because this loop do events is going to exit out as soon as b6 is empty so as soon as we clear b6 it's going to exit out of it so that's going to do that loop until b6 so now that we've made the update dot range b6 dot value dot clear the contents clear contents that's going to automatically exit out of the loop once we've done that okay good now we're making progress and the last thing i just want to select a cell dot range that's going to unselect anything any shape that's been selected so j2 value dot select okay there we go now let's take a look at that save our code which i should have done already save it and then we're going to fix i'm sure there's some bugs because i type and talk at the same time always create bugs run this macro all right that looks good now let's just take a look at this drag it up here update and then let's just check this is variable background shape okay just one more line of code here we need to get the exit we need to exit out so if doll range b6 b6 the value equals empty then i want to go i want to skip everything else in this case go to end check now let's let let's add that so i want to exit out of it when b6 so i want to exit out of that loop if b6 the shape is clear then go to and just call it and check and then what we'll do is we'll drop down here and we'll just place it right here and check that's going to exit out when it's empty because that's what we want to do so let's reset that okay let's take a look the selection looks good now what i'm going to do is i'm going to move this over here and it's updated here great so now notice it's 8 2 and now if i move this one let's move it to a different room it's going to be located on 106 on the 7th 106 to change to the seventh now let's increase the date i want to increase it all the way to let's say the 18th and it's increased to the 18th very cool now let's increase it over here way over here even more so now it's into the next month perfect all right and last but not least i want to check for conflicts i want to know if there's going to be a problem if there's two different bookings that are in the same dates i want that to show very very clearly i want to let them know that there is a conflict how can we do that well we can use some if so let's go ahead and take a look at a specific cell that i've dedicated to that in b15 and basically what i'm going to do is i'm going to use countifs i'm going to look for all the booking rooms that are in ae2 i'm going to look for all the check-ins that are less than b i 12 less than the checkout date and also all of the checkout dates that are greater than the current check-in date and if that is greater than 1 if it's greater than 1 that means there's more than one booking that has all of those in that case i want it to return true otherwise false that means so if we count that means more than one booking contains that information if that's the case then i want to add some additional formatting to that so how do we do that so that's conditional formatting manage rules and you'll notice based on b15 is true if that's true i want to make it bold red i want to make the background red i want to make it really clear and i also want to add conditional formatting to this cell here letting the know that there's a conflict again into the conditional formatting managing rules if b15 equals false then we're just going to color the same cell in the which is the background color so we're going to make that font the background color that is going to hide that conflict unless it is true so basically it's the opposite world but in this case i want to show that conflict when it's true so as soon as we take a feature and move it out of it that conflict is going to go away all right i'm glad i got to show that to you as well that's really important one more thing to do and then i'm going to let you go it's looking really good so let's go ahead and update that i do need to do the delete booking everything else is done so let's add that in there in the delete booking very fast we're going to go into the booking macros scroll down we just need to fill out the delete and that's it very very simple so let's update that accordingly just have to write down if sheet1 dot b8 if there's no row dot value equals empty then message box please select a booking to delete exit sub and if okay good so just in case there's no nothing we can do in case there's that and then what do we want to do also give them an out in case they don't want it if message box are you sure sure you want to delete this booking question mark and then vb yes or no and then delete booking is the title of that pop-up equals vb no they say no then exit sub nothing to do if they say no okay assuming that they haven't said no we do need to set the row so we're ready to go now so the booking row is going to be equal to b8 equals sheet1.range b8 that's where our root book goes that's something we need to clear out in this case so booking row is going to be and now what we want to do is just clear it i'm just going to keep the information what i want to do in this case is deleting i'm going to keep the id i just want to clear out all the information probably all this information let's just say from b all the way to the all the way to j let's just clear out that information in this so sheet 2 dot range b and the booking row and quotation colon and all the way through j and the booking row dot clear contents and then i want to run the macro that's going to be new booking which will clear out any of the form booking new and then schedule refresh i also want to refresh the schedule so that any booking on the schedule will be gone refresh okay good that's going to do it now it's called booking delete let's take a look at that in here and let's update the macros on this i'm going to right click and assign the macro called it booking delete we can delete which is actually the first one here and click ok now i'm going to select on a specific click delete we're going to get are you sure you want to delete this booking yes it clears it out it puts it in the new and clears it out excellent well you've seen this training thanks so much for joining us going to show you how to create this amazing gantt chart with hotel booking drag and drop functionality thanks so much make sure you download this using the links down below also if you'd like pick up the 150 application that helps us out keeps these trainings free make sure you subscribe to the channel thanks so much and we'll see you next [Music] week you

Channel: Excel For Freelancers

Views: 57,763

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Excel Hotel Booking, Excel Gantt Chart, Excel Gantt, Drag & Drop Gantt, Excel Project Manager, Excel Drag & Drop booking, Hotel Application, Hotel Application In Excel, Room Booking Application, Excel Room Booking application, Hotel Room Booking Free, Free Hotel Booking application

Id: ioro9kzW3t8

Length: 116min 18sec (6978 seconds)

Published: Tue Aug 04 2020