Introduction
Get the VBA code from here: trumpexcel.com/select-multiple-items-drop-down-list-excel/
An Excel drop-down list allows you to make one selection at a time. You can not select multiple items from the same drop down list in Excel.
However, you can use VBA to make this happen.
In this video, I will show you how to use VBA code to select multiple items from a drop down list in Excel.
The video will show you how to select multiple items with and without repetition.
In addition, it will also show you how to modify the code to make the drop down work in different cells/rows/columns.
Free Excel Course - trumpexcel.com/learn-excel/
Paid Online Training - trumpexcel.com/excel-training/
Best Excel Books: trumpexcel.com/best-excel-books/
⚙️ Gear I Recommend:
Camera - amzn.to/3bmHko7
Screen Recorder - techsmith.z6rjha.net/26D9Q
USB Mic - amzn.to/2uzhVHd
Wireless Mic: amzn.to/3blQ8uk
Lighting - amzn.to/2uxOxRv
Subscribe to get awesome Excel Tips every week: www.youtube.com/user/trumpexcel
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
Video
Hello and welcome to the video back trump excel, I am somewhat bunsen.
And in this video, I will show you how to create a drop-down list in excel so that you can make multiple selections from it.
Let me show you what I mean by that.
I have these items here and I have created a drop-down list in cell c2.
And if I select one here and then I, select two, you can see that the second selection gets added in the same cell after a comma.
Similarly, if I select three and if I select for all these I don't, get added in the same cell had this been a usual drop-down list.
It will only allow you to display one item, which would be the latest selection.
But in this case, you can have multiple selections.
Now you cannot do this using inbuilt functions in excel.
You have to use the VBA code in the backend.
Now, if you have not used VBA coding in your life, do not worry I will give you the exact code and the exact steps that you can follow to implement this and create this drop-down list.
If you are watching this video on YouTube, you will find the code in the link in the description, if you are watching this video on my blog on my website, then you can simply scroll down, and you will find the code that you can copy paste.
Now let's come here.
And let me show you how to build that drop-down list.
I have these items.
First I would create a drop-down list in cell c2.
So I would go to data.
And in data validation I would select list.
And in source I would specify these cells.
And now when I click OK, you can see I have a drop down here.
But as of now, this is a usual drop down list where I make a selection.
It will only display the latest selection.
Now I'll have to copy the VBA code.
I have the code here again.
You can copy paste the code from my website, and you will have to go to the Developer tab.
And here you have the visual basic option.
You can also use the keyboard shortcut, alt f11.
If you cannot see the Developer tab right, click on any of these ribbon items go to customize the ribbon.
And in this list, you will find developer simply select that that option click Add.
And if you get added here, and when you click OK, it will be displayed here, or you can simply use the keyboard shortcut, alt, F 11.
Now here if I press alt F 11, it opens the VB editor, which is the back end of VB editor and I have the project Explorer here.
So I have these sheets note that these are the same sheets that I have here example1 example2 end demo.
Since my drop-down list is an example.
One sheet, I would have to double click on this options, Y when I double click on sheet1, it opened this code window here and I would simply paste the code that I've copied and I would close this.
And now see what happens when I select one and then I select two.
It gets added in the same cell.
So what is happening here is that whenever I make a change in this cell.
The code gets fired up, the VBA code gets executed.
And it adds this here.
So you can see when I make multiple selections.
These get added one problem with this drop-down list is that if I come back and select one again, if you again, be added here.
So if this is what you want, then the code works for you.
But in case, you want only unique selections here, which means that you do not want one again to appear again.
If that is selected, then you will have to modify the code again.
This is the code that I provided on my website.
All you need to do is simply copy/paste it.
So in this case, I would simply copy this and let's create it separately here.
In example, 2, worksheets.
So I would select this cell I would go to data data validation here I would select lift and I would select these cells to create the drop-down list.
So I had the drop-down list here.
Now, again, I would press, alt F 11 to go to the VBA editor.
And here I would double click on sheet 2.
And when I double click on it, it opens the code window for this sheet.
And now I would simply paste the code and close it and see what happens.
Now when I come here and I, select one, then I, select 2, it gets added I, select 3.
It gets added again, but when I come back and select one, nothing would happen if he not allow me to select an item more than once.
So if you want only the unique items, then this is the code that you need to use.
Now, let me also take you through the code so that you can modify it a bit.
Let me come back to sheet one here, because this is the easy one and let's say that, in this case, your drop-down is not in C 2, but in C 4.
So in this case, if I create a drop-down list here, let me quickly do that I would create a drop-down list in cell c4.
But you would notice that as of now, this is a simple drop-down list, which would allow you only to make one selection.
But if you want this kind of drop-down list where you can make multiple selections in cell c5, instead of c2 simply go back here in this VB editor and change this line.
Here if target dot address is equal to c2 and make this c5.
And when you do that, it will tell the VBA code that if any change is done in this cell.
And if this cell is C 5, then execute this code else do not do anything.
So if I come back here and I, you can see this drop down list does not work anymore.
But if I come here and I change this in C, it gets added.
So you can change the address if you want the drop down list in two or three cells, you can go back here.
And you can use target dot address is equal to C 2 or target or address is equal to C 5 den.
So you can change this.
If you want this functionality in the entire column, which means that you want all the drop-down list in this column to work this way where one can make multiple selections, simply change this and change this to target dot column is equal to 3 3.
Because this is the third column, a B and C and C is the third column here.
So when you do this all the drop-down list in this column would work the same way.
So here in this case again, you can see this one works as well as this one.
It would also work.
So you can change this line of code to use different conditions.
If you want these in a row, then you can make it a row if there are different cells, then you can do that.
And if you are looking for more conditional, if you want to understand how this works, you can go through the comments on my website.
There are many suggestions that I have provided to all the people who are asking questions regarding these changes.
So this is how you can use VBA code to make multiple selections from a drop-down list in Excel I.
Hope you found this video useful.
Thank you and have a nice day.
FAQs
How do I select multiple items in a drop-down list in Excel? ›
Go to Data –> Data Tools –> Data Validation. In the Data Validation dialogue box, within the settings tab, select 'List' as Validation Criteria. In Source field, select the cells which have the items that you want in the drop down. Click OK.
How do I select multiple items in a dropdown? ›Windows: We need to hold down the CTRL button to select multiple options. Mac: We need to hold down the command button to select multiple options.
Can you select multiple items in a Data Validation list Excel? ›On an Excel worksheet, you can only select one item from a data validation drop down list of items. Later, if you choose a different item from the drop down, the new item replaces the previous selection in the cell.
How do I create a multi select checkbox in Excel? ›To insert more than one checkbox, go to the Developer Tab –> Controls –> Insert –> Form Controls –> Check Box. Now when you click anywhere in the worksheet, it will insert a new checkbox. You can repeat the same process to insert multiple checkboxes in Excel.
How to select multiple options from dropdown without select class? ›- Method 1: By storing all the options in List and iterating through it.
- Method 2: By creating Custom Locator and without iterating the List.
- Method 3: By using JavaScriptExecutor class.
- Method 4: By using sendKeys method.
Note: Be sure to hold the Ctrl (PC) or Command (Mac) key down while selecting multiple items.
How do you select multiple items at once? ›- Click on one of the files or folders you want to select.
- Hold down the control key (Ctrl).
- Click on the other files or folders that you want to select while holding the control key.
- Continue to hold down the control key until you select all the files you want.
Add data validation to a cell or a range
Select one or more cells to validate. On the Data tab, in the Data Tools group, click Data Validation . On the Settings tab, in the Allow box, select List . In the Source box, type your list values, separated by commas.
- Select the cell where you want the drop-down list.
- Navigate to Data >Data validation.
- In Criteria, select Dropdown (from a range) and then select the range that has the items that you want to show in the drop-down.
Place the cursor in the form where you want the multi-selection list and then go to the Controls Task Pane (View > Design Tasks > Controls). Under the Repeating and Optional section, click Multiple-Selection List Box. Configure the multi-select list box as you would a standard List Box.
Which property of the ListBox is used to select multiple values? ›
The SelectionMode property enables you to determine how many items in the ListBox a user can select at one time and how the user can make multiple-selections. When the SelectionMode property is set to SelectionMode.
How do I select multiple values in a combobox? ›In order to perform multiple selection, you can use the ComboboxAdv or MultiColumnComboBox controls, that contains a normal ListBox that allows you to select multiple items, internally.
How do you select multiple items in a combobox in access? ›Multiple items are selected or deselected by clicking them with the mouse or pressing the SPACEBAR.
What is the fastest way to select multiple objects in Excel? ›To select one object, click the object. To select multiple shapes in a group, press and hold Shift or Ctrl while you click the shapes.
Where is the multi select button in Excel? ›Click anywhere inside the slicer box, and press Alt + S to toggle on the Multi-Select button. Select the items, and then press Alt + S again to toggle multi-selection off.
What is the difference between dropdown and multiple choice? ›Multiple Choice Fields: A question with a group of answers choices that lets the user select one choice only. Dropdown Menus: A question with an answer fields that expands into a list of options that lets the user select one choice.
How do you select multiple things not in order? ›Press and hold CTRL. Select the next item that you want. Important Be sure to press and hold CTRL while you select the next item that you want to include in the selection.
How do I get the selected value in multi select dropdown? ›- // Instance created on multiselect component.
- var multiselectObj = document.getElementById('ddlFilterEmployee').ej2_instances[0];
- // Get the selected value through Value property.
Multiple items are selected by holding down Shift and choosing them with the mouse, or by holding down Shift and pressing an arrow key to extend the selection from the previously selected item to the current item. You can also select items by dragging with the mouse.
What action must be taken to select multiple objects at the same time? ›Select multiple objects
To select all the objects in a rectangular area, use the Selection tool to drag a marquee over the objects you want to select. To select nonadjacent objects, use the Selection tool to select an object and then press Shift as you click additional objects. Clicking selected objects deselects them.
How do I select multiple items in a group? ›
To do this, click , and then hold down the Shift or Ctrl key as you click each object that you want to select. Similarly, you can use the Shift and Ctrl keys to remove objects from a group of selected objects.
What is the shortcut for select multiple items? ›- Click the first file or folder, and then press and hold Ctrl .
- While holding Ctrl , click each of the other files or folders you want to select.
Selecting multiple cells with the shift key is the fastest and most effective way. Click the first cell in the range you want to select. Scroll the screen until you find the last cell in the range to select. Hold down with your shift key on the keyboard and then click that cell.
What are the three methods for selecting multiple cells? ›Select one or more cells
To select a range, select a cell, then with the left mouse button pressed, drag over the other cells. Or use the Shift + arrow keys to select the range.
Pressing CTRL+A a second time selects the entire worksheet. Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges. You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection.
How to select multiple options from a drop down list in Selenium? ›- Launch the browser.
- Select the Old Style Select Menu using the element id.
- Print all the options of the dropdown.
- Select 'Purple' using the index.
- After that, select 'Magenta' using visible text.
- Select an option using value.
- Close the browser.
Create a custom Data Validation rule for multiple criteria
From the “Allow” drop-down menu, select “Custom.” In the Formula field box, type an “ = “ and use Ctrl+V to paste your nested formula in the field. Click OK. NOTE: Only the Ctrl+V keyboard shortcut will work for this.
- Press and hold down the CTRL key while using the UP ARROW and DOWN ARROW keys to scroll through the list of items. ...
- To select or deselect the item currently in focus, continue to hold down the CTRL key and press the SPACEBAR.
A standard list box is a box containing a list of multiple items, with multiple items visible. A drop-down list is a list in which the selected item is always visible, and the others are visible on demand by clicking a drop-down button.
What is the difference between picklist and dropdown? ›The drop-down allows you to select from a list of defaults. This list is usually shorter than the complete list available in the pick-list. This menu can display up to 200 records. If you can't find the item you need to select, use the pick-list instead.
Which attribute is used to select multiple items in a drop down list? ›
Attributes of the Select Tag
multiple: This attribute lets the user select multiple options from the dropdown menu.
You can use Check Box component is one of the best component to select multiple values.
What is the method used to select a value from ListBox? ›selectByValue: selectByValue(String arg0) : void
This method asks for the value of the desired option rather than the option text or an index.
The SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.
To allow multiple selecting option, need to specify 'multiple' attribute in select element. Call the function . multiSelect() with the select element on which you want to show multiselect dropdown options as checkboxes.
What is a combobox selection? ›A combo box is a GUI feature that combines a drop-down box, list box, and/or an editable text field, giving the user multiple ways to input or select the desired information. The term "combo box" may also refer specifically to a drop-down list, which only allows choices to be selected (user cannot type a value).
Can a drop down list have multiple selections? ›Multi select dropdown list is used when a user wants to store multiple values for the same record, whereas dropdown list is used to store a single value for a record. You can create custom categories of either dropdown list or multi select dropdown list and define items in each category.
What is a multi select? ›multiselect (plural multiselects) (computing, graphical user interface) A control or widget that allows multiple items to be selected.
What is the difference between single and multiple dropdown? ›Single Select List. Allows you to pick a single value at run time. Multiple Select List. Allows you to pick multiple values at run time.
How do I copy a drop-down list from Excel to Word? ›- Open the worksheet contains the drop down list you want to copy to Word document.
- Select the drop down list cell and copy it by pressing the Ctrl + C keys simultaneously.
- Go to the Word document, click Home > Paste > Paste Special.
How do I get selected items from a dropdown? ›
The value of the selected element can be found by using the value property on the selected element that defines the list. This property returns a string representing the value attribute of the <option> element in the list. If no option is selected then nothing will be returned.
How we can select values from dropdown? ›- selectByVisibleText: selectByVisibleText(String arg0): void. ...
- selectByIndex: selectByIndex(int arg0) : void. ...
- selectByValue: selectByValue(String arg0) : void. ...
- getOptions: getOptions( ) : List<WebElement> ...
- deselectAll()