To get started right away but before we do that i want to make sure that you get our trainings every week they are absolutely free i'm going to teach you how to create these incredible applications with excel make sure you get these trainings each and every week to do that all you need to do is just download this workbook absolutely free using your email or facebook messenger and we're going to get that sent over to you right away also if you like these workbooks i've got a template workbook of over 200 workbooks for you and that's just 77 it's a complete package i think you'll really love it that will help us out tremendously we've also got a brand new patreon platform and patreon every single week i add on to these trainings whatever you feature.
You want added you may want me to fix something or focus on an area i add that on patreon of course all the resources that come with these pictures icons i'm including that in our patreon along with a whole lot more including pdf code books and that's a great way to see your application in the code and then fully organized colorized index manager in a pdf code book all right let's get started on this training i'm going to go ahead and explain an overview of what we got going on and then we're going to get down into it so let's go ahead so basically what we have is a list of events we can hide those events so we can show those events so every new event we create we.
Can show that we can include past events or we can include just future events so i've got a lot to show you and also what we have here is an event summary right what is the second what tickets were booked in that we can see all the guests who booked it what tickets they purchased we can get an information about the event the event name the location the date and the time of the event also we noticed how many seats were sold of course and all the seats available we'll be updating that live and also we have information on tickets so when we select a.
Specific ticket we know who it was sold to if they have a phone number or something like let's take a look here we got some more details on here we can also know their phone number email information and the tickets they sold all we need to do is click on one and add those tickets if we want to add and then we're going to save the purchase that lets us save it and everything gets updated when a ticket is booked it is automatically blocked out those colors are fully customizable we can also create new events if we decide we want to add a new event all we do is just click add event when we edit now we're in a fully customizable add-on we can add an event name let's just say we want to add spider-man another version of it and we also want to put it on let's say uh 1 22 whatever day we want and then let's say 10 pm of course this can work for.
Any type of event even bus tickets or something like that what the location of that and then we can decide what about the number of rows we want to create that so we put on a date on that and a time of that so maybe we want 10 p.m so we can create brand new events very very easily so we drop down here select the time here and then we want to say let's say we want the number of rows we want to create those rows we want to create a custom grid so that we can create those events and use those for our seats and then a duration here so we can set that up we generate the seats on that it's going to automatically generate a seat of grids right and now what we want to do is now our seating chart may not look exactly like this maybe these upper left or maybe there's a you.
Know some rows that the seats are not used maybe there's no seat here so we can make that hidden maybe on this side we want to do the same thing we can make that hidden that allows us to create a fully customizable then what we do is we just save that event and then it's fully saved once we hide those details we're now ready to book tickets inside this event all we need to do is just put in a guest name phone or email if it's necessary a steed and then all we need to do is just simply select the seats that we want the added so we want to add a block of seats if we want to remove one.
All we need to do is just remove it and it's going to go back to white colors are fully customizable save that purchase it is now saved so when we select another event and we go back to that one we see all the seats that were booked we can drill down on that specific ticket details just like that very easily we can also loop through the tickets we can print individual tickets we have different seat assignments that we can use along with a seat so we've got the seat just fix that the theater the location that it's at and a bar code that's going to allow us to scan it so we can print these tickets we can print a selected ticket or we can print all the tickets it'll loop through all the tickets so this would be a great master class on how to create events work with shapes create different codes and formulas so we've got everything packed into one training so we're going.
To get started right away let's take a look inside the admin screen and see what kind of customization we can add to this we're going to take a look we've got our lo we've got a inside location we've got dynamic rooms and locations so any type of room or location that you want it's going to be a named range dynamic we've also got some seating defaults now the really cool thing is if we take a look inside back inside the events we see seat b4 c4 right so we have specific seats so what we can do is this would be seat a2 this this hidden one is a1 right if we were to go ahead and unhide it we could edit it a1 on the upper left and then a2 a3 so a is the first row.
B is the second row and then it calls so but that type of naming right can is dynamic and the user can decide on that based on their current room so we could say okay well i want the rows to be alphabetical and i want the columns to be numerical and then i want the row order from top to bottom the row order right our a row order is going to be ascending right so we want it from lowest to highest and then we also want the column order left to right to be from ascending so that that allows the user to completely customize their events as far as the naming is concerned.
Whether it's a1 and that we use that we've also got the occupied c color when a c becomes occupied we can set a c color we can then set a color using this really cool so if i were to change that occupied c color and go back to events here all we need to just load that event again and you're going to see that down now that's yellow so we can fully change that using this really cool pop up okay so i've used the dark red it's good with the theme when we're creating a brand new event what is that initial seat open color i've set it white so what are we going to set that color to white and also the selected color here we've got here in yellow what is that selected if i select a specific seed i want to know what color this is going to be used but if we're editing a specific.
Let's go ahead and into the edit we want to edit event and we want to select a seat i want to know what color that is what color did we select here so that's going to be really important we want
To know what selected c why do we want to do this we may want to change it right we can also set up colors based on a seat type right so if i have a standard maybe i want standard in white a front wall i want to give it a color if i want to change the front row to a darker color i can do that all right so i want to change it to purple if i want to i can do that so then if i decide to select a c then i want to change that seed type and i'm going to change it to front row i want that color to be changed this way we can color code a specific color or we can actually set a color.Here so there's many many options here i'm going to move that to the top notice this color palette will be taking care of that notice it's behind it we need to set the z order on that notice we don't want it behind so make sure when we get to that part of the code we'll set the z order that's going to move that on top and i'll show you how that's done okay so we can set the colors we don't realize there's lots of custom ways to customize this and that's really great because it's going to give you a really great lesson on how to work with colors how to work with shapes how to set defaults and all kinds of that we also want to default ticket type for new events what ticket type do we have a purchase status what is the status of the purchase of those tickets c types we need to set a default seed type so we can set that default to anything we want and we've got of course ticket prices based on that and that's important because if i decide.
We have a specific event and we're going to hide those details we're going to go back into purchase and i want to change i want to i want to change a specific ticket to maybe a child ticket that price needs to go down and change for that right so that's automatically if i save that you see now we've got a brand new updated so ticket prices can be based on the ticket type and that's great okay great so we kind of understand that how we've got tickets seed types we've got ticket types and then we've just got some times and durations that are going to help us for the initial event so that's really it for the admin i was cr we'll be going over it as we go in the code.
And we'll be going walking you through this again especially this area here where we're going to be able to design that i'll walk you through that events we were over now we need to keep those events what type of event when we edit event we see each event contains a name location date time duration and the number of rows and sales so we need to keep that and that's going to be held inside our event database we have a unique event id we have an event name we have the location date time and all that information the duration in this decimal format is just fine the time in this decimal format is also just fine number of rows the number of calls that's pretty much all and what we want to do also you notice now each event has a number of seats right so for this event we.
Need to track the seat id the type whether it's visible or not and what color it's been set to so we need to know that and so to do that we need also an event seat database and that's where it's kept right here this is the event seat i need to know the event id the seat id the seat type the visible the color in numerical format and the row that's associated with that so that's all tracked in there so now you notice also we hide those details we go into the purchase information we want to track also all the tickets that have been in all the purchases this is considered a purchase.
They have a guest name possibly a phone number here we don't need to put that in really but we can it's automatically formatted and we need to know an email so let's just put mike at gmail okay so we got an email and we can also save i want to know how many tickets were sold we're going to use account a for that i want to know the total of the tickets if i decide to add a ticket it's going to be added automatically and i want to know the status of that now when we save that we need to make sure that that initial information the guest phone and email is all saved into a database and that's going to be our purchase database right here now purchase database we need to know the purchase id when unique id for every single purchase i also need to know what event it was associated based on a unique id those are the same event ids that are stored here right.
So the same event id we need to keep track of that what i also want to know is i also want to know the purchase date what date did they purchase who purchased it their phone number email the status i also want to know the c quantity how many did they purchase and i want to know the total so all of that is super important because we're going to have to track now that's going to help us for as far as the purchase but we still need to know what seat was assigned right so if we take a look back in our seats we see mike purchased but mike purchased a lot of seats right i need to track what seats he purchased which the id what ticket type and what was the amount that's all going to be tracked inside our assigned seed database so those are all the database each assigned seat.
Of course it comes with a specific event we have to know what event that was booked on i also need to know what purchase id this is how we really handle multiple databases and how they inter inter connect together and so that's really important of course and also how they interact together i need to know the seed id the c type the guest the tickets type the amount and then the row that's associated this is the road the database that's it that's all that makes up our fundamental of our application okay so we've got all that going on here and so what i want to do is i want to share with you the overview the basics so we have some things going on of course we have a button set and some icons here that's pretty much it it's relatively standard as far as what you see.
On the screen but there are a lot of things that are going on and i'm going to walk you through now that we've been through an overview i want to go into the individual so every time we create a new event that event name gets here when i select on it i want that event to load along with all of the assignee details and i want it color coordinated if i decide i want to change a seed type or change some things about an event i would then edit that event then this this is the place where we can then select a seed maybe we want to change the seed type maybe we want to change it to front row maybe we also want to change this to from so we can do that so there's going to be some change events that are located on that so we can automatically set that okay so how are we going to do that well let's go ahead and go through some of.
The code that handles author and of course we save that event it's going to save those changes if i decide i want to give this particular this one is hidden of course but if i decide i want to make
It visible i can and you see that seat's going to show up right but maybe we want to make it so it's fully customizable so we're going to go over that as a lot of change events as i save it those changes are going to be saved so each time we build it it's going to build those colors are going to be back that's why it's important to save these colors when we bring that event back i need to know what colors associated with these seats and where they're located just a standard database but some code that's going to help us do all of that okay so let's get into vba and start working through some of these uh events and seeing how they work together.All right so inside the developers we're going to go to the visual basic you can use alt f11 to get you there as well for a shortcut okay and as mentioned before we have some change events are going to go on we've also got four modules here we've got event edit event macros and that's the events when we actually create an event or we edit event we have some macros that are actually going to be on the sheet right you saw inside the sheet itself we were able to change some columns we were able to if we edited at if we hide those details it's going to show columns k through u right in when we edit events gonna show columns f and also through u all the way through j so it's.
Gonna hide and show some columns so there's some things that are going on inside the sheet okay and inside the code so that's what we're gonna focus on initially and also as you know we had a color palette remember we mentioned something about the color palette we needed to make a change right we need to set that to the top if i launch that color palette when we make a selection change right if i make a selection change i want that color palette to show up right when do i make a selection change to where to here but what we don't want we don't want it appear behind any shape so if i make a selection change anywhere from i all the way down right but i need to ensure.
That of course there's a value if i select here i don't want anything to happen only when there's a value in column f right so then we want that color palette we want to make sure that it's on top of each so how do we change that well as we work through the code i'll show you first it starts with a selection change event when we select it we want something to happen so that's inside the sheet code so we take a look at our events sheet here's the sheet that we're focused on the events and we're focused on selection change so it's all the code that appears here under selection change so let's go over the code within this the first thing is if the user selects more than one cell we want to exit the sub that helps create from creating errors right if i decide to select more than one cell i don't want anything to happen so that helps us do so we can do if the target.
Count large is greater than one then exit the sub nothing else if they select more than one also if the user makes any selection at all and that color palette that's the shape it's called that group of all those shapes is called color palette here we can take a look at the name called color palette if it's visible if it's visible i want it to be hidden so if it's visible that means it's automatically going to be hidden but only if it is visible we can use if shapes color palette visible equals true then shapes color palette then just hide it so basically what we're gonna do is we're gonna say hide color palette if visible and we're gonna do the same exact thing with the delete.
Ticket button now you saw that little x that came up that little x right here is the delete ticket button so when we hide those details and we select on a seat we see there's a little button here that button is called delete ticket button or del ticket button i want that to also be hidden if they select anything else anything at all i want it hidden so that's going to do it for us there so same thing if the ticket button visible extrude then hide it so we're going to hide the hide the delete button delete ticket button if visible delete ticket delete kick it button okay.
So we know what that's like and also what i want to do is on selection of any event load the event you saw that selection change happen when i select here that's a selection change i want to load that event we're going to get into that macro but if the user makes a selection anywhere from d5 all the way down and we want to make sure that c sees where our target row is see it's actually our row we'll get into that so there's actually a value in c and that is the row that's associated although you cannot see it but if you look down here you can see there's numbers here one two three four if i were to change them to white of course you would see them right.
I just kind of made them invisible but basically you would see them i kind of like looks and white you'd see it if it's in white let's change that to white uh expand the column a little bit here we see it's a little bit and then we see them here they're white so we can just reduce the column size and you can't see them that's another way so they're there right there inside there just you don't see them so we're going to make a selection change we want to make sure that there's a row that's associated with the event like row 3 is the actual row that's associated with that event here so we want to make sure that that row is there if there's no row there we don't want to load the event so that's just what we're going to do inside the code we're going to make sure that column c contains a value if it is then what i'm going to do is i'm going to set that event id that.
Event id sorry that event id is column c it's not the row it's the event id they're almost identical c is where our id is going to go into b2 and then inside b5 and i'll walk you through this the target the row itself will go into b5 so let's take a look inside that b2 will take on the id so again it is the id these are the id's event ids not the row id this is event id one two three four five and six okay so those are the event ids we'll keep that visible for a while and then i want that.
Event id to go directly into b2 so i know what it is and also i want to know the selected row we're going to use conditional formatting and that row is going to go directly into b5 when i highlight this and i go into the home and conditional formatting and i manage the rules i see that we've got a conditional formatting rule based on b5 equaling the row and in that case we're going to use this light pink to darker pink with the bold font okay that's how we activate that alright so we understand that but i also want to know what row is the event associated that row is going to be based on a named range called event id it is a dynamic named range based on the event id.
Here so when we go into the formulas name manager and event id we're going to use an offset formula to track that i use offsets almost always i'm going to start it off at a3 we're using that row number i want that row because if there's no data it's going to create an error but not if i use that header row but of course we're going to actually start offset from that which is one row down i want to start up and we're going to count everyone including the header we're going to count everything then we're going to subtract 1 because i want to exclude the header the reason we're including the header and then excluding it through negative 1 is because when there's no data no.
Error will be created if we always include the header so that is the dynamic named range using offset for the event id so what i want this here is i want to return the actual row of that event our events start on row four our first one as you saw start on row four so if i put no one here and i want to load in one i want to make sure that it is row four that will be returned here so we're going to use a match based on b2 that event id we're going to use a using the event id as our lookup array right we're going to use an exact match and we want to add three to whatever row results come if there's an error of course we're going to put in blank so that's going to.
Return our event row if it is blank that means there's probably a new event okay i also want another selected event row you saw that if we're editing the event i need to know for editing or not right so when i go in to edit event this is going to go to true we are now in edit mode if i hide the details we are now out of edit mode this lets us edit the event details if you want to edit the events change things around or maybe we may want to hide different seats if we want to for any reason so that's editing the events so we can save that and then those changes are saved we can then hide those details going to show that ticket information what was sold so it's got two different modes and i want to know if that mode this is going to go to true past events are we.
Showing past events that's going to be based on this option here so i'm going to use the format control it's going to be based on b7 so this if i decide i want to include past events or not right i don't want to include past events so this way this is the only event that's scheduled for the future this is the only event october 22nd based on the year so we want to make sure that that date is there and i'll go ahead and set a year on to this right so we want to make sure let's go ahead and format that so we can see then i'll increase it because i also want to know the year of that in case we have the wrong year it's important we'll go into the custom and then just select the short date for that notice that that is we'll explain that column a little bit that is 2022 so.
That is in the future but for now on we'll show the past events first purposes say so that's of course going to be based on b7 i want to know the selected seat if i decide to select a seat i want to know that that's going to be here inside b8 i think we can probably remove that i may not need that i also want to know the seat id right now if we take a look inside this ticket we've got a bar code and that's going to be based on b9b9 now i'm using the code free three of nine if you don't.
Have this barcode i will include it inside the resources for our patreon members along with all the icons and all the bonuses that come with being a patreon member okay so i'm going to include that and this barcode this font here is going to be based on whatever is located inside b9 so b9 we want to set up a specific information based on the seat so we're going to call it asterisk which is three required for three of nine to be read it's got a begin and end with an asterisk then we're going to call it seat on capital letters an underscore and whatever is located in b15 b15 is that selected seat number so when i select a seat you see that that seat number is going to.
Be changed so that is always going always going to be based on whatever we select because i want to put that information inside the cell once the shape is then connected to that seed id it's going to basically read this but in a different format so how do we look at that so if the we've changed the font on that you would be able to see is exactly what we have it's obviously kind of big there but you can see it's c you can see as i hover over here the font but it's on three of nine so that free market will show up in a barcode font we can also be able to print these tickets continuing on i want to know the selected edit see what seat are we added when we're in edit mode here i want to know that selected seat that's going to use conditional formatting the.
Same as what we've used so if you're going to see that's on selection change so we see here that it's now 13 so we change that c to c 10 13 that is back inside our code on the selection change event the selection change event okay so once we do that we get that selected row then we're going to run the macro called load event there's a macro that runs out we're going to be going over that macro shortly continuing on with our code we saw some other selection changes and we saw this one if we make a change from i11 through i 1000 nothing and f doesn't go to turbo then show the color palette.
Let's walk through that remember we're focused on column i column i's located right here if i make a selection to call him i and column f doesn't is not empty then i want to show that color palette now i want to run that macro but that macro notice is showing behind so we're going to change the z order on that so what's going to happen this macro is going to be run this is the first macro in our event sheet macro it is this macro that's going to run now i've got the shape we're going to focus on the active sheet we could also use events that would work just as fine right because we're only but we're using this can be any sheet we want if we use that color palette.
That's the shape that we've created we want to set the left position baked on the active cell row and the active cell column and then i want to subtract left 50 means i want to move it a little bit over the left as far as the column then i also want to base it on the top position that active sheet cells active cell row plus one active cell row plus one means on the row below plus a little more right we noticed it was just the cell but but exactly not more would be way up here but i want to add a little bit more well how much more do i want to add i want to add about 5 pixels from the top of that then i want to set it to visible but one more thing before i.
Set it to visible i want to set the z order so we're going to do dot z order and notice it's not coming up but if i were to change this to events it's automatically come up on intellisense so i'm going to change this to events right the sheet is recognized and now when i move it over now when i try that again let's try that we don't need that period that was just for testing events let's change that to back to events there we go now what i want to do is i want to set the order now it's going to come up on intellisense because active sheet won't work dot z order there it is and what is the z order we want to set well i want to set it above if we take a look inside here i want to bring forward bring to front of text bring to the front i really wanted to bring.
It to the front of everything so that's the what i'm going to choose now when i go in and i launch that color palette back and then go back again you see it now that's on top of everything and that's exactly what i want i don't want it underneath any other shapes so perfect that's exactly what we want setting that z order is going to allow us to do that and i'll save those changes and now we're going to continue on with the color so that's all we need to do to add a color palette now what about when i set that selected color did you notice that the color is going to change of the cell when i select a color if i select a color and i want to make this a darker maroon or something or any other color that color is going to change so all i need to do is simply assign one single.
Macro to all of these shapes so if we drill down into an individual shape and all these are just simply shape boxes with the background color and i format that shape you see excuse me and i actually set the format right format i can go back into format here and we see it's just simply a selected color we just set a color though that's all it is when i want to show you the macro actually to that it is the macro but if we select on the entire group right and we click assign macro we're not going to see any macro that's been assigned however if we drill down into the specific components of the group then we click assign macro then we can see the actual macro that's been.
Assigned and that's the one called set color cell that is the same macro that we're going to be next set color right here and that's the same one that's under our clip out so all we need to do is do active cell change the interior color of the active cell that we're on based on the active sheet shapes application color that is the name of the shape that called the macro based on whatever color the fill four color rgb color of that shape we're going to take that and put that directly into the cell and all we're going to do is we're going to hide that that's going to automatically place that color inside the cell okay great so also inside our event sheet macro.
We also know we have a single macro that's going to either both hide or show those events it is the same macro that is tied to both these buttons so high details and edit events is the same macro so if we drill and do again that individual shape inside that group of button assign macro we see this oh let's add new one this is the one i want high details this one here sign macro it's called hide or show you edited events right if i edit that we go right back in is that same macro so.
We can use the same macro to do two things but we just have to understand what is the state are we currently in edit mode but we're not here we're in purchase mode right if we hide those details excuse me here we're in edit mode because we can edit the events but if we hide those details now we're in purchase i should probably hide this ticket information this group of shapes should probably be hidden more in i don't think we should show the ticket information but i'll work on that maybe we'll add that on so all we have to do is and the same thing for edit event right edit event is tied to the same exact macro so we see that hide or show macros so we edit it it is this macro all we need to do is determine whether it's currently in edit mode or not there's a few.
Ways to do that one is simply to check a column to see if a column is hidden we know that when we're not editing it when we're in the purchase mode here where we can actually purchase tickets right we know in this particular mode column f is hidden so we can use that right when it's not when we're editing the event call per column f is visible right so we see that column f is visible so we can use column f to determine whether it is visible so if with events range ff entire column hidden equals true if it is hidden then we know it's currently in purchase mode right it's hidden.
It's currently in purchase mode if it is currently in purchase mode then we want it to go in edit mode we want to be able to edit events this way we can show a lot of information on the screen but condense it down using hidden columns so then we want to show the edit else then we want to hide the event so with that we don't need this i didn't use that anymore or this we don't need that and then so what i want to do is that i would simply want to hide and show those shapes accordingly so we have an event group called edit event group we want to show that what is that edit event group when i edit the event notice there's some shapes that are associated.
With that we've got the generate seats we've got a delete vent we've got the ability to duplicate an event on one click which is really helpful hide the details save an event and add a brand new event so all those buttons are associated with the event that group is name is called edit event group i do not want to display these buttons unless we're in edit mode when we hide the details i want all those shapes to be hidden so only to show up my data so we certainly want to either show or hide this so when we are called methods hidden when we're going to edit that event we want.