Discover the Bilty Format in Excel for Travel Industry

Bilty format in excel for Travel Industry

hello this is randy with excel for  freelancers and welcome to the trip   manager in this week's episode i'm going to  show you how to create this incredible trip   manager complete with unlimited trips unlimited  bookings per trip total costing attachments   we're gonna have a trip calendar and we're  gonna make an amazing custom toolbar it's   gonna be an incredible training i hope you'll  stick with us i cannot wait so let's get started all right welcome to the training i've got a  really fantastic application after so long of   us not traveling i thought it's time to get in the  mood for traveling and we're going to create in a   really amazing trip manager complete with this  dynamic dashboard going to track our locations   where we're going and of course it's going to  be complete with a map and a lot more with cost   and everything we'll be able to add trips save  trips new trips and delete trips we'll also be   able to add unlimited bookings per trip so we  can keep track of any type of booking we're   gonna have a dynamic admin screen we'll be able  to add icons colors and that's gonna be used on   our schedule trip calendar here we're going to  keep it able to track everything we need on this   calendar each individual booking type whether it's  hotel or car or flight or anything like that can   have dynamic colors dynamic icons and a whole lot  more we'll be able to add select a different year   and we're going to be able to change out months  and we're going to be able to do a whole lot more   so we're going to create that and we'll be able  to add a refresh so a lot of things going on in   this application on top of that we're going to  add this really dynamic cool toolbar a custom ui   toolbar i'm going to show you how to create that  if you've never created one before it's so much   fun and we're going to get into that so we've got  a lot to cover i hope you'll do stick with this   if you like this application you can download  it for absolutely free using your links down   in the description of course we've got that with  your email or with facebook messenger and we'll   send that right over to you however if you would  like to support us with this channel there are   many ways to do that including with our patreon  account patreon of course is a great way to get   a lot more than these trainings i've got things  such as all these icons pictures and everything   that goes into it and additional resources  we're going to put that in our patreon account   i've got that i've got trainings you'll get the  trainings before anyone else downloadable videos   downloadable applications we are going to have  a ton of other stuff and we also have a lot of   pdf code books where you can get every bit of  code that goes into this in a really beautiful   and organized pdf code book so i hope you join  us right there on patreon i'll include the links   down below all right let's get into this so we've  got a really dynamic dashboard really four parts   to this dashboard trips we're going to be able to  select different trips and then each trip has its   own booking so whether it's flights or cars or  taxis or whatever even meals we can have that   too and of course on that calendar that you saw so  it's got a few different components and then we're   going to add it all together we're going to bring  it with this little toolbar here so we're going   to do that now the admin is a little bit simple  relatively we've got a folder where we're storing   those icons we're going to need to have those  icons because those are going to be available   to us on our calendar so we see those different  icons flights and cars and hotels and all that   stuff and of course also in our admins we  want to keep track of the travel documents   folder these travel documents when we go into the  trips we can store unlimited we can add different   documents if we want to do that or we can add lots  of different things with that so we have a lot of   if we want to add a picture we can simply add a  picture any type of a document to that so you know   documents are really important so each individual  booking can have individual documents right so   we can of course those documents can be jpegs can  be word or any type of thing so i think let's see   we've got one here this one's got a few different  documents right here i think the paris taxi here   this one's got a few different ones here so  we've got a pdf we'll be able to preview that pdf   we will also have a jpeg or we can even  do word documents word we can preview   inside there and we're going to be able to open  them up or we're going to be able to delete them   simply by just clicking on this little icon here  it's going to open up whatever application you   have in fact if it's a pdf viewer we'll be able  to open that up so i've got a little pdf viewer   here for my fox at pdf and you can open that up so  we can see that as well all right so we've got a   whole lot of features in this application i'm  going to try to go through it step by step if i   do tend to talk a little bit fast which i often do  as i get excited with these applications because i   love bringing them to you each and every week of  course just go ahead and slow down that video but   i'll try to go slow and i'll remember oh i forgot  one feature showing in the itinerary we'll be able   to show itinerary and we're going to be able to  print itinerary so there's so many features i as   you know i often forget so you better stay tuned  watch the whole application you never know what   features might pop up during these applications  and of course i'm going to explain every line of   code with you some of these i do from scratch but  on a complicated one like this where so there's a   lot of code i'll go over step by step that'll save  us a lot of time as these training videos are well   over an hour sometimes two or even longer so this  will probably be maybe about two let's see how it   goes along all right so let's step into the code  and let's step into the workbook and see just what   we're doing right we've got the ability to create  a new trip which is going to clear everything out   when we select on a trip it's going to load that  trip there's no bookings associated with unless we   select a specific booking if we want to add a new  booking it's going to clear it out and we can add   any booking we want so let's say we want to add  a hotel in let's just say a city or any city so   we could do a hotel nevada if we want to right  and then we can set a booking type here we've   got a whole list of booking types that comes from  the admin we can put the confirmation number here   if we have a confirmation number we want a from  date right if we got a pop-up calendar another   feature i forgot to mention right so we want to  put in a specific from date we can and we've got   a time right if we want to set a check in time we  can do a check in time any time we want let's set   a reasonable check in time nice and early in the  morning there and then of course when we're going   to add check out of that so let's say we want to  stay there a few days and then we add to checkout   time or something like that an email website  if you want location address city so we maybe   we'll do los vegas one of my favorite cities and  then we'll do we can do this is good for state or   city or counties we'll just do nevada okay so now  we've got it down now so now we've added something   on to this europe trip although it doesn't really  make sense to go from europe to nevada but you   get the point some notes so we can save that  booking and it's going to automatically save   it it's going to put that down at the bottom  so you see we now have this we go from rome   all the way to las vegas so that'll be fun and we  can add documents of course as i just showed you   all right so great so now in our dashboard of  course we are going to show that we've got that   ability to show the trips and something as we  went over before okay great so we've got that   that looks really good and of course if you want  to delete that booking all we need to do is click   delete it's going to delete that booking and that  booking is gone now so really we got some really   cool features with this showing the itinerary  what that's going to do is going to hide the   first set of columns take a look at this when we  hide that iteration we've got columns visible from   g through o okay and then we got a set of buttons  when we show that iteration it's going to hide   those columns it's going to show u through a c  and it's going to also determine all of those   stops that we're making for that particular  and then it's going to put them in order and   then what we're going to do is be able to print  that iteration to print to that default so we can   easily print the itinerary it's got confirmation  numbers the type of it and the name and of course   the from to time along with the to date and the  two time the cost and the notes associated with   that also inside our itinerary we've got a trip  total cost so we know the total cost of that trip   we're going to use that and of course we want to  know so if we're going to go to hawaii so that's   really cool i'm also going to show you how to  put this really cool map feature in so and you   know if we want this is going to be inherent to  excel which is great now i'm using the newest   version of excel in this case usually i use  an older one and i almost always almost never   use features but this one i really like  this feature so this feature will come   on just newer versions of excel so that map  but everything else will be fine okay so it's   going to map out we're going to show you how to  put these icons it's going to be very very cool   back to the trips so of course we can add and edit  and update those bookings as we like now the best   thing to do is we want to make sure that we want  to separate everything into proper clear data so   inside this i've got a list of our trips now we  saw the three trips we've got a trip id that's   unique to each trip a trip name ingly and  then the from date the to date and then just some   trip details so the trip is very basic and now  for each individual trip remember trip id one two   or three we've got a list of bookings now that's  going to go into separate table if we go over here   we see we've got a booking id which is also  unique to every single booking we've got a trip id   which is unique to that trip trip number  one trip number two or trip number three   right booking name with the name of that booking  what is the type the confirmation so all those   fields that are associated with that specific  booking are stored in this individual table   so everything that you saw here and we're  going to use data mapping as we often do   which if you haven't seen that before i'll go  over it and basically what that's going to do   is map these fields j11 m11 011 j12 it's going to  map those individual columns with the individual   fields back in our trips so if our booking name  is j11 our booking type is m11 and our information   here confirmation here is o11 so it's mapped that  over okay so we've got those trip bookings we've   also got some travel documents now we noticed  when we were able to add trips of course we can   add in those trip documents right we can add in  some booking information here if we want so we   can preview that or we can not delete it so we  also want to store on a list of documents that   are going to be attached and that's going to be  right here called inside our travel documents and   that's going to be here inside the trip with so  ing to that we've got an individual trip id   we've got the booking id the name of the document  the date the type and the path that's we're going   to be able to create folders and then a row that's  associated with that and we're just going to   use one little pivot table on that but for the  most part everything else is relatively simple   straightforward so we're going to go into that  okay so what i'd like to do is i'd like to start   out with the trips right that's the central part  how do we create new trips and how do we delete   trips and so on and so forth also some information  here on our columns here we've got information i   want to know the if i select a trip i want to  know the id that's associated with that trip   so notice that id is going to change from 1  to 2 to 3 as we select down on those trips and   the row that's associated with that trip i've  got some named ranges they're going to help us   in this let's take a look at some of those name  ranges and go over the ones that i have created   for this so i want to know inside let's start  out with the trip name here let's go for a trip   so we got a trip id here now the trip id is  a dynamic named range for all the trip ids   and we also want to know the trip name that is  a dynamic named range for all of our trip names   and then also i want to know trip cities right  and that's going to be the individual we'll go   over that in a minute as we go over that so that's  going to be something different and so that's for   trips but also what i want to do is i want to go  over some of the booking names so if we go back   into the formulas and named range we take a look  inside the bookings we're going to see we have a   dynamic booking id and that's associated with all  each individual booking it's kind of a brand new   one and i also want to know the trip id that's  associated with every individual book idea we're   going to put that into a named range and also the  types i want to know the booking types right so   all those that's going to help us with those  dashboard and we're going to go into that each   individually so that's some of the basic named  ranges because i need to use that named range trip   id to determine the row so if i know this trip i  d is 1. i want to know what row it's associated on   if we look into the trip list we see that trip  id 1 is located on row 4. so all we're going to   do is use a match formula we've done that a lot  here and of course we're going to use b1 we're   going to use the match and we're going to add  3 because our first one starts on row 4. i also   want to know the next trip id we're going to  use the max formula plus 1 maximum of all of   the trip ids plus 1 is going to give us the next  available one because when i create a new one   is this new trip id that's going to be used for  the next one i also want to know what row has been   selected if i select a different row we're going  to use conditional formatting and it's going to   be based on that so let's take a look inside the  conditional formatting and see what rules okay of   course that's going to be based on b4 if we take a  look at b4 the row that's associated before i want   to color that that dark turquoise with the white  font and also i want to call it the alternating   rows for even rows and odd rows if there is a  value that's contained i want to call it the even   rose a little bit white and i want to call it the  odd rose a very light blue and we're going to get   this that's how we get this alternating color  here we're going to do exactly the same thing   for our bookings not going to go over that but  basically we have a selected row our selected   rows located here on b10 okay i've got the same  conditional formatting for odd and even roles   so basically this one's the selected book is  looking at b10 when i make a selection when i   make a selection change from e5 on down i want to  make sure that there's a value inside e if there   is a value i then what do i want to do is i want  to load the details of that trip i want to put in   the trip name the from date and the to date and  i want to put those in right here okay so how do   we do that well that's going to be based on the  selection change event so let's get into that   and take a quick look inside that so we're going  to go into the developers visual basic alt f11 to   get you there's a shortcut and we're going to go  into the code here now we take a look at the code   inside here we're going to focus on this sheet  our trip sheet that's the one i want to focus on   and we're going to focus on selection change event  worksheet selection change okay and what i want to   do there are some individual shapes that we want  hidden automatically if i select on a specific   document i want some certain shapes to show up  so let's take a look at this right here and you   see this group here there's two groups of let's  zoom in a little bit so you can see basically   one's going to allow us to view the picture and  what's going to allow us to delete that picture   or attachment right so that those two shapes are  in a group and that's called the document group   i only want that to show up if the user makes a  selection change inside there so anything else i   want that to be hidden so the first thing what i  want to do anytime is i want to check if there's   a selection change if that grip is visible i want  to hide it okay we're going to do that i want to   make sure that that's also hidden so let's take a  look inside here the code and take a look at that   if the shapes document group is visible then hide  it shapes document visible equals false i'm going   to hide that also we're going to use saved fade  out messages when i save that trip i want to fade   out message this called trip saved it's going to  fade out automatically right i'm going to do the   same thing for the booking right if i save that  booking i want another fade out message to appear   right here called booking save that's going to  let the user know that the booking was saved and   it's going to fade out now i want to make sure  that those are two shapes i want to make sure   if for any reason those two shapes are visible  which they should not be only for a short time   i want to make sure to hide them so we can do  that with these two lines of code if tripp saved   a message that's one of those shapes the name  of the shapes it's visible then hide it we're   going to do both with the booking saved shape and  the trim so those two shapes that you just saw   we're just going to make sure that they're hidden  because we only want them to appear on a temporary   short-term basis right as it's being saved once  it's done and that's it i also want to make sure   that that calendar is hidden now this is some  code making sure that the calendar the calendar   we only want to show up or that pop-up date picker  that you saw only when i select a date if i select   anything else i want to make sure it is hidden  so that also nested has to be there is some code   that we're not going to get into that's going  to make sure that it exists okay if it doesn't   exist it's going to take it from a hidden sheet  here and it's going to replace it because this   is a shape here so if it's deleted for any reason  it's going to go into a hidden sheet now if we're   going to i'm going to unhide these sheets there's  a hidden sheet called cow pop up right it's going   to take there's some code it's going to take this  calendar and this hidden sheet and it's going to   put it directly over back into there so it's going  to replace it so we keep this sheet hidden i'm   just going to right click and hide it sorry it's  off the screen there but right click the sheet   and hide it so this code right here is going to  help us with that that's selection change code   here we're going to set the calendar we're just  checking to see if the calendar if it doesn't   exist we're going to run a macro that's simply  going to replace that then also i want to know   if the calendar is visible we're going to hide  it that's all and then i also want to make sure   if the target count is large is greater than 2  then x of the sub that means if the user makes   a selection change on more than one cell at a time  then exit the sub and that really helps remove any   errors that might exist that it can happen while  we're selecting okay now what we're going to do   is we're going to focus just on that selection  change that i had mentioned e5 through e99 that's   the on the selection of trips of course we want  to make sure that e contains a value right if e   doesn't contain anything right there's nothing  we should do right so we want to make sure that   e contains a value if it does then we want to run  the macro that is going to load that trip so then   what i want to do is i also want to take whatever  row the user selected i want to place it directly   inside b4 right here so that's the first thing and  then what i want to do is i want to make sure that   we can set an id right once i know the row i also  have this hidden id located right here in column d   if we take a look at this we see inside our  formula bar that that's one if we take a   look at this it's two and three now those are  hidden automatically we can do that with some   formatting here custom formatting so we see the  custom we're going to use custom formatting to   hide that and what is that it's not going to  be sample we're just going to use three commas   three semicolons here one two three and that's  going to automatically hide that so that we cannot   see it so we select on here going back in here  into the custom formatting we now see that there's   custom formatting those three semicolons that is  going to hide it automatically okay so that's kind   of nice but it's there you can see it if in the  formula bar you can see it it's there or if you   double click on the cell you can see it as well so  what i want to do is i want to take that id that   is the id that's associated whatever's in d and i  want to place it directly inside b1 so we do that   with this column here b1 is equal to whatever's in  d and the target row we're going to set that trip   id then we're going to run the macro to load that  trip that is the macro that i'm going to go right   into now and that's going to be located right on  our trip macro so trip macros if i click we have   load list save update and we're going to start out  with just the one that i sewed and then we'll go   back up okay the first thing what i want to do is  i want to make sure that we have cleared out all   the cells associated with all of the booking cells  all the document cells and all the trip once we've   done that on two lines of code i want to make sure  that if there's any preview any document preview   for example this particular shape here let's  say not that one let's find the shape associated   with that which would mean a specific booking  on this particular one we have some bookings   this is always going to be called dock preview  this little shape here is called dock preview as   i load it i want to make sure that there's any  shape that with this name gets removed if it's   not there it could create an error therefore i've  wrapped an on-air resume next and on air go to   zero so we're going to delete remove any existing  picture but it exists okay i also want to make   sure that there's a row associated with this a  calculated row using that if this is empty for any   reason we cannot load it that is the row that's  associated with our trip so that's very important   and we need that here b2 if it's empty then  please let the user know to select a correct trip   okay we're going to turn off application screen  updating to false that's going to make things a   lot quicker then what we're going to do is we're  going to determine our trip roads based on b2 into   a variable i'm then going to run just some basic  data mapping from column 2 to 5 and we're going   to bring in the values and i'll show you what that  means so basically all we're going to do is we're   going to run inside our trip list i've got some  basic from two to five here b to five we're going   to run it so we know the row so in i5 we're going  to put this in k5 we're going to put this and in   n5 we're going to put this and in i6 we're going  to put this so that is data mapping it's going   to take whatever value on base of whatever row and  it's going to place it directly inside here inside   i5 inside k5 or inside n5 or inside directly i6  that's all we're going to do and that's exactly   what this line of code will do right here add an  update application okay then what we want to do is   i want to load the booking list now there's  another macro which we're going to get into   in just a moment booking list so that means i want  to know all of the bookings associated with this   trip right here all those bookings right i want  to load that so that macro is going to do that   great so that is up to trip load okay then we're  good of course we're going to turn on application   screen updating okay we'll be going over this  macro soon but first we want to cover all of the   macros associated with this trip so let's go right  back up and start up at the top first thing what   we want to do is load the list of trips right if  i run this macro we do this every time we save a   trip if i make a change to that if i make a change  and i say i want to call this europe22023 right   and i save that trip i need to make sure to update  this list here with the correct name right so this   list there's a macro that's going to run when  i make a save that trip it is this macro that's   going to run and automatically update this list  so that's exactly what we're going to be doing   so first thing what we want to do is with the  trip screen i want to clear out the selected   row the first thing what you want to do is clear  out that selected row because we don't know what   order it's going to be so b4 that's selected trip  row is going to be cleared out i also want to   clear out any anything from all the ids located  in column d all the way to e and down so i want   to clear everything out so from d to e 99 we're  clearing all the previous trips and id numbers   once we have that i also want to focus directly  on the trip database right the trip database   is where we're going to load all of them  so if we take a look inside our trip list   this is the one we want i want to load up all  those trip names right and i want the trip ids   and i want to make sure that it has a to date so  we're going to bring it all in here these unique   lists just in here just to make sure that we have  everything we need so we're going to determine the   last row right with the trip the last row we're  going to clear out the contents of any previous   results right i want to make sure that we have  the last row and then what we're going to do is   in case there's no data flash rows less than three  we're going to exit this up we're going to run an   advanced filter because i just want to make sure  that we have the to date right and why is that   important why is that important well because  we do have a filter on this another feature i   failed to mention to you again i want to include  completed trips or not right so if i select here   i unselect we don't have any completed chips but  if i select here i want that same macro to run so   if there's a trip that maybe we didn't want let's  put this back maybe a different year let's change   this to 2021 right that would be completed here  so 2021 and let's change the year on here right   go back and put this in back in history a little  bit so bringing this back here and putting that   right about here let's see march 31st okay so now  i'm going to save that trip and oh let me update   that and put the right date on here 2021 and now  what we want to do is save that trip okay and   now we see only two trips appeared right they hit  the ones in the past didn't but if i include the   older ones they're going to appear automatically  right so it's really great so what i want to do   is i want to know which ones are in the future and  maybe i only want to filter out only those in the   future if they've been completed i don't want  to show them so we can do that very very easily   but we do need some criteria for that so inside  our trip list what i'm going to do is i'm going   to set the to date if i know that i've decided  that we are not going to show any older trips or   completed trips i'm going to unselect this now we  have a specific cell that's tied to the value so   when we format that cell we see that the link is  b5 and we take a look at b5 and b5 as false okay   now what we want to do is we need to basically  take some criteria here and we can do a little   bit of a formula so basically if b5 equals true  then i'm just going to show anything with a 2-day   right anything regardless however if it is false  i want to show anything greater than or equal to   the current day and that's for the two date right  so any trips that have a two date greater than or   equal to the current date right that means pass  anything that's completed is not going to show   up and that's how we get this criteria here right  so it's very easy and we see that it's been stored   here so that's how we can easily see that we  have those trips so we can show both in them   so we're going to use that as our criteria so  our criteria is then going to be n2 through n3   and that's just we have in our advanced filter so  we're going to take all the data from a3 notice   the header rows a3 we can use our criteria from n2  to n3 and we want those results coming in through   p2 and q2 p2 now we only want the id and we  want the trip name and that's what we have here   p2 through q2 we only have the id name because  it is the only the id in the name that we want   to bring back into our trips and put them in d5  and down and that's what we're going to do we're   going to determine the last results row based  on column p if the last results was less than 3   then we're going to exit this up otherwise we're  going to bring over that information right here   inside from chips d5 through e plus two we need  to add two because we're bringing that in to row   five as our first but it's coming from p3 right  so we know we need to increase that and that's   all we need to do okay so that's how we load the  list but how however how do we save or update that   and of course that is the macro that's tied to  when we save or update when i click this button   whether we're saving a new one or we're updating  the existing one we want it to show up okay so how   are we going to do that well we're relatively  easy the first thing we want to do is we want   to make sure that we have the fields required i  want to make sure that there is a trip name a from   date and a to date i want to make sure that those  are all filled in if any one of those are empty   then of course let the user know so that's the  first thing we're going to do if i5 is empty k5 or   n5 is empty then let the user know to please add a  trip name from and to date before saving the trip   we're going to exit out of this up i also want to  check to make sure if there's any duplicate trip   names right i don't want to have duplicate trip  names i want to make sure that each trip name is   unique now we can check that with a simple formula  right and we're going to have a red formula right   here and so that formula we're going to wrap that  on iferror i need to differentiate i need to know   is this a new trip or it's an existing trip so we  can separate that now we know if it's a new trip   b2 is going to be empty we know that because  there's going to be no calculated row and now that   we know that we can take a look back inside our  duplicate trip name if b2 equals md then of course   we're going to focus on we know it's new then we  just need to check to see if that same name exists   inside the list of trip names so what i'm going  to use is i'm going to use the match formula now   match filament now i know if there's an error with  this meaning it's not found right if i5 looking   at i5 i'm looking inside the trip name if it's  not found it's going to result in an error right   so what i want to do is if we're going to check  for an error if that error is false right meaning   that there is a match there's no error right is  air equals false that means there is a match if   there is a match we're going to put true otherwise  we're going to put false okay so this is for   if it's found if it's in if it is a brand new trip  however what if it's an existing trip then what i   want to do is i want to look this up and i want to  see if it is found on row five if it is great if   it's found on another row then we know there's  a mat there's a duplicate so we can do that   if we're looking up the trip name plus three  right we're looking for the match if we're if   we're looking for that row and a plus 3 means this  is the row that's associated with it it's found on   this row if it does not equal b2 means it's found  on another row then we know there's a duplicate   then we'll put in true otherwise it's false now  if there's any error we're just going to put   false that is all we need to do so anytime this  is true all we need to do so if i put in hawaii   here we know it's a match right we know it's  found on another row this is going to go to true   right we know that because it is why it's already  been used so automatically it's not going to let   us save a trip it's going to say this trip name  has already been used please create a unique trip   name right so that's all we have to do to just  simply check for that duplicate whether it's a new   and we can do that here if b6 equals true the name  has been used so that's all we have to do okay now   what we want to do is we want to determine is it  a brand new order or is it an existing actually a   new trip right so if it is a new trip we need to  do a few things if b2 is empty as we've discussed   already we know it is a new first thing we need  to do is determine the trip row the row is going   to be the first available row inside the trip  database that's the list of trips now we're   going to use an xl up plus one then what we're  going to do is simply take that next trip id that   calculated through the max formula that's located  right here and we're going to place that directly   in b1 i'm also going to take that same next  trip id that we placed in b1 and we're going to   place it in the column a we're going to put that  directly inside a right here in the next available   row so that's what we're going to do right  here b1 is going to take on that next strip id   column a along with that new trip row is going to  take whatever take on whatever's in b1 that's all   we need to do if it's an existing if it is if a  new trip if it isn't existing all we need to do is   just extract the trip row from b2 everything else  we're going to use data mapping just as we did on   load but this time it's going to be in reverse  from columns 2 to 5 we're going to take whatever   is in that range what's located on row 1 using  data mapping row 1 remember that row 1 whatever's   in i5 whatever's in k5 or whatever is in n5 and  we're going to place it directly in here now when   i say i5 n5 or k5 right that means it is going to  be whatever's in i5 here whatever is in k5 here   or whatever is in n5 here and we're going to place  directly inside that specific specified route here   here and here and that's how we use the  data mapping so that's all we need to do   now what i want to do is i want to run the  macro once we save it right we're going to run   the macro to load that list we'll go over that  list that load list trip load list is what we   went over here and that's just going to  reload the list in case we change the name   so re or add the new one reload trip list okay so  now what i want to do is once i've loaded the trip   list right how do i do that so let's take a look  at this let's say i change this back to 2021 right   where it should be right what i want to do is  i know that i want to make sure it's selected   when we load it when i save it it's going to clear  it out i want to make sure that 22021 gets loaded   again right if i change this here to big trips  big trips right and then i want to make sure that   that name gets selected again so i need to make  sure i'm going to look for the row wherever i   find this and i'm going to make sure it's selected  so i'm going to take whatever row and i'm going   to make sure that b4 contains that row right i  want to make sure it's selected again so we can   do that with the following code okay first of all  i'm going to get that trip id that's going to be   located in b1 then what i want to do is i want to  determine the trip row how do we know the trip no   well we also know we can use the names match  but we also know the ids are located here right   if i know the ids are located here i do one two or  three and i know that we just saved two all i need   to do is look for the id using the find if it's  found in row two here it's hidden but it's found   right if i change that format it'll change it's  here we see the 2 up here i'm going to look for   what row whatever row it's found on including 6  i'm going to place that 6 directly here so that's   what we're going to do we're going we've got the  trip id and a variable already we're going to set   the selected trip rows to be based on d5 through  d9 we're going to use find we're going to use trip   id now we're going to use xl formulas this is very  very important because notice it's hidden when   it's hidden either with a hidden column or with a  custom format like it is it's not going to be able   to be found if we're using excel value so we must  use excel formula and that will find it and what i   want to do is i want to extract the row once it's  found if it's not found it could create an error   therefore we wrapped it in on air resume next  and on air go to zero if the selected trip bro   that's like the tripod doesn't equal zero then i'm  just simply going to take whatever it is and i'm   going to place it directly into b4 that's going  to trigger the conditional formatting and then   we're just going to run that fade out message  that fade out message is simply going to take   that shape the shape that we have if we take a  look inside here and we view that shape right i   don't want to see all those shapes we look at the  selection pane here and i want to take it we don't   need to look at that calendar but we scroll down  here and we can find that shape that trip saved   message or here the booking saved message so it  is this shape that i want to show i want to fade   that shape out called trip save message this  one shape is called booking saved message so i   want to fade those up so when i save that trip  i want to show that message i want to slowly   fade it out to give the user a look and it's  going to be exactly the same thing with the   booking saved message so when i save that booking  we got to select a booking to save it when i save   that booking i want to make sure that that booking  message also fades out so we can do that with this   line of code so it's very simple we're going to  take basically that shape and we're going to use   a loop and we're going to change the transparency  lighter and lighter or increase the transparency   as we go through and then lastly we're going  to hide the shape that's all we need to do   on new trip that's the same macro that we run when  i click new trip i want to clear everything out   and we want to make sure that we want to  clear all the selected trip rows the selected   booking rows everything else gets cleared out  so we're going to clear out a bunch of cells   using the clear contents we're going to delete  of course any document preview and we're going to   select i5 we want to make sure that i5 of course  is our trip name and that's going to allow the   user to put it in okay all right great so what  that's going to do is clear it up now trip load   we're going to do very similar we're clearing out  all the cells we already went over that macro here   trip delete very very simple when we delete a  trip we want to do just a few things this is very   important that not only if we're going to delete  i'm not going to be i'll create a new trip okay   if i create let's call this test trip okay let's  add in a brand new date here and we'll go to date   and we'll add in a brand new date okay so test  so if i add if i save that trip of course that   trip is now going to be here notice that there's  four notice the four is displaying because that's   not custom format right we have not set the  format here just to go over it again because   it's really important a formatter three semicolons  is going gonna hide that in the custom here three   semicolons so one two three is gonna hide that  and it's always gonna be hidden okay so we can   see that there so we know that that's trip four if  i want to delete that trip what do i need to do i   wanna make sure that i'm deleting all the bookings  that are associated with that so if i have a   test booking here and i've got a new book and type  let's say i added this is a tour and we've got a   confirmation and that tour is going to start on  here and it's going to go to a different date here   that doesn't make much sense but fair enough  so i want to save that booking i also want to   delete any bookings that are associated with that  trip if i add a document i also want to delete   any documents that are associated with that so we  have to be able to do that to make sure that we're   saving that okay so not only do we need to delete  the trip right not only do we need to delete row 4   here but i also excuse me row 7 id4 but i also  need to delete any bookings that are associated   with it which is this booking here notice we  have a booking idea for the the booking id of 29   the trip id of four trip id you can see it up here  trip id booking name so this test so this also has   to be deleted i also inside our travel documents  i need to make sure that this gets deleted here   right so when i delete it we have to make sure  that we're doing all that so how are we going   to do that well we're going to do that with this  macro here so first of all we're going to give   a confirmation to make sure that they do want to  delete the bookings if they say no of course we're   going to exit out the sub then what we want to do  is i need to determine has it been saved or not   right if i do a new trip right if i do a new trip  and i put in test here test here and i delete that   trip it's going to be very easy so let's sign that  macro properly i'll probably change the macro name   so what i'm going to do here just select on this  and hold the control down select on both we're   going to look for that trip id so we're going  to assign the macro and then trip delete is the   one here that's the one we want to assign okay so  first we're going to get the confirmation message   are you sure you want to delete this trip if we  say yes then what i want to do is i want to delete   it but if it has not been saved yet we can simply  skip deleting all the rows because we didn't save   that and so that's what we're testing out here  how do we know if it's been saved before well of   course b2 is going to be empty if it has not been  saved so we can use that if b2 equals mp then we   can skip all of this here and just go do not save  and then all we need to do is simply run the macro   that's going to clear out the contents and reload  the order list okay however if it has been saved   i need to delete the individual database rows  both for trips both for bookings and of course   for documents so that i'm going to need to delete  so the first thing we're going to do is determine   that trip row inside a variable it's going to  be located in b2 that's going to be our trip   row so our trip row is associated with that and  then of course we're going to delete that entire   row in the trip database okay that's easy enough  for that but what about our bookings for our   bookings we could have multiple multiple bookings  right so if we have multiple bookings i'm going   to need to delete more than one right so if i test  this trip out and i add an additional booking here   and no let's say add an additional  booking here and then just put flight   anything is going to be fine here and then i'll  put in the date the booking types our house that   doesn't make so much sense a house shouldn't be  a flight at least a little bit of making sense   even though it's test data and then what i'll do  is just give it a specific time and that's going   to be fine so i'm going to save that booking so  when i have more than one bookings right i need   to make sure that both get deleted both these get  deleted now i know what if i delete row 32 first   then it's going to change what row this is so i  need to make sure that i only delete row 33 first   then i delete row 32 so what we're going to do is  we're going to provide an advanced filter and what   that's going to do is that is going to let us know  where those bookings are associated so if i take a   look inside the booking list here i want to know  which ones we're going to be deleting so let's   take a look inside these two bookings here we  want to show those names which are going to be   right here this is what i'm looking for here so  what i'm gonna do is i have these i know the rows   if there's rows associated with that and we're  gonna bring those in and make sure that there's   a rows so once we run that it'll give me the rows  number row numbers then what i'm gonna do is i'm   gonna reverse it i'm gonna put the highest row  first and the lowest row second so i can delete   the highest row first then delete the second so  that's how we're going to do it so here's where   we can do bookings we're going to determine the  last row if it's less than 4 we're going to go   to not save we're going to run our advanced filter  being an a 3 through u and what that's going to do   is can run an advanced filter for all of our  right i want to make sure that u is included   all because we need that row so a3 all the way to  the last and i want to put those results what is   the criteria well the criteria is going to be only  for a specific trip id it's going to be based on   this trip id this is simply linked to b1 in our  trips here's our criteria we want those results   to come right here from a2 through ac2 that's  where we want the results to come aa2 through ac2   once we're gonna then what we need to  do is determine the last results row   if the last result was less than three that means  there are no bookings associated with that trip   if the last row is less than 4 that means there's  no need to sort it we can skip the sorting however   if there is more than one row i do want to sort  and i want to sort descending i want to put   that highest row first that row is going to come  directly from column a c right here so if i run   this macro up to this point right here right and  then what we're going to do is we're going to see   that list right actually i can run the full macro  the data will remain in the results which is fine   so ac and i'm going to run that our range going to  be aa through ac we're going to do the exactly the   same thing then what we're going to do is we're  going to loop through those results starting   from 3 to the last row i'm going to determine that  database row based on what's in ac3 and then what   we're going to do is we're going to delete the  entire booking row here we're just going to loop   through and do all of that we're doing exactly the  same for travel documents identical except we're   just using we're going to run our criteria and  this our travel documents a little bit different   our criteria is going to come directly from j2  through j3 that trip id is going to be located   here we're going to have those results come  directly inside and through the p right those   results are going to come directly from n2 through  r the r is fine okay r is fine and then what we're   going to do is i'm going to determine the row  here this is located in n the highest number i'm   going to sort it based on n 3 this time descending  right because that's where our row is located and   then we're going to loop through and delete those  results then we're going to load it so that's all   we have to do so again let's take a look inside  here if i delete this trip with these two bookings   delete the trip are we sure to delete it  yes right it's going to delete those trips   clear everything out the trip's gone the bookings  are gone and inside the trip list we see that   uh trip id4 is gone along with the trip bookings  here four is gone and the travel documents here   four is gone okay great so we know how to delete  that i'll take a quick look back inside right so   we just went over delete so what we're going  to do is simply just deleting those associated   clearing out all the forms and fields by running  the macro trip new and then loading the list   reloading that list because we just deleted it  great now let's take a look at the itinerary   right take a look and say when we want to show the  itinerary we see what we've got let's take a look   inside the trip let's add in a specific itinerary  where we have good what i want to do is i want to   basically run another advanced filter i'm going to  load all this information for the specified trip   so inside this what we're going to be doing is  we're going to be loading in all that information   using a very specific trip booking so once  that we have our trip booking i want to load   all of the associated information for a specific  trip id so we've got an itinerary results this   time we're going to run the same criteria y2  through y3 and then what we're going to be   doing is we're going to have all that information  come here and then inside bring it inside there   after we run that so there's two macros that are  associated with that the first that we're going   to do is simply we're going to be toggling  between that so if we take a look inside our   module itinerary macros we're going to find two  different macros here inside this module and then   also we're going to one called itinerary viewer  hide because it is the same macro that we're   going to be using for both hiding the itinerary  and showing the itinerary so all we need to do   is check to see if there's a column for example if  column g is already hidden we know that we should   unhide right if we let's say if the g is visible  we know we should show the itinerary however if   g is hidden we should hide the itinerary so we  can use column g to determine that or any column   so we're going to do just that with the trip  if omg entire hidden equals false means it's   not hidden it is displayed then what we want  to do is we want to show the itinerary so   we're going to take a trip group now trip group  i've created a group for all these shapes here   and we're going to call it the trip group so  i've grouped them all and we'll call that trip   group i want to hide that group i don't want to  show that group i want to display another group   i want to display something called itinerary our  itin group this is the group i want to display so   we need to show and hide buttons ingly so we  can do that here so we're going to the trip group   we are going to make that visible equals false  we're going to hide the trip group we are going to   then show the itinerary group using this line of  code we're then going to hide columns g through t   and we're going to show columns u through a c  so that's simply going to hide these columns g   all the way over and then we're going to display  u through a c after we do that all i want to do   is just refresh run the macro which we're going  to go over next called itinerary refresh however   if we're going to be hiding we're going to do  exactly the opposite except we're not refreshing   the itinerary we're simply going to be showing  the trip group hiding the itinerary group and   then reversing the columns showing and hiding  ingly that is all we need to do is simply   view and hide it is the same macro tied to both  of these buttons it is the same macro tied to this   button and this button next up i want to be able  to print the itinerary i want to be able to print   it to whatever our default printer is i'm using  snagit so i want to default that if i want to do   that i want to make sure that also when we print  it to our default then we're going to print it on   our landscape orientation and we can do that with  just a few lines of code so printing the itinerary   and i probably should have put that let's go  ahead and put that up here this here i'm going to   put this mac i'm going to copy that i'm going to  clear that out i'm going to go into the itinerary   and i'm going to place it right here so printing  the itinerary right here i like to have all these   so we have three macros showing hiding printing  and then refreshing so all we need to do is simply   run this macro trips print out true false and  that's going to print that itinerary relatively   simple there let's get rid of this one here i  have two macros that are associated with the print   this one i like better because this is going to  dimension it better so i want to make sure that   it is this one that is tied to that button and  the icon associated that's what i thought two   different macros this i believe is the one that  we're using because it is correct always but i'm   going to double check it making sure i'm going to  right click this holding the control button there   and then what we're going to do is i'm going to  assign the macro to this just to make sure that   we have the right macro slide itinerary print  itinerary that's the one i want that's the macro   that's going to be used and we're going to click  ok so it is that macro that we're going to go over   along with the refresh so first we'll start off  with the refresh and basically with refresh all   i want to do is simply run an advanced filter  based as i mentioned it to you however inside   the trip bookings i'm going to certainly start  to clear i want to clear all the information here   but more importantly what i want to do is clear  any information here inside that because if i'm   going to be clearing it so we're going to clear  all the way from you through ac we're going to   clear that inside the trips u6 through ac we're  going to clear all the existing alternative   now we're going to focus on the bookings we're  going to determine that last row inside the   bookings list if it's less than 4 we're going  to exit the sub from a3 to you remember we're   going to run that criteria y2 through y3 inside  our trip bookings our criteria is automatically   set based on a formula y2 through y3 those results  are going to come here af through n and then we're   going to bring them down so af through a n is  going to where our results we're going to check   the last row through af it's less than three we're  going to exit the sub out and then we're simply   bringing over the results that's it for refresh  relatively simple printing the itinerary i'm   going to determine once we've brought it in i need  to determine the last row of our results so the   last row we can use here because that's a required  field under u so u is going to let us know so the   u 99 this is going to give us the last itinerary  row last row once we have that last row as long   as it's not less than 6 if it is less than 6 let  the user know to please add some booking details   before they print the itinerary we're going to  set that page area i want to set the print area   i'm just going to start off on u4 which is that  header all the way over to ac in the last row   that's going to be our print area right once  i have that i also want to set the orientation   page setup orientation equals landscape and then  we're going to print it out that's it simply   making sure that we are always going to align with  that page setup print area that we've just set up   here okay we don't want to print outside that  that's it that's all we need to do for both the   itinerary and the trip so what about the booking  macros booking macros is relatively simple right   with the booking macros here we've got a few ones  here inside there of course we are going to be   saving the booking new booking and deleting the  booking and also loading that list up every time   we save a booking we want to make sure to reload  the list to make sure if there's any changes   they automatically get updated so let's go over  those macros now so back inside the vba we're   going to focus on booking the module called  booking macros and that's going to go into   order and this time booking macros first thing  we want to do is load the list right anytime   we want to reload this of course we need to  make sure that we're clearing out the existing   keep in mind that we do have those ids those  booking ids hidden in column f so we want to   make sure we clear out f through g and all the  way down so with the trips here f10 through g   and down clearing the contents of those existing  bookings next up we're going to primarily focus   on that bookings right i want to know all the  bookings associated with a specific trip so we   want to make sure that we're going to focus on  some criteria again link to that trip id located   in y2 through y3 i'm going to bring in all those  bookings from aab bringing in the advanced filter   so we'll move a little quicker because we've  been on these before getting the last row and   then making sure it's less than four bringing in  that advanced filter y2 t