Inventory Sorting Code - Need help with pop-up messages

[jjjj11]

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

[jjjj11]

The VBA code is there in the attachment in the previous message.

[arlu1201]

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?

[jjjj11]

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

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

[abousetta]

Hi,

Put this code before the end of your current Worksheet_Change code:

Please Login or Register  to view this content.

abousetta

[jjjj11]

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

[abousetta]

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

[jjjj11]

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

[abousetta]

Thanks for the feedback. Good luck.

abousetta