Prevent filing of cells

[apgmin]

In column say column A
If have put a drop down list from which the values may be selected
If anyone types some thing the entry gets rejected, but if some one simply drags the value from above sells it works.
How do I prevent this and force the person to select from the drop down list

 

[DanteAmor]

In fact it doesn't work for me. Any data I enter enters a loop, even when I select a data from the list. I have Excel 2007. With the adjustments I made it works for me.

 

[Marcelo Branco]

We don't know the details, so we cannot write the code, but personally, since VBA code is being used, I would remove the Validation and implement it in code.

I agree. If we allow copy/paste, IMHO it makes no sense to create validation rules. The code could/should handle every entry.

M.

 

[Marcelo Branco]

In fact it doesn't work for me. Any data I enter enters a loop, even when I select a data from the list. I have Excel 2007. With the adjustments I made it works for me.

I found it!
Excel 2007 Power Programming with VBA by John Walkenbach (page 650)

M.

 

[DanteAmor]

I found it!
Excel 2007 Power Programming with VBA by John Walkenbach (page 650)

M.

However, it did not work for me, hopefully the result with the OP.

 

[Marcelo Branco]

However, it did not work for me, hopefully the result with the OP.

Dante,

I don't know why it doesn't work for you

Let me show you exactly what I did - it may not be clear what I did and we may be doing different things.

Data

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Select​

[/TD]
[TD][/TD]
[TD]

List​

[/TD]
[TD][/TD]
[TD]

Copy/Paste​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

B​

[/TD]
[TD][/TD]
[TD]

A​

[/TD]
[TD][/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

C​

[/TD]
[TD][/TD]
[TD]

B​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

C​

[/TD]
[TD][/TD]
[TD]

C​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

A​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Drop down in A2:A5 (List, Source:C2:C4)

I copy E2 (Ctrl+C) and try to paste (Ctrl+V), say, in A3; then I get the message "Invalid operation ..." and the code (post 4) Undo (doesn't allow) the paste operation.

M.

 

[DanteAmor]

Dante,

I don't know why it doesn't work for you

Let me show you exactly what I did - it may not be clear what I did and we may be doing different things.

Data

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Select​

[/TD]
[TD][/TD]
[TD]

List​

[/TD]
[TD][/TD]
[TD]

Copy/Paste​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

B​

[/TD]
[TD][/TD]
[TD]

A​

[/TD]
[TD][/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

C​

[/TD]
[TD][/TD]
[TD]

B​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

C​

[/TD]
[TD][/TD]
[TD]

C​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

A​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Drop down in A2:A5 (List, Source:C2:C4)

I copy E2 (Ctrl+C) and try to paste (Ctrl+V), say, in A3; then I get the message "Invalid operation ..." and the code (post 4) Undo (doesn't allow) the paste operation.

M.

Hi Marcelo, I don't know why it doesn't work for me either.
Copy E2 and paste in A3, the code enters a loop.
Copy C3 and paste in A3, the code enters a loop.
In A3 I select the value "A", the code enters a loop.


But if I use the code modified from post #9 everything works for me.

 

[Marcelo Branco]

Weird...

M.

 

[DanteAmor]

This is the excel and many systems, maybe some of my office version.