Set a three column we can do that we can also set an amazing number of filters and we're going to show you how to filter based on category we're going to show you this amazing selection drop down we're going to show you how to filter based on price and any type based on your data and then create a catalog based on that complete with pictures we're going to even set up the orientation whether it's portrait or landscape so we've got a lot to cover in fact this video is going to be one of the best ones to create catalogs for any type of data you have especially when you have attached pictures and those pictures can be located in any folder i'm going to walk you every step through it if you do like these videos all i ask you to do is just a.
Few things and if you could just like this video and of course subscribe to our channel and don't forget to click that notifications icon bell we create these trainings each and every tuesday for you and all you need to do is click on subscribe and you'll get notified each and every week this application is absolutely free you can download it using the links in the description below and if you do like to support us there's a great way i have the 150 workbook pack that is over 150 workbooks for just 56. that's 37 cents per workbook it's a great deal and it helps sets.
Out it keeps these trainings free gives you an amazing number of templates that you can use in any way shape or form that you'd like all right let's get started on this training i'm going to be using office 365 for this i found it a little bit usually i use 2010 but they'll work in either version so don't worry it's going to work in either version not to worry about it and oftentimes i like to create these applications right in front of you as we speak but this particular application has so many features the only way i'm going to get in all those features and teach you everything is to create it beforehand and then walk you through step by step but not to worry i'm going to walk you step by step of course if you do have any questions on an application like this our amazing excel 4 freelancers facebook group.
It's got now over 32 000 numbers i've got the link down below if you want to join and ask any questions i'm there tons of great developers are there to help you out all right let's get started so what is the idea well the idea is this i've got a list of basically data in this list and i've got lots of different data just products you could use any type of product so we've got a you know code the name product category type brand origin just some general data i've got about 70 items here and i've also got a picture now this is not a link to a picture this is a file name and now.
These particular file names are going to go with these pictures in fact what i'll do for you is i'm going to include a zip file for you that means i'm going to include all these pictures for you and the database that way you can play with it yourself you don't even have to bring in your own data so i'm going to it's going to be a zip file this week it's going to include the application and all these pictures in a separate folder all you need to do is set the folder i'm going to walk you through that so i've got all these pictures now notice these picture names are also the same as the same names located in this file here so the picture names are all set up they're png but jpg will work and i've got about 68 items okay different just types of items you can use.
Just drag it down here now if we create that now we've got dairy so if i generate that catalog it's going to automatically generate that same category whatever category we are on so now notice we've got multiple guys i think we've got some different categories this is all gary we've got category groceries and all the different categories so whatever your template is it's super easy to work with and now maybe you want to filter that maybe you only want groceries so maybe we only want a certain type of category so we can just simply unselect these we're going to show you how to use.
An advanced filter and now automatically when we generate that kind of like it's going to only show those items with groceries notice we only have a few groceries a great way what if we want a 4 column not a three column all i need to do is set this to four generate the catalog and it's going to generate a four column fully customizable that's why there's so many features in this video we've got a lot to cover so i want to get right to it so the first thing what we want to do and this is basically we want to have a customized area using columns c through f that's our customization.
We also may want to change the orientation for landscape or portrait we can do that we also got to set the folder i just use a small cell for this and notice the product folder here this is the same product folder that i've had in my folder so all we need to do is just simply map that folder based on whatever's here and are very similar and i've got multiple photos with the picture but that's all we have to do so all you want to do is take your folder put that folder here wherever it is and you're going to automatically have those pictures that way what they what the we're going to teach in vba is basically this path here combined with this picture here this file name the combination of that is going to be the full file path to the picture and that's going to allow excel to display that picture so that's what we're going to do so we've got that we've got.
The number of columns and we've got the portrait or that's the great way to display our product catalog we've also got a customizable header you can put anything you want in here of course and we've got our little sample here so basically whatever this sample however we change this sample for example if i want to left justify that right and let's bring this down and i want to go into home and i want to left justify this and i want to right justify this one i could do that and then we just need to bring the data here so any changes you make so now now when we generate every change is going to be made based on the change you make so we've got a sample and then that's going to take that sample it's going to show that so that's really great we also you know i already showed you.
How we can add additional fields here we also have fields i showed you how to do additional fields so we're going to walk through every step these are also different list type and these are all generated dynamically based on the data which is really cool so based on your data it's going to generate automate so maybe we only want a certain unit of measure or maybe we only want a certain price maybe we only want to show items that are above four dollars so all we have to do is just change this to four dollars and it's gonna show only items above that amount so now when we generate that only items that are greater than four dollar are going to be displayed notice we have just a few items so it's a great way to not only generate a catalog but filter the data.
Based on almost any type of field and then you can generate your dynamic catalog so it's really
Really amazing and the whole point is not just necessarily to create a really cool product but to show you some really cool techniques that you can use in excel to create your own applications so i like to wrap i like to teach these techniques to you but not just teach the techniques per se but create a product and show how those techniques can be used in excel and this can be used in any version of excel so we're going to get right to it first thing is what we have this customization so when we click the custom button i want these columns to show up and i want certain things to happen so let's get into the vba and show you just how we do that into the developers visual basic we go also alt f11 will get you there and we've got just two different modules we've got.Here's what we've got we've got some code that's on the sheet not too much we've got catalog map grows this is going to build the catalog so we've got just this macro to build it and then i have a print catalog macro then i've got a few customizations so we've got open customization open that customization close and then refresh the list now what does a refresh list do refresh the list when i click reset it's going to basically run all the data over here take this data determine all the data and then refresh our list so basically all of these now are now refreshed.
And now we can go ahead and add it so that's it's a really nice way to do that so everything gets refreshed maybe you want to clear your filters and clear everything out so it clears all the filters refreshes it if you add data to it you might have new categories new types or new brands so we get a refresh list so reset's going to do just that for us okay so let's go into the code and just get start going on it so we can get right to it let's take a look in the catalog macros customize we'll start off in the customize first thing we want to do is uh open the customization i want to clear any groups but not the samples so the idea behind this is if i've got if i've generated a catalog it creates a certain amount of groups so we've got basically a group here we're going to call this item group 2 and this would be item group 1 and then i've got a picture one and a picture so.
Basically when i go into the customize i need to clear all this data out i want to clear it out and allow us to customize it customize our one sample we have one single sample and when i click that i only want really this these sample fields to show up we're gonna call these each one is called sample data one sample one data two sample sample one i just used a sample but basically we have data and labels so for each sample we have for example if i click code it's going to create two it's going to create a label one and it's going to create a data one so the data is connected in this case we're just going to use the first row of data just for the sample.
And but then of course with the sample so then all i have to do is just bring it and i may or may not want to show the name we don't we can maybe want to change the name we can also just change it right this is going to change it so we've if we just wanted to put in code code number right instead of code we could do that too so the label is going to stay consistent the data will change right so so now if i do that and i generate that it's going to show code number for every single one of those so let's click on generator catalog and then we're going to see so now it says code number it's not really lined up but you get the.
Point it's really cool because it's going to follow that format for every single product really really easy so get back into the customize and then of course if you don't want to see it you just got to just click delete and it'll it'll be hidden or of course if you don't like that you can just unclick this and it's going to be removed that'll work just fine too notice we reset those that's why they're not selected but we're going to show you i'm going to build this for you all throughout so let's go back into the customization and walk through this macro a little bit more so you get an idea of exactly what we're going to so the first thing what i want to do is i want to run a shape so we're going to dimension the item shape as a shape we need to work through a lot of the shapes in these so we need to run through some loops and focus on these shapes so the first thing the important thing is when you're creating applications like this.
You want to be consistent with the names right so i want to make sure that my sample is named very different notice that my product groups are all called item group and then two or picture two and yet my sample is very different i want to make sure that my sample naming is very different my samples they all have the word sample in it notice they all have the word sample so that's how i can differentiate between them so that way when i delete certain shapes i use the shape name to distinguish between different shapes so that means any shape name we're going to use in string for this in string means does this the item shape name contain the string picture does it contain it.
If it does it's not going to be empty if it does then what i want to do i want to delete that shape i just want to delete the group i want to delete any group that contains picture but please note that the one picture that we have which is our sample here that's called sample pick notice it doesn't have the full name picture therefore it will not be deleted none of these of course also you want to make sure that none of your other buttons include the full word picture too because you only want to delete those so you make sure when you're naming those you differentiate between the names you want to other names you don't okay so back into the code we're also going to delete any shape that contains the text item group and we do.
That again using the in string command of the item shape name remember we're looping through every single shape in the entire sheet in this sheet with sheet one shape so we're going to loop through every single one this does it as long as we have defined item shape as a shape it's gonna allow us to do this it knows item shape as a shape therefore when we use things like shape name it's gonna notice the name of that shape and shape delete it knows to delete the shape so basically i'm deleting every single shape that contains the word picture i'm deleting every single shape.
That contains the word item group what that's going to do is clear out the catalog and of course we wrapped on our resume next just in case it's not found we don't want it to present an error but
Make sure when you use on-air resume next we want onair to go to zero so that we are back in sync and ready for any errors that might come up okay so another one now what we want to do is i want to take this sample group and i want to move it i want to set the placement to move in this case because i wanted to move with the cells and then just in case i want to ungroup it this is probably not so important we don't need that it was it was kind of helpful if we're going to keep it as a group but i'm going to ungroup it so it's not so important so the first thing what i want to do is i want to make that sample group visible and i want to ungroup it you'll notice.That when we close it i'm going to group it so the idea is this when we go into generate code i want to group that shape i'm going to group it all together so it's now grouped and it's hidden it's grouped and it's hidden you can't see it but when i go back into customized i wanted ungroup so we can work with so just understand that these sample shapes are all grouped together it's going to help us if we group everything together we can create additional groups and then keep everything nice and tight together so it's nice to have this group but when you're customizing it you don't want these shapes group you want to be able to move them around and things like that so first thing what i want to do is i want to ungroup that so and i'll walk you through the grouping process of course but so we're going to ungroup that so the thing is shapes sample group ungroup.
When we run the generate we're going to actually make it create a group of those samples and we're going to call it sample group okay for some reason it doesn't exist or something that group doesn't exist we do not want to wrap that in on our zoom next and go to zero okay so what i want to do is i want to reset it to free floating in this case and that's really important because when i have these products notice when i when i expand or delete this i want to make sure notice that these are being rows that are hidden i don't want these to move i don't want these fields to move based on hiding and unhiding of rows so the best way to do that is to make sure that these fields are free floating and when we right click we go into the size and properties here and we see that we have now don't move or size with cells don't move or size of cells that's very important that's.
Also known in vbs free floating it means it's going to free flow it's not going to be sized and it's not going to be moving with cells so that's a really important distinction inside the properties okay so now that we have those shapes because and the reason is obviously because when we expand or move this we can't have these things move we need everything to stay consistently in the same location okay so for each shape now i want to do for each shape any shape we're using our in string command again any shape with the name sample is greater than zero and that basically means that otherwise does not equal empty the same thing we could use the same thing two different ways in that case i want to make sure it's free floating and i want to make sure it's visible we're going.
To hide it you know we don't need we don't need to display it when it's headed we need to hide it so i want to make sure to show it when we exit out of the customization we can hide it after we generate the catalog so it's going to be automatically hidden when we generate that catalog but i want to display it again once we go back into the customization in that case i want it visible i want all these fields visible any shape that contains the word sample i want to make sure it's now visible because only our sample is going to show that so we do that just inside the code okay so we're going to show that now again so we're going to loop through every shape in the sheet looking for sample making sure it's free floating and making sure it's visible that's all we have to.
Do there then what i want to do is i just want to show some button sets i want to make sure obviously this button set the reset button i want to display i want to display something called the close customer button and i also want to hide one other button i want to hide this one the customize button that should be hidden right that's only when we're not in the customization mode so we can do that with just a few lines of code close custom button we're going to make sure that that visible equals ms true i want to be able to close the customization so the button group the shapes group shapes the icon and the shape it's going to be visible by making it msoc true i also want to hide i want to also want to display the reset button we can do that here again also the open.
Customization button we don't need to display that so that's going to be false and also again i want to unhide those columns columns c through f we're going to make sure those hidden equals false that means we want the display so that's all we're going to be doing when we open the customization but what about when we close it well that's pretty simple again what i want to do is i just want to double check to make sure the shapes are free free-floating in case users have changed them for any reason we want to make sure because users are allowed to change them so we want to make sure to reset them just in case to free floating so that they don't move around we want to keep them in the same place that way everything lines up properly and we also want to shapes the sample group i want to in case that's displayed i want to hide that although it shouldn't be but just in case i want.
To make sure that's for the sample group just in case it got created in case they open and close it and i also want to make sure that the club again we're going to take those three buttons and just basically do the opposite close button is going to be hidden the label template is going to be hidden in this case i want the label template hidden and i want the reset button also hit it and of course the open button that's going to be healed now what is the label template let me go over that one over that briefly the label template is basically a sample label so let's go in to the shape so i can show you and if we scroll down here and we on our label template we can see.
That the label template is this right here this is basically a sample label that we're going to use we use this sample so if let's say you decided when i create these new ones it's going to use this label template as a sample so let's say i decided to make this red if i wanted to make this red and i could demo all of our labels we read so now when i create a new code it's going to be in red so it's going to basically take this and that way you can set your own exact format maybe you want it a little bit bigger maybe you want a dark blue color or whatever so it's going to take this.
And as soon as we create another one for example origin it's going to use that same exact format so it's really really helpful we can just hide that now so let's return it back to the way it was we'll go with the black and then a little bit down actually i've got to make it all black and then make it bring it down let's go back to i think 11 is a is a good thing okay so but generally what we want to do is we don't want to display that it's there but we don't need to show it it's always there so we can just hide that so what i want to do inside the code is make sure that that's hidden once we close it we don't need it just in case it's generally never visible but we can also make it visible in fact i should probably make that visible inside just so we can play with that here.
Inside to users so we do want to make it visible here dot visible equals equals mso teacher so that way it's visible when we're customizing and where it hits hidden when we're closing i like that better okay so basically that's it and then of course the columns were simply going to hide those columns what about the customize refresh now this one is going to run through our data so what do i want to do on this one basically what i want to do is i want to rebuild that column this column i want to rebuild completely and why is that because data can change so if the data changes.
Let's go ahead and update that let's go back to our name i want to get that name in here and then we'll update that so again let's say i want to put the name all we need to do is increase this to make sure it com companies all the data there and then we can delete we don't need the word name so delete it okay so now we've got it reset up there so basically what i want to do is i want to rebuild these columns i want to rebuild all the fields the categories whatever categories there are if our data changes if our categories change or increase or decrease i want this list to be dynamic whether it's a brand type and i want to go through every single column in that table and.
Then of course if there's a price column i want to be able to put in a price so let's go through that and see how we do that so basically the idea is we're going to loop through our first all the way to our last probably just through numbers through eight and i want to determine if it is a text then if it's a list what are we going to do if it's a number what are we going to do so we're going to do different things and you can we can use select case to do just that based on the type of column it is so let's go through that data and see just how we build this list so what i'm going to do we have a few columns a and b that we're going to put some hidden information not too much and basically all i want to do is i want to we're going to be working with these icons here so the best way to.
Do it is to know what the characters are how do we know what number these characters are when we insert and we insert what's called a symbol we can find out what type of that in this case is going to be a symbol but we want to know what is the number according to that so if i want to put in this check mark here or i want to put in something like this we can just look at this windings number 252 that's our character and the font that's covered it so basically every single character has a number if i want to know that number in vba we can use the character code 252. so i've done just.
That so i've placed an icon here that i found it and i placed the character code here that's going to help us in vba because we need to know what is what i also want to know the number of the columns here and i want to know some information that's going to help us expand and hide that information and know what column it is so i'm going to walk you step by step through that macro so let's get into that right now so the customizer fest first what i want to do is i want to get the last product row on sheet 2. we need that last product row because if we're going to run through all the products i need to know what the last row so that line of code is going to do just that give us the last word probably row in this case of course it's 68 but we want to know dynamically through vba and.
Using end excel up is going to do just that sheet 2 a is all required and alex that's the last row now with sheet 1 what i'm going to do is just to make it a little faster we're going to take application screen updating defaults and calculations to manual be sure that any time you use this you must reset it at the end we want to make sure that application screen updating goes back to true and the calculations go to automatic so that's very important you don't want to use this if you're having bugs in your code you only want to use this when everything's set up so the first thing we're going to do is we're going to clear all of the contents of those columns all the way from column a a through f i want to clear everything out here inside here because we're.
Going to add brand new so the first thing starting at 8 all the way through e we're just going to clear everything out delete everything all of this because we're going to create it dynamically so we need to refresh that and make sure to re clear it up all right so another thing what i want to do is i want to reset the fonts because the fonts can change based on whatever's data notice the font here for example the font here is uh let's just go over i'm going to highlight let's go we see that that's wingdings but the font here is calibri the font here is calibri so the fonts can change based.
On what's going to be going in here so we want to reset all the fonts back and then of course when i change these shapes i'll then reset the font back to wingdings based on what's going to go in there through vba we can do that okay so that's where we want to reset the fonts and now what i want to do is i want to make sure that i unhide all the rows so starting because notice that some of them are hidden some of them are not based on the data right so when we go here we see some are hidden some are hidden some are not so when we when we collapse the data it's going to be hidden so we want to make sure that it's not hidden so we want to unhide all the rows okay so once it's all unhidden again i want to clear all the criteria now we're going to focus on some criteria if we're going to run an advanced filter i want to make sure to clear all of criteria because.
We're running advanced filter let's go into the data and take a look at that and bring this up for now and so we have our original data here but then i want to set a few things i want to set some unique lists that's important and i also want to set some criteria now this criteria is just empty why is it empty because it's cleared out not only do i want to clear out the criteria but i want to clear out the header names the colors don't mean too much so then what i want to do is once it's once i run the advanced filter i want to get our results this is what's going to make up our catalog so the catalog is going to be based on all this data because this data is filtered meaning that uh we've run through through uh perhaps some type of advanced filter with some just unique record data and then i want to create that catalog based on this filter data maybe you.
Only want to show a single type a single category or so on and so forth or based on a price filter so then we want to only create that catalog based on this filter but to do that we need to set some dynamic criteria and so i want to clear that information out starting with a a and going all the way over and probably want to clear that data too so i want to make sure to clear that out okay so we do that through vba we do that right here a through a s it's going to clear the criteria and results all the way over starting the a4 going all the way down okay so next up what i want to do is i want to set the display details i'm going to set our initial value in a8 to 9. why is that because.
We've got nine additional i want to set that why am i setting it tonight i need to know when i let's pull that here i need to know when i expand this i need to know how many rows to expand it in this case it's 9 rows if i put the 9 here i know when i click this to expand 9 rows so that's kind of nice so if your data contains more columns or less columns you want to display this you could also use a countif to count the data that's another way so there's programically ways to do that we just set it vba but you can do that so basically this is the number of columns number of columns if you want something to show i want to know which fields and i want to know which one is.
The high that way when i click this i'm going to know exactly how many rows to unhide by putting this 9 here so that 9 is going to let us know just that okay next up once we have the 9 there i also want to place in the character what's character 117 and what is c8 character 117 is this character right here i want to place this icon and i want to place it right here so i want to place it in c8 so that's going to do it that's why i put this little table here this legend so you can easily see which characters are which because we're going to be using these throughout this little build okay so once we have that we know that we'll know what character is going to go there and then of course in d8 i want to put the words display fields display fields that's going to go to the next one.
Then what i want to do is i want to display some unchecked fields uncheck because we're resetting it so we're clearing everything out theoretically i could delete the sample but i just didn't want to do that for our purposes in other words theoretically when we're clearing this out this sample should also be cleared out too and we should rebuild the sample but i just didn't want to do that because it's a little bit extra work but so theoretically we could clear this out deleted and that would force the user to then again select brand new ones but for our purposes on training we'll keep the sample here because it's going to save a little bit of time okay so back into the code so we're going to uncheck those d9 all the way through d17 and that's going to put that character all these characters right here d9 all the way through d17 it's going to put the.
Character 168 right there that's the unchecked box as we're resetting this once we have that unchecked box that i want to do then i want to set the fonts programically in that remember that font is not ariel so now we're setting the font d9 through d17 we're going to set the font name to wingdings and now also what i want to do is i want to set the headers now i put the headers very easily in here i've got a set of headers in column e so basically now i need to put in the headers here e 9 through e 17 and that's going to come i just put them in right in here so just taking the headers here just got them a list form here for all the way from l3 through l11 that's.
Going to bring those inside there and so we could do that right here l3 or some sheet 2. then all we want to do is take 9 to 17 i'm going to hide them right we want to i want that list to be collapsed by default and then the user of course through vba can unhide it so again we want to make sure that that i want to make sure these are hidden so i want to give it that default look next up we're ready to add in our filters so let's go ahead and take a look at that we're going to select case for that now we're sitting select filters so the first thing we want to do is set d18 to filters.
Now you can make this dynamically if you want as far as the row based on the number of header you could do some more and then what i want to do is i want to set the custom row to 19 right we're gonna that row is going to increase the custom row is going to increase starting at 19 right our header is going to go in there then we're going to build it dynamically then what i want to do is i want to start a loop from the product column one to eight why am i using the product column well i've got eight column nine including the picture but eight we're just going to focus on right now because eight is the data that we're going to be filtering so i want to filter based on that one all the way through eight in this case that's going to give us eight columns we're going to filter all those and then what i want to determine what type of field what type of field so to do.
That the best way to do that is to simply just add in that one through eight and then what i want to do is determine the data type the data type is going to be on sheet 2 row 2 and the product column which is dynamic so basically i'm going to extract this type here text text list list list so we want to extract that and then we want to make some changes based on that so if it's a list type i want it down here if it was a text type we can add an additional filter additional filters but for list type is the best way for this particular program is it's the easiest to show you the strain is going to be long enough so let's just take a look at that and how we would do that if the type is text what i want to do is set the header just to set the header and d column that's all.
We need to do in this type if the case is text what if it's list if it is a list then what i want to do is i want to create i want to extract delete the criteria why am i deleting criteria name criteria because when i create an advanced filter it automatically creates some named range so we go into formulas and name manager here we're going to see extracting criteria now these are important when we use advanced filters we need to get a criteria we need to get the extract.
But especially when i'm creating an advanced filter where i only want unique records and in this case i only want unique records what do i mean by that i only want the unique records here i only want what unique type or what unique categories and i want to put those unique names in others they're not going to be any criteria i only want the unique list i want all the unique categories so to do that we need to make sure that the criteria is deleted make sure that's very important and what would so programically what i'm going to be doing is basically doing doing this.