asenrene
01-18-2017, 03:18 PM
Dear Readers,
Add First let me explain how the program works.
In the top left you have to select a area where you want to begin you're inventory.
After selecting that Area you can a choose for auto or manual mode.
Once you have finished that you can begin with scanning.
In Auto mode everything you scan will be saved automatic in manual mode you have to push the save button(toevoegen)
In the Field (datum Stock) you have to fill in the date of inventory and beneath the field there you have to fill in your names.
In the field (verwijder alle gegevens) you clear all the data from your inventory.
But here is the problem.
I almost have finished my Barcode scanner prog.
I only think it's a minor issue, In the top right corner i have a option to select a mode.
In the manual mode there isn't any problem and everything works fine.
But in the Auto Mode become's the tricky part.
I use barcodes that are 8 or 13 Characters Long.
When is scan a barcode that's 8 Characters long there is isn't any problem but when i scan an item that has 13 characters long its fills in 8 characters and then the 5 remaining characters in the quantity box.
this problem is only happening when i am trying to put the following code beneath the autosave code in auto mode.
tbxQty.Text = ""
tbxScannedBarCode.Text = ""
tbxQty.SetFocus
it also then do not clear the txb and doesn't function correctly anymore.
What i really want to have when it's find in auto mode the right item save it and then clear the txtbqty and clears the tbxScannedBarcode so it can continues.
hope you guys could help me out here.
so in fact there are the following questions
1. I would like to have solved the issue with the length of the barcode
2. I would like to have solved the issue with option mode Auto
3. i Would like to have solved the issue with Set focus on the txtQty.box after saving so i can scan permanent.
Hope you can help me out guys :crying::crying:
here is my code
Option ExplicitVB:
'Declared in modConstants for use anywhere
'Const ExcludeSheets As String = "Summary, Print_Barcodes"
'Used in tbxQuantity_Exit
Private BarCodeEntered As Boolean
Private AreaSelected As Boolean
Private Sub cbutQuit_Click()
'Close the userform
Unload Me
End Sub
Private Sub ComboBox1_Change()
ComboBox1.List = [index(text(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)-365+row(1:1095),"dd-mm-yyyy"),)]
End Sub
Private Sub CommandButton1_Click()
If MsgBox("LET OP!!!Alle stock gegevens zullen worden verwijderd. Weet je het zeker?", vbYesNo) = vbNo Then Exit Sub
Sheets("Magazijn").Range("H6:H300").ClearContents
Sheets("Hla").Range("H6:H300").ClearContents
Sheets("Noodbar").Range("H6:H300").ClearContents
Sheets("Toms").Range("H6:H300").ClearContents
Sheets("Take_5_koelcel").Range("H6:H300").ClearContents
Sheets("Devinebar").Range("H6:H300").ClearContents
Sheets("Spa_2").Range("H6:H300").ClearContents
Sheets("Joybar").Range("H6:H300").ClearContents
Sheets("Slot_Square").Range("H6:H300").ClearContents
Sheets("Slot_heaven").Range("H6:H300").ClearContents
Sheets("Business_lounge").Range("H6:H300").ClearContents
Sheets("Magazijn_1e_verd").Range("H6:H300").ClearContents
Sheets("Oude_Vip").Range("H6:H300").ClearContents
End Sub
Private Sub LbxAreas_Change()
With Me
If .LbxAreas.Value <> "" Then AreaSelected = True
.lblNowInventorying.Caption = .LbxAreas
End With
End Sub
Private Sub tbxQty_Exit ByVal Cancel As MSForms.ReturnBoolean)
If BarCodeEntered Then SaveData
cbutSave.Value = True
End Sub
Private Sub tbxScannedBarCode_Change()
Const MinimumBarCodeLength As Long = 8
If Len(Me.tbxScannedBarCode.Value) < MinimumBarCodeLength Then Exit Sub
CompareDataShow
If Me.optAutomatisch Then
SaveData
End If
End Sub
Private Sub UserForm_Initialize()
Dim Sht As Worksheet
With COLOR] Me.LbxAreas
For Each Sht In Worksheets
If InStr(ExcludeSheets, Sht.Name) = 0 Then _
.AddItem Sht.Name
Next
End With
End Sub
Private Sub cbutSave_Click()
Dim Found As Range
With Me
If Not AreaSelected Then
MsgBox "Selecteer eerst een afdeling waar je gaat stocken "
.LbxAreas.SetFocus
Exit Sub
End If
If .tbxQty.Value = Null Then
MsgBox "Je moet een waarde invullen voor je verder kunt"
.tbxQty.SetFocus
Exit Sub
End If
End With
SaveData
End Sub
Private Sub CompareDataShow()
Dim Found As Range
Dim rw As Long
If Not AreaSelected Then Exit Sub
With Sheets(Me.LbxAreas.Value)
Set Found = .Columns(cnBarCode).Find(what:=tbxScannedBarCode, LookAt:=xlWhole)
If Not Found Is Nothing Then
rw = Found.Row
Else
Exit Sub
End If
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)
End With
End Sub
Private Sub SaveData()
On Error Resume Next
Sheets(LbxAreas.Value).Columns(cnBarCode).Find(tbxScannedBarCode, , , 1).Offset(, BarCode2Quantity) = CDbl(tbxQty)
End Sub
Sincerely Rene
Add First let me explain how the program works.
In the top left you have to select a area where you want to begin you're inventory.
After selecting that Area you can a choose for auto or manual mode.
Once you have finished that you can begin with scanning.
In Auto mode everything you scan will be saved automatic in manual mode you have to push the save button(toevoegen)
In the Field (datum Stock) you have to fill in the date of inventory and beneath the field there you have to fill in your names.
In the field (verwijder alle gegevens) you clear all the data from your inventory.
But here is the problem.
I almost have finished my Barcode scanner prog.
I only think it's a minor issue, In the top right corner i have a option to select a mode.
In the manual mode there isn't any problem and everything works fine.
But in the Auto Mode become's the tricky part.
I use barcodes that are 8 or 13 Characters Long.
When is scan a barcode that's 8 Characters long there is isn't any problem but when i scan an item that has 13 characters long its fills in 8 characters and then the 5 remaining characters in the quantity box.
this problem is only happening when i am trying to put the following code beneath the autosave code in auto mode.
tbxQty.Text = ""
tbxScannedBarCode.Text = ""
tbxQty.SetFocus
it also then do not clear the txb and doesn't function correctly anymore.
What i really want to have when it's find in auto mode the right item save it and then clear the txtbqty and clears the tbxScannedBarcode so it can continues.
hope you guys could help me out here.
so in fact there are the following questions
1. I would like to have solved the issue with the length of the barcode
2. I would like to have solved the issue with option mode Auto
3. i Would like to have solved the issue with Set focus on the txtQty.box after saving so i can scan permanent.
Hope you can help me out guys :crying::crying:
here is my code
Option ExplicitVB:
'Declared in modConstants for use anywhere
'Const ExcludeSheets As String = "Summary, Print_Barcodes"
'Used in tbxQuantity_Exit
Private BarCodeEntered As Boolean
Private AreaSelected As Boolean
Private Sub cbutQuit_Click()
'Close the userform
Unload Me
End Sub
Private Sub ComboBox1_Change()
ComboBox1.List = [index(text(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)-365+row(1:1095),"dd-mm-yyyy"),)]
End Sub
Private Sub CommandButton1_Click()
If MsgBox("LET OP!!!Alle stock gegevens zullen worden verwijderd. Weet je het zeker?", vbYesNo) = vbNo Then Exit Sub
Sheets("Magazijn").Range("H6:H300").ClearContents
Sheets("Hla").Range("H6:H300").ClearContents
Sheets("Noodbar").Range("H6:H300").ClearContents
Sheets("Toms").Range("H6:H300").ClearContents
Sheets("Take_5_koelcel").Range("H6:H300").ClearContents
Sheets("Devinebar").Range("H6:H300").ClearContents
Sheets("Spa_2").Range("H6:H300").ClearContents
Sheets("Joybar").Range("H6:H300").ClearContents
Sheets("Slot_Square").Range("H6:H300").ClearContents
Sheets("Slot_heaven").Range("H6:H300").ClearContents
Sheets("Business_lounge").Range("H6:H300").ClearContents
Sheets("Magazijn_1e_verd").Range("H6:H300").ClearContents
Sheets("Oude_Vip").Range("H6:H300").ClearContents
End Sub
Private Sub LbxAreas_Change()
With Me
If .LbxAreas.Value <> "" Then AreaSelected = True
.lblNowInventorying.Caption = .LbxAreas
End With
End Sub
Private Sub tbxQty_Exit ByVal Cancel As MSForms.ReturnBoolean)
If BarCodeEntered Then SaveData
cbutSave.Value = True
End Sub
Private Sub tbxScannedBarCode_Change()
Const MinimumBarCodeLength As Long = 8
If Len(Me.tbxScannedBarCode.Value) < MinimumBarCodeLength Then Exit Sub
CompareDataShow
If Me.optAutomatisch Then
SaveData
End If
End Sub
Private Sub UserForm_Initialize()
Dim Sht As Worksheet
With COLOR] Me.LbxAreas
For Each Sht In Worksheets
If InStr(ExcludeSheets, Sht.Name) = 0 Then _
.AddItem Sht.Name
Next
End With
End Sub
Private Sub cbutSave_Click()
Dim Found As Range
With Me
If Not AreaSelected Then
MsgBox "Selecteer eerst een afdeling waar je gaat stocken "
.LbxAreas.SetFocus
Exit Sub
End If
If .tbxQty.Value = Null Then
MsgBox "Je moet een waarde invullen voor je verder kunt"
.tbxQty.SetFocus
Exit Sub
End If
End With
SaveData
End Sub
Private Sub CompareDataShow()
Dim Found As Range
Dim rw As Long
If Not AreaSelected Then Exit Sub
With Sheets(Me.LbxAreas.Value)
Set Found = .Columns(cnBarCode).Find(what:=tbxScannedBarCode, LookAt:=xlWhole)
If Not Found Is Nothing Then
rw = Found.Row
Else
Exit Sub
End If
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)
End With
End Sub
Private Sub SaveData()
On Error Resume Next
Sheets(LbxAreas.Value).Columns(cnBarCode).Find(tbxScannedBarCode, , , 1).Offset(, BarCode2Quantity) = CDbl(tbxQty)
End Sub
Sincerely Rene