Posts by zubinnajmi

    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

    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

    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.

    I am sorry about that.....I am totally new to this whole set of programming.....I started with a simple excel sheet for my business use and slowly the whole thing grew so big....now there are little chances of modifying anything....so I am just trying to accommodate what ever is possible... maybe in future when i am well versed with vba i'll develop the whole thing from scratch and systematically .....

    for time being if you can adjust this code to my requirement....ill be greatfull

    Hi Carim.....your code is working perfect with the sample sheet.....but is creating havoc when i place it in my worksheet

    This might be due to the difference in the initialization of the userform

    My rowsource is defined (frmSaleScan.ListBox1.RowSource = "SaleScan") in the initialization code....... where saleScan= Range("B4:F17")

    The other list box properties have been defined in the Listbox property window.....

    One important thing to note is the Column heads are set to true....

    I have retained my initialization code......

    now the delete code you have provided is giving the following error on this line of code


    Code
    1. If ListBox1.ListIndex >= 0 Then
    2. ListBox1.RemoveItem ListBox1.ListIndex

    I am sure it has to to something with the list index value......



    Please look into the matter and i am sure this is going to workout

    Thanks Carim

    Yes Carim.....all columns in range B4:F14 have different functions...

    I have a userform from where a product Barcode is scanned.....these barcodes are added in Range "B4:B14

    Range C4:C14 has VlookUp formula wherein the product names are populated.

    Range D4:D14 has VlookUp formula and populates the unit Price

    Range E4:E14 is the quantity input column..default "1"

    and F4:F14 is the Total ie Unit Price X Qty.

    so far say if a range of products were scanned. i could not remove any item from the list....I had to cancel the whole transaction and re-scan again.

    This is the reason this is necessary. Only the selected cells of B4:F14 and E4:E14 have to be removed and the rest of the range takes care of itself.

    Hope I have explained to your satisfaction.