Multiple Data Validation, allow inputs

[tiredofit]

Cells A1 and A2 on my worksheet take their data from a list, cells C1 through to C5, which contains the values 1,2,3,4,5.

The Data Validation Error Alert setting has been set so the checkbox, "Show error alert after invalid data is entered" is UNCHECKED.

When I enter a value, such as Apple into cell A1, it accepts it (as expected). Then when I click into cell A1, the drop down shows the options (1,2,3,4,5).

The problem is when I attempt to enter two values (simultaneously) into cells A1 and A2. Suppose I copy values Apple and Oranges from cell D1 and D2 and paste onto cells A1 and A2.

Again these two values are accepted.

However, the drop down options no longer show.

If there a way round this?

Thanks

 

[Fluff]

You can paste special, paste values.

 

[tiredofit]

You can paste special, paste values.

Thanks, I can but my users don't!

 

[Joe4]

Copy and paste will overwrite all Data Validation rules.

You have to disable the paste functionality. See here:

 

[Fluff]

You can use VBA to force paste as values. There are plenty examples on this site & the net.

 

[tiredofit]

Copy and paste will overwrite all Data Validation rules.

You have to disable the paste functionality. See here:

Thanks but my users want to be able to copy and paste, yet retain DV!

I think I need to write some additional code, to set named range upon Worksheet_Change.