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.