Discover the Travel Bill Format in Excel for Healthcare

Travel bill format in excel for Healthcare

running a travel agency has never been easy there's tours trips customers payments seed booking and scheduling and a whole lot more how do you keep track of all that hi this is Randy with Excel for Freelancers and today we're going to do just that with the travel agency manager I'm going to show you how to create this incredible travel agency manager designed completely from scratch complete with customized seating arrangements unlimited tours unlimited trips and we're gonna do it all from scratch complete with full bookings booking detail and a whole lot more you won't want to miss it I cannot wait so let's get started all right thanks so much for joining us today I've got a really fantastic training for you the travel agency manager this one was highly requested we're going to be able to do custom seating for any type tool we're going to be able to do any type of tour bus Cruise Visa service you name it anything for a travel agency we're going to be able to customize bookings we're going to be able to have seats customized seating completely from scratch we're going to be able to design this screen and of course we're going to be able to book out individual seats and then edit those so if we have a booking here and then we can edit that booking it's a brand new screen where we can customize the individual bookings on a tour we're going to have to be able to filter that we can filter by any type and of course we can customize any seat adding or deleting any seat removing it and then also the individual booking so there's a lot to this we're going to be doing the tour screen completely from scratch this is fully customizable and we can have a lot of features so it's not training you will want to miss I'm going to go over every step with you we're going to be designing this section from scratch so if you do like these trainings and you do want this template I bring these to you each and every Tuesday absolutely for free even this template's for free all you need to do is click the links down in the description and I'm going to make sure you get that with your name and email sent right over to you if you could just do me a quick little favor I'd really appreciate it go ahead and click the Subscribe if you have not done that already don't forget to hit the like button and comment below let me know your ideas your feedback what you like what you don't like how what do you want to see I've got over 300 of these incredible trainings and I'm creating more every single week but it's based on what you like your feedback what you want to see me do so I'm here for you to serve you and so your feedback is greatly appreciated all right so this is what we're going to do if you want to support the channel so many great ways to do that and one way is to join our patreon in fact this particular training bike every single week what I do is I take your ideas your feedback and your ideas and I put them all into an additional training so you get an updated workbook and you get a brand new training a video on our patreon platform as well as YouTube YouTube as well we have YouTube members that's the same as patreon silver and I do that along with a PDF code book and a whole lot of other features and that's every single week for just a few bucks a month you get a ton of features including all the resources pictures icons and everything that went into this training along with a bunch of other features so I hope you'll join us for that I'll include the link down below we're gonna get started right away on this training so what I'm going to do now I'm going to give you an overview of that and then what we're going to do is I'm going to move this over we've got a blank worksheet that what I'm going to be starting off here is that blank worksheet here and then we'll be starting it off on that so what I want to do is certainly go over this with you give you a high level overview of all the features and all of the things great things about this and even if you don't want to create a travel agency manager the techniques the tips and the tricks that you're going to learn in this training will allow you to develop almost any type of application because the fundamentals are all the same so you're like well what am I going to do with a travel agency but to be honest with you everything that I'm going to teach you you can then create looks like a puzzle so you can put these pieces together to create any type of application and if you've been with me for a number of weeks or months or years you realize that it's almost always the same components but they just get built together in a different way to create these applications so once you have the fundamental you can design nearly any type of application for yourself for your customers or you can create your own freelance career and then you can sell these on places like Etsy for a lot of money so it's a great way to do it I'm here to help you I'm here to teach you and show you a great way to do this all right so what do we have here in the admin screen what I have is a folder for attachments you notice inside both the tours we've got some pictures here and also so inside the bookings we've got some booking pictures that will be specific to every booking so we need to store those somewhere in some folder it's going to be a photo that looks just like this here and we also have to map where that folder is located that's located right here called the attachment folder I've got tours and trip types and we've got individual icons what this is going to do is going to allow us to build out this Dynamic menu here so when you select on a bus or we select on a boat or Ferry those types of I think I have some tours here here we do I have a Visa service here and I've got a few others I think we have a cruise here I've got two cruises so what we can do is then track your tours and trips by type so that way if you have a lot you can easily drill down to the ones that you're looking for very very easily using this so we have a list of those of course you can customize it once we have that we're going to be able to add or delete an icon simply by there just adding that we can add a Nikon and also we have custom Fields this is really helpful custom Fields tour and trip custom Fields if we take a look inside our tour we've got six different custom Fields right here that means the user can decide what Fields they want for their travel agency just as well inside the admin we also have six different booking customer and custom Fields so this right here so when we book we have six different fields right here these are specific towards every single booking so you may not need the eight additional or you may want additional then the eight fields that I have here so we've got six additional fields that are fully customizable very very helpful okay so now I'm going to show you how to use that also we have seat status so this is kind of nice so once we have we can set a reserve maybe we want to change it to yellow and we want to change the pay to a darker yellow or something like that automatically that's going to be reflective if we refresh the booking you see now that our occupied and paid seats are located in different colors so that's fully customizable we can very easily create a brand new tour with a brand new seating chart all we have to do is Click add new to or here and then we can just call it let's say we're going to call it bus tour again the type is going to be a bus here we can give it a description if we want we don't need to do that right now and then what we also want are we going to sell tickets yes we're going to sell tickets perhaps we want to set the max quantity at 60 tickets and we want to have let's say three rows at uh 20 columns no let's do 20 rows at three columns and so we can create that which is kind of maybe we'll do 40 because it's a little bit bigger so we can do 80 give it some more uh fill so we can easily do that all we would need to do is just save and update that and then just click on that's automatically going to create those seeds so we see now we've got a brand new tour and now maybe we want to add a booking for that bus tour all we need to do is just go into the bookings here add a new booking we're going to select the tour that's going to be our bus tour here we can create a contact name here Fred's gotta make his appearance so quickly already and then we can set a date booking phone number and we can can also add seed to that so maybe we want to then save and update that we can save it we can add attachments to that booking we may want to then reserve seats for that booking so maybe we want Fred's got one seat on seat number one and then we also want to book so notice how his girlfriend Frida and we he's she's going to be next to him at C2 so we can easily book out the seat so we see now both one and two have been booked out if we decide that they're now paid we can just set those to paid automatically and we see that the two seats are now paid and they show up differently so if we were to go back inside our tours and we take a look at that and we refresh that tour we see now that both of those seats are now occupied if we were to click on one of them we can see that the Fred Fredericks has booked one out and we see that Fred Fighters has the information we we would have all the information for Fred on the booking details we could edit those booking details it's going to go right back here where we have the booking details so everything comes really really nicely together we can create this incr incredible application so we're going to get started right now this is the screen this tours and trips to the screen we're going to be creating absolutely from scratch so what we're going to do is I'm going to move this off to the side here so I can keep an eye on it make sure we don't mess things up too much and we're going to create from this blank screen right here now keep in mind that I do have some of the macros which we'll be going over the admin screen is already present so that's going to help us save some time because these videos tend to be long okay so what we're going to do is I'm going to reserve columns A and B for admin as we always do and those are going to be in this gray color so I also want a title for this now this particular title what I'd like to have is just a similar to this one so you see this admin settings so this is going to make a little quicker I want it to look just like this the theme so I'm just going to copy this one it's just a all it is is a shape so if we were to insert a shape and it's just this here particular text box shape it's just been formatted but it's a little bit quicker if I can copy that I'll try to go over every single detail but try to you know make these things a little bit quicker so that we can see so instead of admins and settings we're going to call this tours and trips as you've seen tours and trips we'll also we want a background we notice that we have a special background so let's put that in right away so that we can see it so I'm gonna go into the page layout I'm going to click on the background and then I'm going to select from the file and I've got one saved up already it's right here called background I'm going to insert that okay so that's our background so we get a good idea of what that's going to look like now also Insider Admiral will be putting some information here we also want to have a menu here I'll be going over the menu that and right here inside D2 we can get rid of the grid line so I'm going to go into the view and the grid lines and we can remove those now we're going to be using this bar lot so I'm going to show the tabs and commands for a while so we can see it inside D I want to put in D3 is going to be select tour trip type so we're going to put in capital letters and it's going to be in white font it's already a little bit formatted to help us so I'm going to put up your select tour backslash trip type okay so it's all already in white font notice it wasn't that just saves us a little bit of time if I've saved the font colors then what I want to do is I want to have a list of those tours and trips right here so inside up here in the form of barn to put tours and trips so I want the list so that's the main heading in the subheading I want to put the tour name so the tour name and then after that I want what is the status of that tour so you can have different statuses okay I'm going to skip a column and then this column is our spacer common age so we've got our tours list of two all the tour types we're gonna have some filters here so here right here and then what we want to do is I want to put some information here I want to put all the tour details here notice it's already been Emergency Center tour details Okay so we've got that here now what are those tour details we saw that I'm on the sample screen so we got name and then we've got so notice it's not the reason it doesn't show up here is because it's both white notice the background is white and the font is white but you can see it up here it's right here so as I type it in and of course we're going to change the background to a blue font so then they'll be easier so then and we will have our field here this is where our we're going to enter that we're going to skip a column this is going to be our spacer and then next up I want to put type that's going to be like bus or whatever type then we're going to skip down here and I want to put a long description what is the description of that tour so I'm going to the description that's going to be a long version center field we're going to drop it down once and we're going to put in the destination destination here and I'm going to move 1 over and then I'm going to put in here the status so it's going to be a status now we have tour and trip statuses those statuses are going to come from a drop down list so notice we have tour status we also have booking status and we have seat status so we have all those different kinds of status so keep those in mind and then we've got currency that's going to help us so if we want to change the currency so please note of the different statuses all right so continuing on with our design so we have status inside it's a little bit hard to see but as soon as I change the background we'll be able to see that very quickly okay so then math fact maybe I can do that right now so I'm going to do is I'm going to hold down the control and I'm just going to skip one row we're going to go all the way down to 28 so skipping one and that's going to be a little bit easier so we can see it and we'll set a specific background on that and we're going to go all the way to 26. okay so this is the last one and then we can also do the same thing here not for the destination we'll do this one right here what we'll do is we'll just format these real quickly so that's going to give us a nice look and feel we'll be able to see exactly where we're typing not that one and then as we do that I'll use a standard blue something a little bit close to the theme and a white font so what we'll do is we'll use it we can do control one that's going to launch that so we're going to set the border on that I'm going to use a white border so we'll use white and I'm going to use it on the top on the left and the bottom since it's why you can't see it and then I'm going to use a dotted line which is actually here right now and if you notice you can't see it and then we're going to give it a nice fill look and I've got some colors that are saved up and we can find those colors in the fill effects so when I drop it down here we can see that we have this color right here a darker color here that will help since it's going to be a dark I'm going to use that same color color here so clicking OK and we're going to click ok ok so now we can see it we've got a good idea so uh now we continue our so after the status what do I want to have I want to have the deposit date deposit date and then next up I would like to have the price and then we'll start in with our custom and then after that deposit I also want to put the arrival date actually should be departure date I think departure I think I had departure date and then arrival date that makes a little more sense instead of the pot we'll deposit we could use that in the custom fields and then the arrival date we want to have here we've got there below that I'm going to put the currency we're gonna have a drop down list of all those currencies you saw in the admin contact what is the name of that contact when we create there's a specific contact for a tour someone we need to contact we have to have that and then of course the contact email I'll drop it down here we're going to put phone number one a contact might have more than one phone number if they're leading a tour and phone number two okay next up after that I want to know if we're going to be selling the tickets or not sell tickets it's gonna be a question and that's going to help us know whether we're going to be generating those seats or not and we'll set the maximum ticket quantity that's going to be the field dropping it down here these are going to be our custom Fields now what are our custom Fields our custom fields are the basically actually I got one row seat rows and then C columns when we build out those seats we need to know how many rows and how many columns as you see here's where our custom fields are going to start so basically our custom Fields this one's going to be equal to whatever is located right here our tour and trip so we see right here our first custom field however if this is empty I wanted to revert it to whatever is located in D so it's either going to be e or if it's empty it's going to show what is in custom field so what am I going to do let's take a look at that it's going to be a formula so I'm going to say equals if and I want to know if this custom field is empty if this here requires to have equals empty then what are we going to do then I want to default the label to this custom field otherwise Sim simply show what's in here that's it that's all we're going to do now I'm going to copy that then all we need to do is just change the number for each subsequent custom field so I'm going to hit enter and that's going to put the required staff so this one is going to be 20 e24 since it's simply the row below so we just need to then add that here and update that okay so next up we're going to do 25 26 27 28 relatively simple and I'll speed this up because we don't need to see me doing this each and every time foreign [Music] okay great now that we have all of our custom Fields set up we're ready to set we've got the labels now what I want to do is I want to set the fields up so I'm just going to hold down the control notice we have some data validation that's already here can help us move things along we will go over the data validation that exists and of course the named ranges that I've created to help you along so that you can create your own ones okay so now that I've selected all the fields I'm just going to use control one and I want to set the formats of those we're going to set the fill to White and I said the Border using that same color our consistent color that we have we'll use a dark blue here and then I'm going to color the top the right and the bottom okay so now that we have that we've got our field set up so I like the way that that's looking there next up what we want to do is oh for that name and type so I'm just going to copy this here and then we're going to paste the formats let's use this one right here this one right here and then just we're going to paste the formats right here okay pasting those formats so now we've got all of them the tour dates next up we also have the tour seating so I want to put the tour CD in here so I'm going to tourist seating and this is where I want the seating to be displayed for that tour I'm going to skip a column next up I want to know all the bookings that have been done for that tour so bookings all the bookings we'll list out here we'll use it in two we want to know the contact let's do that in contact name and I also want to know the status of that booking is it fully paid is it partially whatever the status is okay next up lastly what I want to do is I want to have the booking details if I select on a specific booking I want to know all the details of that booking booking details but we're also going to be able to edit that too okay so now that we have that and then down here I want to put notes here so I'm going to put all the information for that booking and we're going to copy this here and then I'm going to paste this down right here all the way down about here so we're going to paste the formats of that that's going to be for our Fields so what Fields we're gonna have we're gonna have contact name and they're going to be the same in our database booking date I want to know the date of the booking these are for the booking details contact phone I want to to the phone for that contact I also want to know the contact email next up after the email we're going to put the status of that booking each booking has its individual status the total amount and the amount paid you may see if they have not paid in full that's certainly important to know okay once we have the amount paid I want to know the departure date and next up I want to have actually I want to have the custom these are going to be custom Fields actually these here these six of these six uh these six these six are going to be all custom Fields so in fact we need a formula for that why are they formulas because these are booking detailed custom Fields notice each individual booking has the custom Fields if we take a look inside our booking screen we see that these six fields are also so in fact I'm going to use the same exact formula here except remember these are going to be based on our book in customs so it's going to be starting here e32 and down so what I really want to do is just copy that formula right down there so we're going to go back into tours this is the we're going to use this one right here this one and I'm I'm just going to hit enter okay that's perfect now all we need to do is just bring that right down for all the six and that's going to be our six Fields okay very very good let's give those a color of course we want to give those color I'm going to copy those here and I'm going to paste the formats in here I'm going to paste those formats oops sorry it's a little bit off the screen pasting those formats here okay so great so now we also have some customs I want to know here the content actually I'm going to bring those up one one more I think we have one more field let's just bring them up and we've got one more custom field right up so I'm going to bring this one up and I've got one more custom field right there okay this is going to be our notes field and then we're going to have our notes in here so I'm going to color this white this is for our notes each individual booking has some notes all right I like the way that that's look what we're going to do now is we're going to give it some format so I'm going to hold down the control here here for all of our main headings and I want to give that a very specific format and let's take a look here that's it that's all we need for now and then I'm going to use Ctrl 1 here it's going to launch our format cells I'm going to go into the fill and then fill effects and then right here we're going to use some darker colors I'm going to use these two colors here and this one and then we're going to also have a subheading color so it can be okay then for our subheadings we've got a few subheadings here here and just those two are subheadings I'm going to again use Ctrl one once more time I'm going to use the fill effects here and give it a subheading we're going to use that lighter color and then we're going to go to an even lighter one which is about right here and that should be sufficient okay I like the width that looks clicking okay and okay all right so now we've got our subheadings and now what I'm going to do is just adding our borders to all of them regardless and then we can set our borders here here so I'm holding down the control again back into control one going to the borders I'm going to use all white so we're going to set it to White and I want all the outlet outside and inside borders and clicking okay all right so that's looking quite nice just the way I like it let's take a look and see where we're at all right everything's up I'm going to add some borders around here I want some darker borders when it becomes the White Field we want to use that darker blue border and we're going to put that all the way around here clicking okay all right everything's looking good we have the basic formats up let's focus a little bit on our admin man I want to know some information for admin and we need to keep some track fields and these of course are going to be hidden so first up I want to know what that selected tour type remember in our sample we're going to have those uh lists that come down here and I want to make sure excuse me they're going to come down here whether it's bus boat car rental or whatever when we select that I want the name of that to go here so maybe it's going to be bus or whatever next up I want to know the selected tour ID what is the ID each individual tour has its own ID if we look in the database we see we've got tour IDs we've got tour name and all the information so we need to know that to our ID okay after that I also want another selected tour database row selected tour database row and that means what row on the database is that two are located so if I have tour id1 it's going to be Row 4 and so on and so forth so we'll go over that I also want to know the next tour ID the next in line if we take a look inside our tour database the next one would be 13 so we need to make sure to add to that after that I want to know how the selected tour row so what we're going to do is we're going to list a bunch of Tours and trips now that selected row when I make a selection on whatever row I want that row to be highlighted I'm going to put that row number directly inside of B5 so what we're going to do is going to take all these I'm going to wrap the border around it so that we can specify that it's admin and then give it a different color okay that helps us keep track now we also have some seed information notice when we select the seat the seats are going to be here those shapes and when I selected I want to know some information about this so I could see so I wanted the selected seat ID I also want to know the selected seat databaseat database row now our seats are also kept in an individual database and it's going to look something like this seat ID that's the unique ID for seed the C number what is the seat number what is the tour ID and the booking ID we'll be going over those in a little bit detail the name who's in the seat what is the status of it and any notes and then the database row so we need to keep track of that so we can do that so that's C database located there also I want to know the selected booking selected booking ID each individual booking is going to have its own idea ID and I'll be putting that here if we have several bookings here we can select on those bookings and I want to know the booking booking IDs also have their own database we see we have a booking ID each booking is part of a tour right so you may have several bookings inside a tour so we need to keep track of that what is the tour information name who booked the tour and all the booking information that's associated with that tour so we're going to have that here all right so we need to have the booking ID and I want to know the selected booking database row that's important and the selected booking raw okay going over exactly what that is and I also want to know the booking ID result and I'll go over that that's going to be a formula so I want to know the result okay so these are pretty much for the seats themselves so I'm going to give them a unique color here this is for the booking here so these are individual for the booking and I want to give those just a little bit of a different color here so we can differentiate between those we also have some attachments almost forgot to put in some attachments here I've got a whole bunch of dots and so I'm going to do is I'm going to copy this here I'm just going to place it right down here and we're going to be calling this tour pictures and attachments tour pictures and the tour pictures we'll just do tour attachments it's a shorter word and the reason is because we're gonna have a button here okay so I want to have a thumbnail preview in here we'll go down to let's say 38 or 39 and we'll give that a color of white I want to make sure because that's where the thumbnail is going to appear I'm going to wrap a border all the way around this we can do the Whiteboard I'll do the blue border all the way around this because I want to make sure that we know the attachments we'll be able to add attachments Frameworks so I'm going to give that a format of cell a border here here and we can use that same dark blue border here and I'll do it on the left the bottom and the right and inside we'll do a dotted border so we can separate that and then up on top we're going to use that white border anytime we have that dark blue I want to add that white border we can do it directly from here if we want to as well we'll select on that white border here color here and then we can just do the lines all the way around so that's exactly what I want okay very good so that's good now let's cover this let's color this white and then what we'll do is We'll add some conditional formatting in there if we want to okay so our tour attachment is going to be right here and I want to make sure those colors are white okay very good so that's the essence of it now the idea is for the entire screen to fit basically within this parameter right here so almost does so that's exactly what our screen looks like now we're gonna have the main menu here now I've got the menu located in the admins it's exactly like this so I can just copy this and we'll go over the macros but it's much easier if I copy all we are is having some three rectangular shapes and some icons and then logo that I created in mid-journing I'm going to copy this and I'm going to place it directly in here that saves us a little bit of time these trainings tend to be more than and that and that's already functioning and I'll go over the macros of course with you so let's take a look so here we want some shapes to generate here we want to click on one of those shapes that's going to be like the boat or the ferry or the bus I want to know all the buses or all the boats are ferry to have that list display here we need some buttons here so we're going to be able to save add new so let's add in those buttons now so we're going to do is we're going to do insert and then we'll do shapes and then I'll just use this rounded rectangle and we'll put the first one right about here okay let's format that let's get it exactly the way we wanted before we duplicate it we're going to give it that dark color I don't necessarily need an outline on it so I'll do no outline and we're going to call this this is going to be for add new to add new tour I'm going to right justify that and then we need to make sure we have room for an icon so I'm going to right just if I put it in the middle here and make it a little bit longer just the way we like it so do 1.45 that'll leave enough room and then okay so I like the way that that button looks what we're going to do now is we're going to duplicate that using Ctrl D and then I'm going to use one I also need one for saver updates we're going to make this save or update it's going to be the same macro that runs both saving and update I'm going to duplicate that once more this one's going to be for deleting or delete tour okay these two we can make them a little bit smaller holding down the shift zooming in to get that third line we can bring those a little bit all right okay so I like that we've got enough room for the icon if we want to line them up I can just line them up here use the separator to separate them evenly that's good okay what else do I need that's it for the buttons I do have one more button I'm going to put it in a different color I'm going to duplicate that and then this button is going to be a lot smaller because I want to add an attachment here so I'm going to drop the font size down to about 10 on that a little bit and what we want to do is call this add attachments we don't have a lot of space for that here so add attachment okay that's going to be a little bit big we want to put that right about here so what I'm going to do is I'm going to do control 1. I need to format that to make sure that the text options the margins are almost nothing here so on the left the right would be 0.02 top zero left bottom zero okay so what that's going to do is give us enough room now I do want to give that a different color the reason I want to give this a different color is because I don't want it to match the background so I'm going to go change that and we're going to do the fill let's do the shape fill on this one we can do uh one of our theme colors which is here and then change the font here on the text field to Black okay so that's add attachment all right that looks good that's sufficient we're going to be able to add that attachment here and then we'll be able we've got some more buttons that we're going to be needing too what other kinds of buttons are we going to needing I need to be able to add and update the attachments if I select on an attachments I want to open it or I want to delete it so we're going to need some buttons to do that so what I'm going to do is I'm going to duplicate this button right here and I'm going to bring it down here I'm just going to get rid of all the text this is going to be an icon only and I'm going to then we're just going to change the height to 0.2 and the width to about 0.2 it's going to be very very small and the reason we're not .02.2 and then the width 0.2 okay so what I want to do is I want to place that right here so when I make a selection attachment I want to open the attachment or delete it so I'm going to have the open icon here and I'm going to delete one right about here this one is we don't need the Border in here because this is where our thumbnail is going to show up we need some sub headers on here don't we so we can know that so one of these I'm going to copy these subheaders this format here and I'm just going to paste them right here so we're just going to paste it we'll paste it all and then we'll go ahead and change the text so the first step pattern is going to be the attachment name so we'll put down attach name next up I want to know the file name and I want to know the type and lastly I want to know the thumbnail preview so we'll put in thumb nail preview and that's going to be merge cell so we can get rid of the one to the right and we can merge the center that so I'm going to delete that merging and setting that that's going to give us the space that we we need okay so that's perfect I just don't need this border here on that one because that's where our thumbnail is going to go so we can remove that border clicking OK all right saving our work so far that's important so what we wanted to do is we want to have a button here that's going to be for open when I make a selection I want that button to appear so I'm going to duplicate that because I also need to delete it I'm going to put that delete right about here it'll be VBA that places it but I want to create those buttons before I create the icons okay very good that looks pretty good so now what we do is we have we're ready to insert our icons so I'm going to again save it and then what we're going to do is we're going to insert pictures and I've got some icons that are already saved up of course if you do want these icons I include this all the pictures all the resources on our patreon so we've already got admin we've got airplane I've got attachment that's going to be used for attachment we have let's see our filter we're going to need that I've got that already on the other screen confirmation this will be used for saver update delete we're going to need edit I'm going to need folder we're going to use that for open up and print I don't have yet refresh to her that looks pretty good okay and we're going to insert those now they're going to insert they're all going to be pretty big right we don't need them that big I'm going to go into format and make them all small so it sets them to point two now we can start to place them okay so we're going to select something else add new we're going to place right here attachments that's going to probably have to be in Black so let's update that because I need that a darker setting the color I'll use this dark blue and then the corrections maybe a dark blue like this I like that okay next up I want the to save and update putting that right here oops let's drag that over here saving the update here I want the delete that's going to need to be smaller probably going to put that right here actually I've got it twice here so let's put that here where it belongs the delete what I'm going to do is I'm going to use this delete I'm going to duplicate it using Ctrl D and I'll be making it smaller a little bit and putting it right here the open we need that right here but it needs to be a little bit smaller and the edit I want to be able to edit the booking so I'm going to place that directly right here that'll have a macro tied to that okay things are looking pretty good so what we're going to do now is we'll just Center and make our buttons so we need to make sure that they're in the middle and we're going to group those individually doing the same thing here Center grouping them and then delete we're going to again Center them and then grouping them together now I also want these individually grouped I want to make sure they're all lined up they're separated horizontally and then grouped as well I'm going to use Ctrl 1 when I group them I want to make sure that we are setting the properties to move but don't size that's going to go very shape okay things are looking pretty good now let's continue on we're going to update these these two icons need to be a little bit smaller because the buttons are smaller so we're going to make those to 0.17 that should be sufficient there okay now what I want to do is I want to line these up I'm going to use my selection tool and then I'm going to just basically line them up both in the middle and the center grouping them and I'll give them unique name same thing with the delete again we're going to line them up and group them to together now what we need is we need unique names for each one of these so what are those names okay we'll select on this we'll call this open attach button open attach button this one we're going to call delete attach button and we can abbreviate that here if we want to and we'll say delete attach but okay very good so now it is VBA that is going to place these wherever we want them based on the selected row let's add some conditional formatting in here in fact I need to add it in a few places so what we need to do is we can just then copy it once we have one so we can add three different rules based on alternating rows and based on the selected to a row so what we'll do is we're going to go home and then conditional formatting and we're going to manage rules and then what we'll do is we'll click new rule now the first one is going to be based on whether there's a value there and whether it's an odd or even row so it's going to be an and because there's two conditions so it's equals and the first condition is based on a value whether it's in column F and any row starting at row five so we're going to remove the dollar sign from there there five and the second rule is going to be based on the mod of row two meaning even rows so for those rows I'm going to give them a very specific color we'll use it a fill of white and we're going to use a border here and I'll use the border the same consistent with the theme that we've been using and we'll use the dotted line on the left the bottom and the right and click ok now what we want to do is we want to copy that rule and click OK I want to create a brand new rule be slightly different this one's going to be for auto so all I need to do is paste in there and then change the 0 to 1. this one's going to be slightly different it's going to have the same borders but a different backing color I'm going to use the fill color here the line style is going to be the same we use the dotted line on the left the bottom and the right and we're going to give it a fill the fill effects are more colors I'm using fill effects because I want to make sure to use the theme color that I have and that's available inside here in the recent colors clicking OK all right so now we're going to we're going to click ok now we need to update the applies to to both of these now where is this going to to apply to it's going to apply to all the way from F through G and then all the way down to a large row so I can just use 999 and click copy and then paste that in here now we have one more Rule and that's going to be based on that selected row so we're going to apply that right and also we want to make sure oops I forgot something here edit rule does not equal empty here does not equal empty right we want to make sure that's obviously the case and we'll do the same thing for the next one and then of course we're going to do one for the selected row now that selected row we want in a dark color and we also want to make sure that has that white font so here's we're going to do your new rule we use a formula and that formula based on that selected two row to a row is based on B5 equals rho okay and then what we'll do is I'll give it a format we're going to use a similar format to our theme so we're going to give it the font is going to be bold and we'll give it a white font and the background is going to be that fill back it's going to be a gradient based on two of the dark colors of our current theme so it's going to use this one and we're going to use this one here so so clicking ok I'll make sure that that's the darkest color so this one these are about the same so once I click OK and OK it's gonna be based on that selected row click OK and then we also want to make sure that that applies to the same range that everything else does so clicking apply okay so of course we don't see any results yet but as soon as we start typing in data that's what we want to see okay so next up based on that selector of that tour row is seven we want to make sure that that row highlights and that's exactly what we want we can right justify the entire column and then we can Center justify just a header there that's going to set everything up just right okay good so we have that so what I want to do is I want to have this pretty much same conditional formatting apply to in a few different locations I just just the attachment so I'm going to copy this here I'm going to paste the formatting down here so I'm going to paste that and paste the formatting then we just have to make some of the updates now obviously this formatting we can stick for white that kind of a white background but I'm going to go into the conditional formatting and I'm going to manage rules and in fact I want to add one more two more things we forgot 29 here I want to know the selected attachment row selected attachment right row when I select an attachment and I also want to know the next attachment ID now each individual attachment has its own ID if we take a look inside our attachments database here you see that every single attachment has an ID we also if there's assigned to a tour we would have the tour ID if it's assigned to a booking we have booking and then what type is it a booking type of attachment or is it a two we have the attachment name we have the file type and then the file name so that's going to be for our attachments so back into the tours so now that we have that let's color these so that they're distinct so we can show them that's going to be based for our cosmen so now that we have that let's change that back to the black phone the black line colors that we'll do back automatic and then there okay so now this is going to be for our selected row so we want to update that the conditional formatting that we've added we still need to update that we're going to go manage the rules and we could essentially in this one the rows necessarily doesn't matter as far as whether we want to color them or not so in this one maybe we want to use whether it's blank or not I still want a color so this one maybe we could remove if we want to and so we can clear that out just we'll use F2 and then what we can do is just come on over here and then we can edit that here so we'll just bring that on over here clearing that out here so we don't necessarily need to use that okay so we're only coloring them based on the mod row and then click apply okay so very good so now that we have that one and we click the apply so let's update the Applause to I want to make sure that we reset that so it's going to apply to all these fields and then just copy that I had to redo it okay so copy paste inside the Rings that's going to be our applied range and that's what we wanted to apply for both so that means regardless of whether it contains values or not we do want to see those alternating rows perfect okay let me give this a strong border on the right side so that we can differentiate between that thumbnail I want that thumbnail when I select it I want that thumbnail preview to show up right here and that's exactly what I want okay very good things are looking good we're pretty much done with the design everything's good I like the way the that's looking we've got the list that's going to go here I do want some additional formatting here so we're going to again copy and paste that we also want to know the the selected row here for the booking name so I'm going to paste that here and then what we're going to do is we're going to go into that condition forming and just again edit those rules based on that so we can go in here and refer to do one more thing this one's not going to be B5 this is going to be based on it and misspelled that B5 what is our selected booking row so let's take a look inside here our selected booking row I'm going to edit that it's not B5 what is it and that's going to be based on that selected so we're going to do is we're going to scroll down over here we wanted that selected booking row is going to be right here select the big and row inside B12 so we're going to change this to B12 that is our selected brick and roll clicking okay and then apply and then what we want to do where do we want to apply this to I want to apply it to all the rows down here so we're just going to select all the way down here that's our applies to range we can use a larger Row in case we have a lot of booking so we're going to put that to 99 and now all you need to do is just copy that range here and then paste it all the way down and we can paste it right down here inside the rest of them okay very good so now that we have that all the way here I'm going to then add that update we need to make a quick update but we also want to update the column it does not call them F what column is it it's column Q so we need to make that update ingly okay we'll click OK and then we'll just update the other one to column Cube and we'll add that in then click ok ok so we're good to go and we'll double check that to make sure that we're set up on that and okay so now as we add items here perfect okay continuing on now what we want to make sure that that selected row as I add that selected booking in B12 we're going to put 6 and we want to make sure that that is highlighted here perfect just the way I like it I need to update our highlighted Row for the attachments as we know that that's going to be based on b29 so we need to make sure to set that to b29 which is where it's not going to be B5 we need to update that to b29 at least F2 so we can scroll down into it and then between 29 okay now we've got our conditional formatting set we want to make sure that we're able to add new save and update our screen's looking good but I want to do is I want to create some sample shapes so that we can have all the tour types down here now this particular shape this sample shape I've got it in my other one it's going to be a little bit quicker because that sample shape is already formatted so let's grab that and then I'm just going to copy that in the other screen I'm going to bring it over here I'm going to paste that in here this is what it is it's just a sample shape here called sample tour shape there's no macro that's assigned to it as of yet and what I oh there is let's just delete that right I'm going to delete the macro by assigning the macro and clearing it out okay so we don't want that even in our sample I want no macro assigned to that because we need to assign our own okay so what I want to do is I want to duplicate this shape and I want to create a list of all the tour types and based on this let's fix this misspelling here because I saw that okay I'm sure there's a few I'll have to look it over and then so what I want to do is I want to Loop through all of our tour types and the lock based on the last row I want to use the name name here and I want to use the icon here and I want that to be visible and I want to be right here to create a list and of course we're going to do that with a macro so how are we going to do that well let's get into the VBA we're going to save our work and then take a look at the macro that's going to help us do just that so we're going to go into the developer and in Visual Basic and I'm going to walk you every step by step now notice I have two projects open one on my other screen I've got this is the one we're working on this is the sample one that we're doing say so I'm going to update this I'm going to close I'll get rid of this extra one which is the sample okay cleaned up the VBA a little bit okay so inside our workbook we've got remember I've got two workbooks our samples aren't working on admin attach database bookings bookings database settings the seeing database tours and our tours database Okay so we've got some modules and we've got a single form that's going to be for our seed so our first one we're going to focus of course on the tour sheet macros tour sheet so that's going to be called load tour types and that's exactly what we want to do I want to load in those tour types so how are we going to do that well let's first thing what we want to do is we want to get rid of any existing ones that might be here because we're going to be refreshing the list what we want to do is we want to determine the last row of our tour types making sure that it's above seven looping from 7 to the last row getting the name which just comes from column D and I also like to get the icon now these icons are individual Name Tour icon 7 to our icon 8 and so on and so forth it has to do with the row notice these individual rows the icon name is the same as the row number that's going to make things easier for us okay so knowing that we're going to Dimension some information type shape as a shape and icon shape we'll be going over these as we come across them inside our VBA macros so again the first thing what we want to do when we reload these two types is we need to remove one any of the icons Associated and neither buttons because each one is going to have its own icon and that is why this has been formatted already if we take a look inside the formats here we go into the text option and we go into the text information here we see that we have a left margin of 0.35 that's going to make sure that we have enough space for that icon so 0.35 is a larger left margin to make sure that we can put the icon here okay so what we'll want to do is delete any that exists already so to check on that we're going to check every single item name that we have is going to be called tour type every icon that we have is going to be called tour icon and of course it's going to have a unique number on the based on the row added on to that but they're also they're all going to contain the names the shape names so what we're going to do is we're going to Loop and check if they exist we're going to delete them so what that's going to do is basically clear all the menu buttons and all the menu icons next up again as I mentioned before inside the admin column D I want to determine the last row of those our last row in this case our last row is 16 we're going to check all the way from 18. so then what we want to do is to make sure that we actually have so we're going to set the top position if we bring in this I want to set the initial opposition somewhere right around D4 and the left position right around D4 that initial top position now this top position and left position has been Dimension as double variables okay for some reason the last row is less than seven we can exit the sub right if we don't have any uh types at all we can exit the sub out if this has been cleared out for any reason then we cannot add any our trip types or tour types we can't add those because there's no data so next up we're going to run that loop as I mentioned for the type route equals seven to the last type row shapes sample tour shape I want to duplicate that creating that here's our sample shape the sample shape it's called sample tour shape I want to duplicate that and then give it a very unique name so that unique name is going to be called tour type and the type row remember I said we need to remove anything that contains tour type so we're creating one so we know exactly what to remove and the type row this makes sure that the shape name is unique then we're going to focus on that so with that brand new shape that we just created we're going to give it a text right we want text inside that text frame text range so the text inside the shape is going to be I wanted an uppercase right so how do we do that we can use the string convert right we're converting a string we haven't used this much in the past this what are we converting I'm taking whatever string is in column D in the type row whatever string here whatever value is in D in the type row I want to convert it to uppercase so we can use the string convert to do just that so string convert and we want to convert it to VB uppercase so if I take a look inside the comma and bring it over we see that we've got lots of lowercase narrow proper uppercase we've got a lot of options here with this we can use y to reuse uppercase because I want to make sure that they're uppercase reading that uppercase name I'm going to set the left position and the top position so now we'll wander is we want to then bring over the icons so what I want to do is I want to set the icon shape the icon shape is a shape right here we want to check to make sure it exists what am I looking for I'm looking for Icon 7 icon 8 and it's going to come directly from here to our icons 7 Turk icon 8 and so on and so forth so first thing I want to check to make sure it exists to do that we're going to set on air resume next and on air go to zero because if it doesn't exist it could create an error so we want to make sure that it does we're going to set the icon shape to the admin sheets tour icon on the type row this is going to set that if it does not exist then it won't we'll just skip it so if not icon shape is nothing these two cancel each other out and basically what we're saying is if the icon shape exists then continue on check for existing icon okay so now that we know exists what we want to do is we want to copy it and then I want to select basically any cell in the currency just to select it to make sure that no other shape has been selected if another shape has been selected and I try to paste it's going to create an issue so we always want to select inside the sheet that we want to paste once we've done that we can then paste that icon in there then once it's pasted we can then work with it all I want to do can keep the same name I don't need to rename it we're then going to lock the aspect right ratio we're going to set the height at 20 and the left position based on the same left position as this except we're going to move it over a little bit to the right so we're going to add 3. I also want to set the top position now the top position if I add an icon let's just say we're focused on this icon here whoops I selected it that's okay I want this icon to be centered I don't want to be at the top I don't want to be the bottom I want to be at the center so what I'm going to do if this is the icon I'm going to take the entire height of this shape and I'm going to subtract out the height of the icon then the difference is going to be divided by 2. so that means we have the same distance above and the same distance below and that's exactly what I want to do for this for these particular shapes so we can do that here we're taking the top position of that shape and then we're adding to that what are we adding we're adding the entire height of that shape we're going to subtract out the height of the icon and we're going to divide that by two what that does is it places it vertically in the middle of that button shape and that's all we have through except for we need to increment the top position if we don't increment the top position the all the buttons will be right here in this place right here we don't want them all there we want them as they move down so we need to increment the top position so the button move down how much are we going to increment it we're going to increment it based on the height of that plus we're going to add five so we're going to add that whatever its current top position is plus the current height plus five okay very good so now this macro now as long as we've run everything I'm going to run this macro here and what it's going to do is going to create those shapes okay here's our sample shape we can move it off and down we don't need that we don't need to see the sample so we can drop it down here okay so now we've got it so we've got our icons and notice our icons are centered we got our bus and everything's good the only thing is we need to assign a macro to this notice now we can do two things we can one assign a macro right here to the macro in the icon so we can do just that if we want to assign that macro or we can assign it to the individual shapes so we have two ideas here so what do I want to happen well when I select one I want to load the end individual tour that's the macro the tour types based on whatever I've selected and so what is that so let's take a look at what is the marker that we want to run we're loading the tour types if I go back inside the tour macros here we have tours list tours so this is going to be based on that so how d