Scribd Logo
Upload

Welcome to Scribd!

  • 56 views

    Select Multiple Items From Drop Down (Data Validation) List

    Uploaded by

    Harry Nuryanto
    The document discusses how to allow selecting multiple items from a data validation dropdown list in Excel by intercepting the Worksheet_Change event, storing the previously selected value, and joining it with the new selection separated by a comma if neither value is empty. An update is also provided to remove a previously selected choice if it is selected again from the list. The document includes a link to download an example Excel workbook demonstrating this code.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Select Multiple Items From Drop Down (Data Validation) List

    Uploaded by

    Harry Nuryanto
    56 views4 pages
    The document discusses how to allow selecting multiple items from a data validation dropdown list in Excel by intercepting the Worksheet_Change event, storing the previously selected value, and joining it with the new selection separated by a comma if neither value is empty. An update is also provided to remove a previously selected choice if it is selected again from the list. The document includes a link to download an example Excel workbook demonstrating this code.

    Original Description:

    Select Multiple Items from Drop Down (Data Validation) List

    Original Title

    Select Multiple Items from Drop Down (Data Validation) List

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses how to allow selecting multiple items from a data validation dropdown list in Excel by intercepting the Worksheet_Change event, storing the previously selected value, and joining it with the new selection separated by a comma if neither value is empty. An update is also provided to remove a previously selected choice if it is selected again from the list. The document includes a link to download an example Excel workbook demonstrating this code.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    56 views4 pages

    Select Multiple Items From Drop Down (Data Validation) List

    Uploaded by

    Harry Nuryanto
    The document discusses how to allow selecting multiple items from a data validation dropdown list in Excel by intercepting the Worksheet_Change event, storing the previously selected value, and joining it with the new selection separated by a comma if neither value is empty. An update is also provided to remove a previously selected choice if it is selected again from the list. The document includes a link to download an example Excel workbook demonstrating this code.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 4

    Select Multiple Items from Drop Down (Data

    Validation) List
    APRIL 20, 2020 BY PHILIP TREACY 51 COMMENTS

    multi-dv.mp4

    Data validation lists will only let you choose one item from the list. But what if we have a
    situation where we want to choose more than one item?

    Select Multiple
    Watch the Video Items DropDown.mp4

    When a selection is made from the DV list, we can intercept the change using
    a Worksheet_Change event.

    Worksheet_Change is triggered by a change to any cell on the sheet, so the code needs
    to check if the changed cell is using our data validation list.

    As usual the changed range is passed into the event with a variable called Target, and I've
    created a named range, DV_Range, that refers to C1:C4 where I'm using the data
    validation.

    If Target and DV_Range Intersect then the data validation lists are being used. If they
    don't intersect then the code doesn't need to do anything.

    Here's the trick. Store the newly selected choice from the DV list. At this point the cell has
    been changed and shows only the new selection from the DV list.
    Undo the change

    The cell now contains the value before a new DV selection was made, store this value.

    The code now needs to do one of three things:

     If the old value was nothing, the cell should contain the new selection
     If the new value is nothing, the cell contents are being deleted
     If neither of these things are true, join the old value and the new value (they are
    strings), separated by a comma in this case, and put this into the cell.

    This can be done using these IF statements:


    That's all there is to it.

    Update : Remove Previously Selected Choice


    With the original code if the same choice was made again, nothing happened. With this
    update, selecting the same choice again removes it from the cell.
    Download the example workbook to use this code yourself.

    Download the Workbook


    Download the Excel Workbook. Note: This is a .xlsm file please ensure your browser
    doesn't change the file extension on download.

    You might also like