Excel: Dropdown box (Data validation)

April 27, 2011 at 20:26:54
Specs: Windows 7, n/a
Hi, i have a column in excel 2003, which uses the data validation to create the dropdown box menu for users to choose from. I checked the box for any other options key into those fields, there will be a error message and prevent the user from editing. However, i found out the user can actually copy and paste to overwrite that cell. How can we resolve this?

I tried using protect sheet, but it will lock the cells entirely preventing the user from selecting under the dropdown box... please advise

See More: Excel: Dropdown box (Data validation)

Report •

April 28, 2011 at 09:05:08
As far as I know, you can't prevent users from pasting over a data validation drop down, you can only handle it via VBA.

A simple way is to turn off CutCopyMode when the cell is selected so that nothing can be pasted. Unfortunately this only works if the user selects the cell either individually or as part of a range. If the use copies e.g. 5 cells and then selects a cell where the pasted range will overwrite the drop down, the code won't see the drop down as part of the selection and won't turn off CutCopyMode.

Another way is to check the cell each time it is changed and if it doesn't contain a valid entry, then run some code to re-apply the data validation and original list. Unfortunately, if the user pastes a valid entry into the cell, the drop down will still be eliminated.

If you could ensure that the valid entries were not found anywhere else in the sheet so that they couldn't be copied/pasted, then it might be possible to have the code not allow the entry.

There is no simple fix to this issue and we'd need more details before any kind of VBA solution could be offered.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question