While working in Microsoft Excel sometimes we need to use VBA code for the submit button in Excel. We can easily do that in Excel. This is also a time-saving task. Today, in this article, we will learn three suitable steps to use VBA code for the submit button in Excel effectively with appropriate illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Suitable Steps to Use VBA Code for Submit Button in Excel
Let’s say, we have a dataset that contains information about several Sales representatives of XYZ group. The Name of the Sales representatives and their sales in several quarters are given in columns B, C, D, and E respectively. From our dataset, we will use the VBA code for the submit button in Excel. Here’s an overview of the dataset for today’s task.
Step 1: Create a Dataset with Proper Parameters
In this portion, we will create a dataset to use VBA code for the submit button in Excel. We will make a dataset that contains information about several Sales representatives of the XYZ group. We will use the VBA code for the submit button for the Sales representatives to send their performance through email. So, our dataset becomes.
Step 2: Make Submit Button Using Controls Command
Now, we will make a submit button using the Controls command under the Developer ribbon. To do that, follow the instructions below.
- To make a submit button from your Developer ribbon, go to,
Developer → Controls → Insert → Command Button (ActiveX Control)
- As a result, you will be able to make a submit button which has been given in the below screenshot.
- Now, we will change the name of the created CommandButton1. To do that, press right-click on that button. As a result, a window appears in front of you. From the window, select the Properties option.
- Hence, a Properties dialog box pops up. To change the name and color of the CommandButton1, do like the below screenshot.
- After that, you will be able to change the name of CommandButton1 to Submit.
- How to Set Option Button Value in Excel VBA (4 Easy Steps)
- Insert Excel VBA Radio Button Input Box (3 Easy Methods)
- How to Create Button Without Macro in Excel (3 Easy Ways)
- Add Up and Down Buttons in Excel (2 Suitable Examples)
- How to Change Cell Value Using Toggle Button in Excel
Step 3: Run VBA Code for Submit Button in Excel
Last but not the least, we will apply a VBA code to activate the Submit button. Let’s follow the instructions below to learn!
- First of all, press the right-click on that button. As a result, a window appears in front of you. From the window, select the View Code option.
- Hence, a module named Submit Button.xlsm – sheet4 (Code) pops up. Paste the below VBA code in that module and save the code using the Ctrl + S key on your keyboard.
Private Sub CommandButton1_Click() Dim x As Object Dim y As Object Dim R As Object Dim M As String On Error Resume Next Set y = CreateObject("Outlook.Application") Set R = y.CreateItem(0) M = "Write down your email message here" On Error Resume Next With R .To = "[email protected]" .CC = "" .BCC = "" .Subject = "Enter the Email Subject Here" .Body = M .Attachments.Add ActiveWorkbook.FullName .Display 'or use .Send End With On Error GoTo 0 Set R = Nothing Set y = Nothing End Sub
- After that, go back to your worksheet, and press the Submit button to send an email.
- As a result, a message box will appear in front of you. From the message box, type your mail in the message writing box. At last, press the Send Hence, give your mail address to send your mail.
Things to Remember
👉 You can also pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously on your keyboard.
👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,
File → Option → Customize Ribbon
I hope all of the suitable methods mentioned above to use VBA code for the Submit button will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
- How to Make a Calculate Button in Excel (with Easy Steps)
- How to Clear Cells in Excel with Button (with Detailed Steps)
- Print to PDF Using Macro Button in Excel (5 Macro Variants)
- How to Hide Columns with Button in Excel (4 Suitable Methods)
- How to Edit a Macro Button in Excel (5 Easy Methods)
- Excel VBA: Form Control Checkbox Value (3 Examples)