Some last problems with barcode prog. ' SetFocus, Barcode Length ,Autosave'

[asenrene]

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

[SamT]

If cbutSave is a CommandButton. it has no Value Property

Private Sub tbxQty_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If BarCodeEntered Then SaveData
cbutSave.Value = True '<<<<<<<<<<<<<<<<<<<<<< Not good.
End Sub

'Now I know the two lengths of your barcodes

Private Sub tbxScannedBarCode_Change()

If Len(Me.tbxScannedBarCode.Value) <> 8 _
Or Len(Me.tbxScannedBarCode.Value) <> 13 Then Exit Sub

' If Auto, no need to see existing data
If Me.optAutomatisch Then
SaveData
Else
'Show existing info before manual save
CompareDataShow
End IF
End Sub


Private Sub SaveData()
'On Error Resume Next 'Uncomment only after thorough testing

Sheets(LbxAreas.Value).Columns(cnBarCode).Find(tbxScannedBarCode, , , 1).Offset(, BarCode2Quantity) = CDbl(tbxQty)

tbxQty.Text = ""
tbxScannedBarCode.Text = ""
tbxQty.SetFocus
End Sub



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.

That doesn't sound like a code problem. :dunno

[SamT]

I don't like the Save sub written like that because it never shows an error if there is a problem with the inventory bar codes. I once spent three months having to deal with bad barcodes while stocking retail stores. Thankfully, I had a Lotus v1 Floppy Disk and a CP/M Portable Computer with a 10 megabyte Hard drive at home, so I could send reports back to Corporate office.

The original one I gave you incorporated a message if the barcode was not found. Here is another variant.


Private Sub SaveData()
Dim Found as range

Set Found =Sheets(LbxAreas.Value).Columns(cnBarCode).Find(tbxScannedBarCode, , , 1)

if Not Found is Nothing then
Found.Offset(, BarCode2Quantity) = CDbl(tbxQty)
else
MsgBox "Ooops! somting wrong" 'Edit to suit.
'Maybe a good idea to record problems on an "Errors" Worksheet
end if

tbxQty.Text = ""
tbxScannedBarCode.Text = ""
tbxQty.SetFocus
End Sub

[asenrene]

Dear SamT
I cannot explain in words how grateful i am.

Everything is working great only one tiny thing in auto mode it is not saving data and it doesn't clear the textboxes.

Once again you have been so helpfull.you have solved a lot of my problems.
And like the beatles song i will quote you "you kind buy a little help from your friends"
a Major!! compliment to you Sam.

Hope you fix the last item

sincerely Rene Roos

[SamT]

Does Private Sub tbxScannedBarCode_Change run when you scan a barcode?

[asenrene]

Dear SamT,
I have figure it out so everything is working great now due some changes.
Could you give me some last advice and this topic can be closed then.

Where in the code should i change the save data in to another row.?
For example its Now saving in row (I )and i would like to use (L) in the future.

thanks

Sincerely Rene Roos

[SamT]

Offset(, Barcode2Quantity)