Data Validation List

[StaceyChm]

Our HR dept uses an Excel based Performance Review sheet. I currently have the sheet set up with a comments section under each key point. The comments are set up to a Data Validation, allow list, in-cell Dropdown, and it pulls the list from the 2nd tab on the worksheet.

The users are running into an issue if they try to edit a comment that they selected from the list. For example, one of the comments reads: "Manages his/her time well". The user is trying to remove the "/her" so it now reads "Manages his time well." Whenever the change is made and you click out of the box, it duplicates the entry. So the first time it will read "Manages his/her time well. Manages his time well." Then the user try's to remove the 1st "Manages his/her time well." and clicks out of the cell it now reads "Manages his/her time well. Manages his time well. Manages his time well." the next time it will triplicate it "Manages his/her time well. Manages his time well. Manages his time well. Manages his time well." etc etc.

Is there anyway to eliminate this duplication from happening? Should I be setting this up a different way instead of data validation?

My interim solution was to remove the "his/her" wording thereby eliminating the need to adjust. But this doesn't apply to all the comments they change.

Thanks in advance for your assistance.

[Aardigspook]

Welcome to the forum.
I can't duplicate the problem - can you upload a small sample file (with confidential/sensitive information removed)?

To upload a file, click 'Go Advanced' then scroll down and click 'Manage Attachments'.

[FDibbins]

Data Validation is the exact same thing as manually typing into a cell, the only different being it can save you typing and typos, and can be used to restrict data entry.
(why not just use "their" for that paticular entry?)

I am not able to see why DV would duplicate entries...like manual entry, it will replace whatever was there befor.

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[StaceyChm]

Attached is a clean copy. They also want to free form type in the same field and that also causes it to duplicate.

[Aardigspook]

The problem is the macro which is running every time the worksheet is changed.
Change this line:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

and the duplication will stop.
I think the original intention was to allow users to just type something additional into the formula box which would be added to whatever was previously there. This won't now work, but as long as users just edit what's there already, it shouldn't matter. (I hope that makes sense.)

Edit: to change the macro, press Alt-F11 then right-click 'Sheet1(Performance Review)' on the left-hand side, select 'View Code'. You'll then see the macro on the right-hand side - look for the bit of code mentioned above and change it.

[StaceyChm]

Thank you for the response, I made the change and the duplication has stopped. But now I am not able to pick multiple entries. They want to be able to select multiple items as well as free form typing in the cell.

I think the intent of the original code was to #1 keep the original value #2 allow free form typing & #3 allow multiple items to be select. Is this even possible?

[Aardigspook]

I see what you mean. I did a quick search for 'excel data validation multiple selection' and the top result was http://www.contextures.com/excel-dat...-multiple.html, which includes the exact code in your file.

It should be possible to create a UserForm instead, which allows multiple entries and also free text, but I'm still a bit of a beginner with VBA so I couldn't do that quickly - though there are plenty of VBA gurus on this forum who could, I'm sure.

The option I see is to un-merge the cells used for the comments (C10:G12) and make them separate rows (C10:G10, C11:G11, C12:G12 - and further if necessary). Each of those rows can then run the same Data Validation, allowing multiple items to be selected by using multiple rows, whilst still enabling free text (and/or amending of the DV text) in each row.

Attached is a file with the VBA amended to allow only single selections (as in the previous post) but with multiple rows for multiple entries.
I hope that's of some help.