Customize the Excel Worksheet Data Entry Form

Customize the Excel Worksheet Data Entry Form



on the contexture x' website there's a worksheet data entry form that you can download and then customize to your own needs so we'll take a look at just the basics of how it's set up and then how you can change things to either add fields or change what's on this sheet the first thing to do when you open the file is make sure that macros are enabled so I'm in Excel 2010 I'm just going to click enable content in Excel 2007 you'd also see something above the formula bar or you might see a pop up message in some versions asking you to enable macros so go ahead and do that and I'm not going to make this a trusted document right now so I've enabled the macros and that will make all these buttons work so we can add or update records or go and look at what we've stored or scroll through the records and to change something now perhaps I just want to change a heading so instead of location I might want to call it area or instead of cost make it price if you change headings here then go to the parts datasheet and make the same changes here so this would be area and we've already changed this one to price so just make your headings here match what you've got on the parts data sheet going back to the input sheet there are some hidden columns between D and G so I'm going to highlight from D to G and then right-click and unhide these columns let us mark the fields that are optional and the ones that are mandatory so I've put an X for any mandatory field left blank the ones that are optional there's a formula in the next column that just checks to make sure there's an entry in all of the mandatory fields so if we want to add a new field here perhaps we need to keep track of color or saw eyes as well as as the part name so I want to insert a new set of cells right in here so I'm going to select all four of those cells and then on the ribbon I'll go up to insert cells and shift down okay so that gives me a new row in my table and I'll put color then I'll leave that as optional so I'm not going to put an X there and to get the formula from the cell above I'll just fill that down so that there's a formula there now in this cell there's a drop down that picked up the drop down for part so I could either remove the data validation and just let people type or I'd have to make a new list to use for that data validation cell there's a lookup list worksheet where I've got my part names and location so you could add more lists to this sheet if you want more data validation for now I'll just go to the data validation and I'll clear it people could just type a color in there when you've added your column here go to the parts data sheet and insert a column with the same heading so that when you copy the data there will be a spot for it to go then when you've finished just select a and F right click and hide so that the users aren't confused by seeing those columns that they shouldn't be doing anything in and now if we update this record 10 102 and go to the database we can see that it's updated it and put the color in so you add new fields and just make sure there's a column for that data so that everything lands in the right spot