Create Your Excel Sheet Bill Format for Production Effortlessly

Excel sheet bill format for Production

hello this is randy with excel for freelancers and  welcome to the bill of materials training in this   week i'm going to show you how you can build your  very own bill of materials complete with inventory   items and we're going to be able to assemble  those with just a few clicks or disassemble   and we're gonna also be able to expand and show  all the parts within that bom it's gonna be an   incredible training i cannot wait to share  everything with you so let's get started all right thanks so much for joining me this week  this was a highly requested training so i'm really   happy to be able to get it to you and i'm going  to walk you through every step but first what is a   build materials and why do we need it well a bill  of materials also known as bom is a basically a   list or a compilation of materials and sometimes  labor that make up a single component so for   example in our sample here we've got a computer  set in this computer setup made up of different   parts including a motherboard graphics computer  so it's made up of all different kinds of parts   if you're a computer store you're going to buy  all these parts individually you may build them   together and sell the computer as a single unit  so what you want to do it's very important you   want to know how many computers you need to build  and it all depends on how many components you have   now that assembly is the list of basically all  the components that you need so you have an item   list and then you what you want to do is you add  so many items for example this computer contains   one motherboard a computer fan a graphics card and  so on and so forth and it also has two ddr so you   can put how many are required what you want to do  if you've got all your inventory items you want   to be able to build them build those and so what  you want to do is if you know based on a certain   limit you've got 10 available to build so you  can build let's say five of those and now you   have five that are available to be sold in this  so when you go to general info we're gonna see   that we now have ten available we just added  we had five before now we've got ten available   so the idea is to take your materials your  raw materials and make up a complete component   or a complete product and it can also include  labor and that you can then sell that part so you   can combine it you have a combined cost and then  you have it combined it can be very very complex   but we're going to break it down super simple and  i'm going to show you how you need this this type   bill of materials is used in almost every  type of industry that kind of sells whether   it's manufacturing whether it's hardware whether  we're looking at different types of sales or or   inventory you use these and you combine these  in fact you can even use these in construction   my former industry for example if we have let's  say we have a bathroom vanity set right so this   is a set you've got a faucet you've got supply  lines and you want to create that assembly so   you can sell that to your customers so you would  take a bathroom vanity uh some silicone caulk   a countertop a faucet and combine and even some  labor and labor and then combine all that into a   single unit that you can sell and so you can see  all the components here so what we're going to   do is we're going to use excel and we're going to  break it down into some really easy to understand   items and also we've got a really really cool  general info so when we select individual parts we   see we have individual parts of these so each one  of these is a faucet and then what we want to do   is we want an assembly or make that up so that's  what we're going to go over today and i'm going   to show you how to do this you can put this in any  type of application so i'm going to walk you step   by step through the entire process all right so  let's get started but before we do that i just   want to remind you of a few things this training  is absolutely free i bring this to you each and   every tuesday i just ask a few things if you could  please subscribe to our channel that would really   help go ahead and click that subscription and  don't forget the notification icon belt that's   going to get you alerted each and every week  that i bring these to you these are of course   absolutely free you can download this workbook  also for free in the links down below i'm creating   these free but i do create a lot more than just  this in our patreon account for example this week   i've got an incredible bill of materials but i may  add on things like invoicing and purchasing onto   this and that's available in our patreon account  so we take your suggestions your ideas and i add   to each application every week and that includes  an update application and it also includes an   updated training whether it's you know anywhere  from 10 to 30 or 40 minutes or whatever it is   based on your suggestions ideas all of that is  available in patreon the link is down below excel   for freelancers you can find us on patreon and  that's going to really help us out if just a few   dollars a month it'll get you a ton more value so  i really appreciate you supporting us on patreon   all right so let's get started on this training  we'll go over the foundation how i created it   every component every function every feature every  formula every conditional formatting that we're   using and how it all does and every of course line  of code so we're gonna get into that right now so   the basic idea is just a pretty basic form that we  have for our materials now these can be any type   of material notice that we also have an item name  here we've got an item type now if we select just   a basic component let's just say silicone caulking  white this is an item type and notice how the tab   the assembly tab is gone right this is not an  assembly part however if we click on an assembly   now an assembly is also known as a kit it's also  known as a group also another bill of materials   so it really depends on how you want to call it  but there's many names for it but basically it is   simply a collection of items and possibly labor  that go into a single part that you can all buy   and sometimes sell as well so however when we do  have a assembly like as in this full computer we   see that this is an assembly type right so we  have different item a service type and assembly   type we've got different types based on that and  an assembly has this tab available so when we   click on this tab here we see that we can then add  additional information so let's say we don't need   this and we don't need this so we've got some  additional information here so this assembly   items we can then have a list of our items these  are all of our items and then we can add to that   list and we can say here's the item description  how many are required to build it for example if   we're building a computer we may have two pieces  of ram or for example if we are building let's say   something that i'm more familiar with here inside  the bathroom let's say if you're into construction   field let's pull up that assembly we've got a  bathroom kit and which is this bathroom vanity set   now this bathroom vanity set is made up of several  items you know and then it's also made up we need   two supply lines for hot and cold water we need a  valve for hot and cold water so each one of these   contains a kit and it's also going to retain  some labor probably not one hour probably maybe   like five hours of labor so we can update that we  just click update and it's going to update that   it's going to take that total assembly cost now  this is all of the item costs that have now where   this item costs come from well that comes from our  item database let's take a look inside this item   database and see what it's getting also known as  an item list we've got an item id this is unique   for every item i've got an item name we've got  the type that we just went over remember we've got   some service some are item and some are assemblies  here we've got a bit of bathtub if we want to work   on that we've got a part number a unit of measure  also known as uom the category which is just   something that we can assign purchase description  a sales description which would come in handy on   the sales description when you're selling would  be on the invoice on a purchase description this   would be on a purchase order uh what is the normal  purchase quantity when you purchase this what is   that quantity that you're purchasing is it one  two or three four this is not a this is not a   you know so how many are you purchasing and how  many are you selling normally right also we want   to know the item cost what is the item cost and  how do we know it so we've got item cost we've   got a sales price this is what's going to go on  the individual both in the item parts also known   when you're invoiced it's also going to go on the  invoice i also want to know what the quantity in   stock how many do i have in stock right so when  we go ahead and build that bath and vanity set   now we have two in stock right so here we have  two in stock but what if we want to build more we   can build more and i'll show you how we're gonna  do that in just a moment also we have a picture   associated with this and we have some notes  so that's all that's made up of this item list   and that is all translated into here so we have  the part and serial number purchase description   here purchase default purchase this should be  general here so we've got the purchase quantity   here the default sales quantity and the item cost  and this so this particular this vanity cost right   we have based on all the parts in stock if we take  a look at this we've got a cast iron bathtub right   so we cannot build anything but if i remove that  we don't use this generally so let's say we remove   a few items because we're always just putting  them out as a sample so let's take a look at   this to create this bathroom vanity kit i need a  cabinet i need some silicone i need a countertop   i need a single handle faucet i need labor i need  a ceramic sink i need two supply lines and i need   two elbows so i need all that to create this right  so here's my quantity in stock if i have all that   but i don't have any this is service rate so that  wouldn't matter but if i don't have if i've got 46   available how many can i create well i would say  i can create up to six why is that because i only   have six tubes of silicone caulking if i were  to change that in half you know we only use a   half tube i can create a lot more but let's just  stick with that for now one so that means that   six we can create six of those and that's just  what i have here available to build six right   if i try to assemble seven of those it's gonna  tell us no we cannot assemble more than we have   right i can't create that assembly i can do  it a maximum of six right so i can create six   and what that's going to do is it's gonna when i  click that it's automatically going to deduct the   inventory now we have no more caulking because  we've combined that all into a single kit we   can now sell it we've put it all together and  we've made it available for sale so now we have   nothing available to build and we've assembled a  total of eight we now have eight of these bathroom   cabinets to stick and of course these bathroom  candidates here they contain a single cabinet   some silicone caulking a countertop a faucet five  hours of labor one sink two supply lines and two   valves so this is combined and we have in stock  on hand eight now let's say we decide we wanna   disassemble we wanna take those apart essentially  and be able to sell them individually we can do   just that now we have total symbol of 8. if we set  10 to disassemble well that's not going to work   because obviously we only have 8 but if we want to  let's say we want to disassemble 6 we can do just   that and what that's going to do is it's going to  disassemble those parts it's going to deduct from   the inventory now we only have two available  but we also have available now more silicon   caulking so we can assemble and disassemble  these um let's say assemblies or buildable   build materials or boms or however you want to  say it back and forth and that's really really   important in manufacturing in the industries where  we can work with these um raw materials to create   products that we can sell so we've got all that  so we can be able to disassemble i'm going to walk   you through every step once we update that product  everything gets saved okay so here in the general   information we have basically notes so how are we  going to do all this well let me walk you step by   step through this application we'll get into the  admin which is just very basic i've got a picture   folder now this is the folder that all of our  parts are located and that looks just like here   again we've got our computer and bathroom parts  two very different industries but i wanted to show   you two very different samples how something like  this can be used so we've got a bunch of pictures   here and that's essentially it i've got some item  types here categories which are whatever you want   to put it and some units of measure which is  just kind of information that's pretty much it   basically items and item data's we've been over  now assemblies is everything that we've got we've   got an assembly id remember that bathroom kit that  bathroom that is called the assembly item nine   what does that nine come from it is this inside  the item database that bathroom vanity set   is item id 9. it is an assembly right we're  selling them as each and we have in stock we have   two in stock right now and so the best way to  do that is to simply track them in assemblies so   once we load that assembly item in there i need  to make sure we load all these items that are   associated with and their associated quantities  the requested quantity we have five hours of labor   to supplies so that means when i load that item  in here and load that item all i need to do is   just change it here loading that one in here let's  see the bathroom vanity set it's already loaded in   we want all those items to load here's those items  right so again this is item number nine and i'll   show you that in a minute this item id9 it's not  displayed here but it is displayed here in the   first column located in b3 so we have that so we  know that those are individually tracked likewise   of course inside our computer kit here our full  computer set that we have all those associated   items here again two rams and one of everything  else that's going to get again we have 10 in   stock here so we have available to build five why  do we have five available to build why is that   because look we only have a total of 10 pieces of  ram if each kit uses two pieces we only can build   five that's why we have the five so it's really  really important to keep track of all this stuff   and again just like here that's item number  21 and here's all the items that we use so   they're all stored in this assembly database  and all i need to know is the quantity required   i need to know the item row what is this item row  we're going to get into nvb basically it's the   row that's associated here 36 through 45. those  are the rows so when we bring it back into that   we know what row to place it on 36 245 which is  correctly right here 36 to 45 those are the rows   i also want to know the database row that's this  row the 14 right all the way through 23 that's the   database row when i bring this information back in  here inside the items if i decide to make a change   here i want to make sure i know what row inside  the database to update right so that in this setup   if i make that update i know that this changed  to 2. if i change it back to 1 click update i   need to make sure i know what row to update that's  row 23 so that row 23 must come in somewhere and   it must be placed somewhere but it can be placed  off the screen in our case we've placed it over   here so we know that that row 23 is right here  so the assembly database row stored here i'll   go through the other things very shortly so keep  that in mind that's what we want to do so we've   got the assembly now i've got some empty sheets  for invoice purchase order invoice database what   i'm going to be doing is i'm going to be adding  an invoice i'm going to be adding a purchase   order but that's going to be 4 exclusively for our  patreon customers so it'd be great to have you in   patreon right you're going to we're going to show  you how to take all this and put it in an invoice   how to put it inside a purchase order and then  how to track inventory ingly for that so   make sure you get on patreon all right so let's  get into it so how do we do all this so we've got   to basically run down that's all the sheets it's  relatively simple because our databases simply   contain an item database here and an assembly  database that's it that's all we have so it's   very simple but what we want to do is we want to  to basically be able to differentiate when we're   loading those items in between an assembly  item and we want to differentiate between   a regular item because there's some changes  that are made notice there's no plus sign here   there's no tab here so we can through vba and  conditional formatting we're going to show you   that however when we load a bom of build materials  we do want that plus we do want to expand on that   and i want to be able to show all the items  within that so that's agreeing we're going to   really use this really cool to vba i'm going to  walk you through that okay so the first thing is   notice we've got some conditional formatting and  that conditional formatting is going to make this   here this tab assembly item show or hide based on  what is located right here in h5 and that means   if h5 here i want this to show up if it's an  assembly if it's anything else then i don't   want it to show up for example this hourly labor  is a service their service is not part of any so   we don't want to make it if we were to change  it we would it would then show up so how do we   change that how do we get that that's through  conditional formatting so let's take a look at   that inside the home and go into the conditional  formatting and then manage rules and i've got   just a few rules to show you okay so the first  thing what i want to do is i want to track what   column the user has selected this in this case is  column number four this and let's go back to our   kit so we can select both of them because nothing  will happen nothing which is what i want nothing's   going to happen here only if it is an assembly  item so on our computer here that would be an   assembly item then so basically i want to either  it's we select column four which is column d or we   select column six and i want to place that column  through vba i want to put it directly inside   b1 so b1 is going to tell us this is going to be 4  this is going to be 6. notice how b1 changed right   so we're going to do conditional formatting based  on the value of located what's in b1 so back into   conditional formatting and we manage rules and  we take a look at this we're going to edit this   rule when b1 equals 6 that would be the assembly  tab then what do i want to do i want to format it   i want to give it a special format and i'm going  to give it this fill effects when we look in the   fill effects we see it's a darker a little bit  lighter blue to a very light blue so it's that   reverse and i want to make sure when we have that  tab effect that this second color this light blue   is exactly the same as the main background color  so that it blends in and also what i want to do is   two other things i want to make sure that the font  is set to bold and i want to make sure that that   lower border is missing and that's how we get  that blended effect for the tab so that way when   we click assemblies you see that lower border is  missing it's in bold font and we've given it that   lighter look right so if we take a look in the  formatting we go into home and we look in the   formatting actually let's format those cells if  we take a look inside the format they both have   the same format they both have a fill effects of  basically the reverse notice it's the same colors   here same colors but this one is this simply just  light to dark light to dark right so what we do is   we simply reverse that when they select okay so we  gives it that reverse notice it starts out light   and then it goes to dark but here it starts out  dark and goes to light and missing the border   so both of them have that same format as the  conditional feminine so when this becomes one   we're going to apply that conditional formatting  for four so let's go ahead and highlight and   back into that conditional formatting again  and then manage those rules so when it's six   but when it's four we're going to do exactly the  same thing the only difference is with this one   is we're going to apply it to d3 through e3 and  why is that because we're using a combined cell   right this is the merged cell here between d3  and e3 so we need to apply it to both of them   and then also one more when we have a conditional  formatting if we edit this rule we see that h5   it does not equal assembly when it does not  equal assembly i want to hide it completely   f3 i want to hide it here's f3 i don't want to  show it if it's not assembly if it's a service   or item or anything else i simply want to hide  this tab so when we edit that wrong format we   see a few things we see it's got the fill we take  a look at the font the font is simply hidden we're   coloring the font as the same as the background  color if we take a look at the border here we   also see that the border is missing on the top  and the right we don't want it to show so that   way those three different rules allow us this tab  feature right so when we make a selection it is   vba when we make a selection change that applies  that and does all the rest of the work for us so   that's what we're going to go in now we're going  to show you this tab feature so i we understand   that the conditional formatting two rows based on  the tab you selected and based on the item type so   when we select a different item that tab is going  to be completely gone when we go into vba when we   make a selection on this this selection nothing's  going to happen if this is not assembly right we   make a selection nothing will happen so we've told  vba hey if this is not assembly when user selects   f3 nothing happens right so that's what we want we  only want to switch that tab when it is actually   an assembly type item so then and only then do  we want that to fill great so let's get into vba   take a look at that selection change that we're  going to have so we get that tab effect now to do   that all we've done we're keeping certain rows  consistent we're going to keep this row for if   we see rows one through three are kept consistent  but we're going to do is we're going to hide rows   so if we unhide these rows and we see what it  looks i'm just clicking unhide i know it's off   the screen so i'm unhiding those rows so basically  all we have is a section of rows for our first   main our general info and then we have our  assembly info right here so all it is is two   simple tabs and then i've got a list of vitamins  this is going to help us for auto complete so that   way when i put in a computer let's put in put in  it's going to automatically complete right so i'll   show you that in just a moment but uh basically  that's what this list is doing right here   okay so we understand that so we understand that  we're simply just hiding and showing rows based   on whatever steps so when we select general info  we want to hide rows 32 through 60 when we want   to click assembly we want to again show rows  33 through 50 or 60. so that's all we're going   to and that's what we're going to do inside vba  so let's get inside vba and take a look at that   and show you exactly how we've done that that'll  be in the developers tab if you don't have the   developer tab or you don't see it here all you  need to do is just click on the file depending   upon what version of excel using properties and  then customize ribbon just make sure you've got   the developers selected you can also get in using  alt f11 that's a shortcut to get you inside vba   we're going to focus on the selection change so  inside i've got several sheets here but we're   going to focus primarily on the items sheet that's  the one marked items here so and we're focused on   selection change if you're wondering how to  get to that worksheet and then you select   selection change from here and that's going to  show up that's going to show up all the selection   change so all the events that we're focused on  at this moment are based on selection change   when user makes a change to the selection of a  cell so this first line of code if target count   large is greater than two then exits up this helps  prints a lot of errors when users make a change to   selection change to a lot of cells we don't want  anything to happen we want to make sure that we're   exiting out of the sub so that's exactly what we  do and it prevents errors all right continuing   on if the user makes a selection change in either  anywhere from d3 through f3 and h value h5 equals   assembly right remember we only want this trigger  when it's assembly if they make a change on   anything here we only want anything to happen when  it's assembly right there's no tab feature when   it's an item so only when h5 equals assembly then  we're going to continue first thing what i want   to do is inside b1 i'm going to place that target  column target column inside b1 okay so i want to   first thing i want to hide everything i'm going to  turn off screen updating this allows those to make   a lot faster and get less flashing so the first  thing i want to do after that is i want to hide a   picture button group what is the pick button group  well that is this thing right here i want to hide   the picture button group right here this button  group here is a set of pictures allows us to add   and hide pictures it's called the picture button  group it's a set of shapes so i want to hide that   so we don't need that right if we're sweet i want  to hide it regardless of the tab the first thing i   want to do is i want to hide it because we don't  know what they're going to do so we would either   show it or show it and then also what i want to  do is the assembly group group here is located   right here inside here inside the assembly we have  two different tabs right here these two different   buttons we're calling the assembly group it allows  us to assemble or just assemble our products and   that's called the assembly group so we want to  hide those features regardless and then what   we're going to do is simply show them based on the  tab that was selected continuing down in the macro   okay so once those are hide i want in case there's  an item picture there may or may not be an item   picture if there is we're going to always give it  that name called item picture i want to make sure   that this picture regardless of what it is is  going to give the name item picture we're going   to hide that again regardless of that that way you  know generally when they're switching tabs i want   to hide it and then we'll show it just in case  they're on the right tab so also what i want to   do is i want to hide all of the rows hiding all of  them 4 360 hiding everything then everything else   is based on what what tab was selected if the tab  column equals 4 remember that 4 that's column d   then it's the general info else down here it's  the assembly so we're going to do a few things if   they've selected the general info the first thing  what i want to do is i want to unhide those rows   remember 4 through 31 entire row hidden equals  false i want to display all those rows i also want   to display the picture button group that's that  group that we hit up here i want to make sure that   that's displayed and then i want to show a picture  but only if it exists that item picture also i   want to know if h5 equals assembly then i want to  know the expand button remember we saw some things   on the expand remember this thing was given that  called expand button i want to show that plus but   i only want to show that plus obviously if it's  assembly i want to make sure just to make sure   although we have to be in assembly almost always  but i want to make sure that we only show this   when it's assembly so if it's assembly then we're  going to show that and then what i want to do is   i want to select something else anytime we use  selection change especially for that like this   i don't want to keep my selection directly on  e3 i want to go somewhere else so it's going to   go directly to f5 same thing here when we're  on assembly i want to change that selection   i want to go to e36 so we're changing it based  on that so we just want to make sure we select   something like a tab feature we want to select  another cell so that's just what we've done inside   here so that's all we need to do if it's general  info next up just a few lines of code if it is   an assembly if it is then in the rows 33 through  60 entire row hidden equals false in other words   we're displaying all those rows the assembly group  those two pictures that disassemble and assemble   those two pictures that's grouped together we're  going to show that we're going to display that   again e36 we're going to select that cell also i  want to double check just again make sure that if   it's assembly it should always be but just in  case i want to show the shrink button i want if   the shape shrink button visible equals true if it  if any of those buttons the show or hide i just   want to make sure that they're hidden so if that  shrink button what is that shrink button here that   is this button right here this one right here this  little minus here is called shrink button right   if you like these icons and these pictures and  all these shapes i've got all those in a folder   available you on the resources inside patreon i've  packaged everything together in a nice zip file   so you can recreate this when you get into patreon  okay so that's what this is called here so that's   called shrinkbat i want to make sure that this is  not shown when i select here i want to make sure   that that's also hidden so that's all we do just  to make sure okay so also i want the expand button   we're going to hide that as well okay so that's  all and then we're going to turn application   screen updating on so that is it that's all we  have to do to get that really really great tab   feature working for us so it's very very easy on  that okay so what else in this screen i want to   show you well first of all we need to be able to  add a picture very easily and that's relatively   simple if we want to add a picture we can add  any picture just select it here and it's going to   place directly in here we can also clear a picture  if i want a clear picture i can do that and we   can just simply add the picture back up here right  here this bath and vanity clicking okay it's going   to add that picture right back into it so we want  to make sure to be able to save it and clicking   update it's going to save any changes that we  made okay we can also delete the item editing   but let's go into the macros that add this picture  and clear the picture they're relatively simple   so all we have to do is go into the item macros  right here we're going to start out and then   we've got some basically some dimensions and  variables we're going to go over i want to know   the item row right this is going to be the row  of which we're going on right we're going to loop   through the items i want to know the item column  that's going to be really really important because   once we save this information we're going to  use data mapping i've got a dedicated video on   that but basically it is here again i've gone over  this several times if you haven't seen my videos   but basically f5 if we look at f5 this is data  mapping and basically it maps the item name h5   to the counterpart here so notice this is f5 this  is our item name and it comes in this column here   item name is f5 our type is h5 so it allows us to  quickly bring all this data inside our database   or it automatically quickly allows us to take  this database information and bring it directly   inside here remember i do have dedicated videos  check our easy form we've got a few different   forms so we've got lots of videos on that so  i hope that helps all right continuing on so   we've got data mapping here so we need to use the  column we're going to be looping through these   columns column 1 2 all the way to the last column  what's that last column we need to know column   because that's going to be helpful it's going  to be column 15 so we're going to loop through   column 15 okay so we need to know that column  inside a variable that's why we have it here   i need to know the last row of the database i need  to know the last result row now this is going to   come in handy when we run our advanced filters the  last assembly row remember we've also got assembly   rows so i'm going to need to know the last row  of that if we need to add them or we need to   run an advanced filter we need to know maybe we  only want to know assemblies for 9. so we're going   to put that into an advanced filter and get those  results and then bring those results in so we're   going to need to know that so we also have the  assembly database row for focus on the specific   row and the assembly row i also want to know the  item id a picture file and the picture folder   remember that picture folder is located directly  inside our admin here so we located in c4 so we   need to put that inside a variable as well okay  also what i want to do is i want to know the full   picture path that picture path is basically going  to be combined that picture folder located inside   our admin here along with the actual picture name  so the combination of the name of the picture with   the folder is going to be our full file path when  we combine those we get an accurate path so we're   going to need that and also the picture path and  the result row as long okay well we need that   for our picture we're going to need an item file  picture as a file dialog and item shape as a shape   we'll be going over this so to add an item picture  again we're going to put that item picture folder   that string variable based on c4 whatever folder  is in c4 then what i want to do is i want to make   sure that that has a proper directory if it's not  please add a picture file path inside the admin   screen just in case there's an issue directory  item picture folder vb directory if that's empty   there's an issue so we did exit this up okay  assuming that there is a value we can right we   can't add a picture if we don't know where to  put that picture so we need to have an accurate   folder that folder all you need to do when you  get this file if you don't know you can just   simply browse for that whatever the folder that  is like in this case browser all we would need   to do is just copy this here whatever the picture  folder is if you've gotten the pictures here and   you just need to paste it inside admin here and  just paste it directly in the cell that's easier   you can also add a browse button to put that  folder in here we've done that in the past the   browse button works just fine as well but okay so  now what we're going to do is we're going to focus   on the items and we're going to have that set the  item file picture that's the one that's been as   a file dialog set that it can be the application  file dialog and we're looking for a file we don't   want to pick a folder we want to pick a specific  file so this is going to be called the mso   file dialect file picker right we want to pick  a specific file and what i want to do is with   that item picture i want to give it a please  select an item picture and then i want to give   it several types what type of picture files we're  going to allow the user png jpg gif there's a lot   more you can add to this if you want to give the  user the ability to add different like gifs and   and other or jpeg or something like that or  bmp's there's a lot more you can add to this   if necessary for our purposes today that is  sufficient allow multi-select no we just want   them to select a single picture if for some reason  they click the cancel button they don't go out we   need to make sure that they've selected something  if not if they select something the dot show is   going to be negative one so if they don't select  anything it's going to be does not equal negative   one then we're going to go to no selection it's  going to drop them down here and just exit out   okay but assuming that they have picked something  what i want to do is i want to take that full   file path of wherever it located on their computer  wherever it's located i need to copy that into our   folder right because it may not be already in this  folder maybe somewhere else on their computer what   we want to do is we want to make sure we have a  known location for that picture to do that we're   going to copy it from wherever they've browsed for  it and we're going to paste it directly into their   folder so we know where to find that picture once  again so the first thing we want to do is get the   full file path of its existing location once we  have that variable we're good to go we can move on   then what i want to do is i want to take the name  of that file just the name i don't want to put   it somewhere else where do i want to put it i'm  going to put it right here located in l if we take   a look at this and we see it's kind of a darker  font let me change that font a little bit to blue   bathroom assembly we're going to put the name  of that file directly in l6 i don't want the   full picture path what i want is just the name  so to extract that name we're going to use the   directory function and we can do that with here so  l6 that values equal the directory of the picture   path what that's going to do is extract the file  name from the file path and it's going to put it   directly there on this l6 why is that important  because when i save that file whatever is located   in l6 when i say update that whatever's looking  at an l6 is going to come directly over to the   and save it directly notice l6 here inside column  n it's going to save right here so that's very   very important once we do then i've saved the name  but now what i need to do is i need to copy that   picture into our folder that's dedicated for those  pictures to do that we're going to use the file   copy function we're going to take its original  location and then its destination that original   location is the picture path the destination is  our picture folder plus the backslash plus the   name of the file just the name of file we're going  to copy the picture this single line of code in   case it's already there i've added on air resume  next and on error go to zero if it's already there   it would create an issue so we just did that  theoretically what you could do is you could   also use the kill function you could kill anything  there and that would do that kill function this   will delete whatever is there and then replace it  in case the picture is different the last thing   i want to do is simply run the macro to show that  picture that is the next macro that we're going to   go over so we've separated that into two different  macros because there are instances when we do want   to show this picture like when we're loading  a specific item that we want to show it so we   have this in a separate macro and it's this macro  right here called item show picture again what i   want to do is i want to delete any picture that  might exist when i show that picture any picture   that might exist we're always going to give it the  same name regardless it's called item picture so   the first thing we're going to do is just going  to delete that in case it doesn't exist it would   present an error so we want to make sure that we  wrap that in on air resume next and on error go   to zero that's going to delete any picture there  what i want to do is if l6 l6 is our picture name   that's very important if it's empty there's  nothing we can do so we can exit the sub out   assuming that l6 does contain a value we can  then wrap that in our picture path variable   it's going to be admin wherever that folder is  located again with that backslash and l6 this is   our full picture path full picture path i do want  to run a check to make sure that it is an accurate   path and to do that again we're going to use the  directory function the directory of the picture   path and vb directory if it equals an empty and  then we're going to let the user know the picture   name or folder is incorrect please let the user  know it exit this up we can't display a picture   that doesn't exist so we can exit out of there  assuming it does exist then we can insert that   picture using the dot pictures dot insert what  are we inserting we're inserting that picture path   we're giving it a name remember we want to give  it that very specific name each and every time   once we know the name always we can then work with  it we can delete it we can move it we can size it   around we can hide it we can do many things with  it but we have to know the name so we give it a   very specific name once we've given a name we can  then work with it so with dot shapes item picture   first thing we're going to do is we're going to  lock that aspect ratio i want to do that to make   sure that it doesn't get contorted so this is  the first thing i want to do i want to maintain   if it's a horizontal picture i want to maintain it  if it's a vertical picture i want to maintain it   then what i want to do is i want to center it  directly based on the size i want to center it   directly here between columns l and m between rows  7 through 14 i want to center it so how do we do   that well we've been over this four if you know  so basically what i'm going to do is i'm going to   take the width of columns alpha m then i'm going  to take the width of this picture then what i'm   going to do is i'm going to subtract the width  of this column minus the width of this picture   we're going to get let's say a certain value  then what i want to do is i want to split that   width i want to divide it by 2 and i want to put  it based on this so whatever the left position of   this l i want to add that with to it and then we  do the same thing for the height again i'm going   to determine the height of rows 7 through 14. i'm  going to determine the height of this picture i'm   going to make a subtraction and divide by 2. then  i'm going to add that whatever the difference is   i'm going to add that directly to the top position  of l7 so that's just what we've done here directly   first we need to determine which is bigger the  width or the height and we need to set the height   if the width is greater than the height then the  width is equal to 90 else the height is equal to   90 so we're just setting limits on the height and  width and it's already been okay so now all we're   doing is setting the left position again the  left position is equal to the left position the   starting left position wherever that l7 is that  left position and then again we're going to take   the width of that column remember l3 and the width  of that i'm going to subtract out the width of the   specific picture subtract and then we're going to  divide that by 2. so that division we're adding   to the left once we do that it's automatically  centered based on the left position we can do   the same thing for the top but slightly different  in this case i'm going to take the top position   of l7 remember just what i said the top position  of l7 we're going to add something to that what   are we going to add to that well again i'm going  to take the height this time of rows 7 through   14 and i'm going to subtract out the height of  the picture i'm going to divide that by 2 and   i'm simply adding that to the top left position  our center top position put that center centered   left position and then we have the center top  position centered top position okay so that's how   we get it and then make last thing i just want to  make sure that it's true it should automatically   be visible when we insert it should be visible  but just to ensure we're going to make sure that   that picture is visible that is all we have to  do to make sure that we're showing that picture   and centering it in those cells i did have a  question on that so i'm happy to answer that   okay clear item picture is very simple that's the  macro that's tied to the clear button again we're   going to simply delete that picture we don't need  to hide it we're simply going to delete it and   then i want to clear the contents of whatever is  in l6 clearing that picture and then making sure   that assuming the user updates that it'll save  or update it great so that's it so we also have   an add new feature we'll go over this add new is  basically going to clear out everything else and   gives us the ability to add new we loaded instead  so the best way to do add new is set a specific   data field i want to set a named range for  all the fields that have to be cleared out   i've done that already and basically we just  select all the fields using control and then   we give it a named range i've done that already so  if we look into the formulas name manager and we   see item data right here item data and we tab over  it and i'm going to move this over and we see that   all the selected cells including the merge cells  are automatically selected you see that including   that so that's how we do that so make sure if  you're going to select on it make sure that you   go in and edit this named range because  especially when it's coming to merge cells   they won't automatically get so in other words if  i select it only the upper left cell is going to   be included so you need to go back in here and you  need to actually update that so we would say items   g8 here you need to add colon through h9 you need  to make sure that the full cell when we try to if   we try to clear out only g8 and it is a merged  cell then it's going to create an error so you   need to make sure it's going to say that it has  all of the cells g8 through h9 in this case here   g8 through h9 we want to make sure to include all  the cells okay so we've created that and then of   course we've given that the name it's called item  data so when i select item data we see everything   is selected here so it is that that we're going  to clear out so now that we have all that in   the named range we can easily clear out all the  data using a single line of code so we're going   to focus on the items the first thing what i want  to do is i want to delete any shapes that is there   so any shapes what shapes might that be well that  might be the item thing so notice if we've got um   let's say we've selected an assembly here  here and let's take this one here so we've   got some shapes so take a look at these  shapes now each one of these shapes has a   they start with assembly pictures so if the user  clicks add new i want to make sure to clear out   all these pictures right we need to clear out  all those pictures so the first thing we want   to do is run a loop through all the shapes in the  sheet and i'm looking for only specific shapes to   delete obviously i don't want to delete my buttons  i don't want to delete my icon i only want to do   certain ones so which ones do i want to use i only  want to focus on shapes that contain a certain   amount of text and that text is going to be called  assembly pick 41 or assembly pick notice even our   text boxes have the same name it's called assembly  pick text so anything that contains the word   assembly pick i want to make sure we delete that  so we can do that with a few lines of code so for   each item and shape using the in string function  the item shape dot name if it contains assembly   pick and that means it's greater than zero it  is found what are we going to do we're going to   delete that and we're just going to loop through  every single shape in the sheet so make sure if   you're using this make sure that assembly pick is  only unique to the ones that you do want to delete   okay so what after that remember we've got our  named range that we went over that item gave the   data that's all the data we're going to clear  the contents of all the data in those cells   also i want to make sure that we have that expand  that plus button we have the shrink button that   minus icon i want to make sure that those are  hidden i want to clear the contents of b3 b3   what is located in b3 b3 is located our item  our selected item id so we want to clear that   out because it's a new one so when we add new  i want to make sure that b3 so we also have an   item row if i've selected an item i want to make  sure that we have set an item we know that this   item we're going to use an item id we've got some  named ranges called item id so what is the item id   that is a dynamic named range based on the item  id here so if we go into the formulas and name   manager and item id this is using the offset  we've been over there a few times if you've   seen my videos and basically as this grows so does  this grow so what i want to do is i want to find   the row that's located on that specific i want  to know that 9 is located in row 12 or something   like that so basically when we select something i  need to know that so how are we going to know that   well in this case i want to make sure that we  use the match function so we're going to match   whatever's in b3 in this case it's item id 9 using  the item id and we want 0 want an exact match   we're going to be adding 3 why is that because our  first one starts on row 4 so we need to add we're   looking for the row number so we need to add 3  to do that the next id is going to use the max   item that's going to help us maxing that plus one  now for it if our last one is 21 our next one is   going to be 22 using the max function because i  need to know if it's a brand new one we need to   assign that next id and i'm going to put that to  the next item so very important that we have that   next one available to us and item load is false  we may be using that okay so that's it for that   then i'll we'll get into these two sample shapes  these help us create our map data so clearing the   contents new item group i want to show that group  that's a two set of button this is called this is   called existing item group take a look at that  these are three buttons the add new the update   and the delete and we also have another button  set called the new item group and those are the   two buttons save item and cancel new so for new  items i want to show this group and for existing   items i want to show this group existing items so  we can do that the new item group we want visible   and the existing item we want that's it and then  also what i want to do is i want to run a macro   called cli item clear picture right that's that  that's that macro that we went over here so i'm   going to clear any picture that might have gone  there that's it that's all we need to do for add   new save and update we'll go over this basically  what i want to do is i want to make sure that f5   contains a value f5 of course is our bathroom name  they cannot save it if they try to save something   that doesn't even contain a name we're going to  let them know to please make sure to add a name   before saving so that's what if f5 is empty we're  going to get a message box if b4 now i wanted to   do is when we're saving we're going to whether  it's saving an item or whether we're adding and   we're updating existing item this one here this  button here it is the same macro the same macro is   going to use for update and save so what i need to  do is i need to differentiate is it a new item or   is an existing item if it's a new item we click  new we know that item row is going to be blank   if it's an existing item we know that item  row is going to contain a value out of b4   so b4 is blank we know it's brand new item if  it contains a value it's an existing so that's   just what we've done here if b4 equals empty  it's a new item else we would go down here   it's an existing house existing item right  here for new items we just want to do three   things the first thing we need to do is determine  the item row it's going to be the first available   row using index left plus one and also what  i want to do is i want to set that item row   remember it's coming from b5 that is our one using  the max forma we're going to place that in b3   then what we're going to do is we're going to  set that first item id is the first available   in row a and the item so all we're going to  be doing that's only for new items is setting   that so in this case this would be 22 if we're  creating a new item 22 right here otherwise if   it's an existing item all we need to do is pull  the item row directly from b4 next up we're going   to use data mapping we're going to run a loop from  2 to 15. again we're going to do run a loop we've   already done column 1 so we don't need so column  two all the way to column 15 here what we're going   to do is we're going to look inside f5 here and  we're going to place it directly in column two   here we're going to look in h5 and place it here  and so on and so forth so that's just what we do   right here the item database the item row and  the item column is going to be equal to whatever   is located in row 1 item column value so this  is our range right here and it's going to be   placed basically as the save data database now  what we need to do is unique for assembly items   when we save and update i need to know is it an  assembly item i need to check because if it's   an assembly item what do i need to do i need to  make sure that we've saved any items that are   in the assembly so for example in this computer  set i need to do is look to see are there any   assembly items that need to be saved and as you  remember those assembly items are saved right here   so are we saving anything so what i want to do is  i want to say is it an assembly item if it is an   assembly item then i want to check what's the last  row if the last row is less than 36 that means yes   it is an assembly item but the user hasn't added  any rows hasn't had any items so we don't need   to save it but assuming that there are items in  this case the last row will be 45 i want to loop   through all the way from 36 to 45. i want to look  has this item already been added to this assembly   how would we know if it's been added already well  we can see here inside our assembly database row   here this one's been added this one's been added  but notice if we add another one let's say in 46   if we add another one this i don't know why we'd  add a bathroom vanity cabinet to a computer but   you get the point so this notice this one it would  have to be saved new right because it doesn't   happen so we'd have to look in column r saying  does a row exist if not added you know if not   there's no then we need to add to the database and  that's just what we do inside the code the first   thing we do is determine making sure that it is an  assembly the last one we're going to get that last   assembly row remember i need to know the last row  from e59 on up what does that mean there starting   with e59 all the way up not below that right all  the way up i want to know the last row in this   case it's 45. so if the last row is less than 36  then go to no items skipping down here going to   no items right that means there's nothing we can  do there's no items assuming that there are items   again i want to run that check has it been added  before if column r of the range and the assembly   row remember we're going to loop through those  rows for assembly row 36 to the last assembly   i'm running that loop starting in 36 going to  the last row i'm looking in r does r contain a   value if it does then it's been added again then  make the update if r equals empty then it's a new   item i need to do a few things first thing what i  want to do is i want to determine what's the first   available row inside our assembly database we're  going to use a and xl that's a lot of nines there   but we're going to add plus one as the first  available row next thing what i want to do is   i want to add that item id that item id is going  to come directly from b3 i need to know that item   id in inside b3 right here and i need to place  it directly inside our first column here i want   to put that i want to know what is the item id  next up i want to place the name there and i   want to place but but basically for new items  i only want to do this item row and the row so   that's what we're going to do right here so inside  d and inside e we're going to place that item row   that's the assembly row right we just pulled  the assembly row what is the assembly row that   is this row right here i want to know row 36 row  37 row 30 and i want to place it directly inside   here i want to know it inside column d row 36 37  and then i want to place the row of the database   14 15 whatever that row is in column e so those  are only for new items d takes on the assembly row   e takes on the assembly database row with the  formula equals row okay next up what i want to   do is i now that we've saved that i want to put r  now inside r we have saved the two database we do   have a row that we can associate with the database  and i want to place that directly inside r else if   it's in existing there's just one thing to do is  then extract that row from whatever's in r so we   have the assembly database row whether it is an  existing one or whether it is a new one now we   have it so now what we need to do is just update  two components the item name and the quantity so   everything els