How To Create A Fillable Form With A Submit Button In Excel

hey everybody melissa here so i was asked if it's possible to create a fillable form with a submit button in excel just like we did in word and it absolutely is now if you're sitting there scratching your head going why would i ever want to do that i get it it doesn't make sense but here's the neat part if you have data sitting in any of your worksheets within that workbook then you can auto populate certain parts of that form based on what the user enters and the neatest part is they won't know the difference they won't see the database and it'll look just like it does in microsoft word i cannot wait to show you how this works so let's go ahead and get started so before we start i wanted to give you an idea of what our form is going to look like so this is our fillable form with our submit button if you notice we do not see any grid lines like we would normally see and it looks just like a form would in microsoft word except for we are in excel so with that let's go ahead and start building our form so the first thing we want to do is format the layout of our form and we're going to do that like we would do any other page layout so go to page layout go to size and tell it eight and a half by eleven if you notice it puts a dotted line in a column and a row and that lets us know that our form needs to stay within this area now let's add our form header and logo so what we're going to do is go to row one and we're going to expand it down quite a bit and go from column a to j because that's where our border is and we're going to tell it to merge and center and we're going to add our text and this is called employee appreciation lunch i'm going to take this text we're going to make it quite a bit bigger maybe about 24 and then i'm going to line it to the left and in the middle now let's add our logo now to add your logo you'll go to insert illustrations pictures and then you'll pull it in from wherever it's at whether it's your one drive your hard drive or wherever now i'm going to make this a little bit smaller and then i'm going to pull it up maybe make it a little bit bigger but wherever it is that you want it placed and that looks good so here is the information we need them to fill out on the form we have their name their manager what they're eating what time they're attending and allergy information so what i'm going to do to make it easier for them is i'm going to highlight any cell that they need to either input information or make a selection so we need their first name last name manager now if you notice i did not highlight manager title department or email because this is going to auto populate based on what they select in manager in time and attendance i highlighted column a that's because we're going to put check boxes over here for them to select now a couple things that i want to do formatting wise is in list of food allergies they're going to need more room to type that in so i'm just going to go ahead and merge and center these so that they have more room to type now down here where it says instructions there is going to be text that populates here automatically based on what they have entered in the epipen requirement so underneath instructions i'm just going to expand this box just a little bit and i'm going to merge and center these to make sure that there is room for whatever it is we're going to display now we're going to be adding some drop downs to the form for them to make selections from and we're going to be working with two different types the first type is going to be a drop down that exists within the form itself the second type is going to be a drop down that references data in a database or another worksheet such as table 1 or database within the workbook so let's go ahead and create one that exists within the worksheet itself so meat and sides is going to be a drop down from within the form itself so if we select meat and we go to data and we go over to data tools and do our drop down on the bottom here we're going to do data validation we're going to come down to allow and we're going to tell it a list now in our source make sure you click in there this is where we're going to tell it what to display in the drop down and let's just say we're having chicken and separate them by commas pork and steak sounds like my kind of selection and tell it okay now if you notice we've got the drop down arrow here we select it and we have chicken pork and steak now we're going to do the same thing for sides data data tools the drop down data validation tell it a list and in our source let's put baked potato mac and cheese and a salad and tell it okay and there you have that drop down that has baked potato mac and cheese and salad next we want to create a drop down that is referencing data in a database or another worksheet within our workbook and we're going to do that with manager so if we go to our manager cell the first thing that we want to do is make sure that our data in our database is formatted as a table so if we go over to database 1 this has all of our manager information our title department email and their name and it is not formatted as a table right now so if we click anywhere within this data and we do ctrl t it's going to say create a table my table has headers we can tell it yes or no i'm going to tell it yes and tell it ok and if you notice it changed it to a table now for manager i'm going to go ahead and i'm going to change this to please select if i can get my fingers on the right keys that way that's the first thing showing in my drop down now if we go back over to our form and to manager we go to data data tools do our drop down data validation and we're going to tell it a list and in our source instead of putting information separated by a comma we're going to go over to database go to column a and select all of column a so just click at the top and tell it okay now when we do our drop down we have please select as our first one then we have manager names now the beauty of what we just did by selecting over in our database tab all of column a is if we add another manager and he is the director of hr and he's in hr and at your email com and we hit enter we go back to our form do our drop down and chris smith is automatically added so any changes or additions or deletions will automatically reflect in your form so if we select a manager from here our manager title department and email is not going to auto populate so we're going to have to do a vlookup so it will pull that data from our database so i'm going to go ahead and back this out now we're going to do the vlookup from the manager title cell and i can do equals vlookup from directly in the cell but i want to do it from our functions argument box so that it makes it a lot more clear as to what we're asking the vlookup to pull so if we go to insert function and i've used the vlookup before so it is here tell it okay our lookup array is what are we looking for well we're looking for the manager our table array is where are we looking well we're going to look in our database sheet so we're going to click on source we're going to click on database and we're going to select the table because that's where we're looking we're going to hit enter our column index number is which column are we looking for well our title is column two so we're going to go ahead and tab to our column number and we're going to tell it column two and our range lookup is either true or false probably about 99 of the time you're going to use false because true means it will return the value or a similar value false means it's going to return an exact match only and that's what we want it to do so we're going to do false and we're going to tell it okay now if you notice we have a hash in a so let's go ahead and address this before we go any further if we select a manager here like arnold wilson it's going to take that hash na out and replace it with his title if we take it out then it's going to take it back to a hash n a and basically what that is saying is i don't see a blank manager or a manager name that's blank within your database so i don't know what to return to you so what we're going to do is use the function if in a to correct this n a error now you could use if error but i don't like to use that when i'm only dealing with if in a because if error will find anything it thinks is an error and in this specific scenario that can cause more issues than then help us so i'm going to go up to my vlookup on my function line and i'm going to do if in a open parenthesis leave my vlookup and typed i'm going to do a comma now this final argument is what do we want it to return instead of that hash in a if it doesn't find anything well i want it just to leave it blank so i'm going to do a quote and another quote with nothing in between it and that tells it to leave it blank then i'm going to close my parentheses and i'm going to hit enter and if you notice it no longer says hash n a it's just blank if we select a manager bill rodis then it's going to put his title so let's go ahead and back this back out so we want to do the same thing for department and email so we're going to copy this vlookup down to these cells but the only thing we're going to have to change is which column they're in title is in column two department is in column three and email is in column four so i'm going to copy my vlookup now make sure you go to the end and you hit a space and you hit enter that way it doesn't mess it up here we're going to control v and everything is the same except for our 2 we need to change to a 3. and then do the same thing in the last one and instead of two change it to column four now when we go up and we select let's say amelia tucker it's going to pull in her manager title her department and her email next we're going to look at this instruction box now it is going to populate different ways depending on how they answer the epipen required question for example if it's left blank or they say no then this instruction box will stay blank if it is answered yes then a special message is going to pop up in this instruction box so we can put our yes and no drop down in this epipen required box one or two ways we can go up to data go over to our data tools do our drop down data validation list and we can put in here please select yes no and it's going to add our drop down the other way that we can do it is if we have data in another worksheet that's in a table such as table 1 we can do ctrl t make it a table and i'm going to change this column 1 to please select then we can go back to our form make sure we're in the correct field data data tools validation list go to our source but this time click on the up arrow go to our table and select our table and tell it okay and then we've got please select yes or no so either way you want to do that that is up to you now to get our conditional messaging to display in the instructions based off of the epipen required field we're going to use an if statement so we're going to do equals if open our parentheses the epipen required field which is c22 equals yes then what if it equals yes we want it to display the message please set up meeting with your supervisor and hr for further discussion and you want to put that into quotes then we're going to put a comma and if it's anything other than yes so if it's blank please select or no then what do we want it to do well we want it to remain blank so we're going to put a set of quotes and we're going to close our parentheses and hit enter now as you can see it's blank because this is blank and if we go up and we select yes then it displays please set up meeting with your supervisor for further discussion now we're going to add our check boxes for time of attendance we need to know if they are attending at 11 12 or 1. so in order to add those we're going to do it from the developer tab if you do not see this tab you can go to file down to options customize ribbon and under main tabs on your right hand side go down to developer and make sure it is checked and then it'll show up on your ribbon so under developers we're going to go down to insert and under form controls we're going to click on the check box and we're going to draw it now if you notice there's some text out here if we expand this out we can get rid of that text because we don't want it we just want the actual checkbox if you needed to label it then you can change it to whatever you need it to be but in this case we don't now we want to move it to where we want it and let's just say that looks good now what we can do instead of redrawing it into every one you can just pull it down and it'll put it into each cell if you do need to format it or change the size just hover over the check box right click and it will bring up the box for you to resize or rename or anything like that now that we have our layout completed with the exception of the submit button which we will get to in just a minute i want to do some additional formatting so let's just say on my header i want to put a border around this let's go up to our borders do a thick border and you can see it i want to put something on the instruction box and let's just say i want to put an outside border now if you notice we are right up against where our margin is for our eight and a half by 11 box now what i generally like to do is add a little buffer in between here so i'm going to take column g and i'm going to make it very small and if you notice it shifted that margin just a little bit so that it's not right up against any of my borders and there's just a little bit of room now to make it look like a fillable form in word instead of an excel document there's a couple more things we need to do now the first thing that we want to do is hide all of our grid lines so we're going to go up to page layout and we're going to go to grid lines and we're going to uncheck view now the grid lines are gone on the side and the bottom so the next thing that we want to do is select the first column outside of our margin which in this case is column h and we're going to do control shift right arrow and that's going to select every column in our excel document we're going to right click and we're going to hide and as you see there is nothing past our column g we're going to do the same thing with our rows we're going to come down to row 43 which is the first one past our margin we're going to select it ctrl shift down and that's going to select every single row in the excel sheet right click and hide and now all we have is our form now the next thing we want to do is hide any worksheets that have tables or databases on them because we don't necessarily want our end users to see those now we can right click and we can hide them but if you have any sort of excel user they're probably going to know they can come in and unhide that and then tell it to unhide the table and tell it okay now there is another way to do it but i'm going to caution you if you have an advanced excel user they may know a way to get around this so if it's anything confidential like employee social security numbers salaries vendor pricing or anything that's confidential be very careful putting it in here so from table 1 we can right click we can go to view code and then this will bring up a visual basic area now with sheet 2 or table 1 selected if we go down to visible we can tell it very hidden under database we can do the same thing and tell it very hidden we can save we can x out the forms are gone and if they go to right click unhide is grayed out now again you may say oh well they can never undo that if you have an excel user that knows visual basic they'll be able to see this information so again word of caution so now our databases and our tables are hidden so this is all that we have so if you want to use the form without the submit button then you're done except for protecting the form if you go to the next to the last chapter it's listed as protect form in the description then it'll show you how to protect the form you can send it then via email or put it on a sharepoint and they can fill it out get it back to you or however you want to do that if you want to use the submit button then stick around the submit button with the visual basic code is coming up next all right here we are it's time to add the submit button so a disclaimer before we get started this will only work with microsoft outlook it is not compatible with yahoo gmail hotmail or anything else the end user and you both must have microsoft outlook for this to work all right let's get going so we're going to go to the developer tools to create the submit button so under developer we're going to go to insert and instead of form controls we're going to go to activex controls now another word of caution or a disclaimer if you send this to your user or your employee and they have trouble opening it the first thing you probably want to do is check with your i.t department some of them lock down activex controls because make no mistake this is code and code can carry viruses so check with them see if it's locked down if that's not the case then drop me a comment and i'll see if i can help you so under activex controls we are going to go to what i have always said looks like an eraser but that is not what it is so go ahead and click on the command button and we're going to draw it and i'm going to make it fairly large because you know i wear glasses there we go there's our command button now we can make formatting changes to the submit button we can right click go to properties and this box will pop up which is our properties box the first thing i change is the caption we don't want it to say command button one we want it to say click here to submit or whatever it is you want it to say we can change our back color by coming up to back color double clicking going to palette and choosing a color let's just say i want this to be green it changed it to green we can change our four color which is our writing to white or whatever color we want by double clicking on it going to palette and changing it we can change the font type by clicking the three dots and it'll bring up a font box where we can change the font style we can change the size and there's other options in here but that's really about all i usually change on one of my buttons so we can go ahead and x out of here now it's time to add our visual basic code now if you've never worked with visual basic don't worry i've put the exact code that you need down in the description except for a couple of changes which are minor and really easy so to get to our visual basic coding area we're going to double click on the submit button and it will bring us here now we want to go in between private sub and in sub and paste our visual basic code now we'll have two private subs remove one of them and we will have two n subs remove one of them because if we try to compile it or try to save it it will cause an error now a couple things that you might want to change is here in the xml body it says type the body of your message here like i'll make this one submit button test but you can make it employee luncheon employee survey whatever it is that you're sending to be filled out and then with x out mail the two now don't confuse this the two is not who you're sending it to it's who they are sending it back to so once they click on that submit button this is the email it's coming back to you can also put a cc email and you can put a blind cc email and then you can put a subject and i put here again submit button test okay so now we're going to save it and it's going to say the following cannot be saved in a macro free workbook do we want to save it as a macro free workbook no we do not we want to save it as a macro enabled workbook so then it's going to bring us to where we're going to save it it's defaulting to mine you can tell it to save it wherever you want to and we're going to do our drop down here type and tell it excel macro enabled workbook and tell it to save and then we can x out of here and now we're ready to protect the form so we can send it to our users okay so the last thing we need to do whether you are using the submit button or you're just using the form without the submit button is to protect the document we want the end user or our recipients to only be able to fill in what is highlighted and click the submit button so if we go up to review and we do protect sheet and we just do locked and unlocked and tell it okay they can't change anything on this sheet so let's unprotect it and what we're going to do is we are going to select all of our cells by using our control button and our mouse that we want them to be able to either change or make a selection so it's everything that is highlighted and once they're all selected from our home tab we're going to go to format and we're going to tell it to lock cells which in this case is going to unlock them and then we're going to go back to format and protect the sheet leave these both checked tell it okay and now if they try to change manager they'll get that horrible sounding error but if they try to type they can okay they can do the check boxes but they can't change the actual time of attendance and that protects your sheet from any inadvertent changes now as far as unprotecting the submit button we do not have to do that because that is all driven by visual basic code so that is a macro running on the back end and therefore it does not need protected and now our form is protected from any inadvertent changes now that everything is done and it's ready to go let's give it a test run so i'm going to go ahead and close out of it and from outlook i'm going to send it as an attachment so when you browse to its location make sure that you use the micro enabled document and i'm going to send it to one of my email addresses i'm going to put in a subject of test and send it on so i'm going to go ahead here and enable the content now let's do some tests so we've got our protection set and it is working so let's go ahead and fill out the document when i select my manager it is auto populating their title department and email i want steak of course mac and cheese i'm going at one i am allergic to everything and yes i have an epipen and there is our special instructions for that being yes now something really important here for your recipients and i will put it in the description as well they have to save this document somewhere on their computer before they hit the submit button and after they filled it out so once they get to this point they have to save it if you see this read only if they try to click the submit button from within here all of this data will be stripped and you will receive a blank form back so let's go ahead and save it and it's going to save it as copy of fillable form if you want them to just make it fillable form and change the name of it you can or they can just leave it as copy and you'll just know in your inbox that's what you need to look for so let's go ahead and save it and now you see the name is changed to the copy which is the one that they want to submit they'll click submit it will invoke outlook if you notice it has the email you ask it to send it to and then it has any information or text that you put into your visual basic code and then it has your subject that you put in your visual basic code so they can go ahead and send it then when it comes back in you can enable the content and you have all of the information they submitted in your form and there you have it how neat is that now you can create a fillable form with a submit button in excel and use data that's in your workbook to help populate it if you found this video helpful please be sure to give it a thumbs up drop me a comment if you have any questions feedback or ideas for future tutorials and be sure to click that subscribe button before you leave and i'll see you in the next tutorial until then thanks so much for watching [Music]

Channel: Melissa Compton

Views: 68,511

Keywords: How To Create A Fillable Form With A Submit Button In Excel, how to create fillable forms in excel, excel tutorial, add a submit button to a form, submit button for fillable forms, how to send submit form to email, vba code to send email from excel to multiple recipients, add command button in excel, vba code to send email, submit form to email, submit button, create a fillable form with submit button, melissa compton, how to, command button, MS Outlook

Id: K6swceuvVBA

Length: 29min 19sec (1759 seconds)

Published: Wed Jul 27 2022