How to use Excel VBA UserForms in Excel office 365?
Tags: touseexcelvbauserfor, vbamacros
Published: 2021-09-13 11:43:31

---
title: How to use Excel VBA UserForms in Excel office 365?
published: true
date: 2021-09-07 14:50:20 UTC
tags: ToUseExcelVBAUserFor,VBAMacros
canonical_url: https://geekexcel.com/how-to-use-excel-vba-userforms-in-excel-office-365/
---

In this tutorial, we will guide you on **how to use Excel VBA UserForms in Excel Office 365**. Let’s get them below!! Get an official version of MS Excel from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)**

## Create A Form for Investment in Excel and Store Record

In Excel Worksheet, you have to create **one textbox for name, one for age, one for investment amount, and a pair of radio buttons for gender**.

### Design the userform

- Firstly, in the Excel Worksheet, you have to go to the  **Developer Tab.**
- Then, you need to ** ** select the  **Visual Basic**  option under the  **Code**  section.

![Select Visual Basic](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- Now, in  **project explorer** , you have to **right-click on VBAProject** (your file).
- You need to click on **insert option** , and choose **UserForm**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Click-on-Userform.png)<figcaption>Click on UserForm</figcaption>

- After that, you have to **change the name of form** to **UserForm1** in the property box.

![](https://geekexcel.com/wp-content/uploads/2021/09/Change-the-name.png)<figcaption>UserForm1</figcaption>

- Then, the **property box** is shown in the **lower left of the project explorer**.
- Now, if you can’t see it, then go to **view** and click on **Properties** Window.

### Add elements to form

- Here, the **new userform** is completely blank, so you need to **add the elements to form**.
- For that, you have to use **tool box** to add elements to your form.
- If you **can’t see the toolbox** , you need to get it from **view tab**.
- Then, you have to **add labels** using **toolbox** for **descriptive names**.
- Now, you need to **add textbox** for **user inputs**.
- After that, you have to add **two radio buttons** and name them **Male and Female**.
- You need to **add command buttons** as **submit and cancel** to perform operation on given input.
- Finally, you will get the **userform** with all **elements**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Add-the-elements.png)<figcaption>Add the Elements</figcaption>

### Name Elements

- Here, the **textbox, labels, buttons** , etc. all are **elements**.
- Then, in order to use them in **VBA code,** you need to give them **names**.
- Now, you have to use the **property window** to change their name.

### Select the element

- You have to **select the textbox** for the name.
- Then, you need to go to the **property** window and change the name to **NameBox**.

![](https://geekexcel.com/wp-content/uploads/2021/09/NameBox.png)<figcaption>NameBox</figcaption>

- Now, you have to do the same for **each element** you are going to use.

![](https://geekexcel.com/wp-content/uploads/2021/09/For-all-data.png)<figcaption>For all the Data</figcaption>

### Show User Form to User:

- Here, you have to use a **command button** to trigger **user form**.
- Firstly, in the Excel Worksheet, you have to go to the  **Developer Tab.**
- Then, you need to ** ** click on the **Insert** icon and select **Button** option.

![](https://geekexcel.com/wp-content/uploads/2021/09/Select-Button.png)

- Now, you have to **rename it to open form**.
- After that, you need to **right-click** on it and click on **assign macro** and then click on **new**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Click-on-Assign-Macro.png)<figcaption>Click on Assign Macro</figcaption>

- Here, the **sub** will be **created** immediately.
- Then, you have to **copy-paste the following code**.

```
 Sub Open_Form()
'Opening form
InvestmentForm.Show
End Sub

```

- Now, you need to **save the code** by selecting it and then go **back to that sheet** and click on the **button**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Save-the-Code-21.png)<figcaption>Save the Code</figcaption>

- Finally, after clicking on the **button** in the Excel, the **user form** will pop up.

![](https://geekexcel.com/wp-content/uploads/2021/09/UserForm-1024x623.png)<figcaption>UserForm</figcaption>

### Fill sheet using VBA user form

- Firstly, you have to go back to **VBA** and **double-click** on the **Submit** button.
- Then, a **new sub** will **create** automatically, and this **sub is embedded** in the form, and you can’t find it in any **module**.
- Now, you need to write the following the **VBA code in that sub**.

```

Private Sub SubmitButton_Click()

Sheet1.Activate

'get first empty row on sheet (read about it here)
lstrow = Cells(Rows.Count, 1).End(xlUp).Row
Set firstEmptyRow = Range("A" & lstrow + 1)

'initialize each cell with data
firstEmptyRow.Offset(0, 0).Value = nameBox.Value 'first cell
firstEmptyRow.Offset(0, 1).Value = AgeBox.Value 'first cell to the right
firstEmptyRow.Offset(0, 3).Value = InvestBox.Value 'third cell to the right

'checking radio button
If MaleOption.Value = True Then
    firstEmptyRow.Offset(0, 2).Value = "Male" 'second cell to the right
Else
    firstEmptyRow.Offset(0, 2).Value = "Female" 'second cell to the right
End If

'Closing form
Unload Me

End Sub

```

- After that, you need to **save the code** by selecting it and then **close the window**.

![](https://geekexcel.com/wp-content/uploads/2021/09/VBA-Code-1024x1000.png)<figcaption>VBA Code</figcaption>

- Here, the above **snippet of VBA code** runs when **submit button** is clicked.
- Then, it finds the **first empty row on the sheet** and fills it with **values** provide in form.

![](https://geekexcel.com/wp-content/uploads/2021/09/Fill-the-value-1024x662.png)<figcaption>Fill the value</figcaption>

- Now, you will get the all **data** which you **entered the userform** in **Excel**.

![](https://geekexcel.com/wp-content/uploads/2021/09/UserForm-Data.png)<figcaption>Data in UserForm</figcaption>

- Finally, in the end, it **closes the form** by using command **Unload Me**.

### Use Cancel command to close the form.

- In case if you want to **abort the input** , you can use **cancel** button.
- Then, in that case, you need to **double-click** on the **cancel command button** in VBA editor.
- Now, the **new sub** will be created in the project explorer.
- After that, you have to write the below **code to close the user form**.

```

Private Sub CancelButton_Click()
'Closing form
Unload Me
End Sub

```

- Then, you need to **save the code** by selecting it and then **close the window**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Closing-Code.png)<figcaption>Closing Code</figcaption>

- Now, after **entering the data** , and you have to click on the **Cancel** button.

![](https://geekexcel.com/wp-content/uploads/2021/09/Enter-the-data-1024x510.png)<figcaption>Click on Cancel Button</figcaption>

- Finally, the **form code** will display as given below.

![](https://geekexcel.com/wp-content/uploads/2021/09/Final-output-1.png)<figcaption>Final output</figcaption>

## A Short Closure

We hope that this short tutorial gives you guidelines ** ** on how to **use Excel VBA UserForms in Excel Office 365. ** Please leave a comment in case of any  **queries,**  and don’t forget to mention your valuable  **suggestions**  as well. Thank you so much for Visiting Our Site!! Continue learning on **[Geek Excel](https://geekexcel.com/)!! **Read more on [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

**Keep Reading:**

- **[Create the Data Entry Form using Macros (VBA) in Excel Office 365!](https://geekexcel.com/create-the-data-entry-form-using-macros-vba-in-excel-office-365/)**
- **[How to Select Chart Elements in Excel Office 365 ~ A Complete Guide!!](https://geekexcel.com/how-to-select-chart-elements-in-excel/)**
- **[Excel Formulas for Counting the Unique Numeric Values in a Range!!](https://geekexcel.com/excel-formulas-for-counting-the-unique-numeric-values-in-a-range/)**
- **[How to Add and Remove Chart Elements in Excel Office 365?](https://geekexcel.com/how-to-add-and-remove-chart-elements-in-excel-office-365/)**
- **[Difference Between Workbook and Worksheet in Excel ~ Easy Tutorial!!](https://geekexcel.com/difference-between-workbook-and-worksheet/)**