How to allow only data validated text to cells

[aek_nikos]

Hi,
I have data validated cells i.e E4:L40 and data allowed is a list in cells Q52:Q80. Data validation is not working when someone c/p and the only VBA code i could find is one that forbids c/p totally. That's not helpful because i want to give editor's permition to c/p if the text, they want to c/p, is from the list (Q52:Q80)

(list and validated cells are not the same in every sheet but if i could find a VBA code i'll be OK)
Any thoughts?
Thanks

 

[awoohaw]

If you want the users to be able to copy and paste the data into the range then they need to be sure the data that is being pasted is already validated against the allowed list. Validation does not process in copy/paste scenarios.

 

[awoohaw]

If you want the users to be able to copy and paste the data into the range then they need to be sure the data that is being pasted is already validated against the allowed list. Validation does not process in copy/paste scenarios.

@aek_nikos
Additionally if you do a regular paste into the range of cells with data validation, you remove the validation from those cells. In the example below, I have pasted invalid data with "data only" and the paste was still permitted.

Some Mr Excel Questions.xlsx
	A	B	C	D	E
2
3	Select by Drop	Pasted		Valid Values	Invalid Values
4	M	A		C	A
5	C	B		D	B
6	P	E		G	E
7	P	F		H	F
8	Q	I		M	I
9	T	J		N	J
10	M	K		P	K
11	Q	L		P	L
12	T	O		Q	O
13				T	R
14					S
15					U
16					V
17					W
18					X
19					Y
20					Z
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A13:A15	List	=Valid_Values
A4:B12	List	=Valid_Values

 

[hajiali]

try adding this to sheet code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
If Not Intersect(Target, Range("E4:L40")) Is Nothing Then
Set rg = Range("Q52:Q80").Find(Intersect(Target, Range("E4:L40")), Lookat:=xlWhole, LookIn:=xlValues)
    If rg Is Nothing Then
    Application.EnableEvents = False
    Application.Undo
        MsgBox "SELECT FROM DROPDOWN LIST", vbOKOnly, "INVAILD VALUE" ' MsgBox row is Optional
    Application.EnableEvents = True
        Exit Sub
    End If
End If
End Sub

 

[aek_nikos]

try adding this to sheet code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
If Not Intersect(Target, Range("E4:L40")) Is Nothing Then
Set rg = Range("Q52:Q80").Find(Intersect(Target, Range("E4:L40")), Lookat:=xlWhole, LookIn:=xlValues)
    If rg Is Nothing Then
    Application.EnableEvents = False
    Application.Undo
        MsgBox "SELECT FROM DROPDOWN LIST", vbOKOnly, "INVAILD VALUE" ' MsgBox row is Optional
    Application.EnableEvents = True
        Exit Sub
    End If
End If
End Sub

Thanks a lot for your answer but it didn't work. C/P of non-validated text is still allowed. Just for being sure i am not mistaken: Your code isn't an addition to an existing code, it's the full code. Correct?

Appreciate your time

 

[hajiali]

It’s a full code. Just to be sure your not using this as a macro. Right click on the sheet name at the bottom click on view code and paste this code on the sheet

 

[aek_nikos]

YESSSSSSSSSS!
You are awesome!
I was using it as a macro!
Thanks a lot

 

[hajiali]

Glad I could help.