if you want to remove an article from website contact us from top.

    how to create drop down list in excel with multiple selections

    James

    Guys, does anyone know the answer?

    get how to create drop down list in excel with multiple selections from EN Bilgi.

    How to Make Multiple Selections in an Excel Drop

    In this video I show you to create a drop-down list in Excel that lets you select multiple items.  Download the featured file here. Please note the featured file does not contain the VBA code required to complete the task.  Unfortunately, WordPress does not allow me to upload macro-enabled workbooks. Step 1: Create a List… Read More »How to Make Multiple Selections in an Excel Drop-Down List

    Home » How to Make Multiple Selections in an Excel Drop-Down List

    How to Make Multiple Selections in an Excel Drop-Down List

    In this video I show you to create a drop-down list in Excel that lets you select multiple items.  Download the featured file here. Please note the featured file does not contain the VBA code required to complete the task.  Unfortunately, WordPress does not allow me to upload macro-enabled workbooks.

    Step 1: Create a List of Values and Name It

    In the video I create a list of values that I wanted to appear in the drop-down list on a separate worksheet. I also name the list.

    To name a list:

    Select the cells in the list

    Click in the Name Box (just to the left of the formula bar – it displays the cell address of the active cell by default)

    Type a name that starts with a letter and does not contain spaces

    Press the Enter key on your keyboard to confirm

    Step 2: Create the Drop-down List Using Data Validation

    The next step is to create the drop-down list.  I did this on a separate worksheet to the list created above in step 1, although it could be created on the same worksheet. To create the drop-down list:

    Select the cell or cells you want the drop-down list to appear in

    Click on the Data tab on Excel’s ribbon

    Click on the Data Validation button in the Data Tools group

    In the Data Validation dialog, in the Allow: list select List

    Click in the Source: box

    Press F3 on your keyboard to open the Paste Name dialog

    Select the name you gave your list in Step 1

    Click on OK

    Click OK in the Data Validation dialog

    By default a drop-down list will only allow you to select one item.

    STEP3: Add the VBA Code to Your Drop-Down List

    To add the VBA (visual basic for applications) code to your drop-down list follow these steps:

    Use the keyboard shortcut ALT F11 to open the Visual Basic Editor (VBE)

    Make sure the Project Explorer is visible – it displays all the workbook’s worksheet names.  If you can’t see it, use the keyboard shortcut CTRL R to open it.

    In the Project Explorer select the worksheet that contains your drop-down list.

    In the Code window (that’s the big white area to the right of the Project Explorer), paste the code below

    Close the VBE and you should find that you can select multiple items in your drop-down list.

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Oldvalue As String

    Dim Newvalue As String

    Application.EnableEvents = True

    On Error GoTo Exitsub

    If Not Intersect(Target, Range("A2")) Is Nothing Then

    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then

    GoTo Exitsub

    Else: If Target.Value = "" Then GoTo Exitsub Else

    Application.EnableEvents = False

    Newvalue = Target.Value

    Application.Undo

    Oldvalue = Target.Value

    If Oldvalue = "" Then

    Target.Value = Newvalue

    Else

    If InStr(1, Oldvalue, Newvalue) = 0 Then

    Target.Value = Oldvalue & vbNewLine & Newvalue

    Else:

    Target.Value = Oldvalue

    End If End If End If End If

    Application.EnableEvents = True

    Exitsub:

    Application.EnableEvents = True

    End Sub

    There are a few tweaks to the code that you make and I go through these in the video.  One thing you should note is that the code, as it is, assumes you drop-down is in cell A2.  If it isn’t, change the range address in line 6.

    Please note, when you save the worbook, because it now contains VBA code, it needs to be saved as a Macro-enabled Workbook (*.xlsm).

    Source : www.bluepecantraining.com

    Select Multiple Items from a Drop Down List in Excel

    In this tutorial, learn how to select multiple items from a drop down list in Excel. This can be achieved by using a simple VBA code.

    How to Make Multiple Selections in a Drop Down List in Excel

    -- By Sumit Bansal

    FREE EXCEL TIPS EBOOK - Click here to get your copy

    One of my colleagues asked me if it is possible to make multiple selections in a drop-down list in Excel.

    When you create a drop-down list, you can only make one selection. If you select another item, the first one is replaced with the new selection.

    He wanted to make multiple selections from the same drop down in such a way that the selections get added to the already present value in the cell.

    Something as shown below in the pic:

    There is no way you can do this with Excel in-built features.

    The only way is to use a VBA code, which runs whenever you make a selection and adds the selected value to the existing value.

    This Tutorial Covers:

    Watch Video – How to Select Multiple Items from an Excel Drop Down List

    How to make Multiple Selections in a Drop Down List

    In this tutorial, I will show you how to make multiple selections in an Excel drop-down list (with repetition and without repetition).

    This has been one of the most popular Excel tutorials on this site. Since I get a lot of similar questions, I have decided to create an FAQ section at the end of this tutorial. So if you have any questions after reading this, please check out the FAQ section first.

    There are two parts to creating a drop-down list that allows multiple selections:

    Creating the drop-down list.

    Adding the VBA code to the back-end.

    Creating the Drop Down List in Excel

    Here are the steps to create a drop-down list in Excel:

    Select the cell or range of cells where you want the drop-down list to appear (C2 in this example).

    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.

    Now, cell C2 has a drop-down list which shows the items names in A2:A6.

    As of now, we have a drop-down list where you can select one item at a time (as shown below).

    To enable this drop-down to allow us to make multiple selections, we need to add the VBA code in the back end.

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

    VBA Code to allow Multiple Selections in a Drop-down List (with repetition)

    Below 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)

    'Code by Sumit Bansal from https://trumpexcel.com

    ' To make mutliple selections in a Drop Down List in Excel

    Dim Oldvalue As String

    Dim Newvalue As String

    On Error GoTo Exitsub

    If Target.Address = "$C$2" Then

    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then

    GoTo Exitsub

    Else: If Target.Value = "" Then GoTo Exitsub Else

    Application.EnableEvents = False

    Newvalue = Target.Value

    Application.Undo

    Oldvalue = Target.Value

    If Oldvalue = "" Then

    Target.Value = Newvalue

    Else

    Target.Value = Oldvalue & ", " & Newvalue

    End If End If End If

    Application.EnableEvents = True

    Exitsub:

    Application.EnableEvents = True

    End Sub

    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’).

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

    Note that if you select an item more than once, it will be entered again (repetition is allowed).

    Source : trumpexcel.com

    Excel Data Validation Drop Down Select Multiple Items

    How to select multiple items from Excel Data Validation drop down list, all in one cell. Short videos, written steps, free Excel workbook to download

    Excel Data Validation - Select Multiple Items

    My Top Excel Videos

    0 seconds of 0 secondsVolume 0%

    Instead of limiting an Excel data validation drop down list to a single selection, you can use this macro to allow multiple selections. Video and written instructions, and get the free workbook to see how it works.

    Multiple Item Selection Articles

    Video Multiple Item Selection Examples

    Select Items With Drop Down List

    Select Multiple Items From Drop Down List

    View the Code for Select Multiple Items

    Change the Column Number

    Change the Sheet and Range Names

    Video: Multiple Item Selection Setup

    Allow Editing With Multiple Selections

    No Duplicates in Cell

    Remove Previous Selections From Cell

    Multiple Selection Sample Code

    Code on Protected Worksheet

    Download the Sample Data Validation File

    Multiple Item Selection Articles

    This is the main page for the data validation multiple selection technique. I have also written several blog articles, and you can join in the discussion in the comments there:

    Select Multiple Items from Excel Data Validation List

    Edit Multiple Selections in Excel Drop Down Lists

    How to Set up Multiple Selection Excel Drop Down

    Excel Drop Down Multi-Select Update

    No Duplicates in Multiple Selection Excel Drop Down

    Excel Drop Down Multiple Select or Remove

    Enter Multiple Items in a Non-Data-Validation Cell

    Multiple Selection Drop Down With Codes

    Data Validation - Select Multiple Items From Listbox

    Select Cell Items From ListBox

    Show Data Validation Items in List Box

    Video Multiple Item Selection Examples

    To see how this technique works, and a few multiple selection examples, you can watch this short video.

    Play

    Select Items With Drop Down List

    To make data entry easier, you can add an Excel data validation drop down list to your worksheet. Then, click the arrow, and select an item to fill the cell.

    In the worksheet shown below, you can select any month from the drop down list, and it overwrites any previous entry in the cell.

    Select Multiple Items From Drop Down List

    Instead of limiting the drop down list to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.

    With a few adjustments to the VBA code, you can display all the selected items across a row, or down a column, or keep them in a single cell.

    Fill Across a Row

    In the first example, each item selected in this dropdown list fills the next empty cell at the right.

    With this option, you could fill player names across a list of innings, or employee names for days of the week.

    Fill Down a Column

    Another option is to fill the selected items down a column, instead of across the row.

    Add Values To the Same Cell

    With another variation in the VBA code, you can keep all the selected items in the same cell, separated by commas.

    View the Code for Select Multiple Items

    The sample workbook uses Microsoft Excel VBA code (Visual Basic for Applications), which is set up to run automatically, when you make a change on the worksheet.

    To see the code for any sheet in the sample file, you can right-click the sheet tab, and click on View Code.

    Change the Column Number

    In the sample code shown below, column 3 is the only one where the change will occur. In the screen shot, the line is marked with a red dot:

    If Target.Column = 3 Then

    In your workbook, you could change the 3 to a different number.

    If Target.Column = 6 Then

    Or, add more columns in the code. For example:

    If Target.Column = 3 _

    Or Target.Column = 5 _

    Or Target.Column = 6 Then

    Or, use a starting and ending column. For example:

    If Target.Column >= 3 And _

    Target.Column <= 10 Then

    Or, include a starting row as well. For example:

    If Target.Column = 3 _

    And Target.Row >= 4 Then

    Or, if you don't want to limit the code to a specific column, you could delete the two rows that are marked with a red dot. When those lines are removed, the code will run when any cell with a drop down list is selected.

    Change the Sheet and Range Names

    In some code samples, the sheet names and named ranges are included. You will have to change these to match your sheet names and range names.

    For example, shown below is a portion of the code from the LineBreakAddSort sheet. The sheet and range names are highlighted in yellow.

    Also, this code is set up for a list in column 3.

    i=ws.Cells(Rows.Count,3).End(xlUp).Row + 1

    You would need to change that number, if your list is in a different column.

    Source : www.contextures.com

    Do you want to see answer or more ?
    James 3 month ago
    4

    Guys, does anyone know the answer?

    Click For Answer