Posts by zubinnajmi

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

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.

Thanks Roy......I will look up this matter...

Hi Carim......not working in my workbook

Same problem...code stops at this line

If ListBox1.ListIndex >= 0 Then
        ListBox1.RemoveItem ListBox1.ListIndex

I think.......you are being troubled unnecessarily.......Your codes in the sample worksheet works just great....

Problem is at my end......

Maybe i'll drop this idea, what say ?

Ok .....got it.....sending the closest workbook.......

of course most of the functions wont work.....

but you can have a glimpse at the userform......It wont open with the command button....lot of missing links..

see if useful

Just let me check if i can get you the userform i am using.....

I think the sample workbook i posted is the closest possible to real-like workbook.......

Only differences are the ranges.....which i think I had mentioned in the sample workbook

Your Question "If you need to display this amount ... you can have a dedicated TextBox to display it ... "

I'll need the whole column F4:F17 in the existing range

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

You check the sample worksheet.........th whole range of B4 to F17 should be displayed in the listbox

your question....."Why F17 ... ???"

as i want the list box to display the "TOTAL AMOUNT" of the transaction ....and this range is in Cell "F17"

and also column heads is set .....true

Set the column count to 5

Hi Carim......Please try the same thing with the Range(B4:F17) and let me know if you get the same results.....

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")

Private Sub UserForm_Initialize()

Sheet1.Activate
Sheet1.Range("C2").Select

frmSaleScan.ListBox1.RowSource = "SaleScan"


frmSaleScan.TextBox2.Value = ""
frmSaleScan.TextBox4.Value = ""
frmSaleScan.TextBox6.Value = Sheet1.Range("G23").Value
frmSaleScan.TextBox3.Value = Sheet1.Range("D1").Value
frmSaleScan.CheckBox1.Value = False
frmSaleScan.TextBox2.Enabled = False
frmSaleScan.TextBox4.Enabled = False
frmSaleScan.TextBox7.Enabled = False
frmSaleScan.CommandButton8.Enabled = False


If Sheet1.Range("B4").Value = "" Then
frmSaleScan.ListBox1.Enabled = False
End If

If Sheet1.Range("C4") <> "" Then
frmSaleScan.TextBox2.Enabled = True
End If

frmSaleScan.Label8.Caption = Sheet1.Range("C1").Value
frmSaleScan.Label12.Caption = Sheet1.Range("B1").Value
frmSaleScan.Label13.Caption = Sheet1.Range("F1").Value
frmSaleScan.Label11.Caption = Sheet1.Range("E1").Value
Sheet1.Range("D21").Value = Sheet1.Range("D19").Value - Sheet1.Range("D20").Value

With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 100)
Width = .Width
Height = .Height
End With
    
Me.Height = Me.Height - 10
HideTitleBar.HideTitleBar Me


End Sub

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

If ListBox1.ListIndex >= 0 Then

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

Thanks Carim.....Have downloaded your test file...just give some time....i'll have to check the functioning in the original workbook.

will revert back soon

Thanks for your time...

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.