How to Reset ComboBox to Default Value Using VBA

In this article, we will show how to reset ComboBox to the default value using the VBA code. ComboBoxes are a great tool to store a list and then select from it 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.

How to Reset Combobox to Default Value Using VBA: 2 Ways

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.

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.

Download Practice Workbook

You can download the practice workbook here.

Reset ComboBox.xlsm

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, visit our website and unlock a great resource for Excel-related content.