For example, we might want a user to be able to select from a film genre from a drop down list, then based on this we might want them to be able to select a film within that genre. This means the drop down list will depend on some other value in the workbook and the available values in the drop down menu will change depending on this value.
Also please visit my website this post we’re going to learn how to create a conditional drop down list in a cell.
If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me.
You can download the file and code related to this article from the link below: The rest of the lines in the function Update_DataValidation creates a drop down list in the cell “J2” in sheet2.
'update the drop down list(data validation)
Private Sub Worksheet_Change( ByVal Target As Range) The highlighted parts must be changed based on the location of your source data and the location you would like the drop down list to appear: The code below must be copied to the sheet with the source data.
Step 2: In the next step we need to update the drop down list to accommodate for the changes made in its source. Again the first step would be to modify the source data:
Or for example lets say we want to remove “item 4”. Again the first thing would be to modify the source data: Or for example lets say we want to add an item to the list of items. For example lets say we want to modify the second item to “New Item 2”, we would need to change the data validation’s source to the values below: Step 1: The first thing you would have to do is change the source data. In order to modify, add, insert and remove items from a drop down list created using data validation, you would have to follow 2 steps. Modifying, Adding, Inserting and Removing Items (Usin VBA): The name of the sheet is Sheet1″.Īfter pressing Ok your drop down list is ready: Note: I this example the input data is in another sheet. If they are in separate sheets you would use a statement like “=SheetName!A1:A6”, where “SheetName” is replaced with the name of the sheet. If the drop down list (data validation) and the data are in the same sheet you would reference them using a statement like “=A1:A6”. Then click on the Data Validation button on the Data Ribbon: Step 2: Select the cell you would like to add the drop down list to. Usually I open a new sheet, name it something no one would ever consider using (like “far43fq”) and print the data there. Step1 : In the first step you would need to print the data you are going to fill the drop down list with somewhere.