How to Edit Drop Down List in Excel Macro (4 Easy Methods)

In this article, we will show you 4 methods of using Excel VBA to edit Drop Down List in Excel Macro. Time is very valuable, we can save a lot of time by skipping mundane tasks using Excel VBA. Moreover, We have taken a dataset consisting of 3 columns: “Name”, “Age”, and “Car Brand”. This dataset represents 6 employees’ car information and age for a particular company.

Download Practice Workbook

4 Ways to Edit Drop Down List in Excel Macro

1. Changing Cell Range to Edit Drop Down List in Excel Macro

For the first method, we will change our range to edit Drop Down List in Excel using a Macro. We can see that our range is from cell B14 to B16. Using a VBA code, we will edit the range to B14:B19. Before doing anything, first, we will show you how to input VBA codes.

Steps:

Before, typing our code we need to bring up the VBA Module. To do that –

• First, from the Developer tab >>> select Visual Basic.

Alternatively, you can press ALT + F11 to do this too. The “Microsoft Visual Basic for Application” will appear after this.

• Secondly, from Insert >>> select Module.

Here, we will type our code.

• Thirdly, type the following code inside the Module.

Sub Edit_Drop_Down_List_Range_Selection()
    Range("D5").Select
    ActiveCell.SpecialCells(xlCellTypeSameValidation).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$B$14:$B$19"
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
End Sub

Now, we will execute our code.

• First, Save this Module.
• Secondly, click inside our code.
• Finally, press the Run button.

Upon executing the code, we will notice our Drop Down List has changed. Thus, we have shown you the first method of editing Drop Down List in Excel.

Read More: How to Edit Name Box in Excel (Edit, Change Range and Delete)

2. Editing Drop Down List by Adding Items Manually

In this section, we will type the Drop Down List inside a VBA code and execute it to edit Drop Down List. Here, we can see there are 5 different car brands on the Drop Down List – Tesla, Nissan, Ford, Mazda, and Lexus. We will change this inside our code. Without further ado, let us jump into our guide.

Steps:

• First, as shown in method 1, bring up the VBA Module.
• Secondly, type this code inside that Module.

Sub Edit_Drop_Down_List_Add_More()
    Range("D5:D10").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Tesla, Ford, Toyota, Audi, General Motors"
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
End Sub

• Thirdly, as shown in method 1, execute the code.

In this way, we will change the Drop Down List using another Excel Macro.

Read More: How to Edit a Cell in Excel (4 Easy Methods)

Similar Readings

• Edit a Line Graph in Excel (Including All Criteria)
• How to Edit Chart Data in Excel (5 Suitable Examples)
• 7 Solutions for Greyed Out Edit Links or Change Source Option in Excel
• How to Edit Named Range in Excel
• Edit Links in Excel (3 Methods)

3. Using Dynamic Range to Edit Drop Down List in Excel Macro

For the third method, we will use VBA to find the last row of our input range and then use it to edit Drop Down List in Excel Macro. Here, we can see there are 5 items on the list. We will add another item to cell B19 and with a press of a button, we will change the range dynamically.

Steps:

• First, type “Audi” on cell B19.

• Secondly, as shown in method 1, bring up the VBA Module.
• Thirdly, type this code inside that Module.

Sub Edit_Drop_Down_List_Dynamic_Range()
Dim lrow As Integer
lrow = Range("B:B").SpecialCells(xlCellTypeLastCell).Row
    Range("D5:D10").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=B14:B" & lrow
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
End Sub

• After that, as shown in method 1, execute the code.

It will add “Audi” to the Drop Down List.

Now, we will remove a value from the list. For that, we will type another VBA code.

• First, as shown in method 1, bring up the VBA Module.
• Secondly, type this code inside that Module.

Sub Remove_From_Drop_Down_List()
    Dim cRange As Range
    Dim z As Integer
    Set cRange = Application.InputBox("Select Cell Range Here:", "Exceldemy", Type:=8)
    z = MsgBox("Do You Want to Delete" & cRange.Address, vbQuestion + vbYesNo, "Exceldemy")
    If z = vbYes Then
        cRange.Delete
    Else
        MsgBox "You Decided not to Delete"
    End If
End Sub

Now, we will insert the VBA button here.

• First, from the Developer tab >>> Insert >>> select Button (Form Control).

• Then, the mouse cursor will change and drag a box inside the Workbook.

After that, the Assign Macro dialog box will appear.

• Then, select “Remove_From_Drop_Down_List”.
• After that, press OK.

Then, we will see the Button in the Workbook. We have renamed it to “Delete From List”.

• Then, click on the button.

It will ask us to define the cell range to remove.

• Next, select the cell range B18:B19.
• After that, press OK.

A confirmation dialog box will appear.

• Finally, press Yes.

Therefore, it will remove the two entries from the Drop Down List. Thus, we have shown you yet another method of editing Drop Down List.

Read More: How to Edit Macros in Excel (2 Methods)

4. Dynamically Edit Drop Down List Based on Changes of Source List

For the last method, we will add a new item to our Drop Down List if it is not already on that list. Then, we will use a Named Range for our input list. Finally, we will add a formula to this range using the OFFSET and COUNTA functions to make the Named Range dynamic.

Steps:

• First, select the cell range D5:D10.
• Then, from the Data tab >>> select Data Validation.

• Select B14:B16 as the Source. We have already named it CarBrand.
• Then, select the Error Alert tab.

• Uncheck “Show error alert after invalid data is entered”.
• After that, press OK.

Now, let us see how we make the Named Range Dynamic.

• First, from the Formulas tab >>> select Name Manager.

A dialog box will appear.

• Next, select “CarBrand” and type this formula on the “Refers to” box.

• After that, select the Green Tick mark and press Close.

Now, we will add code to this Sheet.

• Right-Click on the “AddList” Sheet and select View Code.

• Then, type the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim zReply As Long
    If Target.Column = 4 And (Target.Row >= 5 And Target.Row <= 10) Then
     If IsEmpty(Target) Then Exit Sub
       If WorksheetFunction.CountIf(Range("CarBrand"), Target) = 0 Then
          zReply = MsgBox("Want to include " & _
                         Target & " in the Drop Down List?", vbYesNo + vbQuestion)
          If zReply = vbYes Then
              Range("CarBrand").Cells(Range("CarBrand").Rows.Count + 1, 1) = Target
          End If
       End If
    End If
End Sub

Now, we notice there are only 3 items on the Drop Down List.

Then, if we type something outside the source list, then a dialog box will appear that will ask us to add the item to our source list.

• Press Yes.

We will see that item is added to our source list.

This code works for the rest of the cells too. In conclusion, we have shown you the last method of editing Drop Down List in Excel Macro.

Read More: How to Edit Defined Names in Excel (Step-by-Step Guideline)

Conclusion

We have shown you 4 quick-and-easy to understand methods of how to edit Drop Down List in Excel Macro. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site Exceldemy for more Excel-related articles. Thanks for reading, keep excelling!