#How to create drop down list in excel code
How do I get it for other cells?Īns: To get this multiple selection drop-down in other cells, you need to modify the VBA code in the backend. Q: In the VBA code, the functionality is for cell C2 only. If you have any questions, I request you to go through this list of queries first. I have created this section to answer some of the most asked questions about this tutorial and the VBA code. Note: Since we are using a VBA code to get this done, you need to save the workbook with a. Now when you go back to the drop-down and make selections, it will allow you to make multiple selections (as shown below): In the code window, copy and paste the above code.This opens the code window for that worksheet. Double click on Worksheet Name (in the left pane) where the drop-down list resides.There should be a Project Explorer pane at the left (if it is not there, use Control + R to make it visible).Go to the Developer Tab and click on Visual Basic (you can also use the keyboard shortcut – Alt + F11).
#How to create drop down list in excel download
Download the Example File Where to Put the VBA Codeīefore you start using this code in excel, you need to put it in the back-end, such that it gets fired whenever there is any change in the drop-down selection.įollow the below steps to put the VBA code in the backend of Excel: If you try and select it again, nothing would happen (as shown below). However, you will only be able to select an item only once. This code will allow you to select multiple items from the drop-down list. Now you need to place this code in a module in VB Editor (as shown in the next section of this tutorial). ' To allow multiple selections in a Drop Down List in Excel (without repetition) Here is the code that will make sure an item can only be selected once so that there are no repetitions: Private Sub Worksheet_Change(ByVal Target As Range) Download the Example File VBA Code to allow Multiple Selections in a Drop-down List (without repetition)Ī lot of people have been asking about the code to select multiple items from a drop-down list without repetition. Note that if you select an item more than once, it will be entered again (repetition is allowed). When you have placed this code in the backend (covered later in this tutorial), it will allow you make multiple selections in the drop down (as shown below). Now you need to place this code in a module in VB Editor (as shown below in the ‘Where to put the VBA code’ section’). Target.Value = Oldvalue & ", " & Newvalue
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing ThenĮlse: If Target.Value = "" Then GoTo Exitsub Else ' To make mutliple selections in a Drop Down List in Excel VBA Code to allow Multiple Selections in a Drop-down List (with repetition)īelow is the Excel VBA code that will enable us to select more than one item from the drop-down list (allowing repetitions in selection): Private Sub Worksheet_Change(ByVal Target As Range) The next two sections of this tutorial will give you the VBA code to allow multiple selections in the drop-down list (with and without repetition). To enable this drop-down to allow us to make multiple selections, we need to add the VBA code in the back end. Now, cell C2 has a drop-down list which shows the items names in A2:A6.Īs of now, we have a drop-down list where you can select one item at a time (as shown below).
So if you have any questions after reading this, please check out the FAQ section first. Since I get a lot of similar questions, I have decided to create an FAQ section at the end of this tutorial. This has been one of the most popular Excel tutorials on this site.
0 kommentar(er)
