How To Create Your Own Recipe Manager Application In Excel [Masterclass + Free Download]

hello this is randy with excel for freelancers and welcome to the recipe manager in this week's training i'm going to show you every step on how to create your very own recipe managing application and that's going to be complete with dynamic categories we could be able to create and load your own categories we're going to have dynamic search replacing based on ingredient or we paste on recipe name we are also going to have a selection where we can simply select on a recipe and it's going to load not only that we are going to have up to four pictures where we can use this incredible slider back or forth and we'll be able to select on steps show display add new steps remove steps and a lot more i cannot wait to share this with you so let's get started all right thanks so much for joining me today i've got a brand new training for you we're going to be going every strap i'm going to show you how to create this screen then we're going to go through every line of the vba code even if you have no intentions of creating your own recipe manager there's going to be so much to learn in this week's you won't want to miss a minute we're going to show you how to create a dynamic menu like this it's going to be really great able to show that of course selection we're going to show you this really cool slider i'm going to show you also how you can create your own recipe steps by adding steps removing steps displaying these pictures dynamically and a ton more we're going to do this screen from scratch and then i'm going to go every line of code with you on how to create this home i hope you do like these trainings i bring them to you absolutely free each and every tuesday i ask just a few things all you need to do one click that subscribe button below and don't forget to click on the notification icon bell that'll ensure you get alerted when i create these trainings each and every tuesday comment below i'd love to hear your thoughts ideas feedback that is always helpful these ideas come from you so i'm creating them for you and also of course don't forget to hit that like button let everybody know that you did like this video i put a ton of training in these videos so i'm really happy to share them with you if you want to help support us at excel for freelance just some great ways to do that first of all i've got 77 special for over 200 workbooks of my best excel workbooks you can get that in just a single zip file for just 77 it's an incredible training another great way brand new is on our patreon i'll be taking these applications and then adding features or adding fixes or creating a focus area and a brand new video and a brand new updated training and that's only on our patreon account so if you want to get that extra help support and also extra training you can do that on patreon for just a few dollars a month all right let's get started this is a sample one i'm going to be putting this away but you get the idea basically before we get started i basically will select seafood or whatever our categories will come from the admin screen it's dynamic so each one you can have your names then you have your picture that's associated with that so we're going to have that you'll be able to select on a recipe we'll be able to search for a recipe by name or by ingredient so if you want to search for chicken as an ingredient you can do that and it's going to show you all the recipes that are associated with even this pork schnitzel or this pork but of course it does contain a chicken stock so you you know anything that contains chicken we'll be able to cr select different steps we'll be able to add pictures to steps and of course we're going to be able to create this really cool picture slider we can add up to four different pictures for every single recipe of course we're going to add new recipes save recipes delete recipes email and print recipes it's gonna all be great okay so what i'm gonna do is i'm gonna put this sample away and then we're gonna get started it is gonna be this screen is gonna be somewhat from scratch what we'll do is we'll show you the basic design of it so how i came to it and then i'm gonna share with you some of the the images that i created and the shapes and then what we'll do is we'll walk through every step of code so but i want to give you the idea of how you can create your own screen like this all right the first thing we're going to do is give it a title recipe manager i spell things wrong quite often so that's kind of entertaining for you to catch those and then what we want to do is we'll add an icon here but we'll add that a little bit later on and i want to search by but here basically i want the categories right so i want to know what as you saw them the categories right you can have a list of categories here and then i also want the recipes the result of that as you saw there so we're going to sell recipes there there's been some formatting that's done here so you can see that and we're going to put that in side row 3 and i also want the search filter what are we going to search by so i'm going to have a dynamic drop down list i've already put that here ingredient or recipe so if we look in the data and data validation it simply is just two values ingredient and recipe name because i want to search by both of those so that's already done already and then what we'll do is we'll give these a color and then down here what i want to do is i want to have a list here about a list of ingredients so i'm going to do that ingredients everything's been formatted a little bit because this is an advanced excel so you pretty much understand that but i want to make sure that you understand how we can create these ingredients make sure i spelled that right for a change so then we'll have the quantity i want to know how many the quantity of that here so we'll put down the quantity and then in here i want the unit of measure we'll abbreviate that unit measurement cup but you know a tablespoon a teaspoon and then of course the ingredient ingredient what is the exact ingredient there okay once that i also want to have a list our steps down and i want to have some formats i want to know the recipe name so i'm going to put in the recipe name i want to know the name and i want to know what it yields right in this case what is it yielding what you know what type of is it you know going to be four servings or three servings or whatever we have there and then what i want to do is i want to have the nutritional information actually i'm going to put the recipe name let's put that down here recipe name down here and then put the yields down here i think that's going to be a better yields because i got four of them here to put in down yields and then next up i want the nutritional information i want that nutritional nutrition calories and protein and information okay and then next up i want the recipe information recipe information that's the long information about the recipe as you saw in the sample so that's going to go here in this merge center okay so we have that the yields and i also want to put the cook time what is the cook time right how long is it going to take to cook and also a prep in this case and i want a rating i forgot to show that to you a drop down list a dynamic drop down list i better get to that a rating i'm going to show that to you rating and then so basically what i want is a drop down list we can click on the star so i'm going to show you that and then also i want the category here the category what category is it going to be it's the chicken a beef category now i have a list of categories that i'll show you soon okay so that's the basic idea so here we don't need that there that'll just be empty so basically i have a field here so let's give these some colors and that's going to be pretty much it it doesn't have a lot to do so here this is conditional formatting that's why i got changed color so don't i'll show you that in a moment okay so we'll do this ingredient i want to main and the steps i do want to put the steps here what are those steps i want those steps to show up right here and then i also want to be able to put a picture a picture here this picture right so add step picture we're going to be able to put add step picture right here so that picture is going to be here and i'm going to have a browse button right here so these are going to get a certain color here so i'm going to drop this down we'll format that color just to give that those header rows and then also these two so basically all these are going to get a specific a bit of a darker color not that dark but something like this here and then what i want to do is i want to have that sub information those main information the recipe here these the cook time the category and the rating i want to give that just a little bit of a lighter color something like uh let's take a look at this one right here okay and then i'll just i want to actually a brown i'm going to format those cells i want them in this case i want a brown border so i'm going to give a border color and i'm going to select a specific color we'll use this color here and then we'll just give it a border all the way around and inside okay and we'll make those updates accordingly all right so i like the way that that looks and now as you remember previously we saw a really cool background now i think it's time to put that on so what i'm going to do is i'm going to go to page layout here i'm going to go background i'm going to select the background now i have this wood grain picture it's very very cool right this wood grain i'm going to insert that okay and that's going to give it that really cool look that's nice but we really don't want to see the grid lines in here so view and then grid line so we can undo that okay so now you see how we got that look so now what we want to do is want to color those fields in white to make sure that we can understand that those are the fields that we're going to be adding to our categories then we want to put the ingredients here those user entered fields should be in white that lets the user know that they can enter the values in there so that should be white and again we'll go with the same brown butter i'm going to format border i'm hungry i'm talking about butter brown border around there and then i don't know why i'm hungry looking at all these recipes okay that's cool and we also want to do these two here as well i want those in white and also the brown border good format those cells actually format let's right click and format actually what we can do is we can just do the border we'll select the line color here that we're going to be using here sorry it's off the screen here but that's that brown one and all we need to do is just go to the borders here all right so i like the way that that looks what i want i'll have a shape up here that's called search by that's going to go up here at the top we've got categories that are going to go down here we've got recipes in shapes they're going to go down here so the categories are going to be shapes the recipes are going to be shapes that's why this is so easy and these we do need to give that i'm going to give it that a little bit lighter browner color which is going to be this one right here and okay i like that though let's uh drop down maybe a little bit lighter on that brown so we want that sub that sub yeah that one that's the one i want so notice it's sub and we'll again give it that brown border okay so it looks really good things are shaping up good there's not a whole lot of things going on here as far as the screen because there's a lot of work on the shapes so let's take a look at that so we have a ad step picture right that's going to be we'll give that that brown color there and then they want to be able to add that picture so we're going to have that picture show up here and i want that step in white right because so they'll add the steps and then i'm going to put this brown brown here so we want that light brown color and then we'll just give it the border all the way around that's looking pretty good but now what we want to do is we want to add our shapes saving our work now i created a lot of shapes before i think it's just easier right instead of creating shapes manually i'll just unhide them because they're there they're hidden there they are so what we're going to do is we're going to show all well that's a lot easier isn't it so let's go over what we just added so it's going to save us a lot of time because there's a there's a lot of vba i want to get into so basically all this is a text field and you know we we kind of run out of space here but when you run out of space and you want to put another field just add a text so that's all i did search by here what are these this is a drop down list this is really cool notice how that's working already so this is a this is what i forgot to show you before this is a really cool drop down list you can select on here select on here i'm going to show you how to do that too so we've got our shapes we've got uh everything pretty much set up the way we want to we've got our button see how quick that was basically all we have and of course if you want these icons if you want all these pictures if you want everything join our patreon i'm going to put every single picture every single icon everything you see here will be in one single zip file on our patreon so make sure you join that and i'll include the link down below for you all right so basically what i want to do is let's go over some of these shapes in my notes so this is just a text box all with the search by this here take a look at this this all it is again is just a simple box right it's just a simple rectangle all i did was put the less than that's all it is and for this again oops i clicked the macro there oh well never mind and this is the same thing but this is just the greater than all it is and all i set the transparency let's take a look in there the formatting the shape and the transparency is fifty percent right so if it was a hundred percent it would be right white so it's just a fifty percent that's all it is is this rectangle and there's a macro that's on this line that macro is going to let us switch between there all right now i'll show you of course why those pictures are some we can clear the pictures we can email the recipe okay so what do we want to do now i want to be able to enter a recipe but the first thing we want to do is enter some categories right right i want to have a macro that's going to load all of our categories now whatever our categories by our categories let's go over briefly at the admin screen see what we have here i've got a folder called category pictures this is the folder that all of our pictures are going to be stored in right here so if we take a look at this here i've got inside my folder we've got food categories and i've got a folder with six food categories beef chicken desserts pasta okay so those are all the pictures that i want to go in the categories and of course i'm going to include these pictures on our patreon so i've got recipe pictures these are pictures that are specifically for the recipes right every recipe has up to four pictures and of course we can switch with that slider so we've got that also i've got step pictures remember each step can have its own picture so i've got step pictures too so and then i've got some info just information some helpful information okay so that's what we got so we got really folders so i want to know what folder or categories are going to be stored in what's folder okay i also want to know what folder our recipe pictures are going to be stored in i want to know what folder are step pictures remember got three different folders three different folders so i want to map all those folders here so when you get this file and you go on patreon and you download all this great stuff make sure that you also put in whatever folders you are and i also have a default recipe picture and that means basically i want to set up a specific picture this is the picture here i want to set if there's no picture for the recipe i want to set up some kind of a picture and i'm using this as our default so you'll see that when i clear the picture out or if the recipe doesn't have any pictures i want to set this one as the default so we're going to use that picture and i need to map this entire picture including the folder and the name into a specific cell so that's c6 that is the entire file path of that default picture also i've got the list of categories we'll be able to loop through these as you add categories and as you add pictures for each category they'll be created dynamically and you can click this button refresh and it's going to do just that if i click that button now it's going to automatically create those categories here because the macros are already completed so we're going to go over that macro of course we're going to be going over every macro in every field every formula so don't worry it's going to be a long training so i hope you got your coffee or your beverage of choice so that's exactly what this macro is going to do it's going to refresh and create those categories i've got the quantities these are going to really be helpful for when we want to add a specific ingredient we need to know how much 1 8 1 6 and i've got the values associated here now these values we're not really going to use in this specific training but i think it could be very very helpful in the future so i combine them so that one is that if we're using if we want to combine values or we want to create count calories or something like that we want to convert this into a value it can be very helpful but basically we're going to use a drop down list when we want to select quantities for recipe items we need to know 1 6 and then we also need the unit of measure it is 1 8 cup one quarter cup one drop whatever so i've got all these names and the abbreviation right that's going to be helpful again we're not going to be using this really but could be really helpful in the future so i'd like to add things in even if i don't use them all because you may want to use them so i put them in here for you when you download this application for free using the links in the description below so we've got that that's on our admin screen that's basically it that's all i have there i've got a recipe database i've got up to 20 recipes and then each recipe name they have a category that's associated with it the yields the cook time the rating now notice that the rating is four or five or whatever it is but when we show it and we load it it's going to actually show the stars instead right so it's going to be really cool so if i save a recipe as three stars that's going to be putting a three here otherwise it can be four or five whatever the rating is okay we've got nutrition information recipe information and then we have up to four pictures per recipe picture one two three and four okay remember we'll be able to scroll through those pictures just like this okay so i'll show you how to do that very very soon so that's it for recipes now each recipe has its own ingredients right we've got a list of ingredients we have unlimited ingredients per recipe so to do that of course we need to track all of those recipes right so each recipe has its own id number unique id here and so for example if i want to know all of the ingredients for the butter chicken i need to know what is that recipe id so that would be stored here so all the ingredients up to here are for that butter chicken i think i'll have that for dinner so natural yogurt lemon juice so all these are notice the quantities notice the units of measure right we just went over that the row what is the row this is the ingredient row that's the row that's going to appear here row 12 13 14 and 15 right so that's going to appear as we add ingredients here right we can add specific ingredients assuming that we have something we can simply add ingredients for each one of our recipes just like this now what i'm going to do is i've got another list of thousands of foods and i'm going to include that in our patreon update so i'll make sure to put those foods i don't i think it's about six seven thousand different ingredients i'll include that so you don't have to even type it in they'll be here in a drop down list or i'll make it auto complete so you can start typing so that's going to be i'll put that on patreon as well all right so let's take a look here so basically we have all that so we can enter those ingredients and all those ingredients are stored here so i need to know the row remember the row of the recipe 12 so that when you enter a certain recipe on a certain row it comes back to that exact same row and i'm using conditional formatting here so that we'll see okay so back into the recipes and of course i need to know the database row that's the current row five six seven whatever it is the row that it's stored on that's really important lastly i have the steps each recipe again each recipe has a set of steps so here's the recipe id so that butter chicken in this case it has six different steps and the step number one two three four we need to track that i also need to know the text what's in a large and i need to know a picture associated with this step if it has a picture and of course the row that is associated the database rogue and that's it so when we combine that remember when i load this recipe and i'm going to load all the information for that recipe the name the category then what i'm going to do is i'm going to load all the ingredients i'm going to run an advanced filter i only want to know those ingredients for recipe number one so i'll put a criteria in here it's going to return all those results for that i'm going to bring those ingredients in i'm going to do the same thing for the steps for the recipe steps i'm going to put in a criteria i'm going to load only those steps and what we're going to do is we're going to load them in right here this recipe of course those are going to be in shapes because it's a lot more flexible notice we're not using cells for those we're going to be using shapes okay cool so that's pretty much it now we've got to kind of cut the overview and the good thing is we've got a working version that we can work on i'll be going over every line and this one there's so much code i won't be typing out the code unless i find a mistake but i will be going over every line of code that's going to help keep these trainings maybe two hours i don't know about this training we'll see but that's kind of what i'm aiming for on this one because it's a good amount of code and there's so many cool things in here remember you don't have to create this recipe manager but i'm going to show you think of ways that you can create this using your own applications your own ideas basically it's just a ton of tricks there's some techniques and some great tips that you can learn to build your own application so i want to see you use these techniques in your own application whatever you like so the first thing you saw the macro that ran already that's these categories let's go into that macro and see how we generate categories but before we do that i want to be able to show you a few things that we're going to go so for these categories here we're going to need a sample right if i'm creating this dynamically if i'm creating this shape i need to start with something well i'm starting with this circle now this circle doesn't necessarily need to be filled right this circle here is just a simple circle with a border all i've done is given it a border and a picture if i were to manually insert a picture not using vba i would go inside the format picture and then i'd go into fill and i'd select a picture i'd insert a file and it select a specific picture if i wanted to put that wood grain i don't know why i would but if i wanted to do i could put that wood grain in here so all we need to do and if i want to just put no fill i could put no fill solid fill either way so we'll keep it like that because it's vba that's going to put that picture in vba will take care of it so it doesn't need to start off with a picture but what i do want is i want to sample just exactly the way the right size the right shape the right width everything so all i need to do this call this one called category sample picture all i need to do is duplicate that and then make the updates accordingly okay and i also want the name notice there's a text name now this is again just a shape here it's this shape here let's bring it out here i'm going to bring it over here so you can see it and this particular shape here also has simply a fill color right and it's got a transparency of it should be around 50 right whatever you want so this is just a simple shape simple text box with a transparency of 50 that's all it is on this chicken right and so this this specific name is called category sample name so these are kept they're hidden but it is these that we're going to use to create that category so all i really need to do is do a few things one i need to know the folder that we're going to have those categories two i need to know the last row and then i'm going to loop through these rows i'm going to say okay all the rows that have values i'm going to look for a picture this a picture called chicken inside this if it's found i'm going to create a circle i'm going to put the picture inside that circle and then what i'm going to do is i'm going to create a text box i'm going to give it that name and that's just how we get this chicken so let's take a look inside vba and see exactly how we do that from the developers tab in visual basic you can do alt f11 will get you there now what i have here in this application is pretty much two modules and then we have one based on the sheet code and one based on the recipe so we're going to start off in this sheet code here and we have one here called let's take a look right here this one's one build category menu this is the macro that ran as soon as i clicked this button this button here we signed the macro we see it's build category menu so that's the macro that we're going to be focusing on right now okay now i'm going to bring this down so we can see this both the admin screen and the code at the same time and you can see everything okay so we have that and we've got a few different sheets so you see so basically we're just going to build that category menu the first thing we need to do is if there's any shapes i need to delete them right we're recreating these so any of these menu shapes here i need to delete but i have to make sure that there's something unique about that right they're called everything in this is called continue called has the words category category picture or category name nam in this case nam specifically nam so notice that the sample doesn't have the full word category and there's a reason because if i'm going to delete i only want to delete the shapes with the name full name category right only those that i want to delete because before i rewrite these maybe i've added a picture maybe i've changed the name i want to delete everything here all six of those pictures all six of those text boxes so i want to delete those and so to do that i just need to check for every specific shape in a sheet so the first thing what i want to do is to do that is delete that i want to make sure that of course we do have a folder inside that i want to make sure that we have an accurate folder if we don't have a folder here for pictures of course there's nothing we can do so the first line of code is to do just that if the admin range c3 equals empty or the directory of the admin c3 vb direct mean in other words is it a correct path admin c3 vb directory this would mean incorrect path if that's empty there's something wrong with the path then let the user know please select and add or correct packet right so if i try to run that code and i change just one letter on here and i try to run that code now it's going to give you that error it's going to say hey you know please select correct category picture folder in the admin screen right because i've i've misnamed this purposely so that's why it's not working so we need to know if there's any issue so that notification will come up okay now once we have that what i want to do is i want to take that category folder i want to put it in a string variable called category folder all of my variables have been defined up here okay i'll go over them one by one as we get to them so the category file this is the again this is a string variable let's go back into the macro here admin c3 and what i want to do is i want to add a backslash on to that string that's the entire path of the category folder now we have a perfect file name all we need to do is add on the picture and we have the full correct file path of the picture but again i need to know the last row of the categories if we're going to be looping through all these i need to know the last row in this case it's 14. so we can use a single line of code to do just that to get us that last row and that is last category equals admin b34 and excel x11 the last category row if for some reason the last category row is less than nine then of course we need to know let the user know to make sure to add some users in the admin screen here so if it's less than nine that means there's no categories assuming that they have categories we can move on we're going to focus on the recipe screen recipes this is the vba name for this that i've added it's called recipes notice the name they're both the same recipes we're going to focus on that sheet right there with each category shape this has been dimensioned as a shape up here just like this cat shape as shape so now that i've dimensioned it as a shape i know it's a shape vba knows it's a shape so we can work with it just as that as a shape so to do that we can say for each category shape in dot shapes recipes dot shapes that means for every single shape if the category name if the name of that shape the name of that shape contains the letters category of the string category it does not equal zero that means it's been found in that shape then delete the shape we can delete it we're not deleting the sample we're just deleting out so we're going to do that for every single shape once we have done that what i want to do is i want to set the row now i'm going to be placing these categories about four rows apart notice the first one goes on four right the second one right on nine so about five rows apart here but i want to set the first one the first one the first row is going to be on four then what i'll do is i'll increase it five the next one will go to nine then the next one will go to 14 right so i'm going to put five rows in between but i need that starting row it's going to be on row four because that's going to tell us where to place it so we're going to set that initial row to the recipe category row equals four set the initial category row then we're going to run that loop from nine to the last category room remember that loop as we mentioned starting here at nine going all the way to last in this case fourteen i need to run the loop because i need to get the picture and i need to get the name and i need to put that information in those shapes so we do just that inside this so in this case we're going to set the picture name to the admin c in the cell row this is the name of the picture okay file picture name okay then this is of course i need to set up this here this is the file path the full file path remember it is that category folder combined with that name is going to be the full file picture path right picture path that's the full file okay now what i want to do is i want to make sure that that's accurate to make sure if the directory picture file equals empty then go to next right we're just going to go to we're just going to skip all of this and go right here right we're not going to put anything down here that's all we're going to be doing i guess we could go right here in other words we're going to end the picture but i guess we could put in the text right here right so this is for the picture here this is for the text so we can skip that and just go to the text in other words no picture but we do have to text okay so then we're going to skip if we're going to skip that picture next category which is here but we don't want to assuming that we do have a correct file path name then we want to do the following what i want to do again as i mentioned i want to take this category sample and i want to duplicate it when i have the duplicate i want to name that duplicate i want to give it a specific name what name do i want to give it i want to give it a name that i would understand so in this case it's going to be category picture 9. why is it 9 because i want to base it on this row here or this row or this row that way i know what's been selected right so i'm going to give it a specific name and a number so we're going to do just that here with the following lines of code if the directory picture oh we went over that here shapes category sample picture we're going to duplicate it then we're going to assign it a name a unique name it's going to always start out with category pick and then we're going to add the category row in the first case it's going to be 9. once we have assigned that to our new shape we can then work with it so with shapes category picture category row the first thing i want to do is place the left position it's going to be equal to the recipes in other words i've got to call out the cheat name again because i'm inside another width so this time i have to call out that sheet name d4 right the left in this case right the left position we're going to use an exact cell the four doesn't necessarily matter the d does matter okay plus 16 it means i don't exactly directly want it right on d right i want to move it over about 16 pixels to the right so notice it if it were exactly on the right it'd be about here okay so we don't want it there right on the right side we want it moved over to the right right not exactly on the left side of the cell right or to the right of that cell border we want it in the middle so we're going to bring it over 16 pixels to the right left plus 16 will get us there then i want to set the top position to do that what i want to do is also d and whatever row remember we started our row off here on four the recipe category row it's going to start off in four then it's going to grow so our first row we're going to set it to whatever that row is the top position of that row this is going to set our top position set top position and this is going to set our left position set left position then what i want to do is i want to actually fill that picture and just duplicate it so fill with picture and we can do that with just a line of code i put in width that's automatically created this end width but we don't need that so what we're going to do is we're going to fill we're going to fill that shape this shape we're filling it what are we filling it with filling with the user picture what is the file path of that picture that's now accurate picture file name that fills it with that picture now what i want to do is i want to assign a macro to that we're going to go over that macro next that's the macro that happens when i actually select one of those so there's that macro that's going to load those recipes up here once we select on something so i want to assign it a macro so that macro will got regardless of whether i select the name or whether i select the picture okay so that that macro the name is called category select that's the next macro that we're going to be going after this okay so that's it so that's all we need to do with the picture now we're going to focus on the name right we've added the picture we positioned it now what we're going to do is we're going to create this little text box again all we're doing here is simply copying this duplicating this sample text and then giving it that name similar and then just positioning it so the first thing shapes category sample name duplicating that category name and category row okay again giving it that unique row which as we loop through all the rows then we can focus just on that shape we're going to give it the left position exactly on the left and this time right now moving it over d4 and we're going to set the top position now the top position is going to be the top position of the row plus the height of whatever that picture is right so basically what i want to do is i want to put get this top position right it's not going to be this it's going to be this row plus what plus the height of this the height of the picture right i want to know the height plus a little bit more right so that was going to drop it down so that's all we do the height of that picture remember that picture that we just created here category picture plus one plus one pixel it's going to drop it down that is that's going to set the top position of that text box set top position of the text box okay and this is of course going to set the left position set left position so now that we have that all we need to do is now we need to put in the text right right here we have something called chicken dishes we could put in i can put in sample text it doesn't matter because it's going to be changed but it's good to know that it says sample okay so what i want to do is i want to replace whatever text is here with the actual name where are we going to find that of course that's located in b but we've already turned it into a variable right we already have it or we actually we don't didn't put in a variable that's okay text frame is going to be b just as i mentioned and the category row as we loop through those category rows we're going to set the text right then again we're going to assign that same macro the same macro that we did assign it up here to this picture so both the picture and the text box are assigned the same macro then all we need to do is increment the rows by five remember we're going to increment every category we're going to increase by five the first one starts on row four here second one goes to nine and then of course 14 and so on and so forth so that's it that's all we need to do to set those categories so when we run that macro here and we can run it and it's going to automatically create those nice and organized categories okay now notice that when we actually select one something happens right what we're going to do is we're going to load this so what do i really want to do when i do this well that's the macro that we're going to go right now and basically what i want to do is i want to determine what category was selected was a chicken was it beef and i want to run an advanced filter we have a lot of recipes here but i want to know only those categories that we've selected right so how do we do that how do we know which one well we're going to run an advanced filter if i determine what was selected i can put that in some criteria i can put that in a category right here i can then run an advanced filter based on that and then i can know exactly what was selected based on that advanced filter and have those results come here so all we need to do is get that chicken or whatever they select if it's beef i need to get that beef and i need to put it directly inside p3 so how are we going to get that well we don't we what how what do we know we know what we're going to know what they've selected right so if i know category pick was selected or i know category pick 10 right what if i remove the text what if i remove all these first letters here it's going to leave me with 10. if i know that it's 10 they've selected 10 all i need to do is look here for b and whatever row 10 or b and whatever row right 11 whatever that number is if i extract that number now also notice another thing notice how category pick has the same number of characters as category name and am right same number of characters so if i remove those characters if i know what they've selected and i remove those characters using the replace it's going to leave me with the number no matter how many digits that number is it's going to leave me with that number and that's just what we're going to do inside the code so that's the next macro that we're going to run it's that macro that's been assigned to that so it's called category select and just like we did before the categories this time in the recipe we're also going to create a sample notice that i've got a sample here let's bring that out where it's a little bit more visible here right here and i've got a shape here basically just a text box called sample recipe name and again if i go and format the shapes you can see the transparency is set to 50 it's going to give us that nice look as opposed to using cells right here we have a really nice look if we can use shapes one we can create this really nice menu effect where we don't have to select on a cell right and two we get this nice transparency which really looks good against the back wood and of course we can also change the color really easily when they select it we can then change a color okay whoops i did it too quick but you get the point there so there we go so basically that's all we have to do and yes it's working right remember the macros are all working so basically the idea is when we use shapes we can make it white so i did want to show you that but we need again to start with a sample and we'll let's why don't we give this something a little bit more clear call this sample right recipe recipe name okay that using this sample all we need to do and we can set the width to whatever we want because it's going to automatically be set to let's just say there so it's going to be set based on this column right i want the width if i reduce this column the shape is going to be reduced so again with this shape all i need to do is duplicate and i've just put it over here duplicate that shape and then create the information so that's just what we're going to do in there but how do we know what shape to create well when we run that advanced filter we're going to get all of those chicken recipes right then all you need to do is loop through those and simply take whatever the recipe name is and put it directly inside that shape as a text box and that's just what we're going to do inside the vba but the first thing what we want to do is of course remove any specific recipes that have already been there so each one of these has a distinct name called recipe name recipe name right so anything that contains recipe name again we've given it a row and it's distinct numbered that so we know it and we could probably increase these a little bit but you know for the training it's fine so we've given this a name and what i want to do is i want to delete all those first so we do that again to do that for each category shape right the first thing oh first thing what i want to do is i want to extract that row remember when we click a category i need to know is it 10 right is it 9 is it 10 is it 11 right because i've got to know the row that's been associated with that so the first thing i want to do is extract that row and we do that with a single line of code this is a long variable here equals we're using the replace remember what i want to do is i want to get those 11 characters the first 11 characters and i want to remove them how do i do that well i can use the replace command basically we're replacing something right what are we replacing we're going to take the application caller the application caller is the name of the shape that called the macro remember anytime you try to run this macro right here you're going to get an error why is that it's going to say what's wrong because why because i called the macro from here that's not going to work no that means there's nothing that called this macro right so it's going to create an error but when of course when you run it from a shape there's an actual shape name in this case it's called category pick 11. so all we're going to do is we're going to take the left of that name and we're going to remove the 11 characters we're going to replace the left 11 character we're going to replace it with nothing when we do that it ex leaves only the category row extract category row from shape name that's all we're doing and that's regardless notice this also has the same thing although it's different name but it's got the same 11 characters it starts with the same 11 characters leaving us the 9 that's what we want so that way whether it's one digit and this 9 or in case it's 2 digits in cases it won't matter because we're removing the first 11 characters and leaving us with whatever's left in that case it's the number so once we have that number we're going to put it into a variable it's going to be called category row now we're ready to remove all the shapes so for each category shape in received shape what i want to do is i want to of course i want to remove all of recipe names so for everyone to shape if it includes the text recipe name basically it's going to be all of these right when i select a new one and you need to remove all the old ones right so to do that anything that contains a recipe name we need to remove so we do that with this code if in string category name recall's recipe name does not equal zero that means the shape does contain these words just make sure when you do these commands you want to make sure any other shape doesn't have that recipe name otherwise it's going to be deleted too okay so then what we're going to do is we're going to delete the shape and then i want to do another thing notice when i click here right i want this to the category to go white i want the category that go away how do we do that well if we know that we've clicked on nine we know it so the category name in other words we've got this variable already inside a variable if i combine this category name with this we know which one to make it white right so how do we do that well the first thing what we do is if the in string category name i want to fill all of those clear so in other words if i click this one right and i want to make sure that this one goes back to white notice that it goes back to that transparency the best way to do that is to take all of them make them all all the shape all this one this one this one make sure they all go to 50 transparency right i want to go to 50 so if i select one right if i select this one beef i need to make sure that if i select chicken beef goes back to that 50 transparency so how do i know i don't really know which one was white before we don't i don't keep track of that so all i need to do is make all of them 50 transparency and then the one that i did select make that zero transparency zero which is the full color so we do that here so that means for every single shape that contains the word category name every single shape if it does not equal zero means there's a shape that contains that then what we're gonna do is category shape fill transparency equals five so basically this single line of code all it does is it takes every single name here and makes it a fifty percent transparency that's all that's all it does here fifty percent transparency so the next thing we'll have to do down below is make sure that the one we selected goes to zero percent transparency so we're going to do that a little bit down here so we're just going to loop through that now we know the category row we've selected that if we know the row and we know the category name if we combine those two here we know which one to fill for example if i've selected beef right and i know that i've selected 10 right if i combine 10 i've extracted that 10. if i combine that 10 with the words category name i know exactly which shape to color white so that's just what we're doing in here we're going to color that white so recipe shapes category name category fill transparency equals zero that means full color full color 100 would be full transparency right 100 would be invisible full color no transparency okay and then this is half transparency this is we would call this 50 transparency so 50 transparency so we've got that so that's how we do it so now we've colored the right one so now what i want to do is i want to clear any searches if there's anything in a2 i want to make sure to clear that right and then what i want to do is because there's going to be different types of searches i'll show you that a little bit later on so now we're going to focus on the recipe database right that's where we're going to run our advanced filters so the first thing what i want to do is place that criteria this is called the criteria i want to place that how do we know what the criteria is right remember we know the category row right so we know where it's going to go it's going to go directly inside p3 in p3 what i want to do i want to get that category number if that category number is 10 then i know admin b and the category row going to place that directly inside p3 so that's just what we do inside the code p3 equals the admin b in the category of the category criteria now i need to get the last row of the database right i need to run that advanced filter based on the last row we're going to run that advanced filter our criteria is going to be p2 through p3 and we want the results to come into s2 through s3 so we do all of that with a single line of code a3 through c this is remember based on the recipe database including the headers and running an advanced filter we're going to copy that to another location we're going to run that criteria again that criteria is p2 through p3 as you see above and we want those results to come into s2 through t2 s2 through t2 that's we're going to results then what we need to do is determine do we have any results usually do so we're going to get the last row of the results in this case i wanted the last row of s right so in this case the last row last result row we're going to put that into a long variable it's going to be s9 that's going to be the last row of our results if it is less than 3 then we have no results we can exit the sub assuming that we do have results what we want to do is we want to set the initial row remember again we're placing shapes we're going to place shapes based on our initial row so our starting row is going to be 4 then we're going to go to 5 6 7 8. so we're just going to increment the rows based on that so we need to set that initial row just like we did the categories in this case the recipes is going also going to be 4. again once we get that we'll just have it then we can then we increment everything i spelled something our nutrition forgot a t or i told you i'd forget it so continuing on with our code we're going to set that initial recipe row and then we're going to loop through our results right i need to loop through all of the results based on that so starting in three going all the way to the last row we're going to bring all that in so the last result so first of all again we're going to that sample recipe name that shape that you saw right here we need to duplicate that that's this shape right here i need to duplicate that and then we're going to update it so with recipes shapes recipe name and the result row right we're going to write i want to position that it's going to be based on column e and the left or the top position of the left right all based on the recipe row right here and then i want to set the width just in case we change the width of the column here i want to make sure that the width so if i want to increase that right and notice if i run that again here that width is automatically going to be adjusted based on that okay so that's very very important i want to make sure that the width is contained so all we need to do is rerun it and that width is automatically going to adjust so we do that here the width is going to be based on whatever column e that width of column e is going to automatically be the same width of the shape then i need to add the text in the box well that's simple the text frame text range text is going to equal to the recipe database in other words i need to remember i need to call out our sheet again because i'm inside another width the t and the result row so as we loop through these rows this is going to be the name this name name name so we're going to get all the names here bringing them into the text box and we just loop through that incrementing the row by 1 as we do the first row is going to be 4 5 and 6. so that's all we need to do and of course we do need to assign a macro to it right and this is going to be another macro called recipe select when i select that recipe i want to load that recipe which we're going to be going over very soon i want a macro because when i select that recipe i want that recipe to load in so we do just that with this great all right so recipe search and filter we can do that now so i want to show you that while we're going down how do we do that well basically that's going to be a change event if i want to search for a recipe by ingredient or by recipe name then i want that result to come here so if i want to search beef i want to know all the recipes that have beef inside the recipe so that's going to be based on a change event notice that we have all the recipes that came about even creamy meatballs right that's not part of the name but you'll notice inside the ingredients we do have something called beef so which right here called beef stock right so that's a recipe that uses beef stock so beef is one of the recipes so how do we do that well that's based on a change event notice e2 is the change event so when we make a change to e2 we want something to happen but we also have another one right what if we search by recipe name right if we search by recipe name double click and into that i want the recipes to restart based on that so now we're searching by recipe name notice that we only have four recipes where beef is in the name so there's we've got two different types of searches based on this option right here search by here ingredient or recipe name so how do we do that well of course the first thing we do is with the change event on e2 so let's go inside the code and we're going to go into the recipes here we're going to take a look at the change worksheet change event right this is going to be found here worksheet worksheet and then change event right so when user makes a change to e2 and e2 is not not nothing what we want to do is we want to run a macro called recipe search filter recipe search filter when i go to the definition that's going to bring us to the sheet macros called recipe search filter so how do we do that well the first thing we do when we have a brand new search we need to remove all of the existing results so i need to remove all those shapes just as we did before running a loop any shape that contains the word recipe name we want to remove that deleting those shapes okay so we're going to run that loop that's going to delete all of those recipes going to delete all of these right here every single one of those will be deleted with that little bit of code once we have that what i need to do is i need to determine is it based on recipe name or is it based on ingredient because they're going to be two we're running a base one we're going to be running that advanced filter based on this database here the recipe database here the other one we're going to be running the macro and the filter based on the ingredient database so they're going to be using two different databases so i need to specify which one if the recipe d2 equals recipe name then we're going to be recipe name that we're going to be running that advanced filter based on our recipe database right it's based on this recipe name i want to look in this name to see if those keywords are found using advanced filters so we're going to be running that something like this so basically containing the word beef so to do that what i need to do is i need to add asterisk before and after so all i need to do is use a formula for that and it's right here equals asterisk and recipes e2 and asterisk okay so what that's going to do regardless of what the user puts in it is going to add asterisk so that means any recipe name containing where beef is found inside that recipe name i want to return those results and i want to put them right here however if it's an ingredient what i want to do is the same thing here the same code but this time what i want to do is i want to know any recipe in which the ingredient was found right so if it's been found i want to know the results of that so i want those results to come here the recipe name right i want those the recipe ids and i want to bring down the recipe names of any recipe there so how do we do that well i'll go over each one with you inside the code the first thing is we're going to focus on the recipe name and that's going to be this database right here so again all we need to do is get the last row and run an advanced filter so we're going to do just that last row based on the recipe database the last row is going to be with them if it's less than three then exit the sub so it's the same thing went over we're running an advanced filter but this time the advanced filter criteria is going to be based on q2 through q3 q2 through q3 then we're going to copy that again just like we did before s2 to t2 and we're going to get those results i'm going to determine the last result row based on column s and then if the mess if it's less than 3 we do want to alert the user that no results were found right so in case that happens let the user know no results are found and exit the sub because there's nothing that means there's no results here it's less than three let the user know assuming that we do have results again we're gonna do exactly the same thing we're going to set our initial recipe row we're going to run our loop for results 3 to the last results row we're going to duplicate that just as we did before we're assigning it a name we're going to assign that result row i want to know that result row this time i want to rub the result row because when i want to look it up i need to know that result row very very important so we've got that result row here again we're going to do the same thing assigning at the same positions setting it to the same column width setting the text this time the text is going to based on again what's in t in the result row that text here t in the result row i want to know that text and the name keep in mind the name of that shape recipe and the result row that name is going to come in handy when we load that in okay and then i want to assign a macro called select macro okay so that's it we're in committing the row so we're going to basically doing the same thing we're just simply building this list of shapes based on these list of results okay that's great perfect but what if it is so that's all but what if it's a search by ingredient else ingredient then we need to do something different we're going to be focused on the ingredient database row this time what i want to do is i want to clear some results so here inside the ingredients i want to clear any previous results okay from q3 all the way down to r and i want to clear those results now what i want to do is run an advanced filter based on this criteria here but notice that we don't have the recipe names here we don't have any recipe names here we only have ingredients here we do have the recipe ids so what i want to do is i want to return all the recipe ids where let's say beef is found if beef is found i want to return the id those results are going to come right here then what i do is i have a list of recipe ids but i don't need the recipe ids what i really need is recipe name how can we get the recipe name from the recipe id well we can use named ranges so if we take a look inside the formulas name manager we have two named ranges that i'm going to talk about recipe id is a dynamic named range based on the recipe id using the offset formula recipe name is very simple using the offset formula recipe name so i've got those two dynamic named ranges okay once i have those i can easily extract that name using a formula so if we take a look at this formula if there's an error we're going to index we're going to index that recipe name it's going to be based on the match right i need to know the row where can i find that recipe name well we're going to match based on the recipe id what's in q3 and then we want an exact match we're going to use 0 and i want to use a single column and if there's an error it's going to be empty so all i need to do is determine the last row i need to copy this basically copy this formula and bring it all the way down here but it's we can do a formulative formula exchange as long as our formula here is based on our first row then i can just simply say the formula of these is equal to the formula this then it's going to bring in that's going to extract all of it and bring that name down once i have that name i can then run a loop just as we did before running a loop so if i put in the recipe in this case ingredient and double click on here it's going to automatically run that and then our results we can then loop through those results and down like that so that's just we do inside the code so the last row again based on the ingredient database this time we're focused on that ingredient database we're going to get that last row if it's less than three eggs of the sub we're running an advanced filter this time a2 through d a2 i only need from a to d only those ingredients nothing else we don't need to go beyond here i want the criteria to be based on h2 through h3 right here h2 through h3 we're going to copy it to the range q2 remember i'm only copying those ids the names will come from the formula so we're only bringing those results into q2 and we're going to determine the last row so once we brought in our results into q2 those are the ids only we're going to get the last row if it's less than 3 then let let the user know no results are found and exit the sub if it is not less than 3 then i need to bring in that formula using column r again r3 through r in the last results row dot formula equals the formula r1 i can do that with one single line of code bring over the recipe formula as opposed to copy and paste or something like that so this is a little bit easier and then the rest is exactly the same we're going to set our initial row we're going to run through our loop we're going to duplicate that sample shape we're going to work with it setting the left position the top position the width and setting the name this time the name is going to be based on the remember we need to call out that sheet again because we're inside another width whatever's in r in the result row making sure that that result row now keep this in mind we have to not understand that if we're in a search right if i need to know right if i click on be strong enough i need to know notice that this result row is three right so we need to know it's based on this search result ingredients and beef so it's based on the search result so i need to know what result row because i'm going to need to basically extract that id that id is going to come from q so keep that in mind if i know the row three i know the q and whatever row that id is going to come in handy because we need to load that recipe based on that id okay very good so we've gone through that we understand how the filter works and that's all we need to do simply that so everything's the same we're just going to increment throw and that's going to automatically create menus and filter our recipes based on either ingredients or based on recipe names okay so far we've been over how to create those categories how to assign the macros how to recipe but now what i want to do is i want to show you exactly how we are going to load in those recipes with just a click so that we're going to cover now and of course that's the macro that has now been assigned to this this recipe select that's the macro that we're going to go over now when we select that so when we select a recipe what do we want to happen well the first thing before we actually load that recipe what i need to do is i need to determine what was selected and what rows so based on the search right i need to know again i need to extract this four why is that important or i need to extract this five why is that important because is that four or that five that's going to let us know what was selected that 4 or that 5 is going to come from here in this case 4 in this case 5 or it's going to come from here in this case 4 or 5 and it really depends on what type of filter we have set so for example if the ingredients it says ingredient and this has a specific ingredient listed it's not blank then i know we've searched by ingredient however if we search by whether it's category or like clicking here or whether we've searched by recipe name here either one of those are going to be based on this list which is our recipe database not our ingredient database okay so we need to differentiate between those two so to do that we just check here so first of all we get the result row i need to extract that result row that result row is going to come here 3 or in this case 4 right here so all i need to do is remove the text recipe and m so to do that we just do it here so replace based on the application column the shape name that called it taking recipe nm and replacing it with nothing that's going to extract the result row once it's there well again what i want to do is i want to color that shape how do i know what shape has been colored well i've got the name now so what i'm going to do is i'm going to take all the other recipe names and i'm going to color them that transparency color i could probably make this two rows you know so it's larger so what i want to do is i want to make maybe you can maybe make it a little bit wider here but that's fine for our purposes so what i want to do is i want to color all the existing names transparent basically that 50 transparent then i want to determine which one was selected and color that white so that i know which one select so the first step we want to do is color all of them that 50 percent transparency so we do that with a loop for each recipe shape in the shapes right for anything that contains the word recipe name then i want to fill the transparency of that 50 giving that 50 transparency then what i want to do is i want to recolor the one that we've selected the recipe name and the result row that's the one we called we could also use application collar with this that would be sufficient as well fill dot transparency equals zero giving it that full color full color no transparency full color and this is 50. so that's the selected we'll call this the selected recipe so great so we have that now what i want to do is i want to determine which type of search was set if d2 equals ingredient and e2 does not equal empty then i know it's an ingredient search was done then what i want to do is set b2 b2 is very important b2 is going to take that recipe id very important once i put b that recipe id but how do i know where that recipe is coming from that recipe id is going to either come from here based on a recipe database or it's going to come from our ingredient here but we need to know using that if statement right here if it's an ingredient and there's a ingredient then we need to come from the ingredient row then the recipe id b2 is going to contain the recipe ingredient database the one we're looking at above and q and the result row remember that result row comes from here right we've already extracted it right however if it's a recipe search right if it's based on whether we've selected here a chicken or whether we've based it on here chicken right in either one of those cases those results are going to come directly from the recipe database here so in this case it's going to be s and the result row s and the result row so then that case b 2 is going to equal the recipe database s and the result that's going to set so either one of those were setting the recipe id once i have placed that recipe id directly in here what that's going to do is going to return the result the row the recipe row and that recipe row is going to be based on a formula and the formula is going to be basically we're matching whatever's in b2 we're going to get the recipe id and we want an exact match and we want to add three why is that because that recipe those recipe database start in row four so if it returns one meaning the first value i want to i want the row so we're going to extract that means it's the row so recipe id 1 is going to be on row 4 so that's exactly what i want so that so for recipe id 1 is going to be on row 4. it is that 4 that i want because then we're going to run the macro that's actually going to load that recipe and that brackets here so when i click the definition it's going to switch to our recipe macros here inside our module called recipe macro and the one we're going to focus on right now is called recipe load that is the macro that's going to run that so basically with that macro all i need to do is bring in all this information here bring it directly inside this heading information right here okay so to do that we're going to use data mapping now if you haven't seen this before basically what i want to do is i want to map butter chicken is a recipe name i want to map that to k3 notice this is k3 i want to map that category in this case 03 to the o3 and that makes it a lot much quicker to load in so all we need to do is run a loop we already have our id it's already in b2 so we don't need to start our our loop is going to start on column two and go all the way to 12. what we're going to do is we're going to basically take all this information we have our row a rows located right here inside b3 we have that row and we're just going to load everything in according to that so it's going to so this is the the pictures are going to go in b7 b8 b9 and b10 notice the pictures are coming in b7 b8 b9 and b10 okay so we're going to bring all them those are off the screen columns a and b of course will be hidden okay so we're going to bring that in there and also we have that so all we need is that row that's important we're going to run a loop and then bring all that information in then the second part of it right the second part we're going to load in those ingredients then the third part is we're going to load in all of those steps along with the pictures associated with those steps so we can do that in just three parts so let's take a look at the recipe load first thing i want to do is i want to clear any star ratings why don't clear everything else if there's some ratings here we're going to go over how we did that in just a moment but i want to make sure that we're clearing all of those out so how do we do that well these ratings have a shape notice there's a name it's called three-star rating this one here is called a that's a five-star rating so basically they're all essentially the same here but except for the number that's proceeding five-star rating so anything right i wanna clear anything that says star rating i want to clear out so doing that here so if the in string run running a loop with all the shapes inside the sheet once again that but this time we're focused on anything that contains star rating if it's greater than zero in this case we're not deleting it keep that in mind i don't want to delete it i only i want to reuse them again and again so all i want to do is make them invisible i want to hide them using dot visible equals mso false once i've done that what i want to do is i want to clear out all of the associated fields before i load in the new recipe so we're going to load in all clear out all these fields and that includes b5 right b5 is located our selected picture and it's going to keep track of what picture we're located on it's going to keep track of of course all the pictures i want to clear out all the ingredients and all the steps and everything like that so we're going to clear out a bunch of fields here okay next up what i want to do is i want to make sure if b3 is empty b3 is critical we need to have that recipe row if that's empty for any reason we need to let the user know to select a specific proper recipe in that case if b3 is empty please select a correct recipe display and exit the sub okay so once we do have that assuming we do have we're going to put that into a variable called recipe row rec row and then again all i want to do is then load everything else in and we can do that through a loop because we have data mapping for the recipe column is equal 2 to 12. so basically all it's going to do is go from column 2 in our side our recipe database all the way to 12 which is this column column l and then bringing anything over in that row into the associated cells so we can do that with just this line of code dot range recipe database right cells row one recipe column this is the range this right here is the range that's b8 b9 b10 whatever so inside that we're going to place whatever is located in the recipe database the recipe row the recipe call i'm going to load that recipe data and just run through that three lines of code to load all that data okay now what i want to do is i want to set that star rating if you notice here that star rating five four three two one well those are numbers how do we convert those numbers to this shape right so if we let's say i reset that to three star rating and i save that when i reload that butter chicken save recipe although that looks really good i can't imagine that's a three star and let's just go to another recipe and then we'll go back to the butter chicken right i want to make sure that that three star got saved and it's displayed so what i want to do is i want to convert that now remember if we look in our shapes here and we take a look at down inside our our shapes here we've got several let's find it first here selecting on that and we can see which one's selected so i've got star rating two star rating i've got a three star rating a four star rating a five star rating and of course a one star rating so here so i've got all those into individual shapes right they're all hidden except for the one that i want i what i basically want to do is i want to display the rating that's associated with that number so if they've got a two star or three star rating i want it like butter chicken has a three star rating i want to display that three star rating and i want to display it so how we going to convert it will we do that with this we know right if we take a look inside b6 let's take a look inside a recipe database if we look at that it's going to go directly into b6 so we're going to take a look inside b6 and we see b6 is a 3. so now if i know i want to display everything's been hidden remember we hid all the star ratings we hid that inside our code we did that up here we hid them all so they're all hidden but which one do i want to display well all i have to do is combine this three with the text star rating and i know which one to display so if b6 value does not equal empty and to make sure it's a number b6 is true meaning it is a number and it's not empty then shapes b6 this in this case it would be the three with the word star rating combine that is our shape name so when i make that visible equals true that's going to make it visible okay i'm also going to show you how we did this really cool drop down list i hope you'll i won't forget and i won't so that's all we need to do is simply show that star rating based on the number right so if this changes to five or whatever if i change that to five right and i save it here saving our work and i load that again it is that five that's going to be replaced and now we're showing that five star rating which is probably a good recipe so now what i want to do is we're ready to follow the picture so we've done everything we've loaded all the information we've set our star rating but now what i want to do is i want to set this picture how do we set this picture up well first of all this is based on a frame all i have here is a simple frame right this is just a simple square box right all it is is just a simple square shape nothing more than that and i've taken it i've given it a brown border and we're going to fill it with a picture okay so in this case i've given this a name this particular box is called pick frame so what i want to do is i want to see do we have a picture in here if we do then i want to take that picture i want to combine that picture with our recipe pictures folder here so our folder combined with the picture name and then what i want to do is i want to combine it and then i want to fill that square filling that square rectangle in this case with that picture so that's how we're going to do it inside here so set picture 1 or the default picture but if it's not there if we don't have one we do have to have a default picture notice i've got a default picture here when i clear it right so i want to make sure that if there is no picture here i want to set it to the default picture so if i i didn't save that change so it's going to automatically load up if i were to save that change it would have cleared out that picture so if b7 value equals empty there's no first picture right our first picture is going to go into b7 right here remember b7 is that field that's been mapped automatically to that first picture in column i so if there's no picture then we want to load that default picture where is that default picture located it's located right here in the admin inside c6 so that is our default picture so what we're going to do is determine if b7 is empty then i want to fill this box with the default picture otherwise i want to load in that picture so b7 equals empty then the picture file name is equal to admin c6 that's our default picture right setting that default picture else we do have a picture the picture file name is based on the admin c4 of course that is our default folder for our pictures located here in c4 our default recipe picture we combine that with what's located in side b7 along with the backslash and we have our first picture this is the full file name then what i want to do is i want to set b5 to one right now because i'm going to have several pictures here that i'm a loop i need to keep track of what picture on mod b5 is going to help us with that b5 if we take a look in b5 we see if i select previous notice that b5 changes as i loop through these pictures take a look at b5 it's changing right so b5 is going to keep track of what picture we're on then so now we're going to set that initially to 1 set the selected picture to 1. as we run the macro which i'll show you in a bit as looping through basically looping through those pictures or as it looks like it's just a simple slideshow we can we need to keep track of one so we're gonna set that to one okay so that's it so that's what we do all we're doing with that is basically setting this string variable to whatever picture we want to display now it's easy we can display with one line of code but i want you to make sure that it is an accurate file name so if there's any issue it would come up here if the directory picture file name does not equal empty then we can load it in then we know it's an accurate picture shapes the picture frame that frame that i showed you filling that picture frame with the user picture based on that piece of file name now a picture file name is either the default or the existing picture that's it that's all we have to do next up we have a specific macro that's going to load the ingredients and another macro that's going to load the steps so let's go over those macros right now so let's take a look at that first macro loading the ingredients that's a macro right here the first thing what i want to do is i want to make sure that we're clearing all the ingredients now we do notice when we select a row we've got conditional formatting that's going to be in b1 b1 is going to take our selected ingredient row so i want to make sure we clear that out i also want to clear out any existing ingredients that might be there although they shouldn't so we're going to clear those out but they're important because we're going to run this macro when we remove an ingredient we're going to refresh this macro again so not the whole so i want to clear again i want to clear out everything from g12 all the way through high and down and i also want to know the database rather there's a database row that's going to keep track now that database rows all the way over here into column y that's the ingredient row that is the database row notice we have ingredients that's the database row whether it's 3 4 5 right i got to keep track of that too because if we make changes to that row i have to have that updated so to do that we need to keep track of it but we can keep track of it way off the screen here so we need to declare also column y so we do that through this here so the first thing we want to do is load ingredients b1 gets cleared out the select row g12 through i-99 all the ingredients and our database row located in column y so we're clearing all the existing ingredients and the data associated clearing that out so now we're going to focus primarily for the most part on the ingredients database again i need to run that advanced filter and i need to base it directly on this recipe i need to know all the ingredients for that recipe so to do that we need to know what's in b2 so inside our ingredients we have here a criteria located in i3 and that's linked directly to b2 in the recipe so we have our criteria always set up so all we need to do is get to the last row run an advanced filter only based on here i2 and i3 having those results come in to here so those results are going to come directly inside k through so we're going to do that inside an advanced filter so if the last row is less than 3 we're exiting out we're going to run our advanced filter a2 through column f we're going to have the criteria again in between i2 and i3 and those results coming from k2 through o2 and equals true then we're going to determine the last results row right we need to know the last row in this case it's 19 but i need to make sure that we have that and that's going to be based on column m and that's going to be our row m or because there may not be equation i want to make sure that we have that ingredient row so our last results row if it's less than 3 that means we have no ingredients we're going to go skip all this and go directly to here assuming that we do have i want to run a loop we're going to run that loop here if we do have ingredients so for the result row equals 3 to the last result we're basically running a loop i'm going to place this information inside our row and we also need to set our initial row here so we want to make sure but what is that row where are we going to place it i'm placing it in row 12 row 13 this is our ingredient row this is the row that's associated here 12 13 14. so i need to know what row to place it in so what we'll do is we're going to get that row that ingredient row directly from here inside column n and put that into a variable once i know that row i can place this information here and i also place the row in column y so this goes into the recipe this goes in column y but i'm extracting that ingredient row from column n so that's what we do here the ingredient row is equal to n in the result row this is called the ingredient row ingredient row so once we have that on the recipes we can then place it so the recipes g through i equals k through m here g through i equals k through m bringing it over the last thing i just need to put that row and that's going to go directly in y that's going to go right here okay so we bring that in with just that second line right here so recipes y in the ingredient row equals whatever is in o in the database row that's going to be it that's all we need to do to bring our ingredients in that's it now what about the steps the steps is also really cool but i'm using shapes in the step and the reason i'm using shapes is because i want the picture to show up exactly right if i have a picture that's associated with the step so i decided to use shapes as opposed to cells it kind of gives it a nice look too and i can do that so what i'm doing as opposed to i could have done a dynamic shape where the shape could grow or shrink as the text do but what i want to do is i wanted to put a picture and i wanted to have that picture exactly so what i did is i decided to have a fixed height for our shapes that way the picture associated with that also has a fixed line it's got a nice look okay so it should cover it you can increase or decrease your height as you see fit based on the text inside your steps but basically these are all the steps that are involved to create that recipe so i put those into a shape and just like with our other shapes i've also done the same thing i've created a sample shape so you get to see the repetition doing it over and over and over again and you can be able to create these very very quickly so step sample shape i created this application in about two days so step sample text right here is our text here that's the step that's going to be here then we have our number that's called step sample number so it is these that we're going to duplicate for every step associated so again what i need to do is i need to determine all of the steps that are associated with this with this particular recipe so to do that again we're running it in an advanced filter this is our steps database our again our criteria is based directly on the receipt id located in b2 we're going to run that advanced filter then again i need to do that but i need to do one extra step right in this case what i want to do is i want to make sure we don't need it that long or column and so we can reduce the column width here a little bit we don't need it at 242. i think 50 is sufficient here okay so what we want to do is i want to then make sure that they're sorted according to the step number in ascending right because if we add a step or we change a step right we may not want to add them in way because i do need to make sure that they are sorted accordingly step one through step step then i'm going to loop through these starting here going here creating shapes for each one of those creating that number shape placing that number creating that shape that's going to be storing our instructions then adding the picture if it exists and then associating the row now i need to know the row and i need to know the picture i need to have that information here but i don't really want to see the row and i don't really want to see the picture name so what i've decided to do is store those and i'm going to store them right here notice for every these shapes are kind of in the way but if we move these out a little bit here we can see that we've stored those individual information here so the butter chicken here so each one of these rows corresponding this is the picture if we see that this is the picture associated with the step this is the database associated and i'm skipping look i'm skipping four four rows here and going so every fourth row i'm going to add another one that gives sufficient space and so what i want to do is when i select on it i want to know i want to be able to load in that step so that i can make changes so if i right click on here right click on the shape and i see it's called it's called recipe step text 15 15. notice that 15 is associated with this row again so if i extract this one's called recipe 19. so if i remove when the select the shape if i remove this it's going to leave me with 19. if i know that it's 19 then i know the picture and i know the database row and i can load all the information in here okay so when we load the steps all i want to do is basically start out on 15 skip step 4 and load each one of the pictures in the database row and then load in the text so we need all that information again i need the number i need the text i need the picture and i need the database row all that's coming from here the step the instructions the picture and the database row so that's what we're going to do as we loop through the steps just like we did before when we create new steps i want to make sure that any pre-existing steps have been removed so if we take a look at these this is called re the receipt step text here this is called receipt step picture this is called receipt step number so notice that everyone starts with receipt step excuse me recipe step rec step so each one of those so if all three of those things have that same in common if i want to remove everything associated every shape that's associated with a recipe step i can use those first same characters to remove them all and then we can do or we can do them individually like i've done here we could easily do recipe step in a single line but i wanted to do it just in case we enable them but you could do a single one with recipe steps but in here recipe number delete if it clears recipe text or recipe step picture all of those are deleted now they could be done in just a single line but i may have some other ones that call this so i wanted to make sure to be very specific here on air resume next is important in case there's an issue with one of those shapes when we remove one it could create a problem so it would create a bug but this takes care of it for us so basically we're removing all the shapes all those numbers all those text all those pictures associated before we add the new ones and then what i want to do is i want to clear any selected recipes b11 and b12 when i select a recipe we need to store some information and that's going to be here in b11 i need to know the step that was loaded notice that we click on step 4 step 3 and i also need to know the row that's associated so for example if i click on here this is 19 so i want to know 19. and then of course i want to know the next step we'll get into that in just a moment when we add steps so we understand that we're clearing out and we're clearing out any selected recipes right when i load it i want to make sure that we're clearing out all this information here so we don't need that b11 to 12 clearing those contents okay now we're focusing primarily on the recipe steps database so we're going to click the step row is the initial step remember i need to know as we add steps here we need to set that initial row the first step is going to be on row 15. the second step is going to be on 19 and then of course on 23 and so on and so forth we're stepping four so setting that initial row here to 15. now we're going to get the last row i need to know the last row right when we loop through all the steps of course just like with any other advanced filter we need to get the last row before we run in this case it's 105. so my great assistant shane took a lot of this and took on this data so thank you very much for taking care of all this incredible data so we've got to we're going to run our criteria here ring and of course we are going to have our results come into here so we can do that with an advanced filter but we want to make sure that we've got data if the last row is less than three then we can exit the sub out we're going to turn off application screen updating gonna put that to false that's going to make things a lot quicker as long as we set it back to true before the macro ends here we are good to go okay which we did all right so assuming that we've done that off now what we want to do is get ready to run our advanced filter and we're going to do a2 through e all the way through e we're going to set that criteria from g2 through g3 and of course we now want those results coming in from i2 through l all the results are going to come all the way to i2 through l once we have those results i need to determine the last row and make sure that we have last row so the last results are going to be based on column i and then if it's going to three that means we have no steps we can skip all the way down to here no steps because there is no data assuming that we have data we can then set our loop right i want a no loop but before we do that before we begin as i mentioned we need to sort that data i want to make sure that it's in the order of those steps to do that we're going to set with the sort we're going to clear any existing sorts we're going to add a key based on i3 i3 is our first row in the data i want it ascending right the lowest to the highest and a sort of normal we're going to set the range here going to be based again and we have to again we have because we're inside the sort we again have to call out the sheet call out the sheets sometimes i used to forget to do that a lot of times it wouldn't work right because we're in the sheet here but again we're inside another width so again we have to call out that sheet and then based on the data i3 through l in the last results row that's going to make sure we get the range and then apply that sort so that's going to run the sort to make sure that those steps are in the correct order once we do that we're ready to run our loop right i'm going to loop through every single result starting in row 3 going to the last result we're going to set that step number into a variable long variable based on what's located in i and the result row i also want the instruction text inside a text string variable called instruction it's going to be located in j and i want to put that picture remember that picture file name where is that going to go inside recipes it's going to go to column a and k it's going to go based on whatever this remember we've set that row up here where's that row going to be it's going to be based on that step row so a and the step rows where it's going to be placed so right here we're going to do that right here a and the step row that means it's going to put that picture directly inside a so that picture is going to go right here it's the first a15 then a19 and the last thing i also want to put that database and i want to put that inside b so that's the second thing we're going to do but before we do that i want to set the picture file name that picture file name for those steps again we've got our location for our step pictures located right here inside c5 so i want to combine c5 along with the backslash here c5 along with the backslash along with the result row whatever's in k here's our k here's our picture i want to combine that right so we have the full file path for that step picture and then we have the step database row remember that's going to come from l i want to place that step database row in column b remember we got the picture in column a we've got the step database row in column b now what i'm ready to do is i'm ready to create that step right i want to create this step so to do that what we need to do is we need to duplicate this shape right here called step sample text duplicating that just as we did the other ways so we can do that here step sample text duplicating that and giving it a name i want a specific name based on the receipt step text and the step row remember i want that row that row is going to be 15 19 23 so that's out 15 here 19 23 those names are going to show up so that's oh no that net that step row naming those shape very very important because when we click on them we know exactly how the row the information looks like so now what we're going to do is we're going to create the text right i'm going to create this shape here i want to set the width basically to all the way from columns k through p i want to set the width of that same width of b and that's it so then i want to set the top position so we can do that from here the left position is going to be based on k and the step row the top position is also going to be k and stiffer but i'm just going to go a little bit below that so i'm going to add 2 to that so it drops it down a little bit again we're sitting the entire width basically from k through p that's the entire width k through in this case o so it should be um let's see p yeah p so it should be p i'll just update the text on that should be p column p so we're good to go that and then what i want to do is i want to set the text right what's the text the text is going to be based on that variable instructions text we've already put it into a variable so the text frame text range text is equal to instructions set the instruction text now remember when i select on it i want to i want to add a macro to that we haven't gotten to that macro yet but the name of that macro is called step load so i'm going to assign that macro called on action to step load great so we've added this text shape but we haven't added the number yet and we haven't added the picture so we're going to do that right now so we're going to add in the step number shape again we have a sample right here here called step sample number and it is that number that we are going to duplicate and we're going to give it a specific name the recipe step number and then the step row even that has one we didn't assign i didn't assign a macro to this but that's fine you could but i didn't so in this case we're going to set that up we're going to focus on that with that number we're going to place the left position based on k minus 5 right i want that moved a little bit over to the left not directly on k and i also want the top position not directly but a little bit less than the k in the top row so it's going to be -1 setting that top position and of course i want to set the number the text frame that is going to be that step number right so that's going to give it that unique number great so we've added the step we've added the direction text lastly all we need to do is add the picture if any so first of all we've put that picture right we've already defined that full file path of the picture right here in picture pound up so now all we need to do is check to make sure it's accurate if it is then we can add it if it's not then we don't so first we're going to check if the directory picture file name does not equal empty then we know it is a correct picture and we can start at it recipe pictures we're going to insert the picture file name here we're taking the sheet again pictures insert we're inserting this picture file name and we're giving it a specific name it's going to be called the recipe step picture and then the row once we have that we can then work with it again i'm going gonna lock in this case normally i lock the aspect ratio true but this case i really wanna i want them all exactly the same i want that nice look and feel so i don't wanna lock the aspect ratio in this case in this case i want to contort it a little bit and so we're going to make sure that this is false right we do want to change the aspect ratio i'm going to set the left position to column q right based directly on column q i'm going to set the width directly based on column the width is going to be 70. i could base the width directly on column q2 that's fine too that would work just as fine so i want the top position to be q in the top plus 2 right it's going to set that top position right at the top position there now what we do is we're going to set the height i want the height of that exactly to the height of whatever this is whatever this is the recipe step text i want the exact same height as that so the recipe text on the stepper height that sets the height the same as the text box that's all we have to do and then all we need to do is increment the step row is going to equal the step row plus 4. remember we're starting at 15 and we're moving to 19 then 23 so we need to increment and we're going to do that for every single step inside for that recipe then application screen update and tube that is all you have to do to load the steps okay very cool let's move on next up i showed you briefly this really cool picture slider to be able to rotate between four different pictures and let's get to that macro now and basically it's going to be rotating between these four pictures between b7 b8 b9 and b10 and of course we also want to keep track of the picture number located in b5 okay now we know if we combine this picture along with the folder located here inside c4 in the admin we can get a full file path okay knowing that what we've done is we've assigned macros now i've assigned a macro to this if we take a look at that we see it's the recipe picture previous and the one other one is basically next so let's take a look inside that first we're going to start out with the next one so that's basically the one that has been assigned to this right shape right here so when user clicks it they're going to move to the right so for example if they are on currently we are on picture number three located here this picture number three i want to move when i click here once i want to move to picture number four so this is four and this is the one that's displaying so how are we going to do that well we do that with just a little bit of code not too much first of all with focusing on the recipes we've got a string variable called recipe picture folder that's located in c4 then we're going to add the backslash onto that we want to make sure that that is a correct folder that that it does can exist in the computer if it doesn't we're going to let the user know just know to select a correct recipe picture folder within the admin screen okay and exiting the sub out then i want to know existing pictures remember in b5 we've got that picture number i want to know what it currently is on for example if it's currently on four we need to go back to one if we're going next if it's currently on one we need to move to three so we're going to set that into a long variable called selected picture number going to be located and that's the selected picture let's call that selected picture number and if the selected picture is four then i need to move to one remember we only have four pictures so if we're currently on the last one i need to go back to one i want this looping right i don't want to stop so i wanted to continue on so if it's the last one we need to go to the first one okay so that's all we need to do is just let the user know else right if it's four we need to go back to one else the selected picture number is going to be the selected picture number plus one for example if we're on four i just need to move it to two right so we have that so once we have the right selected picture that's going to be what the next one is so if we're on one it's going to go to 2. if we're on 4 it's going to go to 1. then what i want to do is i want to set the picture row what row is that now this picture is on row 7. this is on 8 9 and 10 right so if i'm on picture number one what do i need to display i need to display be what's in b7 so it's b plus the picture number plus six so i've put that inside a row here the picture row is going to be the selected picture number plus 6 that's the row because once i know that row i can extract that picture file name and we can build the full file path so the picture name is equal to b and whatever's in the picture up that's the picture name we can then build that file name it's going to be the picture folder that we set in the variable that includes the backslash along with the picture name that sets the full file path next up we do need to make sure that it is an accurate one we can run that through the directory vb directory or if the picture name is empty for any reason then we let the user know if it's empty or there's something wrong with it what i'd like to do is i'd like to show that default picture if we're on four and i make a change let's hear that picture doesn't exist this picture name doesn't exist so if i'm on four and i switch to one i don't i want to show something like the default picture this picture i want to show so if there's an issue with that i want to show the default picture so how do we do that well we know we've got that default picture that's located directly here inside c6 so we just have to load that up so if there's an issue here equals empty or the picture name is empty then the picture file name is going to be the default picture located in c6 then i'm just going to do a double check to make sure that even the default picture is accurate by writing it through that if it's empty or the picture name is empty then the picture does not exist within the picture then just let the user know we don't have a default picture we don't have any other picture so we don't have anything to show else otherwise if it is correct then what we're going to be doing else we're going to remember we're assigning this the default picture or we're assigning it the regular picture so either one it's in the same variable here now what we're going to do is we're going to show that with shapes picture frame that's that frame we're going to fill it the user picture putting in that picture and then we're going to set b5 to the selected picture we're going to set b5 to that updated remember so if it's if it's currently on one we want to advance it we're gonna move it to two so we're gonna click here and move it to two now it's set to two and we're showing the second picture okay great that's all we need to do for the next picture previous is almost exactly the same right we're going to check to make sure we have a correct folder we're going to select the picture number the only difference is in this one if the current one is one right if we're going previous and we're on this then what do we need to do we need to set it to four we need to set it to four because this time we're going previous so we're going back up it's gonna go from four to three two two to one and if it's on one it's gonna go to four so we're gonna do just that inside the code if the selected picture number equals one then we're gonna set it back to four otherwise it's the selected picture number minus whatever it currently is minus one reset on the first picture or subtract one the rest is very much the same exactly what we've done we're checking the picture row we're setting that up to get that picture name where we're going to make sure it's if it's empty or b to empty then what i want to do oh here's here's a brand new line for this one it's kind of important here and then what i want to just on this one this is important if we're moving back up right if we're moving back up i want to make sure that we're going to go back up for example if we cleared out if i clear out a picture let's say we clear this picture out here right and i'll go over that macro now look we can't go back right i want to know the next available row with a value right i don't want to create an error so how do we do that well what i'm going to do in this case is i want to make sure that we know the last picture row if b the picture equals empty right if there's a blank spot here then i want to find the next available row with the value so the picture row equals b10 and xlap set to the first picture name i want to know the first one available all right so the picture name is going to be equal to b right the pro picture file same as the folder and the picture name we're going to check to make sure it exists if not we're going to set it to that default picture if that default picture is accurate we're going to let the user know otherwise we're going to set that user picture to the picture file name again building out that frame filling it with a user picture remember it is no different no different than just simply right clicking on here again formatting that picture going into the fill and then just choosing a file it is the same exact thing right there's no there's no difference when we have that so if we were to do that a resting picture it's exactly the same as just doing this and inserting that picture so no different than that except we're doing it through vba okay we can do the same thing through add picture that's the one i'm going to go over with you next so that's all we need to do for our previous picture and our next fixture but what if we want to add a picture right what if i have a brand new one or i want to replace the picture maybe i don't like this maybe picture number two doesn't exist and i want to add a picture so if we click on it we can just assign a picture like this and it'll sign although you wouldn't want a wood grain but you get the point and then what i want to do is i want to assign it to the first available cell so how are we going to do that well that's with add a picture so that's back in the recipes and if we take a look down here let's go under here i think it's the top of it here here we go save and after save add new recipe add picture right here recipe add a picture okay so with recipes again we're focused on we're going to make sure we need a folder for that right if i if i don't have a folder where we're going to put those pictures in we need to let the user know that please select a folder for all recipes okay we need to know what folder to put that in then what i want to do is i want to look for the first available space the first available where is that first available space right if they're all blank right if it's an add new right they're all blank on a brand new one right and we want to add a picture we know to add it on the first available one right we can put it anywhere here it's fine right so but i want to know that first available row so if i decide i want to add another one i've got it added this is going to be the second picture right in that case i want to put it in row 2. so i got to look for the first available row between b7 through b10 so we do that with this line of code we're going to set the found space we're looking for empty i'm looking for nothing and where are we looking we're looking between b6 and b10 and we're looking for values so if it's not found if found is nothing means it's not found means they're all full right we need to let the user know if they're all full and we try to add a picture for example in this one they're all full we click add a picture a maximum of four pictures can be added please clear a recipe before why would that happen that means there's no blank spots between b7 and b10 how would we know that because if found we're looking for a space in this range i know it's b6 but this will work probably b7 to b10 b6 will be skipped and it'll be b7 to b10 a maximum of four pictures can be added please clear a recipe before adding a new picture okay all right so picture row if the picture row is the found assuming it is found we're going to set that where has it been found in the found picture row right if it's here right if i delete this it's going to be found on row 9. so we're going to put it in adding that picture right we want it to fill in whatever the first row is found now 9 is done right so we're going to say the picture row is going to be the first row in which it's found found space dot row the first available empty row okay that's where we're going to place it if for some reason the picture rose six just in case remember i said between six and ten right but it's actually for some reason at six we're going to make it one this just corrects any issues that could happen if the picture rose six by any chance then make it seven ensures the correct row so we're gonna set the recipe picture folder equal to admin c4 that's the poll we're adding the backslash that's the folder where it's going to be located right i need to know that folder user is going to browse for it we don't know where they're on their computer they're going to look for but once they find it once they pull that picture what we want to do is we want to make a copy of that picture put it in the correct folder so that it is available for the next time so we can do that with application file dialog and to do that what we're going to do is we're going to set the recipe pictures application file dialog now this variable rest picture is already set as a file dialog here the recipe picture as a file dialog the same thing with a step picture and the same thing with the file dialog so we've got all of those down there so we'll be using them in the future okay so uh continuing on here with the ad picture so we're gonna set that picture now we're gonna give it a title right that pop-up that's the title that comes up and when you click here you see this pop-up oh we got a clear space out before we add that in let's just let or we can clear it all we need to do is clear that picture and then add a picture here now the title here is called please select a recipe picture that's the title that's up here and we want to make sure notice down here in the lower right it's got pictures jpeg png gif we want to specify what kind of pictures they can add so we do that with a filter and that filter is added in right here picture files jpg png gf1 okay we want to allow multi-select no we only want them to select one picture if they cancel out of it right it's not dot show is going to be negative one so if it's if they cancel out of it does not equal negative one when they select something it's going to be negative one if they don't select anything it's not going to be negative one then you need to go to no selection means they haven't selected anything we need to skip everything else but assuming that they have made a selection we can continue on what i want to do is to mention before is i want to copy the file from wherever the location is on their computer into the correct folder which is going to be the recipe so we do that with file copy we're going to file that selected items this is the full file path of the picture that they've selected and what i want to do i want to place it i want to copy it make a copy of it i want to place it in this folder along with the backslash and the directory of the select items meaning i want to extract only i don't want the full file path i don't want the this is the full file path i only want the file name to extract that file name we can wrap it in dir that's going to be just the file name so when i combine the file name and the path i get the full file path so that this is the original we're using file copy this is the destination this is where we copy it to this copies the picture into the correct folder so that we can find it next time we want to display it then all we need to do is just place that picture name inside b in the picture and i want to place that updated name directly in whatever here so if we add a picture here and i decided to add a picture i want to place it directly inside that row that picture name must get placed directly in b and whatever that row is so that's what we do it here placing it directly inside so once it's in there all we need to do again is simply add the picture to the frame using the picture frame fill user picture just we said before the folder right that we haven't put it on and the be in the picture basically the file name and the folder combined right and we're going to set that picture up and then also we need to do is just update b5 with whatever the selected picture number is that select picture number must go with b5 i want to know what picture we're on and then of course we're going to be simply the picture row minus 6 right so this the selected picture number that's sufficient enough right we could also add it to that so we know the picture whether the picture row is nine or whatever so we're simply going to subtract it now it's how we get the picture number okay great so that's what about clearing a picture right i want to clear a picture how do we do that we won't need any of this actually so clearing a picture is relatively simple all we need to do is locate that clear the picture and clear the name so we can do that that clear picture that is the macro that's been assigned to this so basically what i want to do is put the default picture in here if it exists okay instead of clearing and of course we need to clear out the associated row if we know the picture number it's 3 and i know i want to clear out the row 9 all i need to do is add 6 to that so that's what we're going to do inside the it's the clear picture so with recipes if b5 does not equal empty and range b and 6. valid what is this clear the contents this is the picture remember b5 to value let's say this is three let's say this this is six so this would be b9 right if we're trying to clear out picture number three plus six b9 we're going to clear out whatever the picture name is located directly in here right b9 three plus 6 equals 9 right so that's how we clear that out so assuming b5 then we're going to clear it out then what i want to do is i want to set the picture file name being that default picture this is our default recipe picture and we're going to put that directly inside a string here then i want to make sure that it is accurate so we're going to use if directory file name does not equal empty if that means it is accurate we do have a correct file path then what i want to do is fill that picture frame with that default picture right here then what i want to do is set the found and then we we don't need this this is already done i've already cleared out the row so we don't need to do anything else we're done that's not necessary all we need to do is clear the out and clear the picture when we save that remember when i go ahead and save this recipe only when we save it then do we actually put the space inside the picture right so if i were to save it it's going to then put this blank spot directly inside here if we don't save it then no changes right so i can if i don't save it then all the pictures are going to come back because i didn't save it okay great so that's how we clear the picture very very good all right let's continue on so we've got recipe load we've got ingredients and steps load we know how to add a picture we know how to clear the picture how do we save a recipe how do we add new and how do we delete so we're going to cover those then email and print and i think we're done we'll do a double check okay so let's do on the save recipe when i save this recipe what do i want to do well basically what i want to do is i want to determine is it a new recipe or not right how do i know if it's a new recipe if b4 is not blank that it is an existing recipe if i click add new we know that excuse me b3 it's not not not b4 b3 is our row right b3 is a row associated with the recipe so if this is blank we know it's new if it's an existing b3 is always going to have a value so b3 is our differentiator so that is the macro that's been assigned to this save recipe button so when we go inside here and we go up here to i believe it's here recipe save okay that's the one we're going to focus on now okay with the recipes again i want to make sure that we have some required fields we need to make sure that we're saving it we need to have a recipe name and i need to have a category i'm going to make sure that both k3 and 03 are required so we're going to do that to make sure that we have at least those before saving if k3 is equal to empty or o3 is equal to empty then we need to let the user know to please make sure recipes contain both a name and a category we're going to exit the sub those are required okay now what we need to do is we need to determine again is it a new recipe or is it existing b3 is going to let us know if b3 is empty it's a new recipe else it's an existing recipe so we need to do three things if it's a new first what i want to do is determine what row we're going to place that recipe on i need to know the first available row so we can use end excel up that first available row is going to be 24. i also for the second thing i need to do is i need to determine what is the next recipe id we're going to use it here it's going to be located in b4 we're using the max formula to determine that the recipe id which is all of the name ranges make sure for recipe make sure that your spiders are all numbers to use the max then we're going to add one to it that's going to get us our next available so what i need to do is you need to take this and i need to place it directly inside b2 i also need to take this and apply it or it's called a right here so and also no that's it that's all i need to do for the new just those three things so we're going to do that here first determine the recurring row it's going to the rest b row it's going to be based on the recipe database a and x left the first available row plus one that is our first available row right and then also again like i mentioned in b2 we're going to take whatever's in b4 and place it b4 is our next recipe id okay we're going to place it directly inside b2 and then lastly we're going to place it directly inside a and the recipe row recipe database a and the recipe row equals b4 it's the next recipe id so that's it those are the three things we need to do if it's new if it's existing all we need to do is extract the recipe from the b3 inside a variable recipe row so the rest row is equal in b3 or it's a new one everything else everything else which is these three rows is exactly the same whether it is for a new or an existing recipe so again we're going to run that loop just as we did when we loaded that recipe except this time it's going to be in reverse this time the recipe database is going to take and get that information and it's going to be based on whatever is located in the recipe row and the recipe column so we're looping through basically taking all of the data whatever is located inside here here here and here and here and placing it directly in whatever row based on this in k3 we're going to place it here whatever's in o3 we're going to place it here and so on and so forth running a loop from 2 to 12. we don't need to start in one because we've already placed one we've already placed that id there so we only need to run it from there okay so that's it that's all we need to save the data next up i want to save the ingredients right now keep in mind that we need to save all these ingredients now we need to know are these ingredients saved or not right so the best way to do it is look all the way over here and bring that over we don't need it that far over do we now so bring it over so we have our ingredients so what i need to know is does the ingredients already have a row or not for example if i decide i want to add an ingredient here so i want to add some ingredient here a quarter drop of let's say beef juice okay so if i want to add a quarter drop of beef juice i know that this has not been saved to the database yet right so if i know that there's no row associated with this note look at row 29 there's no row i would need to save this as a new row inside here our ingredient database here that would need to be saved right here so when i save it right i need to check for a new database row i would put it down here in row 230 if it has not been saved yet so we're going to do that so basically what i need to do is determine the last row based on the ingredient name then we're going to loop through all the rows and then if there's a database row associated with it just make sure we save any changes that have been made if there's no database row then take what determine the first available row put that row here and also inside that row i wanted to do this i want to put the for that new one just that new one i want to put in the recipe id i want to put the row that is associated and i want to put a formula the row here that's going to come in handy a little bit later on and we load that okay as you saw when we loaded it up we needed that row we needed to extract that row to place it so all right good so we have that so that's only for the new rows so let's get into the macro and take a look at that saving those ingredients again just as i mentioned we're going to determine the last ingredient row based on column i that's going to be located here okay so last ingredient based on columnar the last ingredient row if it's less than 12 that means there's no ingredients we're going to skip all of this and go down to here right there's no ingredients so assuming that there is ingredients what we're going to do is we're going to run that loop ingredient row from 12 to the last ingredient row again we're going to check an eye is i if i is empty we want to skip right if there's if this has been cleared out or deleted i want to skip there's nothing i want to save here if it's empty right if this is empty nothing to save okay assuming it's not empty we do have a gradient what i do is i want to check why remember why holds our database why because it does that's why okay so it holds our database row so if it's empty then we have to know to add one so that's what we need to check here if y in the green order equals empty it's a new ingredient then we need to do all these things for the new for existing it's just one thing so for the new again i want to determine the first available ingredient row based on the ingredient database a and getting our first available row adding in the recipe id very important in column a in the ingredient then we're going to add inside we're going to inside column e and column f we're going to add in the ingredient recipe row and a formula i need to know what row this is saved on 29 so if i save this 29 that 29 is going to go right in column e and then whatever column the formula is going to column f so that's just what i do inside the formula so e and f going to take on that and then also i need to update y y also needs to take on the ingredient row right we need to make that update right so in this case that new row would be put in here so for example if i save that recipe and recipe save you see that brand new rose here in row 230 beef juice got saved so we look at the bottom of this we see the last item is that beef juice the recipe here the recipe id here we have our our quantity here our unit of measure our ingredient plus we know it's been on ingredient row 29 and 230 i should we're doing these things to really freeze these columns so that makes it a little bit easier for us when we go ahead and freeze these panes and freeze the top row so now when we scroll we can always see that you know we can always see the ingredients so now we've just added all this information so when we load it in again it's going to come up so that one hasn't been added okay so that's just what we do here and now in why we did add that row that database row knowing it's been saved so that's what we do here and why adding that if it's an existing it's very easy all we need to do is extract the existing database row everything else is the same regardless whether it is a new ingredient or an existing gradient we're going to update that database and to do that basically inside our ingredient database from b all the way through d is simply going to be equal to whatever is located in g through i so we do that with this line of code b through d of our ingredient database equals g through i of our recipe row there that's it so that's all we have to do and then we're just going to loop through that the recipe steps saved went now keep this in mind the recipe steps are saved when created or updated okay we want to make sure the recipe steps so now those are for recipe steps i'm going to add it in so how do we save the recipe steps i'm putting that in here so recipe add new so how we saved right we see we know how we save the recipe but and the ingredients but how do we save the steps so let's go ahead and go in that that's separate okay so recipe steps are automatic in other words as we add a recipe step which we're going to get into they automatically get saved so we don't need to save them because as soon as we add it here they're going to get saved to the database for example so let's say we've got let's say there's six steps so let's call it a step let's see then we're gonna go to that step seven right so we add a step seven maybe we want a picture associated with this step seven we can just put in here any picture okay and now as soon as we save this step which is the macro we're gonna that's gonna get here so that gets saved automatically so let's go over that macro now how do we actually save that step okay so let's go into the macro okay and then or you can just see here right click if we right click we're not sure what it's a little bit faster assign the macro and just click edit it's going to set us right to the recipe macro it's called save step that's what i want to go in with you now so how to save that step with the recipes of course we want to make sure that we have b3 what's in b3 i want to make sure that if for some reason they try to save a step without first saving the recipe let them know right if we try to add new right and i'm going to let's say let's say test here and i save the step i don't want them to be able to add steps without actually first saving the recipe so i want them to say please save the recipe first so that's kind of important we need to make sure that they do that so b3 of course that's located our row if that's empty we're going to know that this recipe has not yet been saved so to do that just let the user know okay assuming that the recipe has been saved that's important i also want to make sure that they're not saving anything as soon you know if they try to save a step and there's nothing in k11 let them know they haven't actually added anything so if k11 he goes empty this please make sure to add some step directions before saving the step okay if b12 is empty it's a new step right now let's take a look inside b12 when we select something i want all the step information to go inside this remember b12 right it's going to take that step row if i click here this is 15 this is 19 you know it's based on the step remember we're adding this but if we click if i save that step it's going to be cleared out so i know that there's nothing here right notice b12 11 are completely empty so that means it's a brand new step but if they're making a change and smooth and thick right i need to know the difference before may if we're saving it i need to know whether it is an existing or not right this right isn't existing so i need to make sure that we update thick right so if i make a mistake like i always do save the step i want to easily be able to make that change very quick and easy so i have to differentiate is it a brand new one like it is here or is it existing like it is in here b12 b11 is going to tell us that so here in b12 if b12 is empty it is a new step so when we set new step then what we're going to do is we're going to do the step database row it's going to be the first available row on our steps database and our steps database is located right here are going to be a brand new step again i'm going to freeze this row here so we can just see it a little bit easier view and then freeze okay so we're going to freeze that so basically what i want to do is the first available row we've got all these steps i want to know the first available row notice that step 7 i just saved there what do i want i want the id of the recipe i want to know the step number i want to know the instructions i want to know if there's a picture attached to it and i want to know the row associated with it so i want to know all that information so as we move into the code the step database row is going to be the first available one the step row is going to be a 99 plus 4. what is that right i want to know what that row is where are we going to place it right let's see if i've got all of these steps i want to know the last row in this case 39 we know they go every row plus 4. so in this case it's going to be 43 is our next one right so we've got 39 so next one our next step is going to go on row 43. i know it's gonna go remember we're skipping four each time right we're just skipping three so we're gonna have each one three in between us so it's every fourth row right so 43 here and then of course 47 okay so that's how we're going to do it so i want to make sure that we're adding 4 to that right here okay adding 4. finding the last row of the value in column a and then adding 4. it's going to be our next available recipe step row because we want our recipe we want it to go it very very easily that's how we can keep track of which one we're going on very easily because we're skipping they're they're basically equal step six so everything's on the same line it's very easy once we have that okay so we've got our step row our first available step row and our step database row we've got that now what i want to do is i want to add that information to the database i want to add the recipe id i want to add the next step number it is in b13 how do i know what next step number is well let's take a look into b13 right if i'm counting everything right in b let's say column b how many steps how many of there are values how if i use count a i want to know how many cells contain values right if i got one two three four five six seven if i've got seven here with values i know if i add seven plus one my next step is going to be eight so that's what i've done here using count a formula using b all the way to 75 or whatever you could probably put this nine much higher value in case you got a lot of steps complicated cooking plus one right that's going to be our next step so our next steps eight so we know b13 contains our next step number so we can put that inside i'm going to take that and put that directly in b in the step row i want to take it right here i want to put that next step number i'm going to put it right here so if we add that step 8 1 is going to be the recipe id 8 is going to go here then i'm going to put the instructions then i'm going to put the picture okay so that's it so what else do we need to do i also need to place for brand new ones remember for new steps i need to place that row and i need to place it directly inside e right so that row so i can keep track of it's going to go in with a formula that way if we delete something it's still going to be here great so we understand that so that's going to go so now also what i want to do is i want to place the picture name in a and i want to place the database row inside b just as we did for new ones so here inside the recipe i want to put that picture name in a and i'll put a database row and it's going to be based on here so if we add one of course that's going to be placed directly in 43. so if i click save step here and i put let's say we're going to call this step 8 right step 8 okay so this is going to be step 8 and maybe i want to put a picture in here i want to add that information here and i save that step we know exactly where it's going to be placed right it's going to be placed directly in 43 a is going to take on the name b is going to take on that database row if we look in our step database we see that we've got our information here the picture everything is in here so let's continue on with the code so that's all if it's new if it's an existing we're going to extract the step row from b12 right that step row is going to be located in b12 and the step database is b in the step row what do i mean by that the step row would be 43 and the database rows can be located in b and whatever the step row in this case 107. 107 is equal to this the one the database row here okay so we've got that all information i need to know that database row because when we make changes when we update that we need to make sure that we're adding and updating anything like that so here we go so the database row c c is going to be our instructions text right that c here right here column c here is instructions whatever is inside our field cell here k11 is going to take that on so we do just that here equals k11 and then that picture is going to go in column d whatever is in l14 that picture file name is going to go right here okay so we browse for that file name and we're just going to go right here inside out so we're going to save that picture file name okay great great so what about if we delete a step that's relatively easy all we need to do is just simply determine if the step was saved or not right so if i add a step test here right and i remove that step all i need to do is just clear the rows and it's going to be very very simple let's clear that up and i'll make sure to clear the contents k through o we also need to clear that up if step is saved else right oh we've got that in here let's put that in here i want to do this dot range k here making sure we clear all the information out that's important to k 11 through 0 13 k 11 through o 13 right dot clear contents and also what else do we need to do we need to clear that picture up whatever picture's in here that's going to be l 14 through n 14. so l 14 through and 14 and we're going to clear the contents of that right so once i clear the contents then it can be cleared so what else do we need to do clear contents okay so continuing on we've got that there so but i first of all want to make sure i want to make sure you want to remove this step give the user a point has it been removed yes or no okay then exit the sub or if they don't want to remove it there's nothing we can do so if let me just let me just put this i'm going to clear the contents regardless of it this is if it's not been saved that's better there i'll keep it there okay so are you sure you want to remove the step okay then i want to grab the step bro that's going to come from b12 we know b12 is going to take on that step row right here right if it's been saved before if it's not nothing we can do okay so the step database row that's going to come from b in the step row and also what we want to do is just delete the entire row from our step database right we know when we know the step database row 3 or 4 whatever it's located let's say i want to delete this step right if i load step 8 right here and it's loaded up here now i want to delete that row remove the step are you sure you want to delete yes so we want to do we want to clear it out it's no longer there and if we look at our step database it is no longer there i've deleted the entire row so that's all we do here just delete the entire and i've cleared the contents now the cells and then what i do is i run the macro to load the steps so that it refreshes those steps that macro as we've been over loads all those steps so it reloads it and deletes everything all right very good i'm glad we got that part to show you let's go over some adding new which is a very quick macro and of course deleting it we want to go over those so deleting the recipe and adding new so we've got step delete ingredient delete we're going to go over in just a moment but i want to go over the recipe delete so the add new which is going to be recipe let's see recipe add new okay this one here with recipes first of all what i want to do is i want to clear the star ratings right if there's any any shapes right when we click add we've got a lot of shapes i have i've got step numbers i've got step text i've got pictures i've got rating pictures and i want to show you the rating pictures here i want to delete any rating pictures so when we click add new all of that must be cleared out and we also want to set the default picture so for each step shape on air resume next for each shape if it contains star rating greater than 0 i'm not deleting it remember i don't want to delete those ratings i just want to hide them hide them everything else can be deleted and that includes the recipe step number the recipe step text and the recipe step picture so all those steps can be deleted so this text everything there can be deleted so we can clear that out on add new okay so what else then of course we need to clear all of the associated fields right we want to clear all these fields i want to clear all the ingredients out i want to clear the database rows here i want to clear all the picture you know all the step information so everything needs to be cleared out using this line of code then what i want to do is i want to set the default picture when i click add new as you saw just a moment ago that default picture appeared so that's located in c6 and if it's a correct path that what's in c6 then the shape fill set that default picture to the picture file name so that way right just as we've been over before when i click add new that default picture is going to show up and it's ready for an ad picture so all we need to do is just add a picture and it's done like that and if we course we go previous next nothing's going to show up so that's what i want there's no previous picture okay very good so we got that shut them you see that first picture showed up here we're ready to go all right so that's it for add new so recipe adding new but what about if we want to delete it let's take a look at that that is of course the macro that's been assigned to this here and if we edit that we can go directly to delete first of all i want to make sure the user wants to delete a recipe yes no delete then i want to make sure that b3 is not empty right b3 means it's previously been saved before it contains that database row i'm going to pick that database recipe row located in b3 then also all we're going to do is b3 now this particular macro really isn't complete i kind of ran out of time now it works just fine but really when you're doing this if you're doing this for a professional you want to do the following right you want to run a basically you first notice that we have all of these ingredients right what you want to do is you want to run a macro advanced filter that's going to based on this and return all of the ingredients for that row and you want to delete each one individually you want to sort them which kind of this is a large you want to sort them by row starting with the last one and deleting it so you go delete row 230 delete 19 1870 that is the correct way to do it also in steps you're going to do the same thing you're going to run an advanced filter based on that id right whatever's in there you're going to get your results here you're going to start deleting it you're going to sort them reverse right from lowest to highest or you can go in reverse order then start with the last row the highest number row delete 106. then delete eight seven sixty you wanna go in that order keep in mind if you delete row three that this will get the leader two so we don't want that so try not to delete row three or you can just remove this criteria here and then just bring it up a row so keep that in mind it could be an issue if you delete row three so if i were preparing this i would probably move this up one row so it never gets deleted keep those things in mind that's so that's really the proper way to delete we're doing an easy kind of a quick easy to delete so that's an easy way and then all we're going to do is we're going to run the macro that adds new so when i delete it it's going to do that it's just going to clear it up and add new okay and adding you should probably delete it but it's better because this is a required field so we can do that okay great so we've been over adding it we've been over saved recipes we've been over delete we've been over clear pictures add pictures we need to go over email and print and one more macro before that and that is the ingredients we have the ability to remove ingredients which i really really like and so but it can be a little tricky and i want to go so notice if we've got some ingredients here and then let's just say we've got let's do beef juice again okay so we have these and we've decided we're going to save it right so we've saved our recipe so we have these two ingredients but what if i want to delete an ingredient well the best way to do that is to just simply determine what row it's on so the first thing is i want to know when i select a row i want this little icon to appear here that icon is called delete ingredient button or delete ing button and i want it to show up directly and that's going to be based on selection change i also want to highlight the row and to do that we're going to use conditional formatting so if we go into conditional formatting i got a few rules and i also want notice one open row so as soon as we add something it opens a row so let's go over the conditional formatting here yes this is a large application i worked hard on it for you so basically we have three rules that we just three rules these are for the alternating rows it gives them the color and there's two conditions right and i 11 notice our notice it starts on 12. our first one starts on 12 but i put 11 and if you do that then the one after it's going to always be color 2. in other words it's going to leave us a blank row which is what we want that's also colored and also one for even rows so even rows are going to get this light brown color so notice it's equal to zero the mod of row is equal to zero whereas in this one the same rule based on eye 11 does not equal blank but this one's one it's going to get that little bit darker brown color that's how we get the alternating rows and right and so very very importantly i used 11 remember this starts on 12 but i used 11 because i want that blank row right as soon as we add a row here as soon as we keep adding it's going to show that that's exactly what i want right so that's really really important that we have that so the user knows that they can enter the ingredient there so what i want to do is we've got the last conditional formatting is going to be based on whatever row selected in b29 notice it goes away as soon as i select something b1 equals whatever row we selected i can put that in b1 all right so b1 is going to take that so if we go into the conditional formatting back inside here we see that b1 here the formula is b1 is equal to the row and we give it a format all right in that format while my voice is barely holding up bold we want it bold we want to give it a color of white and we want to give it a fill i'm going to give it a fill effect i'm going to use like a little bit lighter color just some brown colors here to give it a nice darker so i want that to differentiate between that to make sure that we understand that that's been uh visible that is the selected row and the last thing what i want to do is run an event a selection change event that places this little trash can right here on the select row so we can delete any row if we want to there's a macro tied to that if we select anything else those are going to be gone so how do we do that well let's take a look inside the development visual basic back into the selection this time recipe we're going to focus on selection change all right the first thing we're going to do if target count large is greater than one then exit the sub so we don't want if the user selects more than one cell we're just going to exit out assuming they've only selected one cell the front i want to hide some shapes that they should always be hidden for example this rating toggle i almost forgot to show that to you this toggle i need to show you that almost so many features in there i can't remember so this basically when i select a cell here this cell i want this toggle to show up how do we do that i'll show you that in a moment as soon as we get done with this one here this macro okay so basically what i want to do is this toggle if we select this little pop up here this is called rating group rating group if i select any cell i want it hidden right i only want to select i only want it to show up when i click here otherwise i want it so i want to hide that i want to hide this trash can automatically and i want to hide a few other things so i also want to so here if the rating toggle here also this one this toggle this is a shape yep called rating toggle i want that hidden too i know it looks it looks like this right kind of cool right almost it's almost perfect i need to make it a little smaller but you get the point so all right so we want to hide that here so how do we do that so just hiding those kind of shapes are only available we only want them to display when we create specific actions otherwise so the best thing to do is just hide them right away but i only want to hide them if they're visible so if the rating toggle is visible equals true then hide it if the rating group is visible then hide it if the delete ingredient button that's a little trash can is visible then hide it so we're just going to hide all those things right away if the user makes a selection to 0404 while we're here o4 then i want to display this little button remember this is just a shape it's a triangle and a square that's all it is it is not this which is the drop down list it's just made to look like it so that people select it so if it is i want that visible right if they select on o4 i want to make sure that visible so i can do that with this if the selections are for nothing then shape rating toggle visible equals true there's a macro assigned to it that's simply going to toggle this okay so if they select that there go continuing on if the user makes a selection change between g12 and i9 here g12 and i9 and i want to make sure that i contains a value right if they're down here we don't want anything to happen if i doesn't contain a value so assuming that it does then what we want to do is i target row does not contain is not empty it does contain a value then do a few things one b1 is going to take on that target row that's going to trigger that conditional formatting i'm going to take that row then what i want with that icon i want to place it directly on the left of column j and the target row and i want to place it directly on the top but a little bit higher than that on the top right and i want to make sure it's visible so basically this code right here is going to make sure that that is visible now we've assigned a macro to this we got to delete it that macro if we take a look assign the macro scroll up here we see it's called ingredient delete if i edit that macro it's going to go on let me just double check before i go back to ingredient group i want to make sure that we covered everything on the selection change okay else b1 clear contents what does that mean so that means if they select here great but if i select anything else i'm going to clear b1 right when i do that i want to make sure that that row is not selected okay so that's it so now we can continue with that specific ingredient delete this is the macro that we're going to run so basically if i select this and i click this i want that recipe saved and i want that deleted notice that it got deleted so how do we do that let me add one more just so we were where we were and then what i wanted to let's do test so it's different one gram of test those are my favorite save that recipe and what we're going to do so now what i want to do is i'll delete but it's a little bit tricky right we know that the database if this is database 230 if i delete this item right let's go look in the database and the ingredients if i delete let's go all the way down here if i delete this 230 then this becomes 2 230 right if i delete this one this row moves up right if i delete that this run becomes and this becomes 230 because we're using a formula i need to make sure that we also change this one here to 230. notice we're deleting 230 here i need to make sure that the one below moves down so we do that that's just the check we need to make so that's what we're going to do if b 1 equals empty the next of the sub right if we don't have a selected row if i don't know what row has been selected we can exit out of the sub if i'm going to delete this one here i need to know that b29 is the one we're going to be leaving we need to put that into a variable so the ingredient row is going to be whatever is located in b1 the last ingredient row is going to be the last i need to know the last row we're going to loop through all the ones if i'm going to delete this we're going to change a lot right so i need to know the last row in this case the last row is 30. so we're going to do that the last row is going to be i if i need to know if it's been saved or not why if it does not equal empty remember y is our database row so that is here right if it's if it's not equal empty then i need to delete it from the database if it is empty it hasn't been saved yet and i just need to refresh the list it's much easier if it hasn't been saved but if it has been saved we need to do all this okay so the ingredient database row is located in y right if the ingredient now i need to know if it's the last row for example if i just want to delete 231 it's much easier if i just delete the last one i don't need to make any changes there's nothing below it very easy it's a lot not only is the last one in the database but more importantly it's the last row of our recipe it's the last recipe row so we need to know if if it does not equal the last row if it does not equal the last row it means the one they want to delete this one or this one doesn't it's not the last row so then we need to do define four now we need to set a loop for the recipe ingredient row equals the ingredient row plus one to the last ingredient row what does that mean it means let's say they want to delete this one right i need to do something for all of the remaining rows i need to do something what do i need to check i need to check if this row is greater than the row then i need to change this to 17 i need this to 19. i need to change this to 229 and then change this to 230. so that's what i need to do inside the code so if y is greater than the recipe row then what we're going to do is we're going to reduce it by one the recipe database row e and the y row this is kind of confusing here's the database row right here here's the database row right there that's it so all i'm going to do is reduce if i want so all we're doing is going in to the recipe looking at this we're going to make this 229 we're looking at this we're going to make it 230 run or we're going to you know we're going to just update that but also if since it's a formula we don't necessarily need to do it but what we do need to do is e this one here we need to reduce here sorry this one because if i'm deleting this row it's going to be this one's going to be 29 what do i mean by that let's say i delete sea salt right i don't want it right 20 this becomes on row 27 right this everything's going to move up so this becomes 27 this becomes a 29 and this becomes 30 right so we need to also change that into the database so again this becomes 29 this becomes 28 this becomes 17 okay so we're just basically reducing whatever's on income that's all we need to do inside here then what we want to do is why this is where we update the database y is equal to y minus one reduce the database row in this case we want to reduce it right 230 becomes 230 231 becomes 230 2 30 becomes 229 so we're reducing that because we're deleting one before it so that's all we need to do okay so we're just going to loop through all of the remaining receipt rows then what we're going to do is we're going to check if the database row is greater than whatever is located then delete delete the database row if it's greater than three i just don't want deleting the third row again just in case right deleting that third row could create issues because we're going to delete this formula here which i don't want so we just made sure it's not 4. okay so we have that there and again like i said i'd probably move this up but it's easier when we're teaching to show you okay so continuing on so that's all we pretty much have to do next up what we now do is i just need to clear the ingredient details from g through i basically clearing whatever's here whatever we have here also whatever's in y that database row clearing that and also we need to hide that delete button that icon here deleting that here the one that shows up here we need to delete that and next up we also want to then save it and then load our ingredients right saving those changes then loading the ingredients right that's going to automatically run it so if i decide i want to delete beef juice but keep testing it's going to delete that the recipe is going to get saved it's going to get reset notice this became before it was 30 now it's 29. so if we look in our database right here inside our gradient here remember test used to be 30 and now it's 29 so we just simply reduced it before we started so we reduced that one row that's the important thing that you have to understand okay very very good now i do want to get to the email right and the print so i believe that's just going to be i'll do a double check to make sure we covered everything and we're going to move these shapes back to where they belong here hidden to keep our very screen very beautiful there all right we don't need them there all right so what we want to do is of course i want to be able to email this recipe if i click email let's assign which i have to assign this macro so assign the macro and it's going to be recipe email clicking ok i want to email this entire recipe so you're going to put it inside a pdf and automatically email when i select that macro and it's going to look just like this so it creates an email basically it's going to give it a subject of butter chicken if i open that pdf we're going to see that that entire recipe is now inside this pdf all the steps are here and everything is there so how do we do that well we can do that with just a little bit of code so let's go ahead and take a look inside that code so first of all recipe with email the tricky part of this is we need to set the range right so we know it's going to start at g i want all the ingredients so it's going to start out on g3 right and it's going to go all the way to q right we know that but what about the last row it's a little bit more tricky because the last row is a shape right this last row is 42. i want to make sure that we include it how are we going to get that 42 how do we know what it is well we need to determine how many steps we have we know the next step is eight so i know i've got seven steps i know that i've got seven if i know i've got seven steps i also know that every step takes four rows right so if i decide okay every step is four rows i've got seven steps so i'm going to multiply that's 28. so if i take that 28 and i add 14 onto it right i'm going to get automatically that last row which is 42. see how we did that we started at that we know we've got seven this is eight right we're adding one so we've got seven current steps so i wanna get that last row we know every step contains four rows so we can do that with just a little bit of mess that last row is going to equal whatever's in b13 right that's the 8 minus 1 that's going to give us 7. we're going to multiply that times 4 it's going to give us 28. we're adding 14 onto it because we're starting off on row 14 here so we've got to add 14 onto that that's going to get us our last row once we have that we can set the print range we know where it's going to start in g3 all the way to q and the last row that's going to the address of that is our print range this is a string variable once we have that print range into a string variable we can then use the page setup to set that print area it's a dynamic print area based on that so the page setup print area is equal to the print range here's the range right now what i want to do is i want to set a file name at least temporarily it's going to be this work we're going to use the current workbook path because that's the safe we're going to add a backslash onto it then i'm going to add in whatever's in k3 k3 is the recipe name i want to add that file name recipe name i'm going to put k3 whatever's in there and i want to add inside that pdf right so i want to add in here pdf so basically and right recipe.pdf so that's with the file name so it's going to be the recipe name underscore the recipe and pdf getting that file name if for some reason that file name exists if the directory does not equal empty i want to make sure it's empty then i'm going to kill it that means if that file already exists in that folder i want to delete it first okay so if as long as it doesn't this will delete it just in case now i'm going to export it export this page based on the setup the file name right and false right so if we take a look inside the comments here we can see that intellisense is going to show up ignore printers right we want to keep that in print area so that's the information and then of course we're going to be type as fixed file format right that format is going to be a pdf right the file name right the quality we can leave that empty and that's all we need just to make sure that we're not ignoring the print areas then we're going to set the outlook this has been defined dimensioned as an object this has been dimensioned as an object blow we're going to create this creates that outlook application this creates an outlook email and with that alec email we're going to set the two to nothing we're going to add an attachment that attachment is going to be that file name that we just created here now we're going to do a subject without a subject basically that's going to be that recipe name and the word in the space and the word recipe nothing in the body and display we're going to display we don't want to send that email we want to display we don't even have a 2 address that's it that's all we need to do to create the email now print is going to be very very similar again we're going to determine the last row we're going to determine the print range and we're going to determine this right so i actually do have to show you that drop down list and then the range right g3 through q that's getting the same address this time we're setting up the page setup the print area just as we did before except now we're printing it out okay very very cool all right i want to show you this right when we select it as mentioned before we select it what about this toggle if we right click we click oh actually this is a group so we need to right click the individual items we right click inside and click assign mac or we click on see it's called rating toggle now this rating toggle is very easy all i want to do is basically show that rating group show that rating group here show her high this rating group is simply the stars right here this is a group of a bunch of stars so how do i do that so that's just this so if the rating group was visible it's a toggle right so if i click it once it goes on clicking it turning off so it's a toggle i mean it goes it goes on and off just like that so basically if it's currently visible then we're going to hide it else we're going to show it okay what about the macro that's assigned to that well that's called rating select when i select a rating i want that rating to appear now how do we do that well we do that very specifically in the names that were created so if i click on here and i toggle it toggle that one more time and i select here and i'm going to show in these numbers let's take a look inside what we have here so take a look at this i've given them very specific names rating one is that one star down here let's move this over a little bit rating two is the same name for both of those two stars so if a user selects on either one it's called rating two rating three obviously we're not going to go over everyone but basically they're all named based on the rating that they're within rating five notice rating five has five different shapes they're all called the same thing so when a user selects on one of those i know it's called rating five if the user selects on one of these i know it's called rating four if i remove the word rating what's what am i left with i'm left with one two three four or five if i take that and i place that one two three or four or five right directly inside b6 i can set the rating and then all i need to do is set the shape so the rate level as the longest dimension the rate level is equal to replace remember we're removing the word rating i'm replacing with nothing we're using the application collar okay now what i'm going to do is i'm going for each ray shape in recipes if the star rating is zero then we're going to hide first of all i want to hide any ratings right so when i change the rating if i if i change this from 2 to 4 right i need to make sure that we're hiding everything else first i don't know what's currently there really i could probably know but i want to hide all the others just in case right so the best thing to do is just hide everything right that can turn the word star rating everything that contains star rating i'm going to hide so we're doing that through here everything contains star rating we're hiding we're not deleting or hiding then what i want to do is the recipe right shapes rate level remember that's 1 and star rating i want to show i want to show only the one based on the rate level that we set right here if they've selected two next up i want to set b6 to that rate level then i want to show the rating group i want to hide that excuse me i want to hide the rating group we don't need that and the rating toggle i want to hide also so what does that mean that means when they select something here we want to hide this right and i want to hide this and i want to show only the rating they selected like this so that's all we have to do so now four now as soon as they save it that four is going to be saved in the database that's all we have to do relatively simple all right let me make sure we've gone over do a quick review see if we covered everything we covered how to create categories dynamically how to search for recipes based on both recipe name and ingredients how to load those recipes based on one of those filters or based on a selection of those how to load those how to load the ingredients up and how to load those steps up how to selection and load and load those pictures up i don't think we went over the browse but that's okay that's just very simple how we browse for a picture we covered that here with the browse also we know how to clear a picture we also know how to rotate picture we went over how to create a really cool drop down list also the macro that's been assigned to there is also the toggle just in so in other words also the macro let me see that's a group but if you select on any individual start the macro let me select on individual the macro that's been assigned is also the toggle that's kind of important right so maybe what if it's fold with five stars notice you can't select on the right you can't select on the box but you can select here so it's the same action selecting on the stars or selecting on the cell itself if it's one star and we select on a cell or if we add new right and we select on a cell we want those same to show up right or clicking on here we want to show up so that's how we do it simply just by selecting running the same macro here okay so we will show you that i'm showing you how to remove and save steps right how to rotate this really cool picture how to use a create a cool slideshow in excel saving and deleting recipes clearing the pictures printing the recipes emailing the recipes and probably something i forgot all right so i'll be adding to this i'll be creating features but it really depends on you what kind of feature would you want to see on this did i miss something do i need to spend more focus on it let me know your thoughts because i'm putting all of that along with a brand new training and a brand new updated workbook it's going to be all inside our patreon so i hope you will join us there that's an incredible platform and when we reach 500 patrons there's a brand new free course for everybody there it's going to be incredible course all of our patrons get that as soon as we get to 500 we're about 20 22 percent there so in a few months we will be there or less depending upon you so join us there it's just a few dollars a month and it would really help us all right thanks so much for this incredible recipe manager next week will be brand new one and we'll see you then [Music]

Channel: Excel For Freelancers

Views: 337,603

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Recipe Manager, Excel Recipes, Track Recipe's, Organize Recipe, Organize Recipes, Organize Recipe's, Track Recipe Excel, Excel Recipe Tracker, Organizing Recipes, Excel Food Tracker, Track Ingredients, Excel Ingredients, Ingredients in Excel, Excel Food App, Recipe App, Recipe

Id: M1uN88Ik4ho

Length: 153min 34sec (9214 seconds)

Published: Tue Oct 19 2021