+ Reply to Thread
Results 1 to 9 of 9

Inventory Sorting Code - Need help with pop-up messages

  1. #1
    Registered User
    Join Date
    07-07-2013
    Location
    LONDON
    MS-Off Ver
    Excel 2010
    Posts
    5

    Inventory Sorting Code - Need help with pop-up messages

    Inventory Sorting sheet need help.xlsm

    Hello Folks,

    Using replies to previous threads in this forum, I've compiled the attached excel sheet that's going to assist me in physical sorting of a bunch of items to determine which carton the item should go to "Carton A" or "Carton B".

    Here's what the excel program does so far -

    1. Select cell A4 and press the click the button on a barcode scanner attached to the computer. If the barcode number read is under column C "Barcode Number" - list which has been previously entered, then the column B "QTY" will get updated by 1.

    2. Similarly if I want to reduce the quantity, then I select cell C4 and take a barcode reading. Likewise if I want to find a barcode, I slect cell E4 and take a reading and the found cell under column C will have a box.

    3. Column N and O contain the allocation instructions I have previously received about how many pieces need to go into carton A and B. This data, like Column C has been entered previously.

    4. Taking item - Toy Car - as an example, the allocation instructions columns N and O tell me that I should put 6 Toy cars in Carton A and 12 in Carton B.

    So here it begins....

    Round 1: Using scanner take a barcode reading of toy Car
    B8 shows 1/ I8 shows 1/ J9 shows -

    Round 6: B8 shows 6/ I8 shows 6/ J9 shows -
    Round 7: B8 shows 7/ I8 shows 6/ J9 shows 1
    Round 8: B8 shows 8/ I8 shows 6/ J9 shows 2
    Round 18: B8 shows 18/ I8 shows 6/ J8 shows 12
    Round 19: B8 shows 18/ I8 shows 6/ J8 shows 12/ K8 shows 1 "excess"

    The program does this already so far.
    ---




    Here's what I'm not able to do, and request someone's assistance. Many thanks in advance:

    1. If a cell in column B <= a cell in the same row in column N, then I want a pop-up message that says "Put item in Carton A". The the user has to click OK button only once and the msg box should disappear.

    2. If a cell in column B > a cell in the same row in column N, then I want a pop-up message that says "Put item in Carton B". The user has to click OK button only once and the msg box should disappear.

    3. As sooon as a cell in column J reaches its maximum limit (prescribed by column O), then I'd like a pop-up message that says "Allocated pieces for Carton B has reached its limit. Anything more means that, physically, excess has been received than anticipated. Keep aside in a separate carton"

    4. Lastly, please make note of the fact that I will be adding many more rows so the program should account for additional rows being added.

    I've attached the xlsm file in this thread
    Last edited by jjjj11; 07-09-2013 at 05:40 AM.

  2. #2
    Registered User
    Join Date
    07-07-2013
    Location
    LONDON
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Inventory Sorting Code - Need help with pop-up messages

    The VBA code is there in the attachment in the previous message.
    Last edited by jjjj11; 07-09-2013 at 05:42 AM.

  3. #3
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Inventory Sorting Code - Need help with pop-up messages

    1. If a cell in column B <= a cell in the same row in column N, then I want a pop-up message that says "Put item in Carton A". The the user has to click OK button only once and the msg box should disappear.

    2. If a cell in column B > a cell in the same row in column N, then I want a pop-up message that says "Put item in Carton B". The user has to click OK button only once and the msg box should disappear.
    This should happen for each row from the top of the sheet till the last row? Or do you want it to happen as each row is added to the sheet?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Registered User
    Join Date
    07-07-2013
    Location
    LONDON
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Inventory Sorting Code - Need help with pop-up messages

    Hi Arlu...thanks for looking in to this.

    This should happen for each row starting from Row 8 going down.

  5. #5
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Inventory Sorting Code - Need help with pop-up messages

    Hi,

    Put this code before the end of your current Worksheet_Change code:
    Please Login or Register  to view this content.
    abousetta
    Last edited by abousetta; 07-10-2013 at 02:31 AM. Reason: Added line color
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  6. #6
    Registered User
    Join Date
    07-07-2013
    Location
    LONDON
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Inventory Sorting Code - Need help with pop-up messages

    Hello Abousetta,

    Thanks a ton!! Works like a charm! Adding a star right away. In fact Ace_XL even recommended you as an expert in VBA.

    I have one last request.
    This feature will be helpful when I have a large number of items as the highlighted row will tell me what product from the list I'm sorting.

    Requesting the feature through an example:
    If I select cells A4 or C4 or E4, and press the trigger in the barcode scanner for an item with barcode number 3607621310326 (for example), then I would like Row 9 to be highlighted in Yellow (temporarily), till the time the next item is read using the scanner which should now highlight the new row where the barcode is present.

    This would provide a visual cue highlighting what product (from a long list in the excel sheet) I'm exactly sorting.

    Sincere thanks in advance!

    JJ

  7. #7
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Inventory Sorting Code - Need help with pop-up messages

    Hi JJ,

    Glad it worked out. I added a few lines to the code for coloring the active row yellow. What it does is first clear all the coloring from rows 8 downwards (clearing the old yellow highlighting). Then it return the light blue you have in Column B from row 8 to the end of the used rows (determined by data in column A). Finally, it colors the active row yellow (highlight).

    Let me know if you need anything else.

    abousetta

  8. #8
    Registered User
    Join Date
    07-07-2013
    Location
    LONDON
    MS-Off Ver
    Excel 2010
    Posts
    5

    Thumbs up Re: Inventory Sorting Code - Need help with pop-up messages

    Thanks its working perfectly as I imagined!
    Thanks for your looking in to this!

  9. #9
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Inventory Sorting Code - Need help with pop-up messages

    Thanks for the feedback. Good luck.

    abousetta

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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