Add or remove a value in a drop down list programmatically
This article demonstrates how to add or remove a value in a regular drop down list based on a list located on a worksheet using VBA.
Cell B3 contains a regular drop down list which you will find on tab "Data" on the ribbon. The data source used in the drop down list is from column H. I am using an Excel defined Table that grows when new values are added.
Enter a value in cell E3 and press the "Add" button to add the value to the list in column H. You can also remove a value using the "Remove" button, note that this is case-sensitive.
The animated gif below demonstrates how to add or remove values from the list.
What you will learn in this article
- How to insert a regular drop down list.
- How to use an Excel Table as a data source for a drop down list.
- How to convert a data set to an Excel defined Table.
- How to add buttons to a worksheet.
- How to change button text.
- How to create a macro that adds a value to a list
- Name a macro
- Find last non-empty value in list
- Copy entered value to first empty cell in list
- How to create a macro that deletes a specific value in a list
- Iterate through a given column in an Excel defined Table
- Find a value in an Excel defined Table column
- Delete a cell in a list
- How to assign a macro to a button.
- Where the macros are located.
How to build a drop down list
- Select the cell you want to use.
- Go to tab "Data" on the ribbon.
- Press with mouse on the "Data Validation" button.
- Select "List".
- Excel won't let you type a reference to an Excel Table, however, there is a workaround. See below.
How to use an Excel defined Table as a data source for a drop down list
Use the following formula in order to be able to reference an Excel Table:
How to create an Excel defined Table
- Select any cell in the data set you want to convert.
- Go to tab "Insert" on the ribbon.
- Press with left mouse button on "Table" button.
- Press with left mouse button on checkbox "My Table has headers".
- Press with left mouse button on OK button.
How to add a value to a list using VBA
'Name macro Sub AddValue() 'Declare variable and data type Dim i As Single 'Save row number of the first empty cell in column H to variable i i = Worksheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row + 1 'Save value entered in cell E3 to first empty cell in column H. Worksheets("Sheet1").Range("H" & i) = Worksheets("Sheet1").Range("E3") 'Clear cell E3 Worksheets("Sheet1").Range("E3") = "" End Sub
How to delete a specific value in a list using VBA
'Name macro Sub RemoveValue() 'Dimension variables and declare data types Dim i As Single Dim Cell As Range 'Save row number of last non-empty cell in column H to variable i i = Worksheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row 'Assign the first non-empty cell to object Cell Set Cell = Worksheets("Sheet1").Range("H" & i) 'Repeat everything between Do and Loop until row of object Cell is equal to 2 Do Until Cell.Row = 2 'Check if object cell is equal to value in cell E3 and is not empty If Worksheets("Sheet1").Range("E3") = Cell And Cell <> "" Then 'Delete cell Cell.Delete Shift:=xlUp 'Clear cell E3 Worksheets("Sheet1").Range("E3") = "" 'Stop macro Exit Sub End If 'Save object reference 1 row below current object to variable Cell Set Cell = Cell.Offset(-1, 0) Loop 'Show a message box telling the user that the value cant be found in the list MsgBox "Can´t find value: " & Worksheets("Sheet1").Range("E3") End Sub
Where to put the VBA code?
- Press Alt + F11
- Press with left mouse button on "Insert" on the top menu.
- Press with left mouse button on "Module" to add a code module to your workbook.
- Paste macro to code module.
Insert button and assign a macro
- Go to tab "Developer" on the ribbon.
- Press with left mouse button on "Insert" button.
- Select "Button" (Form Control).
- Press and hold with left mouse button on worksheet.
- Drag and release the mouse button to create the button.
- A dialog box appears, select the macro you want to assign to the button.
- Press with left mouse button on OK button.
Change button text
- Press with right mouse button on on a button.
- A context menu appears, press with left mouse button on "Assign Macro...".
- A dialog box shows up, select the macro you want to assign.
- Press with left mouse button on OK button.
The macro starts every time the user press with left mouse button ons on the button. Create a new button and assign the second macro.
Move the buttons below cell E3.
Drop down lists category
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
Functions in this article
More than 1300 Excel formulas
Excel categories
5 Responses to “Add or remove a value in a drop down list programmatically”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
We have problem with drop list if you remove the first name of the list may be we can change to:
=OFFSET(Sheet1!$H$2:INDEX(Sheet1!$H$3:$H$990,COUNTA(Sheet1!$H$3:$H$990)-1),1,)
Jacky Harle,
=OFFSET(Sheet1!$H$3, 0, 0, COUNTA(Sheet1!$H$3:$H$1000))
[…] write this one? Any help would be appreciated. An example of what I am trying to do can be found on Add or remove a value in a drop down list | Get Digital Help - Microsoft Excel resource . The difference is that I am trying to use a UserForm instead of another cell. […]
I got error message "Can't execute code in break mode" when press with left mouse button on Add or remove button.
and view code in module1
below code will be highlighted in red
sorry, i am new to computer language and find your site is great for new-learning so I follow your tutorial. but vba is quite difficult to me. as for your formula is help me a lot. Many thanks Oscar.
Jeff Wan,
and view code in module1 below code will be highlighted in red
Wordpress sometimes convert & (ampersand character) to
.
I have updated this post, it now shows the correct characters.
I got error message "Can't execute code in break mode" when press with left mouse button on Add or remove button.
To be able to press a button (run a macro) after a vba error in the vb editor you need to press the reset button, the button is below the menu.