How to Auto-Display a Default Value while Deleting a Value in a Drop-Down List in Excel?

While using the drop-down list in Excel, when we delete data selected from the list, we can see that the top of the list will be left empty. Sometimes this may create a problem, but we can solve this problem by displaying any word instead of an empty cell.

Read this tutorial to understand how you can auto-display a default value while deleting values in drop-down lists in Excel.

Auto-Display a Default Value while Deleting a Value in a Drop-Down List

Here we will insert VBA code into the sheet to complete our task. Let us see a simple process to understand how we can display a default value while deleting values in a drop-down list in Excel.

Step 1

Let us consider an Excel sheet that contains a drop-down list. If we delete the data from the source cell of the selected item, we can see an empty space at the top of the list, as shown in the below image.

Now to solve the problem, right-click on the sheet name and select view code to open the VBA application, and type the programme in the text box as shown in the below image.

Program

Private Sub Worksheet_Change(ByVal Target As Range)
   'Updated By Nirmal
   Dim xObjV As Validation
   On Error Resume Next
   Set xObjV = Target.Validation
   If xObjV.Type = xlValidateList Then
      If IsEmpty(Target.Value) Then Target.Value = "-Choose-"
   End If
End Sub

In the code, "choose" is the word that will be displayed if an object is deleted.

Step 2

Now save the template as a VBA-enabled template and close the VBA application using the command Alt + Q. Every time we delete an object from the list, "Choose" will be displayed in its place, as shown in the below image.

Conclusion

In this tutorial, we used a simple example to demonstrate how we can display a default value while deleting a value in a dropdown list in Excel.

Pradeep Kumar

e

Updated on: 03-Jan-2023

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started