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.