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.

how to edit drop down list in excel macro Intro


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.

how to edit drop down list in excel macro Module

  • 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

how to edit drop down list in excel macro

VBA Code Breakdown

  • First, we are calling our Sub Procedure Edit_Drop_Down_List_Range_Selection.
  • Then, we select the first cell of our output for the Drop Down List, which is cell D5.
  • After that, we are selecting the rest of the cells that contain SameValidation. This means we are selecting the cell range D5:D10.
  • Then, we remove the previous Validation.
  • Next, we set the Validation type as a list and define the input range as B14:B19.
  • Thus, we edit the Drop Down List using Excel Macro.

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.

how to edit drop down list in excel macro Final Image

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:

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

how to edit drop down list in excel macro Code Window

VBA Code Breakdown

  • First, we are calling our Sub Procedure Edit_Drop_Down_List_Add_More.
  • After that, we are selecting the rest of the cells that contain SameValidation. This means we are selecting the cell range D5:D10.
  • Then, we remove the previous Validation.
  • Next, we set the Validation type as a List and define the input range as “Tesla, Ford, Toyota, Audi, General Motors“.
  • Thus, we edit the Drop Down List using Excel Macro.

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


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.

how to edit drop down list in excel macro Add New Item

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

how to edit drop down list in excel macro Module window 2

VBA Code Breakdown

  • First, we are calling our Sub Procedure Edit_Drop_Down_List_Dynamic_Range.
  • Then, we find the last non-empty row in the B column.
  • After that, we are selecting the rest of the cells that contain SameValidation. This means we are selecting the cell range D5:D10.
  • Then, we remove the previous Validation.
  • Next, we set the Validation type as a list and define the input range as B14:B19. Here, we get B19 from the lrow variable.
  • Thus, we edit the Drop Down List using Excel Macro.

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.

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

how to edit drop down list in excel macro Module Window 3

VBA Code Breakdown

  • First, we are calling our Sub Procedure Remove_From_Drop_Down_List.
  • Then, we define the variable types.
  • After that, we use an InputBox to prompt the user to define the cell range to remove.
  • Then, we use the MsgBox return input to our code.
  • If the user selects Yes, then our code will remove the selected cells.
  • Else, nothing will happen.

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.

how to edit drop down list in excel macro Button

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.

how to edit drop down list in excel macro InputBox

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.

how to edit drop down list in excel macro Output

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.

how to edit drop down list in excel macro Method 4

  • 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.

how to edit drop down list in excel macro Name Range

A dialog box will appear.

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

=OFFSET(AddList!$B$14,0,0,COUNTA(AddList!$B:$B)-9,1)

Formula Breakdown

  • First, this formula has the COUNTA function inside the OFFSET function.
  • COUNTA(AddList!$B:$B)-9
    • Output: 3.
    • The COUNTA function counts the non-blank cells. We had 3 items on the list source, hence we got 3.
    • Moreover, we are subtracting 9 from it because our output list starts from row 5, and the list source starts from row 14 (14-5=9).
  • Then our formula reduces to -> OFFSET(AddList!$B$14,0,0,3,1)
    • Output: {“Porsche”;”Tesla”;”Nissan”}.
    • This returns the 3 car names as a list. If we add anything, it will change the code completely.
  • Thus this formula works.
  • After that, select the Green Tick mark and press Close.

Now, we will add code to this Sheet.

  • Right-Click on the “AddListSheet and select View Code.

how to edit drop down list in excel macro View Code of Sheet

  • 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

VBA Code Breakdown

  • First, we are calling our Private Sub Worksheet_Change. Here, we are using a private sub. So, it will execute every time any changes occur in the worksheet.
  • After that, we are defining column D and the row number from 5 to 10. This means the cell range is D5:D10.
  • Our code will execute, whenever there is a change in this cell range.
  • Basically, our code matches our input with the source list, whenever there is no match, then that value will be added to the source list.
  • Thus, we edit the Drop Down List using Excel Macro.

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

how to edit drop down list in excel macro Code Working

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.

how to edit drop down list in excel macro Outro

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!


Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo