PDA

View Full Version : trying to finish my vbaproject barcodescanner



asenrene
01-07-2017, 11:13 PM
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:yes

SamT
01-08-2017, 09:28 AM
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
01-08-2017, 11:24 AM
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:yes


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
01-08-2017, 11:29 AM
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:hi:

SamT
01-08-2017, 11:45 AM
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
01-08-2017, 11:56 AM
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
01-08-2017, 12:27 PM
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
01-08-2017, 12:31 PM
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
01-08-2017, 12:39 PM
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
01-08-2017, 12:43 PM
sorry i wrote the wrong name xld

i appologize

SamT
01-08-2017, 04:45 PM
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
01-08-2017, 11:08 PM
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,:hi:

asenrene
01-08-2017, 11:19 PM
i think that the error is coming from the userform because the userform name is still called userform2.

asenrene
01-09-2017, 02:38 PM
its not the userform i tried to change it but still not working Sam

SamT
01-09-2017, 05:42 PM
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
01-09-2017, 11:23 PM
Thanks Sam,
I will check it thanks for the efford.

sincereley Rene

asenrene
01-11-2017, 06:00 AM
I have include the correct barcodes now sam Hope you can help me further.
sincerely Rene

SamT
01-11-2017, 09:12 AM
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
01-11-2017, 09:38 AM
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
01-11-2017, 08:18 PM
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)

asenrene
01-11-2017, 11:46 PM
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)

asenrene
01-11-2017, 11:49 PM
Dear Sam,

Maybe there is something wrong.

I did everything above and keep getting the error down below.


Private Sub cbutSave_Click()
Dim Found As Range
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

greetings rene

asenrene
01-12-2017, 12:58 AM
i Think i know why its not working Sam.
On or business we use excel with the dutch language.
Maybe that's the reason why it's not working.
It change the "sheet" name into the dutch translation with is "Blad"

asenrene
01-12-2017, 10:34 AM
Dear SamT,

I have fixed it and now it's working.
It only saves the data in the first sheet"Summary" instead of the selected sheet i put in the "area" Userform.
I also like to have that when the selected mode automatic is selected it clears the sheet after saving data and then Set.Focus on the quantity box.
So when i am scanning i have to put in the amount and then scan the item. this is an option on the scanner.

Hope to hear from you,

Sincerely Rene

snb
01-12-2017, 02:30 PM
@Rene

Als Engels niet je sterke kant is kun je je vraag wellicht beter in een Nederlands forum plaatsen, zoals helpmij.nl

Je code kan veel eenvoudiger en robuuster gemaakt worden (bijv. door gebruik te maken van intelligente tabellen).
2 voorbeelden:


Private Sub UserForm_Initialize()
For Each it In Sheets
c00 = c00 & "|" & it.Name
Next

lbxAreas.List = Split(Mid(c00, 2), "|")
End Sub


Private Sub SaveData()
on error resume next
Sheets(lbxAreas.Value).Columns(cnBarCode).Find(tbxScannedBarCode, , , 1).Offset(, BarCode2Quantity) = CDbl(tbxQty)
End Sub

Nu zie je meteen ook waarom de gegevens niet in het juiste werkblad geplaatst werden.
Columns verwijst naar het aktieve werkblad, .Columns (met punt ervoor) verwijst naar het werkblad waar je de gegevens wil hebben.

PS. @SamT asked me to 'interfere'.

asenrene
01-13-2017, 12:10 AM
beste Mark,
bedankt voor je hulp en ja het werkt nu prima.
Er is niks mis met me engels hoor ik programmeerde vroeger vrij veel alleen ik moet de draad weer oppakken.
Maar bedankt voor je advies in ieder geval ga ik daar ook eens kijken heb alleen het idee dat de reacties minder zijn.

Het project is bijna af ik zit met een klein probleempje nog misschien kan jij me daar een advies in geven.

Als ik auto save wil gebruiken voor mijn barcode scanner dan worden alle velden te snel gecleard door mijn code.
Misschien heb jij nog enige ideen om dit op te lossen.
de code staat hieronder.
Door dat dit gelijk gebeurt is er geen tijd om de data daadwerkelijk ook te saven.

Gr Rene



Private Sub tbxScannedBarCode_Change()
Const MinimumBarCodeLength As Long = 8


If Len(Me.tbxScannedBarCode.Value) < MinimumBarCodeLength Then Exit Sub

CompareDataShow

If Me.optAutomatisch Then SaveData

If Me.optAutomatisch Then SaveData


tbxQty.Text = ""
tbxScannedBarCode.Text = ""
tbxQty.SetFocus


End Sub