Excel VBA Hack: How To Use Data Mapping In Any Userform

creating user forms that are designed to add edit update and save information to the database is not difficult when you learn my data mapping Secrets hi this is Randy with Excel for Freelancers and in today's video I'm going to show you in just a few steps how to create your own user form and map the data directly to the database we're going to create it all from scratch including the user form and all of the code itself it's going to be in a great trading I cannot wait so let's get started all right thank you so much for joining me I've got so many questions on user forms and how do we map the data now we've Incorporated this in other videos but in this video I am going to dedicate it specifically to creating this user form and mapping the data to this database you're going to be able to add a new product save it cancel delete and I'm going to show you how to automatically update data simply by Chang changing the field which I've never shared before so it's going to be a great training you won't want to miss it if you do like these trainings I create these comprehensive application videos each and every Tuesday and basic VBA trainings each and every Saturday make sure you subscribe don't forget to click on that notification icon Bell and of course if you would like this free template I make sure it is available to you using the links down in the description all you need to do is look for the word download click the link put your name email and I'm going to get that sent over to you absolutely for free and if you do like these trainings there are so many great ways to support the channel and what we are doing here each and every week one of them is of course with patreon using our patreon platform I bring you brand new and exciting updated videos each and every week so that's based on your suggestions your ideas and your feedback each and every week add on to these additional trainings with new code new features and I'm going to also focus on a different additional details that are not covered in the original training and of course there's an updated application a PDF code book and a whole lot of other benefits that you get as a patreon member so make sure you get on there and it's just a few dollars a month and a great way to support us all right let's get started on this training here's what we're going to be doing I've got a database of products in this we're going to be creating a user form I want to edit a single product so I'm going to click on this edit we're going to be able to edit that product and be able to make changes and save it we'll be able to delete an item we're going to be able to browse and add picture onto that and also we're going to be able to add new products onto that now that's all we're going to be doing but we're going to be doing everything from scratch and that's absolutely all the code all the user forms in every feature so we're going to get right to it what I'm going to do is this is a sample workbook I'm just going to save this and we're going to close it now all I have inside another workbook is just the datab base here so very very simple nothing else here there's no other additional now to get into the VB if you don't have a developers tab here all you need to do is right click click customize the ribbon and you make sure that you have the developers inside that developers tab is our VBA editor which we'll be using it to get into the VBA alt f11 so there's some things that we want to do first okay we've got product IDs and information about a given product I want to create some named ranges that are going to help us when we create a new product I need to know the next available product so in this case the next available well we got way too many here here so let's take a look at this let's create some unique items for that I want to create a unique one we started it off and so to do that what I'm do is I'm just going to update this one right here where it stops at 65 and we're just going to Loop up to that all I need to do is double click on it's going to create a unique one and that's exactly what I want I want a unique ID there we go up to 625 so what I want to do is I want to create the next available one now we can do this I get this question a lot how do I create a unique ID for an invoice for a product that's what we're going to show you today we're going to do it all with VBA but what I want to do is I want to create a named range that will be used for these product ID that named range will grow as our products grow so how do we do that well first of all we're going to go into the formulas and we're going to go to the name manager and we're going to create a brand new one and I'm going to call this product ID so P oore ID I'm going to tab over and the first thing what I want to do is I want to write an equals and then offset what that's going to do is going to create a new formula that will help us identify that range and I'm going to go all the way up here and I'm going to select on the first header row now this is actually not going to be included inside our name range but we want to select on the header because if for some reason everything gets deleted we do not want to create an issue with our named range I'm going to use a comma and I'm going to offset that one row below because that's our starting point I don't want to offset any column so I'm going to use a comma comma no offset on the column now I need to determine how many rows that we have inside our named range so I'm going to use count a for that also again include the header we're going to go all the way down to the bottom we'll just use a large row so I'm going to use 9999 for that okay and again I don't want to actually include the header in the count but I do want to include it in the formula so I'm going to use minus1 that's going to exclude that header I only want to return a single column so I'm going to use one so that's our formula I'm going to use the tab key out and I'm going use shift tab back in I'm going to look the dancing ants and I want to make sure that they go all the way to the bottom of our data so that it encompasses all of our data we have that so our dancing ants go all the way around so that means it is correct clicking okay very good I also want to create another one I want to create a product folder now I haven't created that product folder yet but I want to create a folder in which our products are going to be located our product pictures so what I'm going to do is I'm going to just call this picture folder here and directly inside l1's where that picture is going to be but I want to create a Mac that's going to browse for that folder and then whatever that folder path is I want to put it in L1 I also want to know the selected row so I'm going to type in selected row when a user selects a row I want that row number to show up in L2 so I'm going to WR justify those there we go and so that's where I want that's the only information that I need now what I'm going to do is I'm going to add an icon here and that icon we're going to use VBA to browse for that picture folder so what I'm going to do so going to click insert sometimes we use pictures as icons this time I'll just use the inherent icons so to do that we're going to select on icon I'm just going to write in folder here and I'm going to look for a folder anything we do and we're just going to click insert I can give that a color according to the theme if I want but it's not that important and we can just give it a this color here okay I'll make it smaller and basically when the user clicks on this I want them to be able to browse for a particular folder so that's fine right there so when they click on that a macro will put that folder in there another thing that I want to do is I want to have that selected row so when they select a specific row that row row is going to go directly inside L2 so for example if they've selected eight I want that eight to go here but I also want that selected row to show up here in a different color we're going to use conditional formatting for that so to do that we're going to go into the home I'm going to go into conditional formatting I'm going to create a new rule I'm going use a formula and here we're going to say equals whatever is in L2 is going to be equals the row and then we're going to use the open and close parentheses I'm going to format that and I'm going to use a distinct color for that I'm going to give it a bold and a white font which is very different than what the color is and I'm going to give it a fill I'm going to give it a gradient fill I'm going to use our theme colors any one of these Blues are going to be fine darker so that way we're going to have a white font on a dark color and that's going to be very different it's going to look like that once we have that okay and I just want to update the applies to so we're going to go back into the conditional formatting we've created here but I want to make sure it applies to it now I've got conditional formatting already for the alternating rows I'm going to use that same applies to range to use inside our conditional form so I'm just going to copy and paste past that and click apply so now when we select here VBA will automatically place that row when that row changes here we can see that's automatically going to be updated which is going to be super helpful saving our work so far and that's going to be pretty good also what I want to do is when I make a selection on a row I want a little edit icon to show up here and that edit icon is going to be nice cuz I'm going to select that edit icon and we're going to show that brand new user form that we're going to be creating so we might as well do that now I want to background for that so I'm going to click shape just a square shape and it's going to be small and then we're going to set our format to that same fill that we had which is about right here we don't need an outline on that but I want an icon to show up here so what I'm going to do is I'm going to click insert icon and we can use edit here or pencil would work just fine this is the one I'm going to use I'm going to insert that but I don't want it black I want it white so we're going to go into Graphics format here Graphics fill and then we're just going to give that wipe okay I'm going to make it smaller and I want this to appear directly on top of the shape that we created something like this we can zoom in here so that we can see that and we'll just make it smaller and then I want to Center them both so I'm going to use my selection tool and I'm going to wrap both of them and I'm going to Center them in the middle and Center them in there now I'm going also going to group them together okay now that they're grouped I like that we're going to call this edit product button we're going to give it a name for this group going to call it edit product button okay so now that we've named that group we can zoom back out we want want this to show up exactly wherever they've selected we'll turn off the selection too if I select here we want it to show up here it's always going to show up in column J and whatever the selected row is so let's get started with that again saving our work now we're going to go into VBA and it's going to help us program that so we've got a little bit of code to write but I'll be walking you through every step so we want that to trigger on a very specific event called The Selection change event now to get to that we're going to go into the developers and Visual Basic alt f11 now our focus is going to be on that sheet we only they have a single sheet to work with it's called the product database so I'm going to double click on that sheet and I want to focus on a very specific event we're going to go into the worksheet and which event is this selection change it's already here code's already created here for us very good but not when I select any selection only when I make a selection on a specific range of cells Now where's it going to start it's going to start in A4 so we're going to write if not oops there you go let's quick let's write it out by if not intersect it's my Auto hotkey that does it I want to do it y for you Target and then what is the target range so we're going to use commment range and what is that range now first range let's do a four it's the first row all the way through I and then we'll just use a large row 999 so that is the range is nothing so if not intersect is nothing not and nothing cancel each other out meaning if they've made a selection inside that range then we want something to happen and I also want to make sure that there's actual data in column A so in and range a and the target row the row that they've selected row do value does not equal empty then we're going to do something so again let's go over that if not intersection nothing remember nothing and not that cancel each other out that's a double negative intersection that means the cell that they've selected is within this range and we want to make sure a and the target roow doesn't equal empty then we're going to do something what is it that we want to do well the first thing what I want to do is I want to take whatever row that they selected and I want to put it directly inside L2 so to do that we can simply write range l2. value equals target. row okay so let's take a look at that so that's all that's going to trigger our conditional formatting so as we make a selection on that you see our trigger is working just fine also what I want to do is I want this little icon that we created to follow so we're going to copy the name now if they select anything else on the sheet I want this item to be hidden so let's hide it first so I only want to hide it if it is visible so let's check if shapes then that shape do visible equals true if it's visible only then are we going to hide it so we do that with shapes again paste the name in there visible equals MSO false so that's going to hide it so basically now anything we select is going to be hidden however I only want it to show when they make a selection inside the range so once they've selected inside the range we're going to focus on that shape with shapes now what does that shape here the first thing I want to do is I want to set the left position so the left position is equal to what range J and the target. row. left so that's the left position also we want to set the top position it's going to be equal to range J and the target. row. toop position lastly we want to make sure it's visible visible equals MSO true so that's going to show that it's visible we can get rid of the additional lines we don't need them and we can test out our our code so again saving our work always before we run a code it's a good advice and then we select something and we see that now shape is now following correctly it shows up exactly where we want it to perfect because there's a macro that we're going to sign here to show our user form we'll be creating that user form and we want to also browse for the folders when we click here so all right let's do that now once we browse for a folder I want to put that folder path directly inside L1 as mentioned so we're going to create a module to put that code so what we're going to do here is I'm going to right click and I'm going to click insert module you can also click here and insert module now that I've got a module I always like to give my modules a name even though in this training we're only going to have one it's still a good idea to do that so we're going to call this product macros because we you have multiple modules and you want to know which one is which so option explicit here this is automated through our code I do go over that in my beginning series here's what we're going to do I'm going to Dimension some variables with that D and then product row as a long and product call col them as long I'll be going over these individually product ID as long if we need anything else I'll be adding them a little bit later on okay I also want to Dimension the product pick as a file dialogue and also I want to Dimension some string variables so what is the name of that picture picture name we need that displayed as a string and also the entire path of that picture picture path as string and also we're going to need the picture folder picture folder as a string okay very good I also wanted to mention the product field now this is inside our user form as a control so each field inside our user form we want to put it as a control not that as control okay there we go so product field as a control now what we're going to do is we're going to write that first macro browse for the product folder so sub can write that out browse for product folder to menion the picture folder as a file dialogue and then what we want to do is we want to set that object so setting the picture folder is going to be equal to what application. file dialogue and what type of file dialogue it is is a folder picker so we're going to click here folder picker using tsense okay so once we have that we're just going to put our focus on that with the picture folder we're going to give it a title and it's going to be equal to let's just say browse for product picture folder okay also what I want to do is allow multi select it's going to be false because I only want them to make a single selection and then in in case they haven't selected anything if. show should equal negative 1 so if it does not equal ne1 meaning they haven't selected anything then go to no selection we can just exit the sub or go to no selection either one works let's do then exit sub that's fine okay also what I want to do product database let's call out the sheet if I select on here we see that our sheet code name is called Product database that is exactly what I want to use so we're going to focus on that okay let's close this and continue on so with that product database range L1 that is the field we want to put value equals what is that folder fire path it's do selected items one and that is it make sure that we have end withth that's pretty much it okay we're going to save our work as always what I'm going to do is I'm going to copy this macro let's lowercase this and spell it right and then I'm going to copy this and I'm going to assign to that folder icon we have so I'm going to go in here right click here and click assign macro and then we're going to paste that in even though we have just one here so we can do that click okay all right I already have a product folder here full of pictures so we can browse for that I'm just going to click on this now we've assigned the macro and we're going to then use that so clicking okay and then it's going to place that actual folder path directly where we want to okay now that I have that what I want to do is I want to take this and I want to give it a named range so something that we can use so right up here what I'm going to do is just going to call this product folder and that way I can refer to this inside VBA called Product folder all right very good so now that we've got the product folder we've got our edit button let's create an add new button that is going to enable us to add new products so we're just going to click insert here and then we're going to click on shapes and I'm just going to create a rectangular button here and we're going to call this add new product okay and then we'll just stretch it out a little bit longer and we'll set it to our theme colors I'm also going to write justify it so that there's enough space for we can put an icon okay now that we have that I'll go ahead and make it a little bit bigger here so we got space for the icon I also want to make sure that there's no borders and it is the same as our theme so we'll set our theme color on that which is going to be this one and we don't want any outline on it so we can set no outline now we'll just use an icon for that and I'm just going to use a plus symbol that's going to signify our add new so we'll just type in plus and we'll use this one right here I do want to color that white so inside the graphics format the graphics fill will be white for this icon we're going to set it up inside this and we can zoom in to make sure that everything's set we're going to go into the graphics format here and we're going to set that to 02 height and width next up what we'll do is we'll place that directly inside our button here zooming in which is going to give us more control over that and there we go so now we have our add new button what we want to do is we want to make sure everything's centered and grouped accordingly and then to do that we'll hold down the controls select them both make sure they're in the middle select group and if for some reason we change the column WID we want to make sure that our product button here doesn't move so we're going to set the properties and go to don't move or size with sells we can close that out now okay that's looking good so we've got everything set up on the shape as we want it and now what we're going to do is we're ready to create our user form that is going to help got all the data set we've got everything we need so now what we're going to do is we're going to go back into the developers and Visual Basic alt f11 I want to create a brand new user form we're going to use insert user form here I'm going to select on this I make it a little bit bigger and we're going to set the properties now we have the user form properties here we're going to give it a name now we want to give it a code name so we're going to call this product form frm and then also what I want to do is I want to give it a friendly name here this is going to be our caption we're going to call this add or edit products okay so now we have that we can see that up here add or edit products we can also give it a background now if you don't see the properties or if it's not here all you need to do is just click here on the properties window and it's going to show up there so we have our properties window we can in increase this if we wanted we could also drag and drop and move this around I want to give it a very specific background and so to get that form background I'm going to scroll down here and I'm going to look for what's called picture here and then what we'll do is we'll select down here and we'll browse for that picture now I've got one in here that's saved up called form background it should be a JPEG image so we're going to click open okay that looks pretty good I like the way that that looks now what we want to do is we want to create some fields for that and we want to make sure that those fields are exactly the same as the fields inside our database except for product ID which we can leave out and will be automated and so to do that the first thing what we want to do is we can use our label for the title first now if you don't have this Tool Box available you can just select it right here and click toolbox okay so the first thing I want to do is use a label tool and that's going to be our header here so inside our header we'll set this as a default once we get it right I do not want a back Style on it so I'm going to select transparent here if you want to expand this we can bring this over here and we'll expand it a little bit we can bring it down so we have a little more space to work with very good we can pin it if we want to but I don't necessarily need that and we can bring it up here I want to give this a name this will just be our title label but it's not necessary for the labels and I'm going to give it a very specific font I want to align it here in the center we're going to give it a caption again we're going to call this in capitals add or edit product okay once we have that I do want to select the font we'll increase it to about 12 and bold and click okay all right that looks very good I like that I want it centered in the form so to do that I'm just going to select it and then make sure we're here Center horizontally okay we're ready to create our first product label so I'm simply going to duplicate this contrl c contrl v this one I'm going to write justify here and I'm going to bring down the font and not going to make it bold so again we're going to go into the font here and this is going to be for our standard field label so we're going to set this to 11 I'm going to set it to regular and then click okay and now we'll set the name so this is going to be for our product name so product name which is what we want there and we also needed UPC SKU okay very good so we're going to go in order and we'll bring this this will be our second and then I'm going to duplicate that bring it a little bit I also want one for our product SKU so contrl c contrl v here and bring it right up above here these are just labels so we're going to set this to our product SKU we'll do UPC UPC SKU so that is the same in our database don't we don't have to worry about lying it up we can line them up all at the same time once we have all our labels set we will then set our additional field so I'm going to do contrl c contrl v I'm going to copy both of those and bring them down here the next up is we have a description so again inside our caption we're going to call this description and next up we have our picture so now our picture here we will eventually be hiding it so just keep in mind that picture I don't necessarily need it an order I'm going to put that up above here actually we don't need a label for our picture it's a picture name we don't necessarily need a label so I can change that so the next one is going to be for purchase price so we can just change this to purchase price you can either select inside here or you can set the caption so we're just going to call this purchase price next up after the purchase price I also want a sales price in stock and a value on hand so I'm going to need all field so contrl c contrl v we need two more contrl c contrl v okay I like the way that that looks we're going to bring it down here and then we'll get everything lined up and we're going to add in the field so after our purchase price we can also add in our sales price we want to know what that product is going to be sold for next up I want to know how many are in stock in case we need to make adjustments so we're going to put quantity in stock and next up I would like to know the value on hand what is the total value on hand now there's two ways it could be the purchase price times how many you have in stock so we're going to put value on hand okay very good so now what we want to do is we want to create some Fields so we're going to use this text box here and we're going to be able to fill in the text boxes so the first one we put about right here so we want to set the format as we know we have the font size here as 11 and tooma so we want to make sure to be consistent with that so again for our first field we're going to set that to 11 and regular and also want to set the justify make sure that is a line left which it is and I also want to make sure that we have no selected margin I don't necessarily need a margin on here so I'm going to change this to false once we have it just the way we we want we'll put it into just a test value here to see how it looks that looks very good so what I want to do now we can get rid of that I'm going to copy this and we're going to give it a name this first one's going to be called field one so we're going to change that field and I'm going to copy the word field contrl C and then just put in one so this is going to be called field one now subsequently each one is going to be called field two field three all the way and so on and so forth so crl zrl V we're going to copy this over and we're going to just paste it all the way down here we want description that'll be a little bit longer field if we need to and then pasting that down here and then we also need values for them okay these don't need to be as large of a field but we can fit them in and value on hand okay so these are all the fields as we see we have four five six seven Fields along with the picture so we're going to need a picture here and to do that we're simply going to select on the image tool here we're going to bring it about like that okay next up we're going to bring these down a little bit because I need to have some buttons for the picture I want a browse button to add a picture and I want a clear button to clear it so we're going to use a command button for that we can find the command button right here this one we're going to call browse button browse button and then we also want a clear button so we're going to give this a name now the caption here is simply going to be in all in capitals we'll put browse let's make it a little bit more distinct uh adding a bold in here in the font we'll just make sure it's bold and nine clicking okay and then what we're going to do is I'm going to control C and contrl + V and I want two buttons okay so once we have that this is going to be for a clear I need to be able to clear picture so we're going to give it a name called clear button I also want to change the caption on this to just simply clear this feature will allow us to clear any picture that's been here okay I'm going to add in one more field and that's going to be for the picture although we're going to be hiding it so it's location doesn't necessarily important so I'm going to copy and paste that I'm going to put that right up about just here and this is where our picture name's going to be going and eventually we're going to hide that now the picture itself we don't necessarily want a back style so I'm going to put it transparent and I don't necessarily want a border on that so I'm going to select none now instead of clip I'm going to set this to zoom allow us to zoom to the picture what it might look like if we add a picture we're going to select on a picture and I've got some product pictures in here just temporarily so I can see what it looks like and that's pretty good so that's exactly what I want but I do want to name those fields and this is one of the most important parts we can clear that picture if you want to clear the picture you select on it you go to the picture here you highlight it and just press the delete key and it's going to be gone okay so the first thing what we want to do is I'm going to hold down the control here selecting all and I'm going to align the right so we're going to select here and then align the rights here okay we can move it over to the left a little bit that's going to allow a little bit more space on all them and then likewise I'm going to do that with these fields here holding down the control and then aligning the lefts here now that picture is there but it's transparent and no border so just make sure we know that it's there okay temporarily if you want to work on it you know while you're working on it you can set the Border style so that you at least you remember where it is okay very good so I like the way that that looks we can make the description a little bit larger here we have more room we can make it multi-line so the description might require more line to do that we just enlarge the field and then when we go down here and we see word wrap we're going to make sure that that says true also we go up to multi-line we're going to set that to True by double clicking or selecting on it now it's going to allow for multi-line okay if you want to line these up space accordingly we can do that too so this is going to be called field one I'm going to call this field two the important thing is it should go in order of the database UPC field one product name field two so let's make sure that field one and we're going to call this field two SOI field 2 okay so now that we have that we're going to subsequently set the remaining in order so the description is third and that should be field three actually it's the fourth column but it's the third one picture is the next one so we're going to set that to four so we're going to paste in and then set that to four the next one is purchase price so we're going to set that order is very important here field and then we're going to set that to five then we have sales price we're going to set that name to six so we're going to paste in that setting it that to six Quant the inst stock is seven so we'll set that to seven here and then lastly our value on hand would be eight so going to paste that and put in field8 okay now everything is named accordingly you can also set the tab stops on that but let's position them we see that the top position here is 30 let's say we have a separation of about 30 pixels for each one so we might want this one to be about 60 or something like this so the top position is 60 already that looks good so to make sure this one would be 90 so we'll increase that to 90 and then this one would be 120 actually this one's going to be more because this is a lot larger field so we're going to set that about like here and then now this one is 15 and then this one will make 180 okay so that everything's spaced accordingly very good so we see that how everything's spaced out we can bring down our Fields here just like we want okay things are looking good we want to make sure that things are lined up here so we can bring them down to here if we're snapping to grid it makes things a little bit easier okay I like the way that that's looking here can bring the picture over here everything's looking good we do need some buttons sets for our entire form I need three buttons so I'm going to copy and paste this and we're going to bring it down here now I'm going to make these buttons a little bit larger cuz they're main for our application so we're going to do down here and now we're going to call this save cancel and delete well let's do save button and then in here inside our caption that's going to be called save and also I want to increase the font so we're going to click on the font I want to make this larger it's one of our main buttons so we're going to go to 12 clicking okay making sure it's bold so now I like that and now what we're going to do is I'm simply going to copy this button crl c contrl v and then bring that down here this is going to be for cancel so we're going to give that button a very specific name called cancel button and likely the caption is going to be called cancel instead of save here so cancel okay last up I want to be able to delete it so again I'm going to paste it contrl +v here and it's going to paste what we've had an equal distance this one is going to be our delete button so delete button and lastly I want to give that a a very specific name called delete keep it simple very good so if we want to make sure these are centered and we're not sure if they are or not what we can do is we can select them using the control we can also group them together temporarily and then we can make sure that it's centered horizontally in the form now it is centered okay we've got our form our form's looking really good saving our work so far if we were to run our form here using the play or F5 it's going to run and show us our form everything's looking pretty good we've got all the information that we want we can extend some feels if we want make it a little bit better but I like it it's looking pretty good now this is our picture name it's going to eventually be hidden we can also change the tab stops right if we double click on the form I want to make sure that our tab stops are set so this here is going to be our tab stop so we want to make sure that it's given an index so this index is going to be our first one we can use zero or one I'm going to use zero for that one and the next one would be one so this is the order in which the user is going to tab through it so we're going to say the tab Index this would be one if the other one's zero this one is is going to be two so we're going to go through those and set the tab for each individual one we want this three we don't necessarily need one for the picture but we would want one for the button so the purchase price here the tab index would be 30 012 3 this would be four so we can just go along and that's going to help the user tab through the form and then we also want to make sure that each button is assigned to tabs up so we can set this one to five and six so that allows the user to work with the form without actually using the mouse which can be very very helpful okay we'll set this one to six and then this one to seven here as we move through that let's go ahead and go down to tab index and then labels wouldn't have it you could also have a tab stop as false if you don't want that this one is seven this one is eight here and then we'll set that one and then also we'll do the buttons this would be nine here so we can select on individual one here and set the tab index to 9 and then we'll set cancel be our 10 so we go through through each one and lastly our delete would be 11 very good so we've got the tap indexes all set up for individual that's going to allow them to tap and basically what that means is when we run it and we click tab it goes through the fields each individual one so we can see oops I skipped one I think value on hand but I think we're good to go I'll just update that double click on that we'll set the tab order so it's going to allow us to do the tab order saving our work so far we've got the tab order we've got the information that we need so what I would like to do when I click this add new product I would like this form to show up and I want to make sure the form is empty so how are we going to do that we're going to do that with a macros so inside the product macros here I'm going to create a brand new sub routine called sub product add new inside this what do I want to do I want to make sure that there's no selected row so L2 and our products should be empty so let's do that let's clear that first product database. range l2. clear content so we're going to clear select Ed row once I have that selected what do I want to do I want to make sure that our edit button is false in other words we created an edit button here but we're adding a new product not only do I want to clear this selected row like that I also want to make sure that this edit product button I want to make sure that that is hidden so to do that we can write a little bit of code product database. shapes edit product button do visible equals MSO false so hide edit product button and I'll that properties we don't need that for now we can move that over here so we have that now what else do we want to do well I want to show the form that form that we created called Product form that's the form name do show that's it that's all we have to do so this macro right here we're simply going to assign that to both the button and the icon the group that we created so we're going to right click and we're going to click assign macro and we're going to paste it in there we could also simply select it from here and we're going to click okay so now let's say we select a row remember we want three three things to happen one L2 is going to be cleared out the edit icon button is going to be hidden and we're going to display the form so we're going to select on that perfect and that's exactly what we did our first field that we're going to show is the UPC and it's ready to enter okay very good so the add new part is working great but I also want to be able to select on this and I want to edit a given product if I select here I want all the product information on the selected row to show up within our form so here's where we're going to use data mapping and I'll show you how to do that so we're going to write a brand new macro and this one's going to be called Product edit let add some space here so we can scroll up subproduct uncore edit okay inside this what I want to do is I want to focus on our product database so with product database if it's on lower case and I hit enter and this changes to uppercase I know that I've got the sheet name correct so I want to make sure that we actually have a select row that means L2 which is our select row cannot be empty so if do range L2 dot value equals empty or double quotes then I want to let the user know message box please select a product to edit then we're going to exit this sub if there's no selected row no Row in L2 there's nothing we can do so now what I want to do is I want to set that variable product row this is a long variable called Product row that we've defined up here so product row is going to be equal to whatever is in L2 this is our product row once we have that product row I want to focus directly on the product form so now we're moving into the product form so with product form put the dot to make sure that we have it right I want to make sure that no double endwith we don't need that one we have two we have one for the form one for the sheet so it's Auto hoty that automates my end withd so you may have seen that so now what do I want to do with this form so I want to set a loop and here's the idea I want to loop from one all the way to 9 I want to go all the columns but we're starting on column two the product ID is automated so we don't Focus so I'm going to start on Two And I'm going to go all the way to what so let's do equals column so we see that this is column 10 that means we're going to go all the way to column 9 I want to run a loop from two to 9 so we're going to do four product column it's also another long variable equals 2 29 and we must close our Loop so next product column so what I'm going to do is now that we know the row all I'm going to do is simply extract what's in the individual column and put it in the individual field but we need to set that field up here we have a variable called Product field it's control so now what we're going to do is we're going to set that what are we setting it to we're setting it to each name remember it's called field one field two field three and so one and so forth so that's very important all the way up to field8 remember it starts at one and it goes all the way to eight but our columns are 2 to n so what we're going to do is we're going to set our product field is going to be equal to Dot controls dot meaning because we're already in the form here and what's the name of those controls they always start with field and the product column product column is starting at two right but if I want to address the first field product column minus one so our controls will be field one two all the way through 8 this goes to nine this is for our columns this is for our Fields our Fields go 1 through eight our columns go 2 through N9 that's why we have to subtract one once we've set that field all we need to do is take the value inside the individual database and put it into the field something like this so the product field oops I got to put a D in there so if it's lowercase and it doesn't change something's wrong so I put the D in there it changes to uppercase I know I've got it right field. value equals where are we going to find it we got to call out the sheet again because I'm inside the product form so I must call out the sheet so what is that it is the product database do cells we're using cells because both the row and the column are variables the row is of course our product Row the column is of course our product column do value so basically we're saying whatever's inside this we're going to put inside our field and that is data mapping as far as loading information from the database into our user form very good so let's try that out and see how that works and we also need to show the picture but we'll take care of that in a minute so I'm going to save our work always a good idea and when do we want to run this I want to run this macro when user clicks that edit button so again we can copy and paste or maybe not Neary we're going to select on and what we're going to do is we're going to right click assign the macro and we're going to paste that in here or selecting the product edit here is fine too clicking okay now when I select an individual product and I edit it oh type mismatch let me just update that if we take a look at this we see the product column is three the product rows nine so that's correct so product obviously not visible I need to change that to Value so now this value not visible visible property something different that's why t or false came up running the macro and we'll take a look here one more thing we need to make that form visible so let's do do that outside of our loop we're still in the product form do show that's all we need to do not visible visible is used for shapes so we're showing the form and then that's perfect so we see now how all the information loaded in the form just like we want let's do that one more time closing this out selecting on red wine 4 lit and then we see that the information has loaded in except we certainly need the picture let's close this out we haven't assigned a value to our cancel we know our pictures are located in this folder we know our picture name is here so the combination of that picture folder along with the picture name is going to get us our full file path now it's located here here's our picture name we're going to hide this so we really don't need to see the picture name I really want to see the picture that's all I really need so how do we manage that well let's take a quick look inside here it's relatively easy now I've got some string variables here picture path and we have a picture folder so let's take a look inside here so probably before we actually show the user form I want to show the picture to do that I want to make sure that we have a value in a certain field what field is that am I looking for this is called Field 4 that's our picture name I want to make sure that there's a value inside there so we're going to check if we're still inside our product form field 4. value does not equal empty then we're going to do something and what are we going to do we're going to set the picture path is going to be equal to what well we set a product picture folder this is a named range called Product folder so if I use that name but use it inside brackets it will allow us to use it so we're going to put it inside brackets this is why we use name range and we also need of course a backs slash so we're going to use the quotation marks back slash quotation marks and what else do we need we need the name of the picture where has it been loaded it's been loaded into Field 4 so field 4. value so this is the picture path once we have that picture path I guess we should probably write on air resume next and on air go to zero in case it is incorrect path and what we can do is to add it in is we just do do dot product actually we need to give it a name we need to do this right here the only thing we didn't do I want to give this a very specific name so we can refer to it instead of image one we'll just call this here product pick okay so I think that's fine so now what we can do is we can refer to that in the code so now product pick. picture so we're focused on the picture and we're going to basically insert that picture using equals load picture now all we have to do is load the picture based on the picture path picture path this load the picture very good we've wrapped an on a resume next and on a good Z in case we have an incorrect picture path saving our work so far and then just check on it now so we're going to go into the Sprite 1.5 L we're going to check and you see that it's loaded up okay let's try another one here to make sure we'll get rid of the borders as we don't need the Border really okay it's looking really good I see that we have everything necessary we're going to go back into the form we're going to select on that we're going to go to the Border style and we're going to set that To None okay perfect so now that our picture is showing up we see that all the values are are loaded up and we're displaying the form perfect so everything's looking good let's click add new product we want to make sure that there's no picture in here excellent so what we want to do is when I click add new product or when I click edit I want to be able to browse for a picture I want this button browse to work so we're going to wrun a macro just for that right now so that we can actually add a picture for our product so we can clear this up we don't need the extra spaces here and we don't need the extra spaces here and clean it up a little bit so now we're going to write a brand new sub routine called sub add picture okay and to do that what I want to make sure is there has to be somewhere where I'm going to be putting those pictures if for some reason that's empty or incorrect we need to let the user know so to do that we're going to first say if then put it in Brackets product folder equals empty then message box let these know please select a folder for product pictures and then exit sub so actually what we can do is we can run the macro for for them to do that now we've already got the macro here browse for it so this is going to help them along to do that so we're going to run this and now what I'm going to do is we going to run one more check and then if it's still empty after they do that then exit the sub so we're checking let's do second check okay very good so second check run macro to browse for picture folder that's only if it doesn't exist all right now what we can do is we can set that as a variable remember we created a string variable up here let's close that out called picture folder so that's the string variable that I want to assign so going down here we are going to say picture folder is equal to whatever is located here inside the product folder so just going to copy that and then paste that set picture folder once that is set what we want to do is we want to create an application file dialogue for them to browse for it so we can do this set now we've created it up here one more time I'm going to look it over here here this file do called Product pic that's the one I'm going to use so we're setting a product picture equal to application. file dialogue here and which one do we want to use I want to use a file picker I want them to pick a specific file and to do that with product picture now we're going to focus inside that file dollar going to give it a title that title is going to be something like let's do equals select a JPG product picture should be jpeg okay if there's any filters I want to clear them already so filters do clear clearing any filters that might be there cuz I want to set our own filters do filters here I want to add so what kind of filters do we want to add select we'll do JP G picture so that's the text that's going to appear now what type of files here we're going to set those right now I'm going to use an asteris as a wild card jpg and then what I'm going to do is I'm going to separate by comma there could be others and then we'll do let's say JPEG and then that's should be sufficient enough so then what we're going to do is just going to close that and then comma one I want that a single position now I'm going to check to make sure that they have selected something so if do show does not equal -1 then exit sub we can exit the sub that means if they haven't selected anything we can exit the sub now what I want to do is I want to sign a picture path is equal to whatever they have selected so it's going to be that do selected items that is the picture path that they've selected so that's the existing picture path what I want to do with that picture path is I want to actually load it inside our user form so are user form here is product form. product picture. picture just like we did before equals load picture and it's going to be based on the picture path the only other thing that I want to do is in case theyve browsed for it outside of that folder I want to actually put it in the folder where it's supposed to be so to do that want to make sure that we're copying it over so we're going to set the picture name is going to be equal to the directory I want to know the name of the picture picture path set picture name that's just the name of the picture without the full path we can use directory to extract that picture name once I have that I can then set it so here we say the product going to add it to field name four so product form. field 4. value equals that picture name set name of picture once we have that set we can then let's put this copy to product folder and to do that I want to wrap it an on aor resume next and on eror go to zero because it might already be in the folder and I don't want to have any issues so to do that we can just use file copy for that so we're going to do file copy and what is the source the source of course is our picture path and where's our destination it's basically our picture folder and we're going to use the backs slash here and our picture name so this is going to copy it to the folder right here this file copy our source and then our destination is right here great so that's it that's all we have to do so I'm going to set save this of course our work I want to copy the name of the macro that's going to be the macro that's tied to the button and what we can do is we can do two ways we can go into our form here I can double click on this it's going to go to the click event if that wasn't here and then we were back inside here we can also right click view the code here we can go inside this is why naming is so important go to the browse button and go to the click event which is already selected and then we can paste this in here so this is the macro that's going to run when we click browse so again saving our work what we're going to do is we're going to go into add new product and I'm going to click browse and what that's going to do is launch that so we have the form background or whatever we want product pictures I can select any picture and click okay okay that adds the name and it adds the picture here very nice so everything works so remember we don't need to see this really we're going to hide it but we know it's there okay so the pizza is showing up everything looks good that's exactly the way we want to all right perfect no issues with that so now that we have adding a picture what we want to do is I want to be able to save this form whether I add a new product or whether I am selecting an existing product I want to make sure that we actually can save any change information so how do we do that we're going to write a macro for that and that's the macro that we're going to be tying to our save button so we're going to go back into the product macros and we're going to write a brand new macro called sub product and it could be save or update so save or update either we're saving a new one or we're updating existing one it is going to be the same macro I want to make sure that we have a required field so check for required field I want to make sure that our product name is required that's going to be field 2 so if let's do product form field 2. value equals empty then message box please make sure to add a product name that's a required field so exit sub so they can't save unless they've actually added a product name when saving I really need to differentiate if it is a new product or an existing product now how am I going to know that well let's take a look back to remember if I select on an existing product we see that L2 has a value has that database row if I select add new product we see that L2 is empty so knowing that L2 is empty we can use that as our differentiator to differentiate between whether there a new product or an existing product so if product database. range l2. value equals empty then we know it is a new product else existing product if it's a new product we need to do a few things so what are we going to do well the first thing what we want to do is we want to determine the product row what row of the database is it going to go on so product row is equal to product database. range a 9999 use a large number end XL up. row plus one that's the first available row but also what we need to do is we need a unique product ID now how do we know the product ID if we remember early on in this training we created a name range called product ID right here now we know that this is all numericals and so what I want to do is I want to use the max formula with that and I want to Simply add one to that and that basically means using the max formula maximum is 625 if we add one to that is 626 that is how we're going to get our unique ID however if there is no data at all it would create an air using the max formula so we need to account for that inside the code so what we're going to do is we're going to go on air resume next and on air go to zero so that's going to ensure that we check for error so first of all that product ID which is a long variable which we did up here product ID is long so now that we know it is a long variable we're going to set that product ID is equal to and I want to use the max formula now in Excel we can use the max formula like this product ID + one so that's exactly what I want to do but I want to do it inside VBA so to do that inside VBA we can use application. worksheet function so here we're going to use max now inside that what is the the max we want to use so we're going to use our product ID so we're going to put that in Brackets product ID okay so close the brackets inside that I also want to add one so plus one that is our next product ID what I want to do is I want to check if there's no dat at all in our database it's going to create an error and product ID is going to be zero so if product ID equals zero then product ID equals 1 set default when there is no data okay so now what do we want to do I want to put that product ID inside column A and our row so I'm simply going to copy this and I want to include a and I'm going to paste it down here a and what is our product row and our product row. value equals what our product ID set product ID now that is only if it is a brand new right so that's all we need to do however it's in existing all I need to do is get the product row is equal to whatever's in product database. range L2 so all I need to do is extract that Row from L2 if it's existing product row okay now that we have that regardless of whether it is a new product or an existing product I want to take the information from our user form and save it into our database now we can do almost exactly what we did with load but slightly different so this one right here I'm just going to copy this as we did when we loaded the information in and I'm going to come down here and I'm going to paste it right down here but I'm going to make a few different changes so we're going to tab that out a little bit and the product field is going to be same the product controls going to be same but what we're going to do is we're going to reverse these our product database information is simply equal to whatever is inside our user form so our product value here we're setting the product field just as we did before however we're taking whatever values are in the user form and we're placing them directly inside the database next up all we need to do is just simply unload the form so make sure I think we need need an end width here if we didn't use width and we can probably add it here just for a little bit easier so we can do width product farm so we can focus on that so then we can bring this down we can just remove that we can put that all the way at the bottom here right here so now we can do is we can get rid of any reference to product form like in here which could be helpful and anywhere else we have it here so I think that looks good so we're already focused on our product database so set product controls this is what we need with the form and everything looks good so lastly what else do we need to do we just simply need to unload it so unload product form what that's going to do is going to hide the form and then load all the data so that's it okay so we're going to save our work and then what we're going to do is we're going to make a change and then we're going to reflect that we're also going to add a new one so first I'm going to edit it and then let's just say test description and I want to make sure that this save and automatically update so remember we still need to add that macro to that so we didn't do that yet so let's add the macro that we just created and that's right here so we want to add it to the save button so let's do that so again we can double click the button itself and paste it in there that's pretty simple we'll do it one more time we're going to edit it and then we'll just change this to adding the word test in here and then saving it and we see that our test description has been saved if we edit it back and remove the word test it's also going to be edited okay great now what we want to do is we want to populate our other buttons too and we also want to add the cancel button so I'm going to double click on here I want to look for cancel so cancel what are we going to be doing I simply want to unload the form so that's relatively simple so we're going to select here we're going to look for our cancel button here and on click what do we want to do I simply want to unload the form in this case we can use unload me if we want to unload me me means the form that you're on now we can also use product form if we want to product form both work okay so we can do that but since you're inside the form you can use me so for example me do you see all the fields and everything like that so me is fine too and we run that so we're going to click on here and we click cancel and it's going to clear out the form oops two L's in there we only need one okay so now it's going to work just fine we can click on that and then just cancel we work just fine okay very good so we also want to be able to delete it but what happens if there are times when maybe we don't have a save button or maybe as soon as I make a change to here I want that change to reflect in the database I don't want to wait for the save button as soon as a user makes a change I want to do that so how could we do that well what we can do is we can write a Macro for that then we're going to get to the delete so let's do that now so I'm going to go back into our code here we've got save update the next one I'm going to create something called save on exit and basically as we exit a field we want the changes that we made to save automatically sub product save on exit now I want to use the same macro regardless of what field they exit so I need to know what field number they've exited so field one field two exactly so what we're going to do is we're going to set a variable field do number as long okay cuz that's the one we're going to need to use so what we going to do is when they exit it I need to know what field number I'm going to focus on the product database with product database now this is going to be only for existing records right if it's a new record it doesn't have a row until we actually save it but for existing records we can work on that so first of all I want to check if range let's say L2 we want to focus on L2 equals empty then exit Sub in other words we're only performing this type of save on exit four existing products so if L2 is empty meaning there's no selected row we can exit this up product row is equal to range L2 dot value okay so that's our product row all right what is our product column our product column is equal to field number plus one and I'll explain that so remember we set this we know that this is field one this is field two all the way up to field8 so if we know our field number is one here what column is this it's column two this is column three so our field number let's just double check on that again I'm going to look for that here let's go into the properties here field one field two so if we know our UPC is field one all we need to do is just set it to column two so we just adding one so that's all we're really going to be doing set product column now if we know the product row and we know the product column we can easily make the update what I want to do is I want to get the value from that so how do I get the value so set just as we did before just like we did here you can use it right here I can just copy this and we can paste it up here setting the product field equals control field and the product column but this time we already have the number here and it's simply our field number because I want to extract the value from here so I need to set it so we can write it something like this if our product field. value does not equal what's in our product database do cells product row product column do value only then do we want to make the change so then we want to say simply taking do cells product row product column. value equals whatever's in the field value product. value okay so let's take a look at that and what we'll do is we'll go in here we'll edit it we'll just add it in the word Fred here so Fred can make his appearance today and then perfect so we see as soon as we make the change it changes and when we change it back let's look in column B we see that it's been changed back okay very good so now all we would need to do is simply add it to every individual field and it's automatically going to be updated so how would we do that well we just change it along with the numbers so if we go into the view the code here and we're going to copy this here and now we just need to Simply change the field now field 2 exit is going to get this value here except we're going to Simply change the two and then also we would want to do this field three Field Four and we would do this and so on and so you get the idea so field three would also take on the exit here and then we would change it so that's all we have to do that means when we exit it any changes that exist are going to automatically be inside the database so again let's say we want to change the description and then this is a test we're going to look right up here Sprite as soon as we exit out of this field that description is automatically changed all right very cool so now what we want to be able to do is we want to be able to delete a product withing this button here so we're going to write a small macro to do just that and also we need to test the add new let's go ahead and add new product first to make sure that it's working let's do test test product and we'll add test description and this is fine because we're going to delete this product and then what I want to do is I want to browse for a picture here add in the picture click okay we also need to clear the picture let's do that now clear the picture and delete but let's save this here we want to make sure that it got added so we're going to go all the way down to the bottom here and we're going to see that it got added perfect so everything is working good just the way we want it if we edit it we can edit it perfect so now let's go ahead and clear the picture so that we can write inside our user form so I'm going to double click I'm going to click view code here I'm going to look for our clear picture which is here and all we need to do is just do a few things so let's do em. product picture. picture equals nothing so that's going to clear the picture now we also want to clear the field 4 me. field4 do value equals empty that's it that's all we need to do so let's take a look look inside that once again we'll go to our picture page and if I decide to clear the picture it's going to clear the picture now I'm not going to save the changes on that because I want that picture to be there okay so that looks good so cancel that so we see that it works just fine clearing the picture or adding the picture it came back because we didn't save the changes which is what I want but clearing the picture works just fine all right let's go ahead and write that last macro which is our delete button so we're going to go back in our product macros here and we're going to create a sub called Product delete so scroll down here a little bit sub product delete I want to make sure sure that the user actually wants to delete it so we're going to write a message box if message box are you sure you want to delete this product question mark okay then what we do VB yes or no and give it a title delete product equals VB no if they say no then exit sub so this is going to give them a way out I want to know if it's been save we're going to focus on the product database product database how do I know if it's been saved we're going to check on L2 if. range l2. value equals empty then we know it has not been saved then go to not saved and so down here we're going to just print a note not saved with a colon and they'll be able to go down here so however if it has been saved product row is going to be equal to whatever's in L2 do value okay so we're setting the product row now what we want to do is simply delete it but I also want to hide something so do range L2 we don't need what's in there anymore do clear contents we can clear that selected row I also want to hide the shapes do shapes what shape is that well that is the edit product button this button right here if you want to make sure you get the name right just right click here and we're just going to copy this this is the shape that I want to make sure is hidden so it doesn't get msize product. visible equals MSO false so we're hiding edit product button okay after we hide it what do we want to do I want to delete the row do range we're already inside the database product row and colon and product row. entire row. delete okay let's take a look at that and see if there's any issues just add a double C there too many okay so that looks good entire row delete and then after not save all we need to do is clear out the form so unload product form okay so again if you're not sure about the spelling you lowercase it if it changes to uppercase we know we've got the form okay this is the macro that that we're going to be signing to the delete button again going into the view the code here we can select on that product delete button so delete button here on click we're going to run this macro so let's give it a try so we're going to select on here we edit the product we delete the product we get the message box are you sure you want to delete this product yes and it gets deleted along with all the data information and the form is cleared out okay very cool this has been a really fantastic training we've learned so much we're able to add new products create a brand new user form we are able to use data mapping to both load the information from a database into a user form and we were also able to use Save and update using data mapping in just a few lines of code to save any changes inside that and also we learned how to automatically save changes on exiting a user form any field so that was a really great training thank you so much for your continued support right here I do appreciate it don't forget if you do want to support this channel I have 300 of my best templates available in any single zip file and that comes along with a complete library searchable library where you'll be able to single click to open up any Excel application within that or you can single click to open the training video associated with the template thank you so much and we'll see you next [Music] week

Channel: Excel For Freelancers

Views: 10,101

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, inventory management, inventory management system, inventory turnover ratio, excel data table, excel userform listbox, excel userform command button code, excel vba, excel vba course, userform listbox vba, userform textbox number format, userform

Id: 5k6qNz-h3es

Length: 64min 19sec (3859 seconds)

Published: Tue Feb 06 2024