Edit Multiple Selections in Excel Drop Down

Edit Multiple Selections in Excel Drop Down

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. Now you can edit multiple selections in Excel after entering them.

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. That’s probably not the result that you wanted!

DataValMultiEdit03

Edit Entries Check Box

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

DataValMultiEdit10

To get the new version of the workbook, with the checkbox and revised macro,  go to the Excel Data Validation – Select Multiple Items page on my Contextures site. This example is on the SameCellEdit sheet.

How to Use the Check Box

Here’s how to edit a cell with multiple items selected, in the new version of the workbook.

To start editing one or more cells:

  • 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
  • Press Enter, to complete the editing.

edit multiple selections in Excel

To finish editing:

  1. If you are still editing a cell, press Enter, to complete the editing.
  2. Then, remove the check mark from the Edit Entries check box.

DataValMultiEdit11

What I Changed in New Version

To set up the new version, I made a few changes in the workbook, and in the VBA code:

  1. Added check box on worksheet
  2. Named a cell, on a different sheet
  3. Linked check box to the named cell
  4. Added code to see if linked cell contains TRUE or FALSE
    • Multi-select code will not run if linked cell contains TRUE

How to Add a Check Box

To add a check box on an Excel worksheet, follow these steps:

  • On the Excel Ribbon, go to the Developer tab
  • Click the Insert command.
  • Next, click the Check Box under Form Controls

DataValMultiEdit04

  • Click on the worksheet, where you want to add a check box

In the sample file, the check box is at the top of the worksheet, above the data entry cells

DataValMultiEdit05

In the check box, select the default caption text (Check Box 1)

Type a new caption for the check box:  “Edit Entries”

DataValMultiEdit06

Create a Named Cell

The next step is to create a named cell, which will be linked to the check box.

  • On a different sheet in the workbook, select a blank cell
  • Click in the Name Box, at the left of the Formula Bar
  • Type the one-word name, EditMode, and press Enter

In the sample workbook, the EditMode cell is on a worksheet named AdminNotes. When the cell is selected, its name appears in the Name Box

DataValMultiEdit07

Link Check Box to Named Cell

Next, follow these steps, to link the check box to the named cell:

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

DataValMultiEdit08

  • In the Format Control dialog box, click the Control tab
  • In the Cell Link box, type the cell name: EditMode
  • Click OK

DataValMultiEdit09

See the Revised Code

To see all of the the VBA code to edit multiple selections in Excel, get the sample file from the Excel Data Validation – Select Multiple Items page on my Contextures site.

  • In the sample file, go to the SameCellEdit sheet
  • Then, right-click on the sheet tab, and click View Code.

The screen shot below shows the part of the code where the new instructions were added.

  • At the top of the screen shot, there is a new variable, rngEdit, for the named cell, EditMode

DataValMultiEdit12

Further down in the code, there is a line that checks the EditMode cell’s value, to see if it is False (the check box is NOT checked)

  • If the EditMode cell’s value is False (no check mark), the multiple selection code runs.
  • If the EditMode cell’s value is True (check mark), the multiple selection code does NOT run.

More Articles on Select Multiple Items

For more details on selecting multiple items from an Excel drop down list, and working with the VBA code, you can take a look at these posts:

Download the Sample Data Validation File

To try this technique to edit multiple selections in Excel, you can download the zipped sample file from the Contextures website.

Click here to go to the Excel Data Validation – Select Multiple Items page. This example is on the SameCellEdit sheet.
_____________

14 thoughts on “Edit Multiple Selections in Excel Drop Down”

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

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

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

  4. 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 ??

  5. 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!

  6. Hi,
    Quick question on this – creating the drop-down list allowing multiple selections worked for me. However, I’m trying to put this into a spreadsheet that needs to be filtered though for a specific selection. When using the standard excel data filtering tool it doesn’t seem to work correctly once you’ve changed the column into a multi-select.
    For example:
    Drop-down is multi-select and has the following possible selections: One, Two, Three, Four
    When filtering for all cells that have selection “Two” in them, it only gives you the cells that have ONLY option “Two”, not all cells that include selection “Two” (ie: a cell that could be “Two, Three” or “Two, Four”.
    Is there any way to have the filter / sorting work on a multi-select drop-down?
    Thank you!

  7. Are you able to provide the full VBA code for the Edit Entries check box please. Mine doesn’t seem to be working without it.
    Thanks!

  8. I can’t get this to work for the life of me. Is there code that can be provided for editing the entries instead? Looks like the same thing as Phoebe’s comment above.
    Thanks,

    1. @KK the code is in the sample file that you can download, from the link near the end of the article above.
      After you get the file, go to the sheet named SameCellEdit, right-click the sheet tab, and click View Code.

  9. HI everyone,

    I have the code working but have the issue as discussed above with the info duplicating once edited.
    Since the post is pretty old – is there a workaround so i don’t have to create the edit button?

    Thanks

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.