In this article, we will show how to reset ComboBox to default value using VBA code. ComboBoxes are a great tool to store a list and then select from to perform different operations. It is required for the Combo Box to have a default value from the list so that it resets to that value after the operation. This means the completion of the operation. A default value also means the beginning of the operation. So, it is very important that the Combobox resets to its default value.
Download Practice Workbook
You can download the practice workbook here.
2 Ways to Reset Combobox to Default Value Using VBA
In this article, we will use VBA codes to reset ComboBox to default value. Firstly, we will use the VBA code on an ActiveX Control Combo Box to reset the value. Finally, we will use another VBA code on a Form Control Combo Box. Here, we have a dataset which shows the revenue of a company by months. We will insert a ComboBox and add the names of the months as a drop-down list inside the box. Then, we will apply VBA code to reset the list value.
1. Inserting Combo Box from ActiveX Controls
ActiveX Controls are built-in objects in Excel that are built to perform different functions. Here, we will insert a ComboBox from the ActiveX Controls. Then, we will add a list in it and finally assign a VBA Code to it to reset the value.
Steps:
- Firstly, go to the Developer tab.
- Secondly, select the Insert option.
- Finally, select the Combo Box (ActiveX Control) under the ActiveX Controls section.
- After that, add a Combo Box under the Months column.
- Then, right-click on the box and from the drop-down choose Properties.
- In the Properties box, write B5:B16 as the ListFillRange.
- Consequently, the list of months will be added as a drop-down list in the box.
- Thereafter, select the Developer tab.
- Next, click on Design Mode.
- Now, right-click on the box and select View Code.
- Consequently, a coding module will be on the screen.
- In the module, write the following code and save it.
Sub ResetComboBox()
Dim am_list As Object
On Error Resume Next
For Each am_list In ActiveSheet.OLEObjects
am_list.Object.ListIndex = 0
Next
End Sub
- Now, run the code by clicking the green triangle.
- As a result, the list in the Combobox will be reset to the default value which is the first value of the list in this case.
Thus, we will reset the Combobox to default value.
Read More: How to Use ListFillRange Property of ComboBox in Excel
2. Using Combo Box from Form Controls
In this method, we will apply a simple VBA Code to a Form Control Combo Box to reset the value to default value. The Form Controls are built in objects to perform task of data handling in Excel. Here, we will insert a Form Control Combo Box with a list and then reset the list to its default value.
Steps:
- To begin with, select the Developer tab.
- Then, choose the Insert option.
- Finally, select the Combo Box (Form Control) under the Form Controls section.
- Then, insert the box in the sheet.
- After that, right-click on the box and from the available options, select Format Controls.
- As a result, a prompt will be on the screen.
- In the prompt, write the B5:B16 as the Input Range.
- Then, choose the cell E7 as the Cell Link.
- Finally, click OK.
- As a result, Excel will add the list to the box.
- Now, go to the Developer tab.
- From there, choose Visual Basic.
- As a result, a new window will appear on the screen.
- In the Visual Basic window, choose Insert option.
- Then, from the available options, choose Module.
- As a result, a coding module will appear.
- Then, write the following code in the module and save it
Sub ResetComboBox()
Sheets("Form_Controls").Range("E7").Value = 1
End Sub
- Finally, run the code by clicking on the green triangle.
- As a result, the list will be reset to its default value.
Read More: How to Use VBA to Populate ComboBox List from Range in Excel
Conclusion
In this article, we have discussed 2 methods to reset Combobox values to a default value using VBA Codes. This will allow users to reset the list value after every operation. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.
Related Articles
- How to Add Item to ComboBox with VBA in Excel
- Create Cascading Combo Boxes in Excel VBA UserForm
- Excel VBA ComboBox: Important Properties to Explore
- How to Clear Items from VBA ComboBox in Excel
- Create a Searchable ComboBox with VBA in Excel
- Insert ComboBox with Listindex in Excel VBA (with Easy Steps)
- How to Use ComboBox to Get Selected Item Utilizing VBA
