Edit Multiple Selections in Excel Drop Down Lists

A couple of years ago, I described how you could select multiple items from an Excel drop down list. One of my clients needed that feature in a workbook last week, so I've made an enhancement to the VBA code.

Problems With Editing

In the previous version, you could select multiple items, as promised, with no problems. However, if you tried to edit the cell, you might have seen the data validation error alert.

DataValMultiEdit01

To prevent that error alert from appearing, you can turn off the setting.

DataValMultiEdit02

However, the helpful VBA code simply added your revised text, to the existing text -- probably not the result that you wanted!

DataValMultiEdit03

Add an Editing Check Box

In the new version, there is a check box on the worksheet. If you add a check mark, you can edit the cell, without any problems.

DataValMultiEdit10

To add the check box:

On the Excel Ribbon's Developer tab, click Insert, and then click the Check Box under Form Controls

DataValMultiEdit04

Click at the top of the worksheet, to add a check box

DataValMultiEdit05

Select the default text, and type "Edit Entries"

DataValMultiEdit06

Somewhere in the workbook, create a cell named "EditMode". In this example, the EditMode cell is on a worksheet named AdminNotes.

DataValMultiEdit07

Right-click on the check box, and click Format Control

DataValMultiEdit08

On the Control tab, enter EditMode as the Cell link, and click OK.

DataValMultiEdit09

Edit the Multiple Selection Drop Down List

To edit a cell with multiple items selected:

  • Add a check mark in the Edit Entries check box
  • Select the cell, and edit the entries, either in the cell, or in the formula bar

DataValMultiEdit10

  • Press Enter, to complete the editing.

Return to Multiple Selection

After you finish editing the cell(s), press Enter, to complete the editing.

Then, remove the check mark from the Edit Entries check box.

DataValMultiEdit11

The Revised Multiple Selection VBA Code

In the revised code, the EditMode range is set as a variable, and that cell's value is checked.

  • If the value is False (no check mark), the multiple selection code runs.
  • If the value is True (check mark), the multiple selection code does not run.

DataValMultiEdit12

More Articles on Select Multiple Items

Please read these articles, for more details on selecting multiple items from an Excel drop down list, and working with the VBA code:

Download the Sample Data Validation File

To experiment with this technique, you can download the zipped sample file from the Contextures website, on the Excel Data Validation - Select Multiple Items page.

_____________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

8 comments to Edit Multiple Selections in Excel Drop Down Lists

  • Contextures Blog » Excel Drop Down Multi-Select Update

    [...] Edit Multiple Selections in Excel Drop Down Lists [...]

  • Contextures Blog » Select Multiple Items from Excel Data Validation List

    [...] edit multiple selection cells [...]

  • Kim M

    Hi --

    I love the idea of what you have created, but am a struggling newbie! I used an earlier version where the Edit checkbox had to be used to modify previous multi-entries. It worked great on my PC, but I had attached the code to an Excel tool I delevoped for another department...I've never been able to get it to work for them. :(

    Now I switched to your new code that permits a click to select/de-select and I can't even get it to run on my own version of the spreadsheet. In the original it was obvious what I needed to do to have the code look at the 3 different columns where I had the select lists. In the new code, it looks like I would change this:
    Which was previously a , but my data are in column 7.

    However, no luck. Can you point me to a resource that will help me to determine what I'm doing wrong (or explain it to me like I'm a 3rd grader)? I've been reading blogs and forums for 2 days to try to educate myself so that I know where to look for my error, but to no avail. I've tried to get back to your earlier postings, hoping for more detail, but the hyperlinks don't find the content. :(

    Thanks so much for your wonderful blog and great ideas!!

    Kim

  • Kim M

    Ooops! Guess it doesn't like entries in angle brackets!

    The section of code referred to above:
    If Target.Column = 7 Then

    And the 7 was a 3 in your original code!

    Sorry... :)
    Kim

  • Audrey

    Is it possible to have a hard return after each entry? Currently there is a comma between selections. Thanks!

  • Dianne

    Why is it whenever I try to Edit the list, the previous entry that I entered doubles???

  • Dave

    Hi

    Drop down spreading across columns works great, is there a way to restrict it to only one drop down list rather than the whole sheet ??

  • Craig

    Thank you very much for the code. I am trying to help a person who cannot handle a database but is familiar with Excel.

    I had to remark out the code:
    'If rngDV Is Nothing Then GoTo exitHandler

    'If Intersect(Target, rngDV) Is Nothing Then
    'do nothing
    'Else

    as if I left this in rngDV always returned "Nothing" and just jumped to the exit handler.

    I am using Excel 2002 and the ENTIRE column is set to do validation.

    I need to allow editing of the cell but as this portion of the code is not working as expected I am unsure as to how far to go.

    Can someone at least give me a clue as why rngDV is returning "Nothing"

    Thanks again!

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>