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

Get FREE Advanced Excel Exercises with Solutions!

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.

vba code for submit button in excel


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)

Make Submit Button Using Controls Command

  • 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.

Make Submit Button Using Controls Command

  • 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.

Make Submit Button Using Controls Command


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.

Run VBA Code for Submit Button

  • 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.

Run VBA Code for Submit Button

  • 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

Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo