Limiting the number of rows

Hi Everyone,

I have a work Sheet and a user-form. The Text-Box1 in the User-Form is used for scanning Bar-codes. These bar-code numbers via cell "C2" is registered in Range "B4:B14" and corresponding data is populated in other cells....The attached worksheet is self explanatory.....
The present code is not working satisfactorily.
My problem is that i need to limit these bar-code entries to only 10 entries Max. in the range B4:B13.....any more items scanned by bar-code beyond "B13" should pop up a message...."Screen Full...Continue". So far this works fine.....but if an extra entry is made by mistake thereon ....this is where the problem starts.... It leave behind a residue "1" in cell "E14"......

Please note...There are three such user-forms used independently with three different ranges in the same worksheet. ie "KA1:KA17 and "LA1:LA17"

For simplifying bar-codes entries in the sample worksheet....i have used numbers 1 to 19 for different products.....please input numbers in text-box1 and press "Enter"

The sample User-Form and work-Sheet attached is in its most simplified form....
Please note: I have put this question on another Excel forum on the 31st of last month...but haven't got a single reply so far...and I am in hurry as the lock-down may open up soon and need to commission this n my Shop...... I Hope U can help
Thank you.

Hello Roy....Thanks for the reply....

Tried the codes you sent....but no its the same problem....

Can You tell me which part you did not understand......I shall explain it the best i can.

I think you have worked up some solutions for me on this very same project

Roy You said......"I've amended the code slightly and added the formula in A4"

Sorry I don't see any change in the Codes and why the formula in "A4"........."A4:A14" is the column for serial numbers which is generated automatically with formulas.........

My problem lies in "E14"

Did you use the user-form text-box1 to populate the Range "B4 To B14"

To simplify things in this sample worksheet i am using numbers 1 to 19 as barcodes for different products......example put "1" IN TEXTBOX1 and press Enter on your keyboard .....this will make an entry in B4 and thereafter C4,D4,E4and F4 get populated using LOOKUP formula.

AGAIN put any bar code number say "12" in TEXTBOX1 and press Enter on your keyboard.....similarly this will make an entry in B5 and thereafter C5,D5,E5 and F5 get populated so on and so on.......

Now I make total 10 such entries using different barcode numbers, so total 10 items are generated in B4 to B13 and all corresponding cells in C,D,E,and F are populated..........SO FAR SO GOOD......

Say I make the 11th entry as above....I get a MSG. like MsgBox "No More Space On Screen 2....Continue ??" This should put an end to any further entries made Intentionally or Unintentionally.......and not allow any further entries of barcode.......

Problem comes when i make the 11th entry .....msg pops up ....press ok.....clear the message box.......and out of the blue there is this " 1" in range "E14"........ and beyond this when i enter any more entries Intentionally or Unintentionally it creates a havoc with the woksheet cells

In short i wank the code to stop excepting any entry beyond the 10 entries already made and keep showing me the MSG every time any entry is made beyond the 10 th entry..........

I hope i have explained to your satisfaction.....

Please ask if you have any query .....Thank you

Sorry Roy........I found the solution to my problem.....it was just a matter of shifting UP a few lines of code.......Now I don't get the error....

Thanks for your time

Have a nice day and take care.....