trying to finish my vbaproject barcodescanner

[asenrene]

Hello,
I would like to finish my vba project to some inventory issue's on work
I would like to try to right a vba userform that does al the work automatic
First of al i would like to selct in wich area the inventory will be made.
Second i would like to edit it the selected area in de database that matches the same barcode.
Thirth i would like to add it by the select button auto or manual.
Hope you could help me out here

sincereley Rene

[SamT]

First rename the controls on the Form, mostly by the Caption, but always by the "brand" of data. For examples:
OptionButton1 rename optMagazijn
TextBox1 rename tbxBarCodeScan
OptionButton14 rename optAutomatisch
CommandButton1 rename cbutToevoegen

If a label is used for more than a label, ie, if it is to be clicked or if the code is to change the Caption, rename it according to its use. For example: A label used to display a Date, rename lblTodaysDate

Before you upload the revised Book, please remove the picture on the UserForm. We do not need to see the picture and it makes the file size large.


Example Name prefixes
tbx, txb : TextBox
lbl :Lable
lbx :ListBox
cbx, cmb : ComboBox
chk, chbx : CheckBox.
opt, obut : OptionButton
cbut : CommandButton

My preference is: If the control contains data, its name only gets a three letter prefix, otherwise it gets a four letter prefix. This is because I match the Control names, Captions, (or label's captions,) to the Header names and the Range Names on the Worksheet, so I can use code to "ignore" the prefixes to match the names of Controls and Named Ranges. I also use a three letter prefix on Range Names to indicate the Sheet's Tab Names. For Examples:

Sheet1, Tab Name would be "Inventory", Range("G:G") Range Name would be invMagazijn. Since Magazijn is not Data, The Magazijn Control, an OptionButton, I would name obutMagazijn, with a four letter prefix.

Finally, I personally would prefer each department to have its own inventory sheet. Not that spaces and punctuations are not allowed in Control Names, so I would use Tab and Control Names like: Magazijn, HLA, Nood_bar, TOMs, and TAKE_5_koelcel. Control Names get the prefix. Their Range Name prefixes could be mag, hla, noo, tom, tak. etc. It doesn't matter as long as there are three letters.

[asenrene]

Hello,
I would like to finish my vba project to some inventory issue's on work
I would like to try to right a vba userform that does al the work automatic
First of al i would like to selct in wich area the inventory will be made.
Second i would like to edit it the selected area in de database that matches the same barcode.
Thirth i would like to add it by the select button auto or manual.
Hope you could help me out here

sincereley Rene

First rename the controls on the Form, mostly by the Caption, but always by the "brand" of data. For examples:
OptionButton1 rename optMagazijn
TextBox1 rename tbxBarCodeScan
OptionButton14 rename optAutomatisch
CommandButton1 rename cbutToevoegen

If a label is used for more than a label, ie, if it is to be clicked or if the code is to change the Caption, rename it according to its use. For example: A label used to display a Date, rename lblTodaysDate

Before you upload the revised Book, please remove the picture on the UserForm. We do not need to see the picture and it makes the file size large.


Example Name prefixes
tbx, txb : TextBox
lbl :Lable
lbx :ListBox
cbx, cmb : ComboBox
chk, chbx : CheckBox.
opt, obut : OptionButton
cbut : CommandButton

My preference is: If the control contains data, its name only gets a three letter prefix, otherwise it gets a four letter prefix. This is because I match the Control names, Captions, (or label's captions,) to the Header names and the Range Names on the Worksheet, so I can use code to "ignore" the prefixes to match the names of Controls and Named Ranges. I also use a three letter prefix on Range Names to indicate the Sheet's Tab Names. For Examples:

Sheet1, Tab Name would be "Inventory", Range("G:G") Range Name would be invMagazijn. Since Magazijn is not Data, The Magazijn Control, an OptionButton, I would name obutMagazijn, with a four letter prefix.

Finally, I personally would prefer each department to have its own inventory sheet. Not that spaces and punctuations are not allowed in Control Names, so I would use Tab and Control Names like: Magazijn, HLA, Nood_bar, TOMs, and TAKE_5_koelcel. Control Names get the prefix. Their Range Name prefixes could be mag, hla, noo, tom, tak. etc. It doesn't matter as long as there are three letters.

[asenrene]

Dear SamT,
Tank you verry much for the responce.
I understand what you are saying and i will chance the commands.
I also like to know if you could help me with the codes when i have changed the commands.
again thanks for the reply and i will get straid to it

Sincereley Rene

[SamT]

When the Names are well thought about, the code makes sense to humans as well as to computers.

Compare:

TextBox17.Value = Sheets("Sheet3").Range("F:F").Find(TextBox3.Value).Offset(0,-4)

tbxEmployeeID = Sheets("Employee_Info").Range("Employee_Names").Find(TbxEmployee_Name).Offset(0, EmpName2EmpID)

nb: I can take as much time thinking about Names, (and changing them,) as I do about the code. But it sure makes good code easier and faster to write.

[Bob Phillips]

First of al i would like to selct in wich area the inventory will be made.

Do you mean by that that you want to add a droplist that you can select from, a list of category (Bier, Koffie/Thee, etc), and then only work with items that fit that category[/quote]

Second i would like to edit it the selected area in de database that matches the same barcode.

What do you mean by this? There are not many items in your database with a barcode, and which barcode are you referring to?

Thirth i would like to add it by the select button auto or manual.

What do you mean by this?

[asenrene]

Oke thanks for youre quick reply

The answer on the firtst question should be, That i would like to have that when the person who i going to do the inventory select true de optbutton where he is going to do the inventory.
So for example he is going to the Hla i would like to have him select that area and then when its selected the datbase automaticly put the inventory in the right colum/row.

For the second question i have to answer you that in sheet 1 all the items will be filled with barcodes, but i only have done a few wich are correct by the scanning to check if i had the code correct for searching the right item.
Al items are unique with a different barcode so that is wher i wanted the search from. there are 4 numbers and if i fille in the numbers in de textbox barcode scan the are working oke i think.

There are two buttons auto and manual because i use a radio scanner because of the building is huge i want to have the barcode automodes fill in the count wich is going to set bij the box Aantal on the form and then fill the amount in the correct cells/rows where the inventory will be made.
in the manual mode the have to hit the Toevoegen Button.

[asenrene]

sorry for my terrible englisch SamT
i just have put in the excel sheet just the way i think you have prefered.

sincereley Rene

[asenrene]

sorry for my terrible englisch SamT
i just have put in the excel sheet just the way i think you have prefered.

and for the meaning of sheet2 and 3 are not important to me those wher just for testing Sam

sincereley Rene

[asenrene]

sorry i wrote the wrong name xld

i appologize

[SamT]

Underneath thse lables, you have a single large ListBox, "Lisbox1." For what purpose?

Artikelnum, Omschrijving, Inhoud, Eenheid, and Barcode
In English. (Please verify my translation)
Stock Number, Description, Quantity per Unit, Units, and Barcode.

What is the difference in usage between "BarCode" and "BarCode Scan"?

I changed the UserForm to please me. I also added some code that will put the quantity entered into the correct sheet at the correct row. You can test it by selecting a sheet from the new ListBox, then entering a correct barcode manually or with the scanner, then clicking the Save button (Toevoegen). A further test is to enter an incorrect barcode.

I wrote it in Excel XP, (an xls file,) so open it, then save it as an xlsm file.

My next step is to write a UDF for the Summary sheet to total all the quantities from the other sheets. I await your approval of current work.

[asenrene]

Dear Sam,
Thank you again for changing the form it looks verry good.

To answer youre first question the listbox is letting me showing the right scanproduct from the scanner. i also could only used a single textbox.

To verify youre translation i would like to say thats correct.

The next question is the differents between Barcode and barcodescan should be: Barcode is the product item, and the barcodescan is the item that the scanner has entered.

i have tried to check if the sheets are working but i couldn't confirm with that i get a error on:

Private Sub cbutSave_Click()
Dim Found As Range


[This is where the error is coming from, he says that the sheets are not the same] With Sheets(Me.lbxAreas)
Set Found = Columns(cnBarCode).Find(tbxBarCodeScan)
If Not Found Is Nothing Then
Found.Offset(, BarCode2Quantity) = tbxQuantity
Else
MsgBox "That Barcode was not found on sheet " & Me.lbxAreas
End If
End With
End Sub

and the userform have to be also in automode because of the large building whe have.
so the person dont have to click every time on the button.

and i have to say that it looks verry stunning. Thanks again SamT.

sincerely Rene,

[asenrene]

i think that the error is coming from the userform because the userform name is still called userform2.

[asenrene]

its not the userform i tried to change it but still not working Sam

[SamT]

Here's my latest. It's not fully tested against good Barcodes, but it compiles. It's not compete, not all possible User Errors are covered.

[asenrene]

Thanks Sam,
I will check it thanks for the efford.

sincereley Rene

[asenrene]

I have include the correct barcodes now sam Hope you can help me further.
sincerely Rene

[SamT]

Talk to me about the workbook .

How is it working is it doing what you need what is it doing wrong what else does it need why are we here whats the meaning of life and all the important questions?

[asenrene]

Dear Sam,

Its not working at all.
It doesn't find the product or barcode its just keep on saying i have to fill in the area and quantity.

yesterday i have scanned all the barcodes in to the program. So that took some time but i managed it.

the code also isn't writing any cells so i was hard for me to find out what wash wrong or even to test it.

maybe i do something wrong.?
for what the meaning of life i'm sure it is to understand VBA LOL.
I also like to say that the program looks amazing if it works and it would save me up to 8 or 9 hours every month.

Hope you can tell me why

Sincereley Rene

[SamT]

It doesn't find the product or barcode its just keep on saying i have to fill in the area and quantity.

Strange. That is the part that I tested.

Did you select an area,(worksheet name) and enter something in the quantity textbox?

During my testing, I just added "12345678" as a dummy Barcode in the First Area sheet and it was found and a quantity was placed in the correct cell.

About my Language.
Area means a Casino area or department or what is represented by all the individual inventory Worksheets. These "Areas" are what your original many OptionButtons were used to select. An "Area" must be selected in the Listbox at the upper Left of the UserForm before anything will work.

Quantity is the Aantal textbox.

The Names in the Code were used to try and correlate the English and Dutch words so that speakers of both languages could understand the code without referring to a translation dictionary. For examples, in the Sub CompareDataShow:
Me.tbxItemNumber = .Cells(rw, cnArtikelnr)
Me.tbxDescription = .Cells(rw, cnOmschrijving)
Me.tbxQtyPerUnit = .Cells(rw, cnInhoudProduct)
Me.tbxUnits = .Cells(rw, cnEenheid)
Me.tbxBarCode = .Cells(rw, cnBarCode)