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.
VBA Code for Submit Button in Excel: 3 Suitable Steps
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.
Read More: How to Create a Macro Button in Excel
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.
Similar Readings
- How to Create Button to Link to Another Sheet in Excel
- How to Set Option Button Value in Excel VBA
- Insert Excel VBA Radio Button Input Box
- How to Create Button Without Macro in Excel
- Add Up and Down Buttons in Excel
- How to Change Cell Value Using Toggle Button in Excel
- How to Change Color of Toggle Button When Pressed in Excel
- How to Add Command Button Programmatically with Excel VBA
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.
Read More: How to Create & Apply Option Button Click Event in Excel VBA
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
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
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.
Related Articles
- How to Make a Calculate Button in Excel
- How to Clear Cells in Excel with Button
- How to Edit a Macro Button in Excel
- How to Add Radio Buttons in Excel
- Radio Button in Excel Without Macro
- How to Group Radio Buttons in Excel
- How to Add Option Button in Excel
- How to Use Option Button in Excel
- How to Remove a Form Control in Excel
- How to Create Chart Slider in Excel
- How to Make Games in Excel