Makro-I have 4 barcode scanners, in different areas & i need to scan the Tool.

0
Code_Goes_Here
Hi,

excuse me for my english.(google translate)

I just started a new job. Maintenance.

Maybe what I'm asking of you is too much. But I seriously need it.

I have 4 barcode scanners, in different areas & i need to scan the Tool.

                                    Scan Tool

BarCode Scane                        [Barcode ID]                                                                                                                       Automatic Barcode scaned. & put the real Dt and                                                                                                            Time in Data Sheet                                                                                                                                                                     Enter from:                              1,2,3,4

Exit To:                              1,2,3,4

                                                                 Input & Output from 4 Diferent Area

 Nr in Numrator:     1.000.000

Check By Quality           0 - 100.000 (Charts Bar with % change color.                                                                                               0-90.000=Green &   90.000-100.000 Red)

Answer
Discuss

Discussion

Hello.
How do you get the data from the scanner onto your computer?
k1w1sm (rep: 177) Sep 15, '19 at 12:56 am
Hi,
I'm creating a "Data entry Form" with tutorial suggestions.
I created a database to collect the data.
I need a way to -excel- automatically recognize the scanner and save it to the Database (with the exact location)
Contt4 Sep 15, '19 at 5:09 am
Hi 
My knowledge of scanners is a bit limmited. I undrstood that they had built in software to store and the down load the data using usb connection or even wirelessly over wifi. I think we need the to know the type of scanner and access to its operating or a description of how it interacts with your machine as if excel did not exist.
A most interesting puzzel I hope to help and learn with you. 
k1w1sm (rep: 177) Sep 15, '19 at 3:00 pm
“Wired Laser Barcode Scanner Barcode Reader Handheld Barcode Scanner USB Barcode Scanner“
uses a simple barcode scanner.
I think I should use 4 'DataEntry  Form' (1 for each barcode scanner) 
with separate entry for each barcode scanner, and collects data through a network in a Database.
then process the data I need from the Database.
 thank you for your interest. I appreciate it very much.
i'm an illiterate in programming. and I don't know more than 3% of Excel
Contt4 Sep 15, '19 at 3:29 pm
What have you got so far?
If you have your excel form open and a cell selected then atttach the scanner  is the cell populated when you scan a barcode? 
k1w1sm (rep: 177) Sep 15, '19 at 5:53 pm
Option Explicit
Function ValidateForm() As Boolean
   txtID_Barcode.BackColor = vbWhite
   cmbEntry_From.BackColor = vbWhite
   txtComment.BackColor = vbWhite
   txtNr_Crimping.BackColor = vbWhite
    cmbExit_To.BackColor = vbWhite
  ValidateForm = True
     If Trim(txtID_Barcode.Value) = "" Then
       MsgBox "Name can't be left blank.", vbOKOnly + vbInformation, "ID_Barcode"
   txtID_Barcode.BackColor = vbRed
   txtID_Barcode.Activate
    ValidateForm = False
       ElseIf cmbEntry_From.Text <> "Maintenance" And cmbEntry_From.Text <> "Cutting" And cmbEntry_From.Text <> "-3" And _
   cmbEntry_From.Text <> "-4" And cmbEntry_From.Text <> "Quality" Then
    MsgBox "Please Select the Entry_From.", vbOKOnly + vbInformation, "Entry_From"
   cmbEntry_From.BackColor = vbRed
   cmbEntry_From.Activate
ValidateForm = False
 
 
 
Contt4 Sep 15, '19 at 7:46 pm
ElseIf cmbExit_To.Text <> "Maintenance" And cmbExit_To.Text <> "Cutting" And cmbExit_To.Text <> "-3" And _
cmbExit_To.Text <> "-4" And cmbExit_To.Text <> "Quality" Then
  MsgBox "Please Select Exit_To.", vbOKOnly + vbInformation, "Exit_To"
    cmbExit_To.BackColor = vbRed
    cmbExit_To.Activate
ValidateForm = False
    End If
End Function
Function Reset()
  Application.ScreenUpdating = False
    txtID_Barcode = ""
    txtID_Barcode.BackColor = vbWhite
    cmbEntry_From.Text = ""
    cmbEntry_From.BackColor = vbWhite
    txtComment = ""
    txtComment.BackColor = vbWhite
    txtNr_Crimping = ""
    txtNr_Crimping.BackColor = vbWhite
    cmbExit_To = ""
    cmbExit_To.BackColor = vbWhite
 Application.ScreenUpdating = True
End Function
Contt4 Sep 15, '19 at 7:47 pm
Private Sub cmdReset_Click()
   Dim i As Integer
   
   i = MsgBox("Do You Want To Reset This Form?", vbQuestion + vbYesNo + vbDefaultButton2, "Form Reset")
   If i = vbYes Then
Call Reset
 End If
End Sub
Private Sub cmdSave_Click()
  Application.ScreenUpdating = False
   Dim iRow As Long
    iRow = Sheets("Data").Range("A1048576").End(xlUp).Row + 1
    If ValidateForm = True Then
   With ThisWorkbook.Sheets("Data")
     .Range("A" & iRow).Value = iRow - 1
     .Range("B" & iRow).Value = txtID_Barcode.Value
     .Range("D" & iRow).Value = cmbEntry_From.Text
     .Range("E" & iRow).Value = txtNr_Crimping.Value
     .Range("H" & iRow).Value = cmbExit_To.Text
     .Range("j" & iRow).Value = txtComment.Value
   End With
   End If
End Sub
Contt4 Sep 15, '19 at 7:48 pm
Private Sub txtID_Barcode_Change()
End Sub
        here I arrived.
I need a way to invalidate Entry. (when I made one for the same Tool). And automatically send me to the exit at the moment when Scan Tool
Contt4 Sep 15, '19 at 7:49 pm
I have madde a bit of a start.
From what I understand you do not want to enter the same barcode more than once I woud sugget a find on the barcode entered against those previously enter.
Private Sub txtID_Barcode_Change()
'the following line is just for debug to check that the barcode has been entered
MsgBox "New Data = " & TextBox1
 
Dim NewBarcode
Dim LastRow As Integer
Dim SearchRange As Range
 
 
NewBarcode = TextBox1.Value
 
'check to see if this barcode has been used
' do a ind on the previously loaded barcodes
'get last row in serch column
'LastRow = Range("E1").End(xlDown).Row
LastRow = Worksheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell).Row
 
Set SearchRange = Worksheets("Sheet1").Range(Cells(1, "B"), Cells(LastRow, "B"))
 
' find needs to go here - run out of time will have a look tommorrow and carry on
 'if you have not solved it youself
 
End Sub
k1w1sm (rep: 177) Sep 15, '19 at 11:14 pm
Contt, this is awful! please edit your question to include both the code you already have and the question you have regarding that code. Nobody wants to look for your question in the discussion.
Then add one detail to your question which is conspiciously missing: How do you connect your four scanners to your computer? Are they in fact all connected to the same PC? Are they connected to an RS232 port or to a USB port? Or are you, in fact, using the same scanner to to scan 4 different barcodes?
Variatus (rep: 3063) Oct 3, '19 at 1:39 pm
Add to Discussion

Answers

0

Here is some code that will check the Barcode being added against those already added. Currently it just raise a message box.

You will need to tweek this code to perform your current business function when this situation occurs

This requires that the Barcode be entered completely in one hit (Paste or scan) as the code runs on the entry of a single character if you type it in

Private Sub txtID_Barcode_Change()
'the following line is just for debug to check that the barcode has been entered
MsgBox "New Data = " & txtID_Barcode


Dim NewBarcode
Dim LastRow As Integer
Dim SearchRange As Range
Dim c

NewBarcode = txtID_Barcode.Value

'check to see if this barcode has been used
' do a ind on the previously loaded barcodes
'get last row in serch column
'LastRow = Range("E1").End(xlDown).Row
LastRow = Worksheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell).Row

Set SearchRange = Worksheets("Sheet1").Range(Cells(1, "B"), Cells(LastRow, "B"))

    With SearchRange
        Set c = .Find(What:=NewBarcode, LookIn:=xlValues, _
                LookAt:=VBA.Trim(xlWhole), SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    End With

    If Not c Is Nothing Then  'has been found
      MsgBox NewBarcode & " Found"
    End If


End Sub
Discuss

Discussion

Compile Error
Variable Not Defined
Contt4 Sep 16, '19 at 4:53 pm
Try that.
I had just made up a userform which did not have all the fields names matching those in your userform.
The as I posted it I changed the name without changing all the other references.
As I do not have your excel or userform then this is my best best guess. Feel free to fix any other errors.
k1w1sm (rep: 177) Sep 16, '19 at 8:24 pm
Add to Discussion
0

Thank you so much for your help and patience   k1w1sm
I'm an Einstein :) .


Option Explicit

Private Sub UserForm_Initialize()

Application.ScreenUpdating = False

Dim lw As Long

lw = database.Cells(Rows.Count, 1).End(xlUp).Row + 1
Me.cmbSearch.RowSource = "database!C6:C" & lw

End Sub


Private Sub cmbNew_ID_Click()

'when we click the New ID button

Dim TargetRow As Integer
TargetRow = Sheets("Engine").Range("C3").Value + 1
    'sheets("Engine").range("C3")=COUNTA(DataBase!C5:C1975)-1
    

 Dim lrow As Long
 lrow = Application.WorksheetFunction.CountA(Sheet3.Range("C:C")) + 5
 
 Sheets("DataBase").Range("Data_Start").Offset(TargetRow, 0).Value = txtID_Barcode
 Sheets("DataBase").Range("Data_Start").Offset(TargetRow, 3).Value = Me.cmbTo
 Sheets("DataBase").Range("Data_Start").Offset(TargetRow, 4).Value = txtNr_In_Counter
 Sheets("DataBase").Range("Data_Start").Offset(TargetRow, 5).Value = "Quality"
 Sheets("DataBase").Range("Data_Start").Offset(TargetRow, 7).Value = txtQuality
 Sheets("DataBase").Range("Data_Start").Offset(TargetRow, 8).Value = txtComment
 Sheets("DataBase").Range("Data_Start").Offset(TargetRow, 10).Value = "Quality"
     
 Sheet3.Range("C" & lrow).Value = Me.txtID_Barcode.Value
 Sheet3.Range("C" & lrow).Offset(0, 3).Value = Me.cmbTo
 Sheet3.Range("C" & lrow).Offset(0, 4).Value = Me.txtNr_In_Counter.Value
 Sheet3.Range("C" & lrow).Offset(0, 5).Value = "Quality"
 Sheet3.Range("C" & lrow).Offset(0, 7).Value = Me.txtQuality.Value
 Sheet3.Range("C" & lrow).Offset(0, 8).Value = Me.txtComment.Value
 Sheet3.Range("C" & lrow).Offset(0, 10).Value = "Quality"
 
Call cmdReset_Click
End Sub


Private Sub cmbSearch_change()

Me.cmbNew_ID.Visible = False
Dim i As Integer
 For i = 6 To 700
If cmbSearch.Value = "<>" Then
Me.cmbNew_ID.Visible = False
End If
Dim findvalue As Range
   
   Set findvalue = database.Range("C:C").Find(what:=cmbSearch.Value, LookIn:=xlFormulas, lookat:=xlWhole)

Next
Call cmdSearch_Click

End Sub

Private Sub cmdSearch_Click()

 Dim findvalue As Range
   
   Set findvalue = database.Range("C:C").Find(what:=cmbSearch.Value, LookIn:=xlFormulas, lookat:=xlWhole)
 

 Me.txtID_Barcode.Value = findvalue.Value
 Me.cmbExit_From.Value = findvalue.Offset(0, 2).Value
 Me.cmbTo.Value = findvalue.Offset(0, 3).Value
 Me.txtNr_In_Counter.Value = findvalue.Offset(0, 4).Value
 Me.cmbEnter_From.Value = findvalue.Offset(0, 5).Value
 Me.txtQuality.Value = findvalue.Offset(0, 7).Value
 Me.txtComment.Value = findvalue.Offset(0, 8).Value
  Me.txtLocation.Value = findvalue.Offset(0, 10).Value
  
  End Sub


Private Sub cmdSave_Click()
 Dim lrow As Long
lrow = Application.WorksheetFunction.CountA(Sheet3.Range("C:C")) + 5
    
 Sheet3.Range("C" & lrow).Value = Me.txtID_Barcode.Value
 Sheet3.Range("C" & lrow).Offset(0, 2).Value = Me.cmbEnter_From.Value
 Sheet3.Range("C" & lrow).Offset(0, 3).Value = Me.cmbTo.Value
 Sheet3.Range("C" & lrow).Offset(0, 4).Value = Me.txtNr_In_Counter.Value
 Sheet3.Range("C" & lrow).Offset(0, 5).Value = Me.cmbExit_From.Value
 Sheet3.Range("C" & lrow).Offset(0, 6).Value = Me.txtQuality.Value
 Sheet3.Range("C" & lrow).Offset(0, 8).Value = Me.txtComment.Value
 Sheet3.Range("C" & lrow).Offset(0, 10).Value = Me.cmbExit_From.Value
 
 If Me.cmbExit_From.Value = "" Then
 Sheet3.Range("C" & lrow).Offset(0, 10).Value = Me.cmbTo.Value
 
 
 Call sch_Click
  End Sub
  
  
 Private Sub sch_Click()
 Dim findvalue As Range
   
   Set findvalue = database.Range("C:C").Find(what:=cmbSearch.Value, LookIn:=xlFormulas, lookat:=xlWhole)
   
 findvalue.Offset(0, 2).Value = Me.cmbEnter_From
 findvalue.Offset(0, 3).Value = Me.cmbTo.Value
 findvalue.Offset(0, 4).Value = Me.txtNr_In_Counter.Value
 findvalue.Offset(0, 5).Value = Me.cmbExit_From.Value
 findvalue.Offset(0, 7).Value = Me.txtQuality.Value
 findvalue.Offset(0, 8).Value = Me.txtComment.Value
 findvalue.Offset(0, 10).Value = Me.cmbExit_From.Value
 
 If findvalue.Offset(0, 5).Value = "" Then
 findvalue.Offset(0, 10).Value = Me.cmbTo.Value
 
 End If
 
Call cmdReset_Click

End Sub

Private Sub cmdReset_Click()
Me.cmbSearch = ""
Me.txtID_Barcode = ""
Me.cmbEnter_From = ""
Me.txtNr_In_Counter = ""
Me.cmbExit_From = ""
Me.cmbTo = ""
Me.txtQuality = ""
Me.txtLocation = ""
Me.txtComment = ""

 Application.ScreenUpdating = True

End Sub



'In Sheets(databese)
'put the Code For the real time Scaned objects


               'Private Sub Worksheet_Change(ByVal Target As Range)
                'Application.ScreenUpdating = False

                'Dim i As Integer
                'Dim j As Integer
 
            'For i = 6 To 30000

                'If Cells(i, "C").Value <> "" And Cells(i, "D").Value = "" Then
                ' Cells(i, "D").Value = Date & " " & Time
                ' Cells(i, "D").NumberFormat = "mm/dd/yyyy hh:mm"
                '  End If
                ' Next
Discuss

Discussion

k1w1sm
how does it look. making assumptions i only have 3 weeks of using excel.
Contt4 Oct 2, '19 at 2:26 pm
Hi Contt4
It is very hard to comment on your code. It has features I have never used but I have only been working with macros for for 45 years so still got a way to go (Started with lotus 123 incase someone knows excel wasn't about then)
It would appear that it is using a form which is a very advanced feature of excel macros. Not the easist place to start. To be able to comment I would need to see the spreedsheet that it is in and have some understanding of the business objective you are trying to achieve
k1w1sm (rep: 177) Oct 2, '19 at 8:52 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login