Data validation not working

waheed254

Board Regular
Joined
Jan 2, 2014
Messages
62
Dear All,
I am facing problem regarding Data Validation that when i Type data in data validation activated cell it shows error when i type Data which is not available in my list but when i copy Paste/Paste Special in Same cell it do not show error it just Paste value if it is in my list or Not. I think an error must be displayed while copy pasting data if it is not in my specified List if Source Data...


Can anyone Help...
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
thats just the way excel data validation works !

If you enter the data it does the data validation .
if you copy-and-paste it doesnt do the data validation. even worse when you go back and look at the cell you pasted to the validation there will now be the validation beloning to the cells you copy-and-pasted from .
 
Upvote 0
you can write an worksheet event subroutine that wil be executed every time any cell on that sheet is changed.

the subroutine would check to see if the cell you just changed was the range of cells where you want the validtion , and if is is , do the same check your validation
was supposed to do. In the example below if you change any cells other than a2 to a5 the sub will just exit. if you change a2 to a5 it will execute the validation

at the moment I have just put a comment in the sub to say do the validation here and if it fails dont accept the value entered ; make the user try again

I know this can be done but I'm not enough of a VBA expert to tell you exactly how to do it. But if you update your post with some more detials of exaclty what you want the validation to do someone else who can answer you
will (I hope) read and answer you

Private Sub Worksheet_Change(ByVal target As Range)

Dim targetvalue As String
If Intersect(target, Range("A2:A5")) Is Nothing Or target.Cells.count > 1 Then Exit Sub
'..... some code here that does the validation
End Sub
 
Upvote 0
AA
SDSB
DC
GGRED
E

<tbody>
</tbody>

Now first column is the working area and last is list of Data now when i type in Working area Column it works perfect but when i copy and paste/paste special than Validation does not work..



Any one to Solve this issue....
 
Upvote 0
A little bit...Not enough
I'm about to sign off for the day but if you know a little vba, you might gain something from reading this thread and trying to adapt the code in post #5 of that thread providing you have read and accepted the provisos in post #2 there.


Failing that, any potential helper would probably want to know ..

- what are the actual columns shown below? Are they Columns A and E?

- what is the first row shown below? Is it row 2?




AA
SDSB
DC
GGRED
E

<tbody>
</tbody>

Now first column is the working area and last is list of Data now when i type in Working area Column it works perfect but when i copy and paste/paste special than Validation does not work..



Any one to Solve this issue....
 
Upvote 0
Row # is 4 and Column is D and List of Data is in another Sheet Named DLR range of which is c4:c5000. Secondly
Row # is 4 and Column is E and List of Data is in another Sheet Named DLR range of which is D4:D5000.
 
Upvote 0
Row # is 4 and Column is D and List of Data is in another Sheet Named DLR range of which is c4:c5000. Secondly
Row # is 4 and Column is E and List of Data is in another Sheet Named DLR range of which is D4:D5000.
That isn't very clear to me. Are you saying that ..

- the cells with Data Validation are in columns D and E (even though your previous table appeared to show 3 blank columns between) from row 4 onwards and

- the Data Validation in column D allows entries from a 'List' in sheet DLR range C4:C5000 and

- the Data Validation in column E allows entries from a 'List' in sheet DLR range D4:D5000?

If not a clearer explanation might help.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top