Dont allow to write in a range , if cell contains word "SOLD"

[anilpatni1234]

Hey guys,
I need help,

I need help with a VBA code.

In Sheet "INVOICE" , if a user types any data on - B11:B29 , then it vlooks up in sheet PRODUCT for the data.

Currently, in sheet "PRODUCT" , cell A11, has code "ak46" . Column "P11" shows that it is already sold because it contains "SOLD".

what i want is that , is a user enters any data in sheet "INVOICE" "B11:b29" , then i dont want it to allow the user to write that code if code contains "SOLD" in sheet "PRODUCT"

Example,
sheet "product" - b11 contains "ak46" . Column "p11" shows it is sold.

SO, in sheet INVOICE, if i try to write "ak46" in B11:B29, then it should not allow the user to write it.
Maybe can show a pop up that "CODE IS ALREADY SOLD"

Can anyone please help me ?

Thank you.

I ahve attached my workbook as well

[Mumps1]

Replace the current Worksheet_Change macro with this one:

Please Login or Register  to view this content.

[anilpatni1234]

Hey Mumps1,

Thank you so much.
The code is too perfect.
Accurate.
Thank you so much

[Mumps1]

You are very welcome.

[xladept]

I did the same thing (sort of):

Please Login or Register  to view this content.

[anilpatni1234]

hey xladept,

Thank you so much for your reply.

Thank you for the help.

[xladept]

You're welcome and thanks for the rep!

[anilpatni1234]

Hey Mumps 1,

I am facing a problem,

The code was working perfectly but now I am facing a problem.
The code checks if item contains "SOLD" and shows that "CODE IS ALREADY SOLD"

However the problem I am facing is with this part of the code:

Please Login or Register  to view this content.

If any data is written in P11:P29, then it automatically time stamsps and date stamps in Q7 and A8.

However this part of the code is not working when you mearge your code with this. It wroks when it is placed alone. Very Strange

Could you please check once?
I have attached my workbook

[anilpatni1234]

Hey xladept ,

I am facing the same error (POST NUMBER 8 in this thread) in the code which you provided me .
Could you please check once

[jindon]

1) Add following code at the very top of the code module

Please Login or Register  to view this content.

2) change Change event code to

Please Login or Register  to view this content.

3) To ThisWorkbook code moulde

Please Login or Register  to view this content.

[huuthang_bd]

Hey Mumps 1,

I am facing a problem,

The code was working perfectly but now I am facing a problem.
The code checks if item contains "SOLD" and shows that "CODE IS ALREADY SOLD"

However the problem I am facing is with this part of the code:

Please Login or Register  to view this content.

If any data is written in P11:P29, then it automatically time stamsps and date stamps in Q7 and A8.

However this part of the code is not working when you mearge your code with this. It wroks when it is placed alone. Very Strange

Could you please check once?
I have attached my workbook

The reason is column index of column P is 16, not 21.

[anilpatni1234]

Hey Jindon,
This code works perfectly.
Thank you ...

[anilpatni1234]

Hey Huuthang,

Thank you so much.
It was useful. It worked perfectly for me .
Thanks again

[jindon]

You are welcome and thanks for the rep.
found a bug.

Replace Worksheet_Change event code with the following

Please Login or Register  to view this content.