How To Create A Fillable Form With A Submit Button In Excel
Вставка
- Опубліковано 21 лип 2024
- In this tutorial you will learn how to create a fillable form with a submit button In Excel. This fillable form can be sent out via email. The recipient then fills out the form, saves it and clicks the submit button. The VBA code running in the background opens outlook, attaches the completed form in email and sends it back to the email address specified. Once received, you can open it and in MS Excel and see the answers to the questions.
Please keep in mind this fillable form is only compatible with MS Outlook. It will not with with Gmail, Hotmail, Yahoo or any other services.
This process works best if the user saves the fillable form to their desktop or documents after filling it out. They can then click the submit button and the form will be sent back to you.
Chapters:
00:00 - Intro
00:53 - Completed Form Sample
01:18 - Form Page Layout
01:40 - Form Header and Logo
02:42 - Add and Format Form Fields
03:59 - Drop Down List From Within Form
05:48 - Drop Down List From Database or Table
08:02 - Use VLOOKUP to Auto Populate Fields
09:38 - Fix #NA Error in Excel
11:20 - Copy VLOOKUP to Other Fields to Auto Populate
12:14 - Display Special Instructions Based on Cell Value
14:47 - Insert Check Boxes
16:06 - Formatting The Form in Excel
18:14 - Hide Table and Database Sheets
20:15 - Create and Format the Submit Button
22:44 - Add Visual Basic Code
24:45 - Protect Form in Excel
26:27 - Test Run
28:42 - Wrap Up
Here is the the Visual Basic (VBA) code needed to create the submit form button to submit the form to email. You can also use this VBA code to send the completed form from to multiple recipients. **Be sure to update the Subject, Body, Email To, Email CC, Email BCC, and additional Text with your information.**
Private Sub CommandButton1_Click()
Dim xOutlookObj As Object
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Type the body or your email message here" & vbNewLine & vbNewLine & _
"Use this if you want a separate line of text" & vbNewLine & _
"Use this if you want another separate line of text"
On Error Resume Next
With xOutMail
.To = "youremail@email.com"
.CC = ""
.BCC = ""
.Subject = "Enter the Email Subject Here"
.Body = xMailBody
.Attachments.Add ActiveWorkbook.FullName
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Support me with your amazon purchases: melcompton.com/recommends/ama...
Check out these programs I use for UA-cam
Bluehost discount: melcompton.com/recommends/blu...
Canva Pro Trial: melcompton.com/recommends/can...
Adobe: melcompton.com/recommends/adobe/
Filmora: melcompton.com/recommends/won...
Follow me on social media:
Facebook: / therealmelco. .
Twitter: @melissaecompton
Instagram: @melissaacompton
LinkedIn: / melissa-c. .
This description contains affiliate links and I may be paid a small commission should you purchase using these links.
#melissacompton #excel #FillableForm - Навчання та стиль
How do I save this form as a Template
Hi Susan. When you go to save as there is an option in the drop down called "Excel Macro-Enabled Template." It's a .xltm extension which should work with the Visual Basic Script. Let me know if you have any issues! Thank you and have a great day! :)
Hi Melissa, this is really helpful! I hope you could also do another video, instead of sending it via email. The information will dump to another sheet on the same file. Thank you!
I truly appreciate your exceptional effort in meticulously addressing nearly every specific requirement for those Excel forms. Thank you for your outstanding work!
Really helpful video. Very clear and not too roomy.
Your style is easy to follow. You are a brilliant and a super effective teacher!
Hi Melissa C.,
You're a RockStar! At times(for me), Excel is an alien from another planet, 🤯. Your visual instruction is great! The topics that you cover, examples and tips that you share are perfect; most of all, your voice is not annoying 🙄 its easy listening 🙂. Their are some Excel docs I need to create at work and your video is one of the first videos I viewed👌and will be referring to it ALOT, TY 👏👏🫶
Thank you! I'm glad it helped!
Thank you! I really enjoy your tutorials!
Thank you so much Melissa! You are an excellent teacher.
Great lesson, Melissa. I appreciate your support.👏🏿
Thank you! I'm glad it helped!
Hiii! First of all, super helpful video.
Great video! Thanks for sharing
great video, I used some of the information you shared to create a form in excel, then the user can print it as a pdf so they can digitally sign it. thank you for sharing this.
Great video, thank you so much!!
Great video/tutorial. Thank you. Is there a way to just send one Worksheet, rather than the whole file?
I am Boring (normal) but not anymore thanks to your video. Thanks you Melissa. More please ....
Thank you! I'm glad it helped!
Your tutorial was great, managed to make my form - was a complete basic excel user before. But i left an email address out - how can i go back in and add it to the code please?
Thank you so much!! Very helpful. A couple of questions, if you don't mind, how do we add multiple "to" emails? Do we use commas? Can I make the Subject dynamic, meaning, can I use one of the entries from the form as part of the subject? E.g. Employee: Doe, when I click submit, the email subject will be Doe Order Form. Thank you -
Hi Melissa! Very helpful tutorial. Could you also do another one, instead of sending an email, the data on the form is saved to an excel spreadsheet? Or maybe just share the VBA code for it if you dont want to do another tutorial? 😅
Hello! I am always looking for ideas for tutorials. Especially using VBA. :) I will definitely put this on my list! Thank you!
I am using a mac and attempted to do this and everything works except the submit button. Would be nice if you showed how to go back into the VB code/macros after it was created in case you need to troubleshoot. I have no idea how to do that or solve why submit won't work. Otherwise, loved this video and I echo everyone's comments that we need the data to write into an excel spreadsheet upon submission.
Thank you very much!
Thank you! Glad it was helpful!
Awesom! Pls, I wanna know how to add a button that will enable the user to attach a photo from his/her file explorer. E.g. attaching proof of payment or something else. Thank you
This great! Thank-you. If I want to be the one who fills the form and sends it to the same recipient daily, (like an order form) can the “submit” button be made to disappear when the recipient opens it? Hopefully the question makes sense.
Thank you!
It would be great if there was a good solution like this for Excel for Mac.
Hello! Unfortunately Microsoft Office is not native to Mac's. They do not put as much functionality into the applications for the Mac because the programming language is different. Hopefully functionality will improve in the future!
"Super se Upper". Thank You !!
Hello, Sharon! Thank you so much for all the training you provide...so professional and well done. I do need to know how to bring back the lines on the worksheet that were deleted to create the effect of viewing a single piece of paper. I need to make changes to the form and am not able to add needed lines and information, etc. When I try to insert cells or lines it tells me I cannot "push non-empty cells off the worksheet". Thank you for your assistance!
Hi. Is this for someone else? I’m Melissa, not Sharon. :)
tnx a lot
great video
Hi Melissa. I really enjoyed your video to refresh my excel skills. But how do I do limit the user from clicking all boxes available. I only want them to click one. And I don't want to make a dropdown list since it's a ranking from 1-5.
Hello! Yes but it will take some extra VBA code. I will get it written and tutorial put out as soon as I can.
Great tutorial. Thanks. Almost done with my own form. Just trying to see if I can use a Vlookiup return data, in this case an email address, as the mail reciepient in the vba code on my submit button. I need to send the form to different recipients every time. Thanks.
Hi, I am trying to put a button directly onto my spreadsheet that will open the automatic form command to enter data into my table.
Thank you for the tutorial. What if you need to use this to capture daily records in a retail shop? You do not want to send the form to anyone but just to record transactions. Is it possible to fill the form as a way of recording transactions and the records are accumulated somewhere in a database as you click submit?
Hello! Sorry for the delayed response. What you are looking to do could be accomplished using either Microsoft 365 Forms or Google Forms.
This is awesome! But I am wondering if you can make the submit button populate a table/sheet elsewhere in the workbook instead of send an email.
Hello! I am working on a tutorial on how to do this. It will be out soon!
@@MelCompton I have the same question, using excel for team to submit time-sheets and the data gathered for analysis in another workbook. Are you able to say when the tutorial will be out?
Yes please!!!!
Would love to see this too!
@MelCompton do you have the link for this please?
THANKS for these videos-- GREAT JOB-- and i don't do a lot of office stuff BECAUSE I don't know how to do stuff like this-- eager to try this one.
Thank you! Glad it was helpful!
Hi, instead of sending it via mail can we save the data filled in the forms as customer database in another sheet in same file?
Hello, Melissa! So sorry for the confusion. Yes, it was for you!
How can i make each form as it filled with information automatically fill another list, and the other list will be as a table which represent the whole forms that have been submitted as each row of the new list or table will be for a form?
Is there a way to type custom text on a cell that has a list in it?
Hi Melissa, is there a way to have the metadata, attached to the submission, populate another spreadsheet in mobile version?
Hello! The mobile version is not as robust as the desktop or even online version. I will take a deeper dive into this and see what can be done.
Thank you for the video. However, ActiveX controls are not supported on the online version of excel. And that is what we use. Is there a workaround? Thanks.
Hi! If you are using Microsoft 365 online I recommend doing this to forms.
Hello my name is sean brown and I have an issue with the submit button function will you be able to assists
Hey Melissa, If you email this to 200 employees (for example), can you add code so that, once they click the submit button, all their responses are combined(consolidated) into one big excel spreadsheet/database, that then comes back to you? So you don't have to open 200 hundred different emails?
A simple solution that I use for that kind of thing is making a Google Form that live fills a Google Sheet, then I query that sheet to an Excel doc. Then I just share the Form link and *Bam*, my excel pulls all the data on refresh.
very good thank you but one question - this will only open Outlook as the mail client? what happens if the customer doesn't use Outlook as their mail client?
Hi! Yes that is correct. If they do not have outlook I would recommend using Google forms.
How can I add multiple email recipients or can it only be 1 To, 1 CC, & 1 BCC?
I followed all the steps you outlined (twice), but my Submit Form Button does not send the document via email. I'm on a iMac, does that make a difference?
Hello! This is only compatible on a windows machine. Microsoft does not fully code office for MAC so some of the functionality will not work.
Really Nice job but I use mac version of Excel. Mac Developer doesn't have ACTIVE X CONTROLS and it doesn't seem to excite on 365 either.
Hi! Unfortunately VB code is not compatible with MAC or web versions of 365.
@@MelCompton omg :( any work around?
Can I ask why would someone want to use Excel to make a fillable form instead of using word? Does excel offer more functions? I'm just a beginner with office.
Hello! Welcome to MS Office! Excel allows the use of databases to verify and auto-populate data in the form. Word does not have this capability. Starting around 5 minutes into the tutorial, I explain this in depth. :)
I'm not sure. I noticed one difference is Word would have prompted that Appreciation is misspelled and I didn't see Excel prompt a spell correct.
I'm here trying to learn Excel as well. Maybe Excel has a spell check box? Maybe it doesn't? I just wouldn't want to send out my form to my entire company with my spelling mistake in bold upfront & center. I think I would rather trust Word to spell check for typos.
Spelling aside, this is a well thought out and explained tutorial. Thanks!
But what if you have multiple people filling out the form?
Also I wanted I want this data to go into a data sheet.
Hi, how can I edit this code to have it save to a database (data table) in the same workbook instead of sending the replies to email?
Hello! If you are wanting them to fill it out and it go into a database I would recommend using Microsoft 365 online and using forms. I have a tutorial coming out for that soon.
@@MelCompton Please let me know when this "Form to Database" tutorial is available, it would be very helpful.
Thank YOu
How would we hide both the text Instuctions:" and the box if necessary?
Just delete the rows or do not create them when setting up the file. If you wan them there, but do not want to see them then you can change the Font and Boarder color to white so that they blend into the background.
How do I unhide a sheet after I hided using the View Code function?
Right click onto any of the visible Tabs at the bottom of the sheet with a sheet's name and then click "Unhide..." and select the sheet you want to unhide
Please let me tell about to: is put my email address??
Can you make a Fillable Form With A Submit Button In Libre office
Hi Vince. There is a way to create a fillable form with submit button in libreoffice writer. Here is my tutorial on how to do it. ua-cam.com/video/IJ1cGt-hops/v-deo.html
my submit button doesn't work
You missed spelled appreciated in your form
vey hot
Thank you! This was very helpful.
Great Video. Thanks for sharing.
Thank you! I'm glad it helped!