How to Use VBA Code for Submit Button in Excel (with Easy Steps)

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.

Table of Contents hide

Download Practice Workbook

3 Suitable Steps to Use VBA Code for Submit Button in Excel

Step 1: Create a Dataset with Proper Parameters

Step 2: Make Submit Button Using Controls Command

Step 3: Run VBA Code for Submit Button in Excel

Things to Remember

Conclusion

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Submit Button.xlsm

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.

Similar Readings

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

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.