+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    443

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

    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
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    4,678

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

    Replace the current Worksheet_Change macro with this one:

    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    443

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

    Hey Mumps1,

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

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    4,678

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

    You are very welcome.

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    11,925

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

    I did the same thing (sort of):

    Please Login or Register  to view this content.
    Last edited by xladept; 05-16-2019 at 12:53 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  6. #6
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    443

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

    hey xladept,

    Thank you so much for your reply.

    Thank you for the help.

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    11,925

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

    You're welcome and thanks for the rep!

  8. #8
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    443

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

    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
    Attached Files Attached Files
    Last edited by anilpatni1234; 05-18-2019 at 01:14 AM.

  9. #9
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    443

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

    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

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,135

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

    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.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    151

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

    Quote Originally Posted by anilpatni1234 View Post
    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.
    Last edited by huuthang_bd; 05-18-2019 at 02:56 AM.

  12. #12
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    443

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

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

  13. #13
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    443

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

    Hey Huuthang,

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

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,135

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

    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.
    Last edited by jindon; 05-18-2019 at 06:39 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1