How To Create An Excel Data Entry Form WITHOUT A UserForm

hi this is Randy with Excel for freelancers and thank you for joining me in this week's training we're gonna be developing a contact manager from the ground up so we're gonna start with a blank workbook and we're gonna work every field every line of code and every format so that you can see exactly how to build these it's gonna be an amazing training so let's get started alrighty welcome I'm really happy you join me for the data entry form training and in fact I'm gonna start with a completely blank workbook so you can see every step I do every mistake that I make and every point and feature that we add we're gonna do together in this video instead of me going over something that I created previously so I'm really excited to try this with you and see how how it goes and let me know if you like it door if you don't like it let me give me your feedback on that so we can continue on with that all right we're gonna start off with a blank workbook so we'll go into a new workbook and what I want to do is I want to keep those first two columns for admin so we're gonna color those grey da I always like to these are the ones that are gonna be generally hidden so we'll give those a color I'm gonna use our first row as our header for our data entry so we'll increase that and let's just go ahead and highlight the first color the first one so we can know that we put our title in that so we're gonna go ahead and right-click and format those we'll give it a fade as we do so we're gonna click on fill effects I'm gonna start off with a medium blue say here and then fade down to a very light blue and our light blue will be consistent for our entire page so let's just go ahead and highlight those first the main area with that light blue so we get that fade effect here so we'll go ahead and click on that light blue okay now we've got our background so let's go ahead and put in our title and we're gonna create a contact manager on this so why don't we just write in contact manager and that's what I want to create for your contact manager let's go ahead and merge centered this and then we'll go ahead and increase the font so that we can use that in merge the fun let's choose something a large font perhaps let's take a look at something that's gonna stand out a little bit more like impact and then increase the font a little bit there we go okay and we're gonna change the color of that and also want an icon on this and I've got some icon saved let's go ahead and open that and take a look at what kind of pictures I've got I've got some pictures I'm gonna bring them all in because we're gonna use them all except for the IDS we're gonna we're gonna add those a little bit later on but the icons I want to bring them all into the sheet so let's go ahead and highlight those and we're gonna pull in all the icons that we're gonna use automatically and insert does okay now I've got all the icons and let's draw and move them so I'm gonna use to select all and we're gonna draw them all make a box around them all and then bring them over to there alright now what I want to do is I want to give this a logo so this looks like a good contact manager I'm gonna shrink that and I want to bring this a little bit closer to what our theme is in fact before I before I let's go ahead and right-click and make sure we merge but don't sighs those we're going to change these properties a few times and but we always want move but don't size with cells so that's important all right good now that we're on this let's go ahead and change the color of this something a little bit closer to our theme how about this light blue ok our client our logo set and now we are good to go on that let's go ahead and start putting in some fields and I want to track some fields there let's put in contact name here and then city we want to track that as well phone let's start that off in five I'm gonna start about five seven and nine we're gonna skip rows so let's do that and we don't need this here and then we'll go ahead and we're going to use six fields and then in row nine a little bit the phone number all right we're set with that now let's put in six total field so I'm gonna use this for the name I'm gonna use this for the label right this one labeled this for the field II so we're gonna skip one and then let's go into G and put in more labels address go down to state and then email alright now we can format those I've got all the fields let's format those I'm gonna hold the ctrl down and click them all right click and then format those cells let's bring it up here where you can see it for someone at a border I'm gonna use this blue color it's gonna be our theme of blue and I'm going to outline it all but I want the right side a dotted line but everything else is solid border and then on the left I'm gonna create that that's gonna be white because I want to make those fields in white so let's right click those and format those cells this is where our data is gonna be located so let's fill that in into white color and then we'll give that a border that's using the same color the solid border on the three right sides we're gonna keep that dotted line on the left alright good now we can double click these so we can expand those a little bit also call them e so what I do is I wonder what I want to create a table a table below and when I select on this table I want the information to show above so that's what I want it's a bit much here we want let's let's unmerge this and bring this will just bring this let's say right here that's fine I'm gonna bring the contact manager I want it over the center of the form we'll probably a justice as it goes long contact manager we have now maybe all capitals looks better okay so that's good and we're gonna put a set of buttons up here to which we'll use and so I also want to show the picture so I want to show that picture and I want to start a table the tables where I'm gonna store the data so let's get one row and I also want to use one row for our data mapping so let's highlight this we're gonna use this for our data mapping so we're gonna color different this row is going to be hidden so we're gonna color that in gray and give it some borders this will eventually be hidden our table headers are gonna actually start in 12 so let's go ahead and write down those same names contact name and then we'll go with address because we'll use the long field for that city state email and then I'm gonna use one for this picture link in J alright so we've got out we skipped one phone number phone number yeah we got to have phone number so we'll put that in I and then picture alright so that's gonna be a link for our picture because we want an ID picture here of format these headers let's Center them put them bold give them our font color and then give them a background format cells also a faded background on that so we'll fill it click on the fill effects and give it a fade how about something very similar to the top they just so it's consistent with our theme there we can go to different colors let's go in the middle color so it's a slight fade okay now we've done on a wall so let's put a border around to to actually format the cells I know that's a little bit off screen but border and then we'll use the same color and then which is this blue and then we'll give it a thicker border on the outsides and then a thin border on the insides and bottom all right so we're good there now we've got our table we're gonna use this for data mapping so that's important now let's go ahead and format the the table itself and I know we can use formula we'll just go down a little bit I'm gonna make it all white and then I'm gonna add a conditional formatting that lets go with no fill then I'm going to add a conditional formatting that colors the odd rows so we're gonna add a new rule and we're gonna use a formula for that and I'm gonna use a mod formula and I've got that save it'll automatically pop up because I use it so often and then what I'm gonna do is format this and I'm going to format this and you know just a little bit slight or blue that our theme background color so I'll click on more colors and just go up a little bit I want just a lighter a little bit lighter that now I'm gonna color all the rows but often what we do you'll see me do is color only the rows with data if you want to color only the rows with data atom and here and then then we would do in dollar d13 does not equal and then blank right so we could do that as well that will color all the all of the blank groves but for now I'm just going to leave it coloring all the rows calling it all the even rows which will color that automatically and we'll put a border around here and I'll put a border around around D let's go ahead and pull this up just so you can see what we're doing here format the cells and we'll put a to C using the same color add that thick border and then all I'll put a doll dotted line in the middle okay so now we've got a nice little table here we will have to expand it a little bit to cover the data but I think we're good here let's put those thick borders in the header all right so we're our our data is are coming into the picture here we're gonna add some buttons now now we've got our table we've got our field let's change the font colors on these to our theme color which is that blue here now let's go ahead and add some buttons in here I want save save the form I want add new I want cancel new and I want delete so there's four buttons so I'm gonna add four buttons well insert shape we're gonna use this rounded rectangle here and I'll set that and set that height for about point two six and the width can vary about 1.5 but that can change I'm gonna set the theme on this one I'm going to use that theme there and now basically what I want to do is I'll want to duplicate this we've got our button width and then we have control de control de control de control dealing for buttons five buttons is actually right we're gonna use one for the add add the picture we're gonna use two sets of buttons two sets one set is going to be for new contacts and once that's going to be for existing so for new context if you have a new contact we're gonna we're gonna use a save save contact and for also for the new contact we want to cancel new so it goes back to the existing so we want that and then for the existing button set we we're not gonna it's going to save so we're gonna put add new contact and then we want delete contact so that's a set delete contact okay so we've got those four buttons let's justify them all on the right holding down the shift and then we're gonna set the parameters so let's all the way on the right and in the middle we want that because we want to put that icon on the left side and we'll adjust the button sizes accordingly and we'll do the same thing this one what we'll probably do in the middle let's see right in the middle okay so now we've set them all and this one's going to be ad ID picture okay we're good to go on that now let's add in our icons and let's go ahead and move all them to the top because these buttons were added first if I move if I move an icon here it's gonna be underneath right we don't want it under I want it on top so all we have to do is select all and then bring it all the way to the top under format bring forward bring to the front okay so now we're good with that now let's separate these out I'm gonna size all these icons there are different sizes now but we're gonna size them all correctly I'm gonna give it something like point two point two so I want it just small enough and I think we're good we're good now so they're a bit small but that's okay cuz our buttons are not that big so let's move these over we're gonna use this little check for save so we're gonna keep that for save and then we have a plus here with our content we'll use that for add new we have this it's kind of like a cancel so we're gonna put that on cancel and then we're gonna use this for delete contact and then I'm gonna use this for add ID picture okay we'll get those centered and straight let's zoom in a little bit so you can see what's going on here all right good and now what I'm gonna do is I'm gonna wrap I want all the Middle's to match up on DS so let's do the select all again you see I use that a lot so I've got it I'm saving to my favorites I'm gonna format this I'm gonna line the Middle's there I'm gonna do the same thing for this button these buttons here I want all the Middle's to be the same aligned middle okay now we are good with that and now let's let's just move the sizing a little bit let's set the size all right good that looks like it's correct and Moodle size out the cancel button - based on that cancel because we want to get out of the cancel mode okay so now we got cancel the little one for delete wool this is a bit larger button so we'll move that in a little bit more all right that looks pretty good we'll move the icon over on the left side so it's consistent add new looks like it's almost sized right all right and we can move the icon over a little bit now what I want to do is I want to group them and name them so I'm gonna hold down the control button control and I'm gonna group them and then I'm gonna name this button because I always want to know my button save contact button BTN okay I'm gonna do the same thing with each of those and then we're gonna group it and then we're gonna name it cancel contact button it's helpful so we when we look we always know what our and then we're gonna group this one and then click add new contact button okay and then delete contact and then we're gonna grip them one more time all right delete contact button delete contact button okay so now we've named all our buttons now I've got two different groups and I'm not going to show these buttons at the same time save contact and cancel new I only want to show those two buttons when we have a new contact when ready into your new contact so I'm going to group those and I'm gonna call this new contact group and then I'm going to do the same thing but this one's gonna be for existing contacts so I'm going to group this one and then we're going to call this existing contact group okay so now we've got that and what's good let's go ahead and set this one up as well move this over here and this icon over and then we want to add this ID we're gonna assign macros to all of these buttons of course after we create the macro but our first job is to assign these names and picture okay so we're going to group that and we're going to name that as well add ID button okay that's good enough now we've got that we'll move that over cuz we want that right over what I want is I want to place the picture right here I'm gonna expand this a little bit expand this a little bit oh I forgot one thing one important thing let's go ahead after we group we also need to size you as you see that so let's go ahead and color remember after you group you'll always want to reset size of properties move but don't size move but don't size now when we expand those columns our buttons will not increase in size and that's we want we want we want those sizes to be exact and what if I want to move this button over I just need to hold the control to select and I can move both of these over bring it a little bit closer and consistent with it is alright now what I want is I want to move these I want to keep these consistent these are both of these groups are not going to show at the same time so we're gonna put them right on top of each other because only one's gonna be shown at the same time and then we'll go ahead and align this one to the middle too alright now we've got our buttons at the middle we're good with that and now let's go ahead and set now we've marked the first two columns as for admin and we're gonna hide those and I want to assign just three different variables because there's things I want to know I want to know what the selected row is selected row and we'll put that right here and be let's just say 16 for now and then I also want to know if it's a new contact it's going to be a true or false okay let's put true for now and I also want to know if the contacts being loaded we need to know that because when we make a change here we need to know is it the change because the user has selected the contact below and it's automatically loading or is it actually the change the user is making manually so we need to know there's two difference and when this has changed I need to know those and I'll explain that so I need to know contact load true or false normally it'll be false except when we've selected a contact from the table below and is loading up here alright so we've got those are the really the only three parameters I want let's color them differently something Orange that'll be hidden remember you'll want to hide those when you create your contact manager but uh for now of course it's going to be visible so we can work those are really the only parameters now I also want to do some conditional formatting I want to know the what the selected row is so let's go ahead and do that let's highlight our table and let's create a formula for that table conditional form format under new rule and I'm going to use a formula and what I want to say is basically b3 is equal to row I and I'm gonna sign a very contrasting color to that so let's go ahead and go into fill fill effects and we're going to use a dark blue and a light blue font so let's select a very dark blue let's say here and here alright so we've got that let's go even a little bit darker so all right that's good and then what I want to do is I want to make sure we give it a white font so we're gonna do it bold and set the font to white okay so now we've got that now b3 equals row good so you see now 16 is so now 16 is automatically covered if we change this to 15 it's gonna automatically change and in VBA when we select a row it's gonna change this to whatever row we've selected so that's how that's going to work so now we're done with the selected row and now what I want to do is I want to also we're gonna do some mapping fields that's very important so I'm gonna copy these fields and I'm just gonna paste them over here we're gonna use that for data mapping and data mapping is really really helpful when we are using uploading information so let's go ahead and we want to make sure it's on the same row and these can eventually be hidden we're just gonna paste that in there paste it all because we're gonna use this for din let's color these these gray as well I want to just make sure that you know that this should be hidden because it's not it's a it's an admin thing and it's something so coloring it gray is our admin color so we know that that this is really for admin and these rows will be hidden normally be hidden we're gonna use this for our data mapping and the idea is is that contact name what column is this what column contact name is column let's go write those calm temporarily equals column this is column five okay but a four excuse me so I want to know what all these columns are not wanna map these so let's write that now I want to put the picture here to picture and I'm gonna put that actual picture there cuz I want to know what picture is there so let's right click and just add some white space for that so that we can know all right so because I want to put that picture temporarily I'm gonna put that picture file name right in here and then what I'm gonna do is once it's added and save them and put it right here that file name right in here so that when we load it we know what file alright so the idea is from four all the way to ten we need to save those informations so contact name is in column 4 right and I'll show you how this plays into it in a little bit let's reduce that address is in column 5 we'll just continue on here city is in column six state is in column 7 phone is in column nine and email is in column eight ok great so now we've got all that mapped out now continuing out their data mapping there's two different types of data mapping so when we make a change here let's say let's say I make let's say I make friend right I want to know by going here what what column should Fred go in it should go in call them for right and we know that because I know what what name it is I just need to know the column I already know the row the row is gonna be whatever selected but I need to know the column so what we're gonna do is we're going to say I'm gonna show you what this column is this column is calm thirteen so it's gonna say the current column five right five plus eight what is that for right so so for example in VBA it says if I want to save this to the table how do I counter what's a quick way to say Fred I want to save it right here right I want to save it we've selected I want to put it right here how can I do that fast I know the row so we know where no goes in row 15 but we don't know what column it goes into we do know what column because it's in called four but how do we know this is well we know it's the same row as Fred we know it's the same row we don't change the row but it's the current column 5 plus 8 is 13 so we know all we need to do is add saying what is in the call the current column plus column 8 what is that what numbers there oh it's four okay now I need to know now I know exactly what column to put it in so that's what we're going to do so now we're gonna mapping the opposite way when I select when I select a specific row with a contact how do I get all that information loaded up here well what I'm gonna do is I'm gonna look to right here this row which is going to be hidden I'm going to say what is the range here that I need to put it in in this case II five so let's put an e five now I know what range that is I don't know center that'd be five and what about the address well the address goes in H five so let's go ahead and do that age five so this tells us what range we have and then we can hide this and the city of course goes into e seven the state goes in age seven email goes into H nine and the phone number is in een ein okay so we've got all of that mapped out and the picture we'll put in M four because I'm just going to show the picture here I'm not going to show the field so let's just put em four as the picture so that means when we load when we load this file name we'll go right here into M four and then we can see we can pull this and we can load the ID picture based on that we're gonna end up expanding this a little bit alright so now we've got that let's go ahead and save it better do that right and I'm gonna put this in my folder right here because I've got my folders and we're gonna add it as xlsm we need to make sure that it's xlsm because we're going to be putting hackers in here anything that macros must have an excellent extension so we'll save that there all right now we're saved always got to save your work now let's continue on let's see what else do we need to do we've got pictures we've got our two button sets we're almost ready for doing our VBA I think we're ready for that let's go ahead and check to see what else we've got our data mapping done on all the columns we have already alright great it looks like we are ready for VBA now what we want to do is we want to first add selections change let's go ahead and get some sample data now I created another one let's just go ahead and pull I'm gonna pull in some data here from a recent one we got a sample I want some data in here and there's no reason for me to type out the data let's go ahead and pull this data in here and then we're gonna put that right in here alright now we've got some data that that's gonna be helpful we can close this one okay so we've got our data but we don't have any pictures we don't need any pictures that's no problem let's go ahead we can format these phone numbers let's format this column as special and we'll go into more number formats and then special and we'll do a phone number of course you can use any phone number now we've formatted our phone numbers this state we can reduce this we don't need this as a big we don't need this and we can Center this the state that's okay all right looks good we got our data in there here's what I want to happen when I select any contact I want a one I want to change the conditional formatting by changing this row and I want to load that data right in here we can do that through VBA so let's go into VBA continue on so we can complete this application into the developers mode click on visual basic alt f11 we'll get you there and now we're in the developers mode we're gonna do a little bit of on sheet macros and what do I want what do you mean by on sheet macros well those are macros that run on the sheet itself and we want to do a few things with our on sheet macros I want to do some on sheet on selection sheet change so let's go ahead and do that on the worksheets silicon's change I want to create some macros based on the selection chains when we still a line in that table I want the data to load so let's do that but first I want to remove any possible errors if we do if target count is greater than 1 then exit sub this helps us get when we select large areas to move from possible bugs so that's always something I include if I'm not using merge cells if using merge cells you may want to increase that so that's important next up let's go ahead if not intersect target range and what is the range now it's the range of our table on our table starts on column D row 13 and it goes all the way to J and then a large number so let's just go 9 9 9 ok is nothing right if we select something then do something right but I don't want to just if we select I need to check a few things first I want to make sure that there actually is value that there is data in column D so we'll want to view and range D and target row dot value is not empty then then we can do something now we got into F okay so now what do we want to do well the first thing I want to do is I want to make b2 which is actually our selected row I want to make that our selected row so range B 2 dot value equals target row that's gonna and then next up I also want to load the contact but I'm gonna comment it out we don't have a macro yet on that contact load that's gonna be the macro that runs with 2 lot of context but I've commented it out because we haven't written that macro yet so let's go into our code and check it out and now when we select rows db2 changes I did move that up one row I like it in row to be 2 so it so now that works great okay now what I want to do is I want to load dot information in here so we're gonna start writing a macro to do that back into the VBA we're gonna right-click and we're going to insert a module we're gonna give that a name we always want to name our modules into the properties let's call this contact macros in this workbook we're just creating one module and all of our macros are going to be located in our so the first thing we want to do is I want to create I want to dimension some variables because we're gonna be using these throughout all the macros so dim conflict row as long and we're gonna create another line dim contact row contact column as long okay we're gonna be using those throughout so it's important let's create our first macro sub contact load that was the one that we just wrote but we commented out and of course we're gonna focus on sheet 1 entirely that's all our data so with sheet 1 and you'll notice you'll see this automatically came up just notice it and if I use auto hotkey for that and we'll go over that in another time but that's that's why my end with automatically typed out because I used something called auto hotkey and I program it in I'll probably include that as a bonus in one of my courses autohotkey of course is free but all the programming that comes into the automating the VBA I mean add that as a bonus so keep that in mind alright with sheet 1 what I want to do is I want to first make a check B - of course is where our row of our contact is so I want to make sure that actually has data right if it doesn't have anything in there we gotta exit out so if dot range B 2 dot value equals empty then exit the sub ok we don't want to continue because we need that row that rows critical to load if there is no row exit the sub alright we want to set b4 to true before what is that that is the contact load B for contact load is fall I need to set that to true while it's loading and then once the macro finishes or before the America finishes we're gonna set that back to fall so let's set B for it to true and I'll show you why that's important a little bit later on dot range b4 equals true ok equals true and let's comment that set contact load - true okay so now we're setting it at true all right we'll reset it in fact let's go ahead and just set it to false and then write everything else in between all right that way we have both of those it's always good to do that just so you remember to do a set contact the two false now we're gonna write all the code in between those two lines all right so everything's gonna be in between here so now we haven't forgotten that we would do want to set we have a variable contact row we need to set that we know that's located in B 2 equals dot range like that it was pretty quick huh B 2 value equals all right all right we're done there that's the contact row except I'll try to comment these out all right so now we've got the contact road now what I want to do is now I've got the contact row so now I want to loop through this I want to go all the way through here from for calling for two column ten and I want to pull this number right here scuse me I want to pull this range right here and with that range I want to put the data from this contact row and put this in here I want to put this in here and so on and so forth so mapping is not required but it makes coding a lot there's a lot less coding right so there's more than one ways to do this but if we use mapping it's very very simple coding and that's why we use it so we're gonna create a four next for contact column equals four to ten those are the columns equals four to ten and then next contact we always want to close our lips so we don't forget okay then we write everything in between here so what do we want to do is a very simple loop 1 dot range so what is the range and the cells we're using dot cells what is the cells well the first thing is cell 11 right that's row 11 Y row 11 let's look at that row Elevens where all of our data mapping is this is where I so I want to run I need row 11 will stay consistent but the columns will loop from 4 to 10 I'm gonna pull this value and use that to know where to place the data in column mapping so that's what we're going to do so cells 11 what is the column of call them it's a variable column right and what is this value this value is this value is the range right is that range so that's what we want to do want to range it close that loop so we want to put that in there value equals what is it equal cells we know we know the contact row and we know the contact column because it's looping so dot value so that's it that's how we loop it that's all we have to do it's a very very simple and that's all this is what this macro is gonna do it's gonna let's just go over one more time so we know exactly what it's gonna do it's gonna go through column four I'll go to call them ten it's gonna look at this it's gonna look at the ranging to say where do I put this name I put this in E 5 where do I put this address I put it in H 5 what do I put this I've put in e 7 so that's how we do it that's all that is really necessary for that code and next up all right next up we're going to load the picture right I want to load that picture if there is if there is a link here that links to the picture I want to put the picture I want to put that link right here that this m4 will do that but what I want to do is what I want to load the picture I want to make it visible and to do that I want to make sure that we have not displayed any other pictures so I want to first delete any picture that might be there in case we have no and then I want to load the new picture so if there's a link here I'm gonna take that link and I want to load that ID picture I'm gonna put it right here that's what I want to do so let's increase this a little bit give it a little bit more space all right so that's what we're going to do so let's go ahead and write some code that we can do that first of all when we delete a picture we don't know if the picture exists so we need to catch the air so we'll do on air resume next and we'll create shapes we're gonna call it this is gonna call thumb pick okay that's gonna that's gonna be the name we're going to use throughout its own so that means regardless of the picture they're always gonna have the same name it makes it easy to work with and makes it easy to to delete so we're gonna delete it because we don't know if it exists but if it does we need to delete it delete any pictures silly pictures will put delete some nail picture and then we'll just put if any if any ok so we put that comment out and then we could do on air go to zero okay so that on air go to zero that just closes the on arrow so that if there's any errors after that it'll it'll automatically see now what winners I want to load that picture if it exists if dot range m4 remember picture link is going at m4 dot value does not equal empty includes a link then what I'm going to do them on another I want to run another macro we're gonna call this contact display thumb we haven't written that macro yet but we will soon okay so we're gonna run that macro if it's good and next up we need to set the new contact it's for sure if we've loaded the contact we need to make sure it's not a new contact right so we need to set new contact and that to false and that is located in b3 so let's go ahead and set that now range b3 equals false equals false remember we looked in right here new contact b3 we need to set that false because if we're loading it it's gonna be false for sure so we do that b3 is false all right next up we need to set our buttons right we know it's going to be an existing we know it's going to be an existing contact so we need to make sure that we set our button so dot shapes right remember we group them the existing contact group remember we named that group existing content dot visible equals true right we want to show it's an it's an existing contact and the new group is gonna be hitting dot shapes new contact I think that's I think that's the name we name it if not it'll throw up an error but that's okay I think that's the name we gave it dot visible equals false okay because we do it's not a new contact and it's an existing so we need to make sure we do that alright I think that's it for that macro good if there's any issues at all we will certainly know about that let's take a look now we can go back into she one contact load we've got that now we've created that macro so that'll work let's go see if there's any bugs when we load it let's take a look oh we got a bug oh of course we need to comment that out because I haven't created that yet okay so let's let's just comment this all out let's comment this line out because we have not created that macro yet so that's fine no problem but as soon as we do we will get a bug okay that's good alright so now when we now we select blind loads all the data exactly as we have it great all right we're good we want to format this phone number too so let's give it that let's also give that the special format special and phone number excellent so now we've got that put that on the left side I think we're good now we've got low contact let's continue on and let's do four new contact and when we click now we have the button sets right when we click this button here I want these fields to clear out right and including m4 and I also want to set new contact to true so let's do that and I also want to clear b2 out because I don't want I don't want to show any row and make sure that's cleared out because it's a new contact let's go ahead and write that macro right now all right we'll call this contact new sub contact new and of course with sheet 1 we're only working with sheet 1 here so we wanted that we want that to do all right and next up we're gonna set b4 to true b4 to true that is the our new contact new contact to true ok that's important and I want to clear out these cells right so look we might want to clear out ee 5 e 79 H 5 so let's go ahead and write some code that does that we don't need that e 5 e 7 I believe een ein we want to clear those out as well as H 5 H 7 and h 9 those are all of our six fields and of course M 4 which is our link so we're going to clear dot clear contents ok not clear clear we clear all the formatting we just want to clear the contents so we're good with that and b3 we want to set that - true b3 actually is our new contact before it's contact load contact contact load all right that's a logo to set that temporarily right and b3 let's go to range b3 which is a new contact set that had that had that mixed up for a second equals true this is new content we'll say that and we'll set B for back to false before the before the macro ends so all we need to do is now we need to set our shapes basically that's going to be the opposite of this you see that shapes so let's go ahead and copy those and paste those down and make those the opposite existing contact we don't want to show that this is a new contact it's gonna be false new contact we do want to show that so let's set that to true all right b4 let's go ahead and set that to false our contact load we're not going to load it and I'll show you why that's important before equals false contact load to false okay so we've got that that's a very simple macro and let's go ahead and assign that macro now I think we have everything we've got these we're clearing out the fields we have set B 4 and B 3 right B 3 let's set that new contact so we know what that is okay let's gonna sign that to the button and see how that works well we're gonna we don't want to click on the group we want to hold the control control that and then hold the control we want to assign the macro to this button and this icon so right click assign macro contact new click that save always like to say before running Macker in case some bad thing happened click on new okay that looks good we have our button set change and I'm gonna make contact name required so let's go ahead and add a conditional formatting to this conditional formatting new new role and then format cell if it's blank if it's blank I want to make it yellow so that it's clear to the user that that is a required field that's really bright that's really bright yellow let's do a little bit less okay so now when it's now this is yellow that's do you know it's it's required but when they add something in then it goes back to white okay so now we've got new contact we're good with that next up we're going to use the save contact we want to program now let's get these right justified over here I want to program the save contact macro so let's go back into the VBA and program that macro down here we can add this macro in contact sub contact save alright I do want to run a check with sheet 1 I want to want to check to make sure that they have the name correct and that they have actually at least added a name so what we're gonna say is dot range let's go ahead if if and then now that would be he5 I believe is the name so if e5 value does equals empty then we'll go to a message box this box please enter a contact name all right so that we got that just in case they don't and exit sub we don't want anything to happen exits up ok if having into the contact name at least we want to exit out of there alright so now we have set the contact now what we want to do is I want to determine the contact row and the contact row is going to be basically the first available row and contact row equals dot range D let's go nine nine nine nine dot and Excel up dot row so that's gonna give us the last row but I don't want the last row I want one after that so plus one that's gonna give us the first available row so now we've got the contact row now we can run our loop remember our loop for contact column equals four to ten next up we have our ability the same thing we were left hand out cells we know our contact wrote contact row we've just set it and now we know our contact column it's going to loop from four to ten contact column we've got that covered and the value of that what's the value of that the value equals what is it equal dot range and then again we're gonna do the same thing we did before cells 11 right the row 11 cells dot cells 11 is the row number where our map data is and the contact column so we've got that to contact contact contact column all right so we've got we know our contact columns gonna loops gonna that's gonna tell us our data so we know that value and so whatever that value is value okay so you see here right here it's gonna tell us what the cell number like 'if I've or H 5 or what the cell number is and this is going to tell us so this gets the reigned the value of that so it's like the value of the cell and this tells us exactly where to put it so that's it that's all we need to do in our loop then we can just close out our loop next contact column all right again we need to let's go ahead and copy these shapes again we need to copy the shapes let's do a head this we're going to make sure since it's a no longer a new we are going to paste in our shape we existing contact is true new contact is false once we save it it's no longer new so we want to set that and that's it that is all we need to do there we want to make sure let me just reset we do want to make sure that we have an existing once we save it it's no longer a new context so we want to set b3 to false so let's go ahead and do that dot range b3 that is whether it's B equals false okay good now we're here now just so we know it's not no longer a new contact sometimes this can be important in this one it may not be critical but I want you to get used to knowing the difference between a existing contact and a new contact so we've differentiated those there all right we're good with that let's go ahead and take a look at this macro we've such a simple simple macro here so that's why we have it there it's very very simple that's why we do data mapping because the code we have to write is much less all right contact save is done let's go back in and sign that macro to our Save button we have our Save button here but within a group so we're gonna hold the ctrl down and click on both of those right click assign the macro we're gonna sign it to that new macro here contact save all right and of course we want to make sure it should tell us that there is no please into a contact name perfect because we haven't yet all right let's go ahead and save a contact name Sally Smith and the address is 1 3 Main Street give it an address and a city of Los Angeles and the state of California and let's go ahead and give it a phone over 300 phone number and an email Sally at gmail okay so now we've got everything saben let's go ahead and save that contact and there we go Sally Smith all the information has saved alright now we are we've saved the contact let's move in to contact delete we also have to delete a contact so let's go ahead and write that macro so we can assign that back into the VBA we go we'll go ahead and write some code here sub contact delete alright okay let's go ahead and we want it let's go ahead and add in a just a double check to make sure that they do with sheet one that's for sure and we're gonna say if message box will add a message box with the prompt there are you sure you want to delete this contact and then we're gonna say ok that's VB yes no grow up a little bit there we go and then we'll give it a title delete contact that gives us a title alright and then if equals DB no means if they say no then exit the sub so that means that gives us just a check to make sure that they want to do it and also we want to make sure that if the row if row is empty we want exit' dotsub - we need the row number so if it's empty we're just gonna exit so if dot range b2 equals empty then exit the sub b2 is the row so the contact whether we're going to delete so that's very important we need to make sure that that it actually is a value so we can set the contact our contact let's go ahead and copy this first make it easy on us contact rows gonna be b2 so contact contact row equals b2 that's going to give us a contact row now we have the contact row we are ready to delete it so we're gonna say dot range and then contact row and and then colon and contact row that's good there's many ways to delete that but let's say entire row delete okay great so now that we're gonna entire row delete we're good with that and next up we can let's just select the first row the first row data the first contact just so after we delete it we'll know in case that so we can select here's the idea when we delete when I delete let's say I delete this contact what what am I going to show here I really have two options I can show an existing contact or I can go into the add new so in this case I'm just gonna select d13 and select whatever the first contact is so you have two choices after you delete it so in our case we'll say dot range d13 let's capitalize D 13 dot select it we'll select that and that's gonna load it automatically and let's go ahead and save it and we'll run our macro well first of all find the macro click on the delete contact button hold the ctrl click on the icon right-click assign macro contact the lead okay great now we've did that let's go ahead and select the last one Sally Smith that's loaded up delete contact are you sure you want to delete this contact let's click no for natural ok that exits right let's try it again delete contact are you sure you yes okay great Sally's gone and the first rose selected and loaded perfect now we've got it deleted all right let's go ahead and click cancel new when we add new I want to be able to cancel out of this new one so let's go ahead and write that macro as well and this will be a very very simple macro sub contact cancel new and all we're going to be doing is making sure that if there is a value if there's a value in d13 it means there has to be at least one contact if there is at least one contact then we can select that contact so we can say with sheet one we don't really need this it's only one line of code she won and then if dot range d13 dot value does not equal empty then okay then we can do then we can just do dot range d13 it's like select okay good that's it very very very simple macro that's all we really need to do there and then we'll go ahead and assign that macro to the button back in there click on the button here and the icon here and then right click and then click assign macro so cancel new okay now we can try it out click cancel new perfect it's selected the item in the icons went back let's go ahead and update that I want to move that icon a little bit over here okay all right good that works great now let's take a look now I want to add the ID picture so I think we're done with those so let's go ahead and and write that macro back into it so I want to add the ability I want to give them the ability to add a picture here and then I wanted that picture to load when they select the context well let's go ahead and write that macro the first thing is we want to add the macro that we're going to attach it so let's write that here as well sub contact attach them so listen write that we're gonna dim picture file as a file dialog because we need to pull the picture up as file dialogue and we also need to set with sheet 1 so let's do that we're always working with sheet 1 and next up we're gonna set the picture file set the picture file is equal to application file dialog and then MS OH we're gonna file that we're going to do a file picker so that's what we need and then we're gonna say ok with the pic file let's go ahead and give this some parameters here the title and we'll take title as select a picture to attach title we're going to say equals select a contact contact picture okay that'll give us our title and we're gonna add some filters onto that because I don't want any type of any type of file I only want all picture files let's do that all picture files so the filter is gonna be all picture files alright that's good and next up with this I'm going to see what type of filters they are let's put a comma here we need a comma okay and let's see what kind of our they are we're gonna say I'll picture files filters add and we've added what kind I want JP geez and I want JPEG cuz you know many pictures have that let's see I also want gifs so we're gonna do star that means any star anything text before that so we're gonna want that gif and how about we also of course need PNG those are those are common pngs picture file in PNG next up we also want let's see BMPs ok and TIFF that's enough BMPs or not this common anymore but still common and we'll do Tiff's as well okay so now we've got them all and then we're gonna set that to one okay in the position one all right we're gonna set that if dot show does not equal negative one then go to what in case they don't select anything this prevents there's show does not equal negative one then go to no selection no selection and what that's going to do is just going to skip so we're gonna read no selection down here so that'll skipped our next so we're gonna say sheet one where we want to put range and for that's where we want to put the file link right and for dot value equals selected items right one okay this is gonna place who's gonna put the file name and M for let's go that's it that's we want that file name in m4 and that's gonna put that full file path right there alright so we're done with done with that and we're done with the picture file we can do end with on that I think we need to place no selection far there down here way down here because we don't want to the next up I want to display it but I don't know if there's no selection I want it I don't want to display anything so next up we are going to check to see is b3 false what I want to sees I want to know if it's a new contact here's what I want to happen if this is a new contact we don't know what row it's in yet but if it's an existing contact we can put it we can put the file name right here right I can put it here if it's an existing because we know the row so let's check if b3 is false then we know it's an existing contact so that's what we want to write if dot range B 3 dot value equals false then dot range J that's the column where the picture file name will go and what is that b2 value sheet 1 range B 2 dot value that is this is our contact right here this is our contact row so we know J in the contour o dot value alright that is gonna place equals dot range and forks we just placed it an m4 right so we know where it is already that's going to place the file name also in the table but only on existing contacts so that's important as well all right next up I want to then display the thumbnail but we're gonna do that with another macro so we're gonna write that contact display thumb okay so we're gonna do that I'm gonna write that macro in fact I'm gonna write it right now but I'm not gonna put anything in there just to we can avoid sub contact display thumb okay good so now that there's nothing in that but it's not gonna prevent an air contact display thumb so let's go ahead and take a look at that and see if we have any issues or errors let's go ahead and assign this again click on the button and ctrl click on the link hold the right click assign the macro and we're going to click on that contact attach them there now when we click on this let's go ahead and take a look at that contact display thumb displays got to be there okay all right now it'll run now ok good now let's go ahead back into that let's go ahead and click add picture now and it'll add a picture we can add a profile picture all right and the file name got added both in the column the row here and call them J row 19 as well as m4 so that works great alright now what I wanna do is I want to display that picture I want displayed here in the thumbnail so let's write that Macker now complete that macro and we'll click on display thumb and we're going to dimension the picture path I need to know that path as a string and also with sheet 1 as we have done in the past also I want to again delete any picture just as we've done here so let's copy this alright and copy that and bring that down it's the same thing here just in case that picture exists I'm gonna we're going to delete it and then we're gonna create a new one just like that and we're going wrap that in a air trapping there as well so next up what I want to do is I'm going to set the picture path equals M for picture path equals dot range and then M for that's going to set us our picture path well let's go ahead and comment that picture path okay so now that we have the picture path we can work on that okay and with what we want to do is once a dot pictures and then we want to insert what are we going to insert picture path okay so now we're gonna insert that and what I want to do with that is I want to say with the shape range shape range and I want to do some things well first of all with that I want to lock the aspect ratio so dot lock aspect ratio that's important in equals M must true that's important and then also I want to set the height probably 80 about or so because I don't want to make it too big we need to equals 80 and then now I want to sign a name that's very important because if we're going to delete it we need to set that name every time equals and then thumb pick okay so that's going to set our thumb and then end with so we're done with that as well as we're done with the and with for the picture insert this with is for the sheet so that we get our we keep our wizz and end with correct so we're done with the picture answer we're done for the same range but now why don't do is we've set it we've set the height we set the aspect ratio but we haven't set the location so we want to set the location now we can do that with the shape that we just created so with dot shapes and then what is it thumb because we've just named it now we know the name with thumb picture and what do we want to do with that well we want to set the left and I want to put it right there I want to put a rain call him J so we'll set that up left and equals and then I don't want we can't use dot range here why can't we do because we're already set da ship so in this case we need to specify the sheet name it's easy mistake to make not specifying the sheet name why do we need to specify the she name because the width is with shapes it's not with sheet anymore with she 1 equals sheet 1 range and then J 4 cuz that's the top area and then left so I want to place that picture right about here not quite here I want to paste it a little bit to the left but we're gonna start here I want to place it right here and then I'm going to move it over to the left a little bit so let's go ahead and do that so we're gonna place and the same thing with top top equals sheet 1 dot R and J I guess I just could have copied a paste of that range J 4 and then dot top okay so that's gonna place it but we don't quant we want to move it over a little bit right so when we want to move it over little use dot increment increment increment left and perhaps - say 20 or so and then increment top I don't want to I don't want it right at the top I don't want it right at the top I'm going to lower it just a little bit maybe 10 or so so let's lower it from the top dot increment that one you can see both and it's gonna be increment top and then perhaps 10 why 10 why - because it's if we 10 means going down - left my the left - 20 means moving to the left so now we're done placing that so that is we're set up with that alright great now we're done with that and we've done we've got the end with this is for the sheet this end with is for the sheet and of sheet 1 right and she okay so now we're now we're good with the display now let's go ahead and take a look in that and see if we have any bugs or issues and so let's select on this one and select on this one and add an ID picture we've already added it actually we can just add it again okay all right let's take a look at the issue lot ratio no end here alright run that okay good perfect uh yeah I think that's just about right we can we can reduce this a little bit and so there we go now what I want to do is I want to just when I selected I want to display that right when we load it I want to display that so we have the macro display thumbnail but I want to add that remember add that into the contact load and here it is right here but we need to uncomment that out and just making sure so now when we run it now when we select something else we select it now now we get that contact picture to load we can add that picture again add another person here great all right that looks really good let's say we want to move it over a little bit to the more to the left always deer's increase that display scroll down here let's say 25 a little bit more to the left we can also increase the height let's let's let's make it a little bit bigger maybe 85 so it's a bit bigger and go back in here and now when we select it now it's a little bit bigger a little bit too left okay we can Center this over a little bit over the picture all right we're good to go now maybe we don't like these names extending all the way let's put a space here two spaces and I'm going to drag that all the way down here and that gets rid of that of the bleeding out of the text over into the other cells all right now what I want to do is what I want to make a change to any type of cell I want that change to reflect in the row and the cell down below when we do that so we can do that with change with the worksheet change and let's go ahead back into the VBA and see how we do that in the visual basic on the on sheet we've done selections change but now we're gonna focus on worksheet change and when we make a specific change to any cells let's say between be 5 and h9 so let's go ahead and use if not intersect between those so we can start out if not intersect target range e5 through h9 is nothing and range and I was playing this in a second B let's take a look three we want to say only but not on contact low so beef 4 must equal false before must equal false b4 dot value equals false then and what are we gonna do well here's what we're gonna do when b4 make sure contact load is not a false we only want this when the user is making a change not the type of change that when we select the contact not this type of change only on this type of change only on that type of change so we have to differentiate between those two types so that type of change we want that to happen what do we want to happen well what I want to happen is I want to take let's say e 5 and I want to put that into D and whatever the selected row is now we know the rows 17 because we've got it here in b2 but we don't know the column well the column is actually 5 the column is actually here it's four the column is here is five columns here so it's the current column which is 5 plus 8 which equals 13 or in this case column 16 so be this calm 8 plus 8 is 16 so all we need to do is add 8 to locate the column so let's do that in Excel VBA right now what we can say is that cells and then what's the row we know the row is in b2 so let's put that in range B 2 dot value now what we need to do is get the column we know the column is exactly 8 columns over from the target column and we know that the value so it's cells target row and then the target column plus 8 and the one of the value is of that plus 8 that is dot value okay and then dot value so what we're saying is here whatever the row is that's where we want to put it the row of the table the column of the table what's the column of the table the column of the table is located right here target row plus the target column plus a this value right here is our target column right here this is our target column so our target row and our target column equals target value we want to be forced false we want to make sure that we also do this on only existing and not new records so let's go ahead and say and range b3 God value goes false right we don't want it on new only only existing records and only when it's not loading so b4 + b3 must also be false let's look at that b3 new contact must be false before contact load must be false now when we change Fred Fred Inc it automatically changes here and we want to change the address here you can change this to Canoga board and it automatically changes now the mapping because it pulls the column so you see how little code that is we can change the email address as well simply by clicking here and it automatically changes in the selected row automatically here so that is how we do that and we want to change the ID picture we can change that simply by clicking here clicking here and the ID picture gets changed automatically and the link gets updated so when you change it back again it's automatically updated and if we want to change it back just click on the picture and then click the ID and add it right back and then it automatically goes right back so good now we're set now we've created our contact manager when we selected that is how we do it let's take a look add new contact cancel new delete contact worked we've tested that all right we are good to go I'm really excited I was able to show you this from beginning to end a lot of you've been requesting that if you do like this video make sure you download it and test it out yourself play with it make changes for you remember this is very important if you want to increase these columns right or if you want to make any changes to Rose you must update this mapping this mapping has to update any changes you make rows or columns and you will want to make changes make sure you update the mapping accordingly so you don't into too many bugs so I've seen that that will help you out again all I ask if you just share this video I really appreciate that subscribe to our YouTube channel and our Facebook of course join our Facebook group we have nearly 10,000 Excel experts at this stage waiting to help you so go ahead and if you have any questions join that group there make sure you see the links in the description thank you so much for joining me today [Music]

Channel: Excel For Freelancers

Views: 1,054,062

Rating: 4.8881001 out of 5

Keywords: Excel Data Entry Form, Microsoft Excel Data Entry Form, Excel Data Entry Form Without Userform, Excel Contact Manager, Data Form In Excel, Create Data Form in Excel, Excel Form, Create Excel Form, Saving Data in Excel, Saving Form Data in Excel, Load Form Data in Excel, Save Form Data in Excel, Data Form Without UserForm, Data Form in Excel without User Form, How To Create A Data Form In Excel, Excel UserForm Data

Id: SnA3cct-2IM

Length: 70min 51sec (4251 seconds)

Published: Tue Aug 21 2018