Organize Office Equipment & Inventory with Excel + FREE DOWNLOAD

managing office equipment inventory and items has never been simple what items have been assigned to what employees desks or offices and where they located or what condition are they in all this is Randy with Excel for Freelancers and today all of that changes with the Office Inventory manager I'm going to show you how to create this incredible template with drag and drop so you can quickly drag and drop items to assign and unassign any items and create them easily it's going to be incredible training we're going to design this completely from scratch I cannot wait so let's get started all right thank you so much for joining me I've got a really fantastic training today because we're going to be creating this incredible Office Inventory manager we're going to design it absolutely from scratch by the end of this training you're going to know how to create this fully functional drag and drop Office Inventory management system complete with item details we'll be able to add save new we're going to be able to customize the colors of our items based on item types we'll be able to create a dyamic application based on equipment assigned to so for example it could be assigned to employee to an office to a desk or whatever you want it is fully Dynamic we're going to be doing all of this so I hope you do like these trainings I create these comprehensive app applications within Excel each and every week on Tuesday if you're new to VBA or you just want to brush up on your VBA code each and every Saturday I do create a VBA Basics training for you when we go over the basics and fundamentals of VBA so I've got you covered either way make sure you do get subscribed as well don't forget to click on that notification icon Bell that'll ensure that you get these trainings each and every week to you this template is absolutely free all you need to do is click the link down below into your name email and I'm going to get that sent right over to you I'll never spam you I do have some incredible ways for you to support and get a ton of value if you like these trainings but you want more features more additions or you want something customized I do that every single week on our patreon platform patreon is a great platform because for just a few dollars a month you get tons of extras including an updated video an updated application a PDF code book where you can study the code and you get all the resources that go into the trainings including all the icons including all the pictures or anything else that went as a part of this plus you also get early access to our workbooks early access to the video training and a whole lot of other features I'll include the link down below that's on our patreon platform all right let's get started what we're going to do I'm going to go over an overview of this application so you know exactly what we're going to be building I'm then going to start from scratch and we're going to design this we're then going to go over every single line of code so you know how to create these types of applications for yourself at times I'll be moving as slow as I possibly can but this is a lot of good information so you feel free to watch this video as many times as you want so the idea is this we have the ability to add update and save items now items are things in our office there could be computers phones they could be laptops anything that you would want to know inside an office you want to keep track of you want to know who it's been assigned to we want to know the value of that item and the conditions so that we can quickly select on an item and know exactly more information about that item also within our patreon I will be adding a label so we can actually print labels for these and then put them on the equipment that's just one idea I have but I want to hear from you what ideas you have how would you want me to customize that these names up at the top are the assigned employees however they do not need to be employed they could be offices they could be desks or they could be any type that we can assign so we could put these in specific rooms we can create room numbers these are all linked to a list on our admin so for example let's say we decided we wanted our equipment to be assigned to a very specific office we just type in office here and then everything changes from there so now we could probably have something like the HR office or the sales office and things like that so we see now it becomes an office list moving back into the office Office Inventory we now see that we have the HR office and sales office available for us to Simply drag items in so all I need to do is Click an item drag it in there and there it is it's located in the HR office and it's taken on the appropriate color when I select it we can see all the information and where it's located here and if I decide I want to unassign it I simply just drag it back to the unassigned section and it becomes unassigned so it's very very simple very user inuitive and we can also hide the item details if we don't want want to see that or we can select and edit any individual item to display the item details so it's very very intuitive very helpful and I'm going to show you how to create everything we have individual icons inside our admin we've got our item types where we have all the different types of items I've got an icon the name of the icon that's going to be located in the folder and we going to set a color for the individual items we can also customize the condition of any individual items we also have an icon picture folder we have our item picture folder where those pictures are stored so it's relatively simple our databases include all of the inventory item databases information about the inventory the type the value the condition who it's been assigned to what data it's been assigned and the picture that's associated with that and the assigned database is entire history of all the items that were assigned when they were assigned and if they were returned if they're not returned they're still assigned to them so that's exactly what we're going to be creating so what I'm going to do is I'm going to going to take this template this is our sample and I'm going to move it over to the other screen and what we're going to do is we've got some pictures here I'll be going over with you and I've got a blank template right here which we're going to be going over now this blank template has an admin screen and a database setup that is going to help us move things a little bit quicker but we're going to be designing this template absolutely from scratch so we're going to start right here on the first two columns those are going to be a and b what we're going to be doing is we're going to add some information into them and then going to hide those I'm going to use contrl F1 and we're going to drop this down so that we can see this and we're going to be able to then customize it we certainly need a title and an icon I've got something similar inside my admin so why don't I just copy that instead of formatting it and I'm going to use control C through our office inventory and I'm going to paste it directly in here I'm going to bring it up to row one which is where we're going to place it and then I'm just going to Simply type in a new title that we're going to use for this page let's get into a single row office inventory manager that's the name that I'd like to call it there's a lot of names that we could use I want to use a single line of code so we're going to get rid of that Top Line and that's exactly what we want Office Inventory manager now I'm going to hold down the control for both of these and I'm going to use control one on that one that's going to launch the formatting I'm going to go into the properties section here and I'm going to make sure that we're moving but don't size cells for both those because we're going to be hiding some columns that's great that's a good start now I do have a little bit of formatting as far as merge and centers to help us move things along so what I would like to do now is we're going to create the main section it's going to be relatively simple so what we want here inside D3 I want those to be unassigned items so in all caps I'm just going to put in unassigned items here so that you're going to use shapes to list those and they're going to take up two columns we're going to be able to drag and drop those next up I want to have some information for our item details those are actually hidden here so let's unhide those here those two columns and inside these two columns here I'm going to put item details so that's where we're going to list those item details next up what I want to do is I want to have all the information from our categor so here we have an employee list and we're going to put in all these employee lists let's take a little bit of the formatting so what I'm going to do now is I'm going to format these I'm going to hold down the control for item details and all of the categories or all of the assigned to so remember assigned to can be desks can be offices what have you and what not so I'm going to go all the way to X I want to format these all the same as these are our headers so again we're going to use control one here we've got our format cells we can use this we've already got it open in here if I want to give it a fill an individual fill I can use a gradient fill here so what we're going to do is I'm going to open up the fill here and we're going to use a fill effects now I've got some saved colors that are a little bit closer to our theme so I'm going to use them right here I'm going to use this darker blue and I'm going to use a little bit of lighter blue and then I'm going to use this one here from dark to light clicking okay all right so that's going to give us that nice color so we have that now now I also have a saved picture that we're going to use as the background so we're going to go into page layout and click background and I've got this saved and of course if you want this picture or any of the icons I do make those available on patreon so of course you don't necessarily need to use that I did have a complaint on Facebook they said how come the pictures aren't included but it's a lot of information and uh we make those available on patreon of course you're welcome to download this template and add your own pictures and data for absolutely free so I've got it saved here and I'll just click on the exact one that we're working on here and I've got this picture this is the background image so clicking on that and that's going to a nice background image on here very good so for the unassigned items we're going to put some borders around these and uh we'll use just a standard color give it a nice look so I'm going to use again one time control one here to launch that the borders are going to use a standard color we're going to use this color right here it's a little bit closer to the theme I'm going to use a thin border all the way around and inside very good so now let's go ahead and work on our item information the first first one I want is the item name I want to put that item name right here I'm going to skip a row and we're going to go to the item type so I want a list of items I'm going to skip another row and I want to put the value what is the value of that item next up I want to put the condition we have a drop- down list of conditions inside our admin we'll be going over that and I want to make sure I spelled that correctly condition next up I want to know it's assign to now this is just going to be informational we'll be able to assign or unassign through drag and drop so assigned to what we want to know I also want to know the item picture I want to be able to add update delete an item picture and so we're going to have a button set there and then I want some rows here this is going to be used for our item picture so we need a set of cells that we want to make available for that item picture and we can go all the way from 15 to let's say 19 on that so 15 to 19 will give us the information that we we're going to use g& H all the way here so we're going to color those in white but I also want the information in which the users are going to be entered to be in white except for the assigned two so we're going to do that user entered Fields will be in white so we're going to color those white just like that and with that in picture now what we want to do is we want a background color that's similar to our theme so I'm going to hold down the control and then it's any type of cell in which users are not going to be entering any information we're going to give it that standard background color so that's what I'm selecting now as I hold down the control this is going to be our standard background color so I like the way that looks all we need is just some borders to R wrap around our Fields so that it is consistent we can use this and then we'll customize it so again I can use format cells here and I'm going to use that same color that we've been using which is this blue color here I'm going to wrap the solid border all the way around and then a dotted line in the middle very good so now we have our information here we'll be adding the item picture inside here I want a thick border to wrap all the way around it so what I'm going to use is the same color but a thick border all the way around that's going to give us this nice look and feel we've got our item details here next up I want to skip a row and I want the item history I want to know that item history so I'm just going to copy this and I'm going to paste it here because I want to know the history of that item who had it and when and all that so we're just going to call this item history and inside that item history I also want to know the employee actually I don't want to know the employee I want to know the default field so it could be employee or could be office we don't know it's going to be whatever's listed here could be office right we don't know or it could be desk or whatever it is I want to put it in here we'll keep an employee for now so what I've done is given this particular cell D3 the equipment assigned to it's assigned to an office it's assigned to a desk or whatever you want a room I'm going to give this a name range called assign type so that way I can use this inside our code so for example inside the office inventory what we're going to do is we're going to use that assign type so equals assign type that's the assign type that I want just like that okay it's going to show employee next up I also want to know when it was returned if any returned turned on in the history it's going to be returned and we're going to give this a specific color using also our theme color I also want to add some borders around that so we're going to again use our formatting cells and give it that color that we've been sticking with us on our theme and then we'll use a solid line all the way around and inside so now we have our history that's going to go here I also want to add some conditional formatting here I want alternating rows of conditional formatting and to do that what we'll do is we'll select on a large number of cells for these two columns and I'm going to go into the conditional formatting manage rules I don't currently have any rules I'm going to click on new rule I'm use a formula and the formula is going to based on two instances two things must be true so we're going to use the and equals and what is the first condition first condition there must be a value in G and the associated row G23 but that's the starting row so I want to remove the dollar sign does not equal empty so that's the first condition the second one is I want to make sure it is an even row so only those even rows mod of row to equals z those are going to be even rows I'm going to copy this cuz I'm going to use something very similar for the other one I'm going to go into the format and I'm going to go into the fill and I'm want to give it a fill effects here and the reason I'm using fill effects although I want to use a single color is because that saved color is right here I'm going to use this color here and I'm going to use the same exact color and click okay I also want to add a border onto the bottom so what I'm going to use I'm going to use that default color that we've been using I'm going to use a dotted line and I'm going to use it on the bottom click okay now what I want to do is I'm going to click okay and the applies to as fine as it is I want to create another new rule this one's going to be for the odd rows so I'm simply going to paste in that and I'm going to change the zero to a one that's going to be for odd rows I'm going to use a very similar format again using the dotted line with the same color selecting that and using that on the bottom next up on the fill again I'm going to use fill effects I'm going to go in I'm going to select a lighter color this time which is going to be this color right here and then I'm going to use that the same for both it doesn't matter which one we select it's the same color clicking okay clicking okay and then clicking apply now we can increase the rows if we want to but I think that's sufficient and so now as we type in new information we see that we have that alternating row look which is exactly what I want very good so we've got the conditional formatting set on this what I would like to do is I would like to populate these cells here with whatever information I've entered here whatever names are entered here I want to populate them and if I make changes to the employee list or the office list or the desk list or whatever it is that I want I want those names to be changed here automatically so we're going to use an indirect formula to do that we see that we have our first one located in C8 we know this is in C8 but I want to use a relative formula to make it easier on me so if I take a look at the column I see this this column 10 so if I know that this is column 10 and I know that it must equal to row eight and that means that column 11 must be row 9 so we can use that along with the indirect formula to help us so that's exactly what we're going to do we're going to use equals indirect so I'm going to type that in once we have that let's put that in direct now I want to put in the reference text so simply it's a good way to do just select the first cell and then we're going to make adjustments now we know it's C but we want to actually add quotation marks into that c and what C and so we can type in and we also need to make sure that it is the row number so what is the row number the row is eight right so how do we get that eight well we know the First Column is column 10 so we're going to use the column minus 2 that's going to get us our eight and of course we want to make sure that it is A1 style true so we're going to set that to true and we're going to hit enter on that and what that's going to do is going to return us Fred Freds if I decide to change that to Frank and it automatically will change an update that's exactly what I want okay let's return that to Fred freder since we have some data with his name on it I'd like to use that very good so now because we've used the column in the formula all I simply have to do is drag it all the way over here like that perfect now you might get zeros in yours you might this might be zero it shows zero so let's take a look at how we fix that if you're showing zeros I've already fixed it here but on yours so we're going to is going to go into the options here and we're going to go into the advance and we're going to scroll all the way down until we see show zero so it might look something like this for you click okay so if we see all the names have gone but we don't want to show zeros here so again just like I did now I'll undo it what I want to do is go into the files options here we're going to go into the advanced tab scroll down here all the way look for they show zero and cells that have zero value and unselect that and that's going to show that the go away so now we can get rid of this column so now we see that we have this Dynamic and relative here's the blank one if I decide I want to add a new one just call it Tiffany tames so now we see as we add it it's automatically added here and that's exactly what I want very good so we have that Dynamic list based on whatever's in the admin we can move this over here perfect so what we're going to be doing now is I don't want this stretched up too far we're going to reduce this here just enough to where it includes all the text Office Inventory manager making sure that I spelled it right which I usually don't very good so that's kind of the basics of that so we do need some buttons that I want to put some button sets up here we need to add any admin so let's focus on the buttons what we're going to do is we're going to insert shape on that and I'm just going to use a square shape we're going to add our new button here this is going to be for our save item we want to get this first button exactly as we want it formatted perfectly and then we can duplicate it so we're going to use I'm going to use the shape fill I'm use our form color here and I also do not want to have any outl so I'm going to click no outline I want a text fill of white so we're going to set that to White and we're going to Simply type in this called save item so we can actually save item actually I'll probably put new then save I think new should be first new item it doesn't really matter the order so once I have that what we want to do is we want to write justify in Center so we're go to the home I'm going to put in the middle I'm going to write justify it and then what I'm going to do is I'm going to go into the text options here and I want the right margin a little bit less 05 since we don't have a lot of space to work with that looks good I like the way that that looks we want to add all our buttons then we want to add our icons so I'm going to duplicate that using contrl D and I'm going to bring it over here and this one's going to be called save item save or update is fine so this one we're simply going to call it save item once we have that we also want for delete so again we're going to duplicate that using contrl D and this one's going to be called delete item okay very good so now that we have delete item we also want to have the ability to hide these columns so I'm going to use another one called hide item details so I'm going to duplicate that once more and I'm going to call this hide item details hide item details that's the text inside the button we can increase the width of that to about 13 here and we want to make sure that we have enough space so that we have something for the icon that should be sufficient we'll bring that up here very good so I like the way that looks with this one we need to increase that to about 1.1 to make sure that we have enough space for the icon everything's looking good here I also want to create buttons for our picture so I'm going to duplicate this here and I'm going to call this simp browse and we're going to drop that down to where we want our picture so the I might make this button a little bit thinner I'm going to use the font a little bit less on the font size on this one as we have limited space I'm going to set the height to about 22 on this one and also we're going to set the width to about 8 that should be sufficient for an icon or we have space so looking there that should be let's do0 2 we still have limited okay I also would like a button to clear it so I'm going to duplicate this inside our clear button to clear the picture all I'm going to do is just use an iccon we'll use the recycle bin icon so I want to make this button Square going to make sure that the width is the same as the height and that should be sufficient there and we're going to use that to be able to clear a picture I'm going to line these up to make sure they're lined up and we're good to go on that once we've created all of our buttons we want to then add in our icon so to do that we're going to use insert we're going to use pictures and place over the cells in this device and I've got some saved ones right here under the icons here and I'm just going to select the white here I'm going to hold down the control we're going to use all of these here and that's exactly what I want here so I'm going to use this edit icon 2 so we're going to insert that great so now that we've done all of that we want to set the size so I'm just going to set them to 0. 2 for now and we'll reduce them if needed so we'll do actually 0.8 because our buttons are a bit smaller very good so we've reduced the icon size now we simply need to drag and drop and place those items over so we're going to add this for the new item here we're going to use this twice actually I need this for both the delete item and I need it for a clear picture so I'm going to use controll D to duplicate that and I'm going to bring it down here I'll have to size that accordingly to about .16 as that's a smaller button next up what I want to do is the edit I'm going to be adding this in a little bit so I'm going to drag that over here the folder we'll use that here and we also need to drop this down a little bit to16 on the height as we have a smaller button so now that we've got the browse next up what we want to do is I want to use this closed eye that's going to be for our hide item details and lastly we want to save item we'll use this check so I'm simply going to hold down the control of both of these make sure they're in the middle and group them individually and the save item I'm going to do the same thing I'm going to reduce the space of this a little bit to .95 as we have a limited space and again holding down the control here make sure they middle and group them individually same thing with the delete I want to make sure that we have this centered here a little bit holding down the control as I select both middle and then group together lastly same thing with the high item details and then grouping them very good so I like the way that that looks I'm going to keep these separate here but I want this group all grouped together so I want to make sure they're evenly spaced horizontally I want to make sure that they're in the middle here and I want to group them individually and we're going to do the same thing here I want to make sure these are also grouped and sized accordingly so the browse button will increase that a little bit to maybe 0.9 so that there's enough space for the icon and we have that there perfect moving that over so as we move and Design This screen according again making sure that we're grouped individually and we're also going to group them together when they're so small like this and you can either zoom in or you can use your selection icon I've got that saved and I want to select both of them I'm going to put them both in the middle and centered and I'm going to group them together likely I want these two buttons group together so now that they're grouped together I'm going to need to hide or show this when I hide these columns here I'm going to need to also hide this so that's why and then this one we're going to either hide this button in other words if these columns are hidden we don't need to show this so we need to also give this a specific name very good when I select on an individual item I want to be able to edit that item and I'm going to click this so what we need is a background for that button so I'm going to use this here I'm just going to copy this here now I don't want to duplicate that because if I duplicate it it's going to be within the same group let me show you duplicate it you see how it's a group and I really want that so I'm going to undo that what I want to do is copy and paste so control C and then select something else and contrl V so I've copied it out of that and now I simply want to create create a square button so something like this and then we'll do25 on the width now because I created this icon after it's going to be behind it and I want it in front so I'm going to move it in front using the bring to front I want to make sure these are centered here so I'm going to use my selection middle in the center and then undo and then group them together and I want to give this a specific name called edit item button so that's given a specific name I also want to give this group a specific name we're going to call this picture group picture group and basically I want to hide and show the picture group and I only want to show and hide this specifically when I select on something we don't need to display those grid lines so that's going to be it for a cleaner look so far so good we've created the primary information that we needed to have on this I need to create some sample shapes very very important now what kind of sample shapes let's go a and take a look at my sample I'm going to bring this back in view for a minute and I've created some sample shapes these are just basically rounded corner shapes so all I want to do is just copy these just going make things a little bit quicker and I'm going to paste them into that okay there's no macros associated with them they're just sample and they've given some names so all these are is rounded rectangle shapes that have a little bit of a shadow to them nothing else you know simply just insert the shapes it's just using this one rounded rectangle so that's all we have there very good so let's move our sample back over to the other screen as we work on this we're going to focus on our admin so these are the sample shapes that are going to get duplicated this particular sample shape we're giving this a name called sample un assigned when I create all my unassigned items I want a sample there and we've got a sample that we're going to be duplicating when I create all of those assigned items that are going to go in here I want to use this sample we can keep them down here this one is called sample item shape this one again is called sample unide the text inside is just used for sample purposes to make sure that it all fits text doesn't mean anything in here as it is going to get replaced very good so now that we have that we're going to focus on our admin section right we've got some admin columns that we're going to be adding some code in so let's go ahead and put our focus on that first up each individual item has its own ID as you see we have a unique ID Associated so I want the ID of whatever selected item Whatever item we're editing to go into B2 so we'll put in the selected Item ID I want that located in B2 in the item database row is going to go in B3 and then B4 is going to be our next item id as our sequential so that's going to be it and I also want to know the picture name and the assigned on so assigned on what is the date that it's been assigned and I also want to know the picture name the name of the picture that's associated all of that is focus on our items so we're just going to do borders all the way around and we're getting with a unique color we can use this yellow so what is our item id let's say we have selected item id want I want to know the row that's associated if you followed my trainings you kind of know what's going to go on I want to know that it's r four I've got a named range that is associated with this item idea I've already created created it to help move things along to look for information on that we can look in the name manager and we can look inside our item id here and we see that we have using the offset formula is a dynamic named range starting on the header row of A3 except we're moving one row down now the reason we start on the header is because if there's no data at all it will create an error unless we use the header so that's very important so that's why we're using the header and then offsetting one row down we're not offsetting any columns so there's a comma there then we're going to use count a we want to know how many we should count we're going to use in column A again we're also using the header to count except we're subtracting one as we subtract that header and we just want a single column so when you Tab In and Out you see that we've got the dotted dancing ants around the correct amount of data that's called the item ID so if I've got Item ID I want to return row number four and that's exactly what I want to have inside B3 so we can use a formula for that equals if air and then we're going to use the match formula what AM matching that value I'm looking up is located selected ID here and I'm looking it up inside our item id named drain which is right here I want an exact match then if there's an error it's going to show empty so what that's going to do is tell us it's one but I really don't want one I want r four so to do that I want to add three and that is going to return four I also want to use the next Item ID so we're going to use equals if air we need to use if eror because if there's no data at all it will create an error so I want to use the max formula I want to know the max of the item IDs now keep in mind that item IDs must be numerical they can't be alpha numeric you can't have letters in there so I want the maximum the highest value plus one if there's an error probably because there's no data I'm going to set the first value to one that's going to tell us our next Item ID is 26 if we look inside here we see our last one's 25 + 1 is going to be 26 very good so assigned on and picture name these are going to be left a blank for now next up inside our admin I want to know the selected shape selected shape name so when I select whether it's an unassigned item or it's an assigned I want to know the name of that let's abbreviate that so we have enough space for it shape name I want to know that I also want to know what is the left position of that anytime we use drag and drop we need to know the initial position of the shape or the left position I also need to know the top position of that shape so that's going to let us know if it's been moved and I also want true or false if it's been moved so we need a Boolean shape moved and this is going to be true or false since these are all focused on drag and drop we're going to give those also a distinct color in borders around it so I'm going to give this a green color and that's going to let us know that they're focused on drag and drop I also want to know the assigned row assigned row and I'm going to go over this in just a bit we're going to use some product formula in that so we're going to give this a blue color here and all the borders around it so now that we know the sign row I also want an item list if we take a look inside or unide them I want to be able to show only very specific types of items so I'm going to type in show type here I want the user to be able to put in whatever they want want so what I'm going to do is I'm going to create here a whit space and this they're going to be basically allowed to filter out based on an item type so to do that we need a drop- down list so what I'm going to do is I'm going to format those cells and we're going to give it a border all the way around and we'll do a dotted line in the middle that's sufficient very good so what I want is a drop- down list but I want that drop- down list to be based on our item types here but I also wanted to include all types so let's add some information in here inside b113 here we're going to call this item types list now inside the first one I want to know all item types all item types so I want the user to be able to add all types and then next up I want basically a link with all these types so all I'm going to do is I'm going to hold the shift down I'm going to copy the entire column I'm going to go into the office inventory and I'm simply going to paste the links and that's important because that means any changes here so if they add a new category here it's automatically going to be added so let's just say we want to know stapler or something like that and then we can put that in here and we see that it's automatically linked and that's exactly what I want so let's give that a unique color here so we know that that's a unique list and we want to go down so we don't accidentally rewrite anything we want to look for the last row here and we see that we have all the way until let's say row here we go 34 so what I want to do is I want to hold the shift down and I want to give these unique color and some borders so that we see that we've got a unique list now what I want to do is I want to create a named range that's it's very specific to this I have already a named range on here for our types if we take a look inside our named range we see that we have item types using an offset so I've got that but what I really want is I want to have a named range just like that but I want to also include all types that's going to be the first value so let's take a look and create a brand new one and that new one's going to be based on this list right here I want it to include all item types very important so again name manager we're going to create a new one and this is going to be called item types with all once we have that I'm going to use offset equals offset here just as we did before this time we going to include this as our first then we don't want to offset any rows down or any columns over I want to count a however I don't want to count these these are all linked right so if I try to count these it is going to Simply return all them because there's formulas in these cells so it's not considered blank so what I want to do is I want to count the original list starting here so that's what we're going to count however I want to include the first one which is all types so I need to add one onto that it's a single column so we're going to use one we're going to tab over tab back in and we want to make sure that it encompasses all the data the dancing ANS you see how they include all item types and staplers and click okay so now before I do that I'm just going to copy this here and I'm going to click okay and the reason I've copied this now that I closed it I'm going to add that as a data validation right here so we're going to go into this and we're going to go into the DAT of validation here we're going to use a list here and I'm going to use equals and I'm simply going to paste paste in that named range clicking okay perfect so now when they select here they can select all item types or they can select individual types what we'll have is when we make a change to here the macro is then going to add the item types based on that that's exactly what I want very good so we're good at saving our work so far and now let's move on so we've got just about everything we need for our device to be able to add items and save items and we can just about go into the code so everything is just about on our screen let's go into some of the code and see how we actually make some of this happen a few details that I want to do item types I want like a drop down list of item types so we can go into the data validation and we can do list if you're not sure what the named range is you can use F3 it's going to list up those and we want item types we're going to click okay and we're going to click okay I've also got a named range already created for conditions again going into the data validation we're going to use list and I've created a named range again f three we have conditions and we're going to click okay and click okay all right very good so we've got the drop- down list of conditions we've got a value let's see if this is formatted with a dollar it's already pre-formatted which is nice and item types okay very good so we're good to go now what I would like to do in here is I would like to list all of the items that are unassigned based on this filter or all item types or maybe only specific item types when a user makes a change to that I want to list those item types and the original is going to come from this in items so basically what I want to do is I want to know all the items but only those that are not assigned how would we know what is not assigned well basically anything where the assigned to is empty so it's going to list these items in which the assigned to is empty and what we can do is we can use an advanced filter for that and the criteria is simply where the assigned two is equal to empty so this means empty equals just the equals but I also want to know based on the item type if this says all item types then I want to show every single item type however if we've selected desktop then I only want to show those that have to do desktop so I want to create some criteria based on what the user has selected into show type so we can do that directly inside here we created a formula here if the Office Inventory E4 now keep in mind that E4 is this cell right here if this cell is equal to all item types so we're going to type that in if then statement if equals all item types then show it so I'm actually using inventory A14 if it equals that so basically A14 is right here if it equals all item types I could easily just copy this I think it might be a little bit more clear to you as a user if I were to type in equals this so instead of here I can just put in right here in quotations marks all item types so it's the same thing and then I want to put in does not equal so basically does not equal empty meaning all types otherwise if the user has entered a specific type I want to know what that specific type is what whatever is in E4 I want to place it directly here so that means as we change this to a very specific type such as desktop only that type desktop will show here so our criteria is going to be based on item types first criteria is based on we only want desktop second is where it is assigned to is empty so for now I'm just going to set it to all item types as we don't have a lot of items so this is our criteria I want the results to come in this is our assigned I'll be going over that in a minute our results are going to come directly here unassigned results what do I want to show I want to show the item ID I want to show the name and I want to show the condition so I want all that into the results now the reason I want all of that is because I want to know what the condition is inside our sample I want to put the name here then I want to put the ID then I want to put the condition so I want all that in every single shape and then what I would like to do is I'd like to have about two rows per shape and I want to just simply list all the items down in this column as we move down here between columns d and e so that's exactly what I would like to do by duplicating the sample sheap and then adding that in so as we Loop through the results we have the item id the name and the conditions so we're simply looping through these results and we're going to use a macro to do that now when does that macro run that macro runs a few times it runs every time we delete an item every time we save an item and every time we make a change to E4 so that's going to be that change event so this entire macro is going to get triggered on the change event of E4 so let's take a look at that change event and that's where things are going to start so we're going to go into the developers and Visual Basic you can click alt f11 as a shortcut to get in that and this is my sample so I'm going to close this up and we're going to focus on our Office Inventory this is the sheet that I want to focus on I've got it commented out but I'm going to uncommented out right now here let's keep a selection change event I want to focus just on our worksheet change event to get to the worksheet change event you would just select worksheet here and you would look for change event it's going to get us this worksheet change event inside this that means any cell that has been changed I want to update the unassigned list on filter change that cell is E4 so when the user makes any change using not intersect target range and basically all this means is that the cell that the user has changed intersects with this target basically it's the same cell not and nothing cancel each other out so it's basically like a double negative and I want to make sure that E4 is not empty if all those conditions are met then I want to run a macro called on the sign item list that's the list we can find that macro of course we can right click and we go to definition it'll take us directly there we can also find that macro in this module called unassigned Macer so I'm going to double click on that and that's the first Macer that we're going to go over called unassigned list and I just walked you through the kind of a general idea of that macro now we're going to get into the details of exactly that macro I will need to use some variable here so we've dimensioned the item shape as a shape the item Row the last row the last results row and the result row all as long variables I need string variables such as item id item name and item condition so those are going to be all string variables we can bring this up here all right next up what I would like to do is we go through this the first thing what we want to do is I want to delete any particular unassigned item shapes that are already there so each time we create one we're going to give it a very unique name once I have a very unique name for every individual item I can then ensure that we can remove it based on those names so to do that we're going to run a loop we've got our item shape variable as a shape so for each item shape for every single shape inside our sheet name here called office invoice shape so for every shape within the sheet we want to check for something I'm going to look inside the name of that sheet using the instring function the instring function is going to let us know if it contains the text unassigned if it does then I want to delete it so that's going to clear all of our existing unassigned shapes so this Loop right here is going to delete all those next up we're going to turn our Focus to the invoice database we're going to run an advanced filter as i' mentioned to you we're going to take that data here all the data determine the last row make sure that we actually have data we're going to run it through criteria K2 through L3 the results are going to come directly inside here AA through a C then what we're going to do is we're going to sort them by name which kind of Handy Based On A to Z and then what we're going to do is I'm going to start looping through these and then creating those shapes and it all happens very fast so the first thing we're going to do is determine the last row of the database if the last row is less than four that means we have no data as long as we do have data we can then move on running our Advanced filter we're going to go from A3 these are the header rows all the way through column H in the last row this is the original data it's very important that our header names here inside original data must match our header names here in the criteria as well as matching those header names in our results so we want to make sure that all those headers do match our criteria as mentioned is K2 through L3 and the results are going to come A2 through AC I want to determine the last row of our results in this case it is 14 we can do that using column AA so our last results row is equal to AA and xep this is the last row of the results if for some reason the last results row is less than three we can exit the sub I'm going to turn application screen updating to false that's going to make things a little bit quicker and I want to sort our data I want to sort it as sending however if there's just a single row of data we don't need to sort so if the last result SS less than four meaning just one row of data we can then skip the sort and go right down here go to skip sort it's going to skip and go right down here however if there is more than one row I do want to sort it and I want to sort it based on that now keep in mind that when we're in with sort we first want to clear all the sore Fields I want to add a key we must call out the database again why do we need to call out the database if I've called it out here why do I need to call it out here again because we're inside another width so anytime you're inside another width the last width is the one that takes precedence so we must then call out the database again ab3 is the first item inside our sorted that is our item name I want an as sending A to Z then what we want to do is we want to set that range so setting that range is going to be aa3 our first row of data and our first column all the way to our last column and our last row of data then we're simply going to apply the sort once we have applied the sort we are ready to Loop through the data so I'm going to Loop starting from three all the way to the last row I'm then going to duplicate a shape and I'm going to add this information inside the shape so we're going to do that four the result row equals 3 to the last result row I need some variables some string variables the item ID is going to go into a string variable that's going to come from call a a our item name string variable is going to be from column ab and of course our item condition is going to come from AC now what I want to do is I want to determine the item row it's very important the row is going to be five or it's going to be seven or it's going to be nine so basically it's going to skip two why is it skipping two because each shape is going to be two rows so I need to know that row is important so if our first row is five and our second row is seven and our third row is eight and so on and so forth so this is going to take on row five this is going to take on seven and this is nine so how do I get from three to five or four to seven or five to nine so I need to make sure that we have a little bit of a formula to do that just a little bit of a MTH so we're going to set that item row that is a long variable now I know the result row let's say the first result row is three If I subtract two I'm going to get one if I subtract one from that I'm going to get zero If I multiply times two I'm going to get zero also if I add five it's going to be our first row five so what if we need the second one on row seven so let's assume we're on the second result the second one's going to be row four this is Row three this is row four so our second one is row four so 4 - 2 is 2 - 1 is 1 I know we can do that I'm just doing it twice here to help us understand what we're going through I'm going to multiply that times two so we multiply that we get 2 * 1 + 5 is 7 so this formula is going to go from 5 to 7 to 9 to 11 so it's basically going to do that very good so now that we have that the reason I've done minus 2 and then minus1 is because I want to bring this down to zero or one and then skip subtracting then what we're going to do is we're going to focus on our office invoice I'm going to turn our Focus to this particular sheet I want to take this sample called sample unassigned shape and I'm going to duplicate it and give it a very unique name so the first thing is to duplicate it shapes sample UND sign shape we're going to dupc licated and we're going to give it a very unique name we're going to call it unassigned and the item id making sure that it has that unique name because when we delete the items we're looking for unassigned so we're giving it that unique name now what I want to do is once I've duplicated it I want to work with it so we're going to use with that duplicated item I'm going to give it a left position of column D and the item row left I'm going to give it a top position of D and the item row top plus one so we going to move it just a little bit off the top I want the width to be the exactly the same widths as both column D and E I want to stretch both columns so to do that a width is simply equal to the range D through e the width the column width set to column width once we have that I want to set the height the height is going to be basically the same height as two rows so if we know that the height is going to be two rows we can set the height we use the range function D and the item row all the way through e and the item row plus one so that means two different rows starting in this row and we're going to use the height of those two rows and I'm going to use a slightly less so I'm subtracting one now what we want to do is we want to take that shape and we want to populate it with very specific text what text do we want well we want the text frame text range I want to first start out with that item name then I want a space and then I want the number and then I want the ID number then I want a brand new line we can use a brand new line using vbc LF or also new line works too and then in the second line I want the condition with a colon and a space and then I want that item condition and then I want to assign it a macro I want to assign it a macro because when I make a selection on it I want a few things to happen we're going to assign a macro called unassigned select and that's the next macro we're going to be going over here so we're giving it a macro and that's it that's all we need to do and that all happens on change here so if I simply double click here and hit enter it's going to automatically trigger that change event and populate it just like this so we see how we triggered that event and we ran the macro very good and we see that there's a macro that's assign because we see the finger as I move it over the hand and finger pointing is there and then we know that macro assigned so what macro is assigned well it's called unassigned select and that is the next one we're going to be going over now one of the most important things is when we make a selection I want to know what item has been selected and I want to put that item ID right here it's very important because I need to know the database row we need to load so whatever that item information is I want to load it here now as we see each individual shape we have the word unassigned and then 16 or unassigned and 25 so we have the word unassigned and then after that we do have the item ID so I want to extract that item ID and I want to put it directly inside B2 it's very easy to extract that all we need to do is remove the word unassigned from the name of the shape now the name of the shape that called the macro it's called application color so if I take that name and I combine it with the replace statement and I want to look for the word unassigned and I want to replace it with nothing everything that's left is simply the item id I'm going to take that item ID and I'm going to put it directly into cell B2 now that I have that I also want some information when we go over drag and drop in just a little bit it's very important to know some information about the shape I want to put the name of the shape that was selected inside B7 I want to put the left position inside B8 I want to put the top position in B9 and I want to set B10 to fall so this we're going to use for drag and drop it's very important that we track the information because I want to know if it's been moving moved or not so if I know the left position and I know the top position initially when it's been moved I simply can check to see if the values have changed if the values have changed then I know that it has been moved so we need to add that information into some cells so that's exactly what we're going to do here however there's one additional thing that we have remember this edit item button here I want to be able to select on it and edit the item individually if I select that I don't necessarily want to edit it only if the user selects something so what I want to do is I want to display this button here called edit item we can see it here called edit item button I want to display it right up at the top right of the cell so if I select it notice how it appears at the top of each one and that's exactly what I want so how do I place that edit item button we can do that right here with shapes edit item button we're going to set the top position of that button based on the top position of the shape that called it I want to know the top position and and I also want to know the left position so if I put it to the left here how do I do that if I notice the left position of this shape plus the entire width of the shape is going to put it just to the right of it so we're going to do that we're going to take the left position of the shape we're going to add on using the plus the entire width of the shape once we combine that that's going to set that left position and we want to make sure that it is visible so we're going to do all that next up as mentioned to you before for the dragon drop we need some information into cells B7 all the way through B10 so as we see here let's bring this over here so we can see both at the same time we want to make sure that B7 takes on the name of the shape that called it notice if I expand this fully we're going to see that it's called unassigned 25 or let's say 21 so I want to put the entire name of the shape in B7 we just selected something and we refreshed it so we'll go over that in a minute so here's what we want to do I want to put that here I want to put the left position here I want to put the top position here and move fals so all that information is going to go inside B8 is going to take on the left position of the shape that called it B9 is going to take on the top position of that shape and lastly B10 is going to set to false very good I also want it to be set to the front and the reason I want it to be set to the front is when I'm moving this shape and I move it over here I want to make sure it's on top of every other shape and the reason we want it a top we don't want it under anything else so we can set the Z order Z order bring ring to front so if I were to type this in again I'm going to stop the code it's running now because it's checking for drag and drop which is okay so if I retype this in Z order and then I put a space we're going to see the intelligence pop up in the list here and we see bring forward bring to front so this is the one I want bring to front I wanted to bring it to front of everything else and that's it for the shape that we focused on next up we're going to run this macro that's the one that was just running called check for move this is the macro we're going to be checking to move which will be going over it is the same macro that we're going to be using when we select something here so we're going to be going over step by step now what we want to do is I want to select on something and I want to actually edit the item so if I click on here I want the item details to load up we can use that through macro called item load so let's go into the item macros and we're going to focus on item load so this is the one that I want to use so I'm going to copy this here we'll be going over every single macro not to worry and I want to add it because I want it to function I'm going to assign the macro and we're going to paste that macro and click okay now when I make a selection on here it is going to load those item details so let's take a look at what just happened first important thing is we need to make sure that B3 contains that database row that database row let's say it's row 19 is going to contain all the information for our item it's going to contain the name it's going to contain the type the cost and the information for the picture name so all that's going to go into here so to do that we need some information so we're going to focus on that off we're going to first set B10 to true and the reason we're going to do that it's going to help us exit out of the loop remember how we started a loop right now it's looping that's why it's going through it's waiting for us to drag and drop so there a macro that's running right now as soon as I select this it's going to exit out of that Loop so that's kind of an important thing we'll be going over a little bit more in detail so the first thing what we want to do is when we load an item is I want to clear any details that are located in these fields and I also want to clear any picture that might be there this picture is called item picture every time we create a brand new item picture and I'll show you exactly how that works we're going to give it exactly the same name called item picture so I want to delete any item picture that might exist so the first thing what we want to do is clear the contents of all the associated cells I want to clear out assigned on if it's been assigned date or any picture name so B5 and B6 must be cleared out I also want to clear out all the cells Associated so H4 all the way through h12 also must be cleared out so actually we need B5 as well so B 5 and then B6 as well good so we need both of those I want H4 through h12 and I also want the history any history that might be listed here I want that all cleared out okay I'm going to bring this down here a little bit so the history is going to be located in G23 through H so I want to clear all of that out that's why we have these cells next up I just want to select another cell and the reason is when I make a selection on here it's waiting for me to drag and drop it maybe I don't want to drag and drop it maybe I want to edit the individual item if I select another cell say such as A4 any shape that's been selected is automatically going to get unselected so that's why I want it unselected so we select H4 and that's going to help us unselect any shape very important so moving on we have that let's go ahead and go back into where we are so H4 select and then what we're going to do is we're going to run a macro called item show item now this macro if for some reason it has been hidden we have some macros that going to hide it and I'll be going over those in a minute it's going to Simply unhide these columns here which we'll be going over so as I mentioned we're going to delete the picture in case it doesn't exist we do need to wrap it an on air resume next and on air go to zero so that's very important next up what we want to do is I want to make sure that B3 contains a value B3 is that item database row if for some reason that is empty that means we do not have a correct item and we can let the user know and we can exit the sub out once we do that we're going to turn off application screen updating that's going to make things a lot faster and also I'm going to take whatever that row is in B3 and I'm put it into a long variable that is the row that's associated with the database next up what we're going to use is can to use data mapping as we see here item name is located in H4 item type is in H6 8 and 10 Associated so we want to map those to our database and we've done that just here inside Row one of our database item name is H4 type 6 8 and so on and so forth B5 and B6 are the picture name assigned on very good so once we have that simply to load it all I need to do is I've got the row that's associated I need to Simply look at whatever's inside here and place it inside H4 whatever is inside here and place it in H6 so we can run a loop all the way to the last column if we look at the last column we see that the last column is column 8 our Item ID is we don't need that so we're going to start on column two all the way through eight to do just that and we're going to do it here for item 2 to eight we are going to then take the information from our item row and from our item column and we're going to place it directly in the cell that cell is located right here inside Row one in the column that is the cell we're going to place it in inside this sheet so that looping through that going to add all the data from the database into our field forms we're going to run a macro called show picture I'll be going over that that's going to display the picture and next up I want to load any history that is associated with that item right any history if there's a history I want to make sure let's see if there's any history in these I don't know if I have any history so I want to below the history and that means all the history is going to be sent here so how do we do that well basically what we want to do is we want to run an advanced filter I'm just looking for anything that with the history but I don't have much data in here so we want to add the data in here these have histories here so how do we add the information inside so it's going to be based on that it's going to coming from our this one's got a good history it's coming from our assigned database here inside of the assigned database we've got the item id I've got it assigned to assigned on and returned on so what I'm looking for is I'm looking for anything that's been assigned to a very specific item where is that item id located it's located right here in B2 so if we take a look at our signed I want to know all of the sign items from seven so to do that we're simply going to link this using Office Inventory B2 so this is our Item ID and I want to run an advanced filter for only those items that are seven I want the assigned to and I want the returned on and I want to display here and then going to take this information and I'm simply going to to bring it directly inside here so that's exactly what we're going to do let's go ahead focusing on the assigned database that is this database right here going to run that advanced filter here as mentioned before so determining the last row of that database if it's less and four we're going to exit the sub the advanced filter all the way from A3 through column D the criteria is going to be H2 through A3 a single column and our results are going to come L2 through M2 determine the last row based on column L if it's less than three we're going to exit if it's less than four we're not going to sort I do would like to sort it based on our most recent date which is going to be right here to the least recent date so if we have a long history of results I want the most recent at the top to do that we're going to run a sort and just like we did before we are going to clear the sort Fields we're going to add a key M3 this time we're going to be descending because we want the dates newest to oldest so that's going to be descending and it's going to be M3 is our starting point our range is simply going to be two columns L3 through m in the last row so that's going to apply the sort based on the return date descending then all we need to do is just bring the data over it's going to come directly from L3 through m in the last row it's going to go into G23 and column H now we see that our starting row is three here and our starting row here is 23 so we certainly need to add 20 to the last row to compensate so that's what we did last row plus 20 and that's simply going to bring over so that's exactly how we load that so selecting an item here is going to load the items and the item history very simple with that let's move into some of the other macros on the item details as we turn our Focus now we're going to start this module off with show item and hide item now we have a button here called hide item and we've got a macro that's associated with that so let's do that right now now before we hide it what I'm going to do is I'm going to select all these buttons here including this and this just to make sure and I'm going to use control one and I want want to make sure that inside the properties here we're going to set move but don't size okay I don't want those the button shrunk very good so here we see what we call this pictures group when we hide it I'm going to Simply hide these columns probably all the way through G through I I'm going to hide those and I want to tie that macro to hide item details so I'm going to select on the group itself here and I'm going to right click here and we're going to go to assign macro I want this workbook only that's fine and we're going to look to see that we want to hide item details so I'm looking for this macro here and we see item hide item and we're going to click okay so when that happens we're simply going to hide the item and it's going to hide the item so what happened there well we did a few things first thing is we want to make sure that we hide the items let's see I have two buttons with the same name let's just make sure that we do not have that I'm just going to delete that because I had it previously so that's fine so we want that now when I want to show it I want to select on here and I want to edit it which is going to show the items now how did that happen well we have two macros one called item show item and one's called item hide item item hide item is the one that we just assigned to this button here item show item is also running inside the load macro if we load it remember I said item show item we're going to call this unhide columns and buttons and that's exactly what we're going to be doing so the first thing is we're going to use item show item which is going to show that so it's very simple I don't want to run this macro if it's already displayed in other words if this is already unhidden I don't want to run it again just in case so what we're going to do is we're going to run a loop so if I use f8 we're going to run a check if column GG the entire column hidden equals false it's displayed we can see G we know it's not hidden there's no need to continue on with the macro because it's not hidden so we can exit the sub and that's exactly what I want I only want to continue this if it's currently hidden if it is currently hidden we're going to turn off application screen updating that's going to make things a little quicker and also avoid some unnecessary flashes to the screen I want that picture group visible that picture group is going to be used for both browse and clearing the picture and I want that visible so we need to make sure that is visible using visible equals MSO true if there's any item picture I want that visible as well so that's called item picture we want to make sure that's visible if there's no picture it'll create an error therefore we've wrapped it in on and resume next and on aor go to zero I want to unhide columns G through I to do that we're going to use the range G through I we're going to use the entire called hidden equals false that's simply going to unhide The Columns also this button here located right here we're giving this a name called hide item button and that particular name we want that displayed I only want that button displayed if the columns are unhidden and then we're going to turn on application screen updating back to true so that's all we have to do for the hide items we're simply doing the exact opposite we're going to turn off application screen updating we're going to turn it back on before the macro runs we're going to hide the picture group we're going to hide the item picture if it exists we are going to then hide the item details button and we're simply going to hide the columns G through I so when I click this button it's going to hide everything just like that so it's very simple so that way we don't need to hide the items and we're going to show them when we edit it and I'm also going to show them when we edit these okay very good so we've seen both how we show and hide item columns next up new item right when I click this button here I want to assign this macro to both the button and the icon signing the macro and what we're going to do is we're going to call this item new we might as well assign all the others so as we click save and update this one is going to be for both saving and updating icons and we see that it's called item save update lastly we also want to delete an item so for the top we are going to then assign the macro and that's going to be called item delete I also want to browse button so I'm going to hold down the control for both the button and the icon right click here and this one is going to be called add item picture here next up I want to clear the item picture so what we can do is I want to select on both of the back and there if we're not sure if we've selected the right items we can use our selection pane here and I can scroll down to the selected item here and I want to use both the rectangle and the pictures holding down the control here for both of those right clicking those clicking the assigned macro and this is going to be for our clear item picture right here clicking okay very good saving our work so far and now we've got those macros assigned let's continue to go the first one is the item new when we want to click item new I want to do a few things I want to clear the picture I want to clear the fields associated with that so let's take a look inside the code to see how we did that back inside the developer and we're going to move into the item new so the first thing is again I want to make sure that we're showing the item why is that important and that's because if I've hidden the item details and I want to click new item I want to make sure to show The Columns right we need to make sure that so that's also essential I want to run the Macer to show item I want to delete any picture that might exist I want to clear the associated Fields including B5 that should be cleared also and I also want that edit item visible false if I've selected something and I've got this edit item visible I want to click new I want to make sure that edit item is no longer visible so we're going to hide that button and again we're going to select H4 very good what about when we save an item if it's an existing item or if it's a new item I want to be able to save and update it so if I decide I want to change the price to 700 on this and save that item I want to make sure that I'm able to save or update that item or saving a new one so the macro that tied to that is called save or update so that's the next macro we're going to go into right here focusing on the office invoice we need to make sure that certain fields are required I want to make sure there's an item name I want to make sure that there is item type that's associated with it and probably conditions so H4 H6 and h10 are required so we're going to set that up H4 H6 or h10 if any of those are empty we're going to let the user know through a message box to please fill in an item name type and condition for saving we're going to exit the sub now I need to determine if it is a new item or if it's an existing looking in B3 we know that there's assed row if I decide to add a new item and I want it put in a computer desk here and I want to do a Furniture here and I want to do a value so we see that the item database row here is not present so we know that it is a new item so we can add a value here and a condition here and that's it that's all we need to do and we want to save it so to do that if B 3 is empty we're going to create a brand new row it's going to be based on that first available row inside our invoice database and then what we're going to do is we're going to take that next Item ID from B4 and place it directly in B2 that is our next Item ID so we can do that I also want to take that same Item ID that new Item ID and I want to place it in the First Column located inside the inventory database column A and it's going to come directly from B2 however if it's an existing item I'm going to take whatever's in B3 next up we're going to run the reverse Loop data mapping simply this time we're going to take whatever's inside this range here and we're going to place it inside our database so our database is here it's going to come from the range and going to be placed inside the database 2 to8 then what we're going to do is I want to run a macro that's going to update that unassigned list and want to update another macro that's called the assigned item refresh which is the macro that's going to refresh all these items so when we save that item we see that it's not assigned we're going to save that item it's going to add that brand new computer desk and it's going to be added inside or unassigned so if we take a look inside here our computer desk is located right here at the top so we select on it and we can edit it here so it's here great so now we have that we see that we can save new items and add them very quite easily we can also browse for a picture of the given item so that's a macro that we're going to run how do we browse for the picture well that's going to be here we've already gone over item load and we're going to call Item add picture that is the Macer that we've already assigned to this browse button so we want want to add a picture I can do that right here clicking okay and it's going to add that picture to that so how do we do that so the first thing is we're going to dimenstion the item pick as a file dialogue we're going to set that item pick to be the file picker right I want to pick a very specific file I need a picture folder now we want to place them in a very distinct folder where is it located it's located in whatever folder we have set right here called item pictures folder we can browse for that it's a very just simple browse and I've set it a named range for this called item picture folder it's on my desktop called picture folder so the name range I've set is called picture folder I want to make sure that that exists if I combine the picture folder with our pictures we see that we have it so if we look on my desktop here inside our item we see we've got the item pictures Associated here so all the item pictures are located here so if there's any new picture I want that new picture copied over into this individual folder and so to do that we want to make sure that we add the Macer to do that so for example if for some reason the picture folder we've just assigned a name to this now make sure that these variables this is our named range picture folder in Brackets this is our variable our string variable we don't want these to be the same it can create confusion so make sure that these are actually different we're putting that entire folder into a string variable if for some reason it's empty or the directory is incorrect then we need to let the user browse for the item picture and reset the variable if it's still empty or still incorrect we're going to exit the sub going to focus on that item picture now this is going to open up that dialogue we're going to give it a title called select item picture I'm going to clear any filters that might be associated with that dialogue and what we're going to do is we're going to add some new pictures we just want to add just picture files so we're going to set the filters to jpeg PNG JPEG and GIF so that's all we really want so that means when I click browse we see that we have those filters set up this is the filter we set here's the name and the filters that we've set up so it's going to automatically look only for those types of pictures so that's what then what we're going to do if for some reason the user doesn't make a correct selection we're going to skip it and we're going to go to no selection if they do make a correct selection of a picture we're going to set that picture path it's going to be based on the picture folder plus the backsplash plus just the unique name of that to get that unique name the selected item that's the full path however if I wrap that around directory in the parentheses here we see that it's just the unique file name not the full path but just the file name so if we combine the file name with our folder and back we get the destination folder that means that's the destination that's where we want to copy that into so let's just write that in here it's our destination path our original path is right here selected items one so then I just want to know if the user decided that they are going to already grab a picture that's in the path meaning if they're pulling a picture from where we want to put the picture it's already there we don't need to move it there so we're going to run a check if to select item equals picture path and go to skip copy so that means the destination and original path are the same okay so we don't want that so then what we're going to do is just in case it's there we're going to basically check to see if something with the same name as there if not we're going to kill the path we're going to delete it now what we want to do is we want to copy it from the original location where the user has browsed into the destination which is the picture path we're simply copying the picture to the new location that's it next up what we're going to do is I'm going to set that file name into B6 I want to save that and I want to save it directly inside here so we're going to put the name the director of the picture path we're putting that in B6 I want to run a macro to show the picture and I also want to run the macro to save and update that so to show the picture is going to be right here so this macro is basically the one to display the picture now you might be asking why not just show the picture inside the macro after we add it and to show the picture after browsing well the reason we want a separate macro to show the picture is because we're going to run this macro both when the user browses for a new picture or when the user load loads an existing item so when we load it we're also going to show the picture here so let's take a look here so I want to make sure to do both so here we see show the picture here so I want to run it twice so inside that show picture first of all I want to make sure that B6 B6 of course is our picture name our picture name combined with our picture folder located here is going to be that complete file path to be able to create that picture and load it in there so we need that picture path we want to make sure that B6 does not equal empty and we want to make sure that the picture folder is not equal empty so both of those things must be true we're going to set the picture name to whatever is in B6 and the picture path is going to be combination of the picture folder that named range along with the backslash and the picture name so that is the full picture path I want to make sure that that is accurate so as long as the directory of the picture path here does not equal empty then we can load it in we're going to use our sheet here Office Inventory picture insert picture path and we're going to give it a very specific name called item picture then what we want to do is I want to place that picture and I want to place it inside this section here I want to place it centered within columns G and H horizontally and vertically I want it centered between rows 15 and 19 so to do that we're going to focus on the picture that we just added called shapes item picture I don't want that skewed so using lock aspect ratio equals Ms true that'll lock it then if the width is greater than the height I'm going to set the width to a maximum of 80 otherwise we're going to set the height to a maximum of 70 then I want it centered so we're going to start the position off that left position based on g15 then we're going to add I want to use the entire width of column G throughh and I want to subtract the width of the picture I want to divide that by two and what that's going to do is going to center it horizontally I also want to center it vertically within rows 15 to 19 so to do that we're going to set that initial top top position based on the top position of g15 we're then going to take the entire height of all those rows 15 through 19 we are going to subtract out the height of that picture and we're going to divide that by two what that's going to do is going to Center that picture inside that range that we've set that's all we need to do to create that picture so that's exactly what we've done to clear the picture is relatively simple all we need to do is clear whatever's in B6 and we need to delete the item picture here so to do that B6 clear the contents of that we're going to delete the associated picture now one thing you can do is basically so if I select here it's going to clear that picture and clear that now it will come up again if we don't save it if we save it it's going to save it without the picture if we don't we can then simply added and again by editing and notice it came back the reason is that we didn't save those changes so that's kind of a way in case they decided they didn't want to delete the picture however if for some reason they clear it and then they save that item the next time they edit it that picture is now gone very good so we see how that happens through clear item picture item delete is relatively simple when we delete an item I want to know the database row associated with it we're going to find that row and we're going to delete it from here so that brand new one computer desk maybe we want to delete that all we need to do is just click delete item we're going to get a warning that says are you sure you want to delete this Office Inventory item if I click yes it's going to be deleted the information is going to be cleared out and it's going to be loaded up so how do we make that happen well first of you want message box are you sure you want to delete this Office Inventory item if it's no then we're going to exit the sub with the Office Inventory if B3 is empty that means it is not currently saved we're going to go to not Saed if it has been saved I'm going to take that database row we're going to put it into a variable called item row then inside that inventory database we're going to delete that entire row then next up I want to refresh the unassigned list and I also want to refresh the assigned item assigned items is macro that we haven't gone over and it is this macro that's going to create all those shapes and color them accordingly then what we're going to do is we're going to run the macro to create a new item which is going to clear and then we're going to hide this is questionable whether you want to hide or not hide this so we've hidden The Columns here we can edit another one either by clicking edit or we can select here and it's going to show those items again very good so we see how we can do that and of course deleting item that is the last macro within our item macros next up let's focus on our assigned items once they're assigned I want all those assigned items to be located here we can close this out and show that full screen let's click that here okay so what we want to do is I want to show all of these assigned items into the individual in this case employees how we going to do that again we're going to run an advanced filter but a slightly different Advanced filter we're going to use that same data our Office Inventory but this time we're going to focus on only those that have been assigned so we want to make sure the assign to is not empty so anything in which the assign to is not empty we want the results to show up here so our criteria is assigned to does not equal empty this is our criteria our results are going to come through here just like we did we're going to Loop through all the results ensuring that we have results I don't necessarily need to sort them in this case but what I want to do is I want to create a shape based on a sample shape this time the sample shape that we're going to use is this shape right here this is our sample shape and it is called sample item shape so that's the item that we're going to create and we want to make sure that we duplicate that it's just the drag and drop macro that's running which we're going to go over in a moment so what we want to do is we want to create the assigned items refresh that is the macro that's going to refresh all these shapes of those assigned items so the first thing is we need to Dimension a bunch of variables I'll go through those as we go through the macro again item shape is our shape variable what we want to do is I want to remove any icons and any item shapes that might exist here so to do that we're going to create a loop that is going to simply delete any existing shapes I'm going to look for the word sign anything that's called assign I'm going to then delete it so to do that we're going to run the loop first of all assign item if it's greater than zero we're going to delete all those assigned items I also have something called icons now icons are shapes that we've displayed but they are actually hidden so if I select on the station Pane and I click show all and they're right about here they're all kind of bunched together but that's fine it doesn't matter where they're located as long as they're located here but they're hidden so we see that we have all these icons that we've loaded into this sheet here and we you can just place them we see that the icons are big in here so we have all of them in the sheet so these are the icons that we're going to be used to let us know what kind of item type and we've hidden them because they're not necessarily important to be displayed unless they are displayed within the shapes so where do we get these icons from well we've got a list of names for the icons here and I've got a folder here for the icons so if we take a look inside the folder here and that inside a specific folder these are item pictures so I've got the icons here so these are all the icons we saw these these black icons so the name accessory Furniture desktop so they're all located in this folder and we want to make sure that we can have those displayed so to do that we need to map our icon folder along with the icon name so when we combine the folder with the name we get the full file path of the icon if I decide to add or update any of these icons here I want to make sure that our sheet has an updated list so I want to delete all these icons remember they're hidden normally I just showed them for you so that means any icon that contains icon and underscore I want to delete so we're deleting those shapes as well so for example if I decide to run this up until this point and we run this we see that all the icons are gone and all the shapes are gone so we've looped through the entire sheet and all of those are gone okay great so we've removed anything that contains icon meaning deleted anything that contains a sign item and deleted it that's going to clear it out next up what I want to do is I want to clear any item quantity data now also what I want to do is notice that we had multiple items here and I don't want them on top of each other so if Fred has three items I want the first item to contain these cells the second item here and the third item here so basically I don't want them to overlap but to do that I want to keep track of how many items Fred currently has as we Loop through if Fred has one it's going to go here if Fred has two it's going to go here and so on and so forth so all the way down here into row 100 let's just put that right up here I'm going to put that right here and I'm just going to color these rows notice we see some numbers here and all the way until X and I'm just going to give that a kind of a different color just to help notate that so we see here that inside this we want to clear that out so all the way from j00 all the way to I think it should be around X or something it depends how many you have all the way through there I want to clear anything out as Fred or whoever gets added we're going to increase this number so this number is going to tell us where to put it if we have one item it's going to go here two item three four item so on and so forth so we need to keep track of that number so clearing that out so if we tab through here we see that we've now cleared out that item so we take a look back in here we've cleared out all the data inside there and that's very important as we reset that and I'll go over exactly how that's going to help us determine the last row let's run this macro here we're going to determine the last row of data as we determine it we need to know the last row to run it through our Advanced filter and get the results here so the last row is very important we're going to run Advanced filter from A3 through H just as we did before our criteria is a single M2 through M3 just basically making sure the assigned two contains a value and the results are going to come Q2 through X2 next up we want to determine the last result row using column Q that's a required Item ID so that's going to get us our last result row if it's less than three we're going to exit this up as we have no data going to turn off application screen updating and we're going to Loop through the results from three to the last result row and I need a lot of information inside some string variables such as the item ID from Q or the item name the item type condition all coming directly from here so we need to have all that information we also want to know the assigned to in v and the assigned date in W next up what I want to know is the assigned two I need to get the column we have assigned two here what I need to do is I need to know what column is it going to go into is it going to go into column j k whatever so how do I know what column is going to go into what I'm going to do is I'm going to look it up we've got a named range here called assigned to so let's go into that and look at that formulas name manager and we're going to click assigned two which is right here inside that we see that it's a named range based on that so if Fred is found inside this list I'm going to look for Fred or I'm going to look for John and I need to know what row it's found on if I know it's found on row eight then I need to know what column to place it in Fred is in column 10 so if it's found on Row 8 and I add two I know exactly what column to place it in so here's what we're going to do we've already extracted it into a string variable from V so I've got the name into the variable called assign two now what we want to do is I want to know the column that's associated if it's not found it could create an error so I've WRA it in on and resume next and on eror go to zero so what am I going to look I'm going to look for the assign two and I'm looking it inside this named range called assign two it's from our admin screen if it's found I want to know the row that it's found on let's say it's Fred Fred's found on row Row 8 if I add two to that then I know the column to place it in we're going to run a check if item column equals z we're simply going to skip the item actually I'm going to pull it less than 10 just in case less than 10 then skip item because let's say it's not found and it's zero we're adding two so the item column equals two so that could be an issue so I'm going to put less than 10 if it's less than 10 we're going to skip it I could put two if equals two as well but less than 10 is a little bit safer if it's less than 10 that doesn't exist now we want to know the item row so I know what column to place it in but what about the row the number one is placed on row four number two is placed on row seven the third one will be placed on row 10 so we see that it's an increments of three how do we know what row to place it in well the number here is going to help us if I know that our next one if Fred has another one it's going to be placed on row 10 we're going to use this number to tell us so that number is located in the column and the row so it's the the column that's ow and the row is 100 so we're going to look in that item column and I'm going to look in row 100 and I want to know the value of It Whatever the value is I'm going to multiply it times three and I'm going to add four so let's say it's the first one there's no numbers in there yet the first one this is going to be zero because we've cleared it out 0 * 3 of course is zero we're going to add four so we know that the first one is going to be placed on row four then what we're going to do as soon as we add it we're going to increment this actually I increment it down here probably should be done next here it is every time we add one I'm going to increment it so when we add it it's going to increase by one so the next one it's going to be one so what does that mean here's our formula let's take a look again if it's one here it is 1 * 3 is 3 + 4 is 7 so that means we know the next one's going to go item row is seven so we know the item column and we know the item row once we know that we know exactly where to place our shape I also want to extract the color if we have the item type as a printer I want to give it a pink color or a laptop I want to give it a green color so I want to know based on the type what color so what we have is the types here so again let's look at the named range here that I've created the name manager and again we've called it item types so if I look up the item types and I find desktop on row 9 if I look inside column G and row 9 I'm going to extract that color if I extract that color into a string variable I know exactly what color to color that shape so that's exactly what we're going to do but first we need to find that type and we need to find it in this list so we're going to use that if it's not found again it could create an error so we've wrapped it in on and resume next that type row again we're looking in this named range called item types we're looking for what that item type that information that we extracted from column S I want to get the row if the type row is zero then we're going to skip the type which is going to go right down here if it is found we're going to get that type color that's the string variable where am I getting it I'm getting it from column G and the type row and I want to extract the interior color of that cell I want to take that inter your color which is a number and I want to put it directly in here I also want to extract the icon name that's very important the icon name is going to come from column F so we can extract that icon name from F in the type row and we've got the icon name perfect so now that we've got the color and the name we can continue on we're going to turn our Focus now to our office inventory sheet here first thing we want to do is we're ready to place the shape so we need to duplicate our sample item shape so here we're going to set the icon shape to nothing we want to clear that variable and we're going to set the icon shape what's going to be the icon and the icon name setting the icon shape does it exist or not if it doesn't exist on the sheet we need to create it so this is kind of a nice new feature that we've done if you followed a lot of my videos this is a great way we've had some issues with bringing over icons from admin screens but I like this because it's faster it's more accurate and less error prone so what we're going to do is we're going to look does the icon already on the sheet if it's not if the icon shape is nothing then we need to insert the icon in the shape we're going to use that icon folder and the backslash and the icon name this is the full path of that icon as I showed you combining the folder which is located right here which is our icon folder and the icon name is going to get that full file path so we're going to use that inside the code we do want to make sure that it is an accurate path so we need to check it if the icon path does not equal empty and the icon name does not equal empty then we can insert it using pictures insert icon and the icon in the underscore very unique name and the icon name then what I want to do is I want to set that variable this is a shape variable we're going to set that variable to the shapes icon and the icon name that brand new icon that we have created we only need to do this once every time it's refreshed then we're going to focus on that icon shape I want to lock the aspect ratio I want to set the width to 12 and I want to hide it there's no reason to show this particular icon this is the icon in which we are going to duplicate to create other icons so we're simply creating this and hiding it it doesn't matter the location so we're not concerned about the left of the top location that doesn't matter it's simply hidden great so we've created the icon for this and we know it's available to us next up now we're ready to duplicate that shape so now we're ready to go in here and we're going to take this sample shape now that we've ensured that we do have a correct icon for that sample item shape we're going to duplicate that and give it a unique name called a sign sign item and then that item id making sure that it is unique for every given shape with the shapes sign item and the item id we're going to focus now the left position is simply based on the item row and the item column left + one so we're simply placing it the top positions based on the item row and the item column setting the top position the width is simply equal to the width of the column the height is simply equal to the height of three rows so to do that the width is simply the item row and the item column the width of that and that's slightly less just so it doesn't take up too much space the height is simply office invoice sells the item Row the item column height times three so we're taking the height of a single row and we're multiplying it times three so it's going to be three rows and we're just taking a little bit off so that it's some gaps and spaces between them next up we want to set the text of that shape and we see from our sample we have the name of the shape the number on a new line we have the condition and also on a new line we have assigned the date that it was assigned so we can see here the text frame text range text name here is going to be that item name then a space then the number then the item id then a brand new line then the condition then a colon then a space then the item condition and lastly a new line and the fact that it was assigned and then the assigned date so we have all that information I also want to color the shape as long as the type color does not equal empty we are going to fill that shape with a four color and RGB of the type color so we're coloring the shape based on the type so here we can put in color shape based on type Okay so we've set that and now we're going to assign a Macker to that it's going to called select item that's the next Macker that we're going to go over down here so that's the Macker that we want to assign assign macro next up we want to add that icon as long as do it exists if not icon shape is nothing meaning does icon exist does icon exist if it does what do I want to do I want to duplicate that icon remember it's hidden but we're duplicating it and we're giving it a unique name called icon and the item id then we're going to focus on that this I don't necessarily need to I've set the height already and I've locked the aspect ratio so we don't need to do that really we're going to set the left position based on the left position plus the width minus 15 what does that mean that means I'm going to set the left position based on the left position of the shape plus the entire width of the shape minus 15 if it was just plus it'd be way over here so I'm subtracting 15 is going to place it right right about there so that's going to place that icon right in the shape next up what I want to do is I want to place the top position based on the top position of that cell plus two so we're just going to set the top position meaning it's going to be slightly down I want to make sure that it is visible remember when we duplicate something that's hidden the new one is also going to be hidden so it's important that we do make that icon visible next up what I want to do is if there's an icon I want to group both of the shape and the icon together into a single group as we see here we see that there's a group here they're both grouped together and it's called assign item group s so we want to create a group to do that we're going to use shapes range array so what are the two shapes called assigned item that's the first shape within the group we want the second one's called icon ID so these two we want to group together and we want to give it a name called assign item group else what does this mean no icon if there's no icon at all I also want to rename it so basically I want to take this shape and rename it assign item name equals a sign group so that means whether it is a single item or whether there's two items they're both going to have the exactly the same name and that's exactly what I want very good and here's where we increment the column count so we know how many are there in a single column next up I want to clear some variabl before I Loop to others I want to clear the type color the type Row the item row and the item column all those variables should be cleared out before we move to the next item so we simply Loop through all of the results to create that so We're looping through all the results here to create our functionality here great so now we see how we've refreshed that and we see how we've created all these shapes and the icons and the text within them but now when we select them we want something to happen select item this is the macro that's associated so that means when I select the shape I want something to happen well what do I want to happen well I want to load the item details in here and I want to get ready for our drag and drop so how do we do that first of all what we're going to is we're going to set that item ID it's going to be based on assign item we're going to use the application color remember I want to place whatever we select I want to make sure that that item id goes directly inside B2 so how do we make that happen well we take the name of whatever we've selected we're going to remove the name and what's that that going to leave us that's going to leave us with the item id we're going to put that into a variable right if I select here and we see the name here is assign item 14 if we remove assign item it's going to leave us with 14 we take that 14 and we place it directly inside B2 which is going to be the next step so B2 equals the item id then we're going to run the macro item load which you have already seen next up what we want to do is remember we're going to either drag and drop this one or this one so again we need to populate those drag and drop cells I want the name of the item to go here I want the left position the top position and the shape move so all those drag and drop Fields need to be populated B7 it's going to take on the name of the shape B eight is going to take on the left position B9 is going to take on the top B1 is going to set to false also what I want to do is I want to select the group of shapes meaning when I select this I want to select the entire group of shapes not just the shape that they selected but the group of both so using that group item select and I also want to make sure to bring this entire shape to the front so that we drag it it's always on the top of other shapes then we're going to run a macro called check for move now item check for move this is the the same macro remember when we had the unassigned macros and we had a unassigned select item check for is the same macr so whether we're selecting this one here or we're selecting this one here it is the same macro in which we are checking for move now this macro is going to help us drag and drop so whether we're taking something and we're reassigning it to something else or we are taking something unassigned and we are then assigning it dragging and dropping it to something else we want a single macro to do all of that however when I make a selection on something I need to know whether it's been assigned notice that this has been assigned already I want to know what the row that it's been assigned on that's very important that's the last formula we're going to do when we click the assigned database I want to know that this has been assigned on row 25 notice and why is that important because if I decide I'm going to drag it over to the unassigned here I need to know where to put that returned on date I need to know what row so what I need to do is I need to look for where item 25 is but I also need to look where returned on is blank so wherever item number 25 is and wherever is blank I want to extract that row and I want to put that row right here now if this is empty that means I know that it has not been assigned yet right so it's not there so how do we do that so to do that what we're going to do is I'm going to add that information in directly using a formula so we're going to use equals if a and I want to use a sum product formula so sum product is the formula that we want to use the first what we want to do is I want to determine what is the assigned item ID so I've got a named range assign Item ID that is a named range for the assign that's based on all the IDS and it's going to be equal to selected ID so this is the assigned Item ID that is all the IDS located inside column of our signed database so that is the first criteria and we're going to multiply that what is our second criteria our second criteria is we want to make sure sure that our again I'm going to use a sign here I want to know the assigned returned on well that must be blank so we're going to use the returned on it's equal to empty that is it and I'll show you those named ranges in just a bit but I also want to extract the row I want to know the row what row is it on so I want to multiply that times the row of again we're going to use the assigned item id that's it so let's update that so we've got parentheses here row of that so we don't need that here very good so now what we want to do let's do get rid of this so if there's an air I just want to show empty perfect that's exactly what we want very good so we see how we've got the formula so that's going to tell us row 25 perfect that's exactly what I want so for example if I select something else this one selected item 23 if we take a look inside our assigned database we see that 23 is not here or if it's 10 you see it's been returned so it's not here and showing empty so that means we know that this is unassigned we know that this is assigned because it's giving us the row perfect so now if I drag drop this over here to this section we know exactly what row to update so you see I know that on 320 we've now updated our returned on so that's the last feature of that so I've got three different features using this again dragging unassigned to being assigned reassigning something or unassigning something so all of those markers are going to take place very cool so let's get into that macro right now and that is inside our module called drag and drop macros here's what we're focusing on and we're going to focus on item check for move so that's the same macro that we saw for both items first of all we need to some variables I need to know the destination column the item Row the item column I don't think we're using destination column here sorry about that just an extra variable we don't need item column item row count delay assign row as long that's important the item id the shape name and the assigned to so what we're going to do is I need to make sure that we actually have a database row if I've selected an item obviously we need to have a focus on our database row if B3 is empty for any reason we can exit the sub so we want to make sure that B3 contains a value if it does what we're going to do is we're going to take the item id I'm going to put that into a variable called Item ID I also want to know the item row that's B3 so we want that I also want to know the shape name what is the shape that's very important because I want to know what name was selected that's going to be of course located in B7 the name name of the shape that call the macro so we're going to grab that from B7 and we're going to run a count DeLay So this is going to give the user time to actually move it we want them to be able to do other things while it's running so we're going to use do events I want to exit the loop as soon as B10 becomes true as soon as B10 comes true we know we've added it so B10 is initially going to be false when I make a selection as soon as it becomes true we're going to exit the loop we're going to go to something called end macro we're going to focus on the shape name remember I said I need to know if the left position is changed from B8 or the top position has changed from B9 then we know that they' have moved it so we're going to check if the left position of that shape is different than B8 value or the top position is different than B9 then we know the shape has been moved we need to make sure that it's been moved to the proper area if they move it to the left column C or they move it above row four we need to make sure that they haven't moved it into the wrong place so we're going to check that if the left position is less then D1 left then we know right they can't move it into column A or B so that's too far or the top position is less than row four then we need to let them know to please move the item shape to a specific and I want to use a sign type could be specific desk specific office specific employee whatever that assign type is or to the unassigned areas and we're going to go to end drag and drops it's going to drop it all the way down here it's going to refresh everything I'll be going over that in a moment now what I want to know is I want to know is the sh shape moved within the item detail area so if the left position item detail area is here if for some reason they move the shape into here between columns G or H I want to let them know if they decide to move a shape in this area it's not going to really help please move the item shape to a specific employee or to the unassigned area on the left so we're letting them know they can't drag it inside here so how do we know that well we're going to check that left position if the left position is greater than F and it's less than J greater than F here or less than J here we know they've moved it into the wrong section we're going to let them know with that message box that you just saw and we're going to go to end drag and drop it's going to drop that down to the bottom of the macro what about if it's moved to an unassigned area how do we know it's move to the unassigned area because the left position is greater than D and the left position is less than column F then I know they've moved it to an unassigned position however if it's already unassigned and they decide to move it around and then move it back into the assign we do need to let them know the item is already unassigned okay so they can't really do that we need to let them know then we'll refresh everything else so if the left is greater than D1 or the left is less than FS1 then we know it's been moved into the unassigned area but I want to know if b11 is empty we know it's not been assigned right so we know anytime they select on something that's not been assigned b11 is going to be empty anytime they select on something that's assigned here b11 is going to contain that row number so that's how we know if B1 is empty then this item is already on a sign go to end so we're just basically letting them know it's already on a sign however what if b11 is not empty that means it is already assigned and they want us to unassign it by dragging it in this section they're unassigning it all we need to do is take that row there and I want to update the current column D to The Returned on and I also want to get the original database so it's no longer assigned so we want to clear out column F and column G assigned on of that particular item so we want to do those two things so an existing assigned row return the item so the assigned row is what's located in b11 I want to know that row that's been assigned put that into a long variable and also I want to set the return date assign database D value equals date so that means that we're simply adding the current date into column D showing it's been assigned very good and we're going to go to end drag so that's all we have to do for that check for an empty assign to if for some reason it's empty why would it be empty if I've tried to drag something let's see let's go back here and I just decide I want to drag it to an empty I want to check Row three please make sure to drag that into a specific employee so notice that there's nothing here in column T so we want to make sure to let them know how do we know that well what we're going to do is I'm going to look in row three and I'm going to look at the top left cell column if the value equals zero then we know no assign 2 so then we're simply going to let please make sure to drag item to a specific again assign type is variable it could be desk office whatever and we're going to go to end drag and drop what about when it's moved to an assigned area so that's going to be reassigned so basically when we're taking some shape here and we're reassigning it to a different location how do we deal with that well we know that if the left is greater than J1 then we know they're moving it to an assigned area we're going to set the assigned to to whatever's in row three in that top left so so this is the assigned to name assign to name okay so we put that into a variable called assign two then what we want to do is we want to know is it currently assigned so set the return date if B1 currently has a value we know that it's already assigned if it's already assigned to somebody we're simply moving it over like if I decide I'm going to move it back all we need to do is make some changes to that so how do we do that so in this case what we're going to do is we're going to set the assign row to whatever's inside B1 and we're going to update the return date so basically here's what we want to do if Fred moves it to John it's like unassigning it and assigning a new one so basically we're simply unassigning it at this point we're unassigning it and then we're going to assign it new right down below as we move the code so we're simply setting the return date now we're making the update so how do we do that so that means like as I just said John James if we look in the assigned database we created a brand new row same item id John James has now assigned it on 320 so we see all also inside our invoice database if we look at item number 20 here we have now also added the assigned to is John James and the assigned on is 320 so we've updated that as well so to do that the assigned row is going to be based on that first available database row so that means we need to create a brand new assigned database that first available row here and we need to add the item id the assign to and the assigned on date in columns B and C respectively so a will take on the item ID B will take on the assigned to C will take on the assigned on date we also want to update the item database remember we also want to update columns f and g here with the assign two so we can do that here F and the item row is going to take on the assign to G and the item row it's going to take on the date so we're setting the assigned on date and we're going to go to and drag and drop so that's basically how we handle each one of those individual situations now we see we saw that a lot go to and drag and drop during this regard regardless of whether they come from here or here or here or whatever it's going to skip directly down here with this we are going to set B10 to true what this does is it automatically stops the loop from continuing on and that's very important how does it stop the loop because here we're continually checking if B10 equals true we're going to go to the end macro it's going to drop it all the way down to the end of the macro also assigned we're going to refresh our items refresh any assigned items and we're going to refresh the unassigned list so we're refreshing both the assigned and the unassigned and that's very important because if I take something that's been assigned and I unassign it I need to update both of those lists or if I take something that's been unassigned and then I create an assigned I want to make sure to update both of those lists so both of those lists must update accordingly very good so that's all we have to do that is it that's very very cool so we check for move we see how the same macro can handle unassigned and assign items and also moving items individually we also saw how we could create colors based on types unique icons we can edit individual items we can also hide item detail or we can show it either by selecting here or we can also show the item detail by editing an individual item here so lots of ways to do that we can browse for a picture or clear the picture we can also create a brand new items and we can also have the item history here displaying the entire history of a given item this has been a really fun training thank you so much don't forget make sure you subscribe comment below and ALS Al if you do want to support this channel so many great ways to do it whether it's on patreon or our YouTube memberships you can also pick up our great 300 workbook template file I've got that on sale this month and also our mentorship course our Flagship mentorship program where I teach you how to define design develop and deploy your own Excel based applications for passive income while I created an incredible and comprehensive accounting application directly in the course that is an amazing application and course that allows you to supercharge your Excel Journey thank you so much for your continued support and we'll see you next [Music] week

Channel: Excel For Freelancers

Views: 9,035

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, excel vba tutorial, excel vba programming, office inventory manager, inventory management, inventory management software, inventory management system, inventory management in excel, inventory, excel inventory management, excel inventory, office inventory, office

Id: EqOImXheBgY

Length: 108min 8sec (6488 seconds)

Published: Tue Mar 26 2024