Barcode scanner

[Krister]

Hello. i got a question about barcode scanner.. when i scan a number with the barcode scanner my macro`s arent working..? but the formula`s are working.. i cannot link the sheet i`m working on since its a invalid file.. but i will link the macro i am using, and link a picture of what`s not happening.. The red colour is when i scan with the barcode scanner.. the others is when i manually write the numbers.. the job of the marco is to post the date today (B), and copy the number from F to G

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    'disable events to stop changes made by this macro re-trigering it
    Application.EnableEvents = False
    'test if changed cell is a number
    If IsNumeric(Target.Value) Then
    Target.Offset(, 6).Value = Target.Offset(, 5).Value
        'only add a date if no date in column I
        If Not IsDate(Range("B" & Target.Row).Value) Then
            Range("b" & Target.Row).Value = Format(Date, "YYYYDDMM")
        End If
    End If
    're-enable events
    Application.EnableEvents = True
End If
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

 

[Logit]

I don't have a scanner here so cannot test this ... however, try placing your macro code into the :

Option Explicit


Private Sub Worksheet_Calculate()
If Target.Column = 1 Then
    'disable events to stop changes made by this macro re-trigering it
    Application.EnableEvents = False
    'test if changed cell is a number
        If IsNumeric(Target.Value) Then
        Target.Offset(, 6).Value = Target.Offset(, 5).Value
        'only add a date if no date in column I
            If Not IsDate(Range("B" & Target.Row).Value) Then
                Range("b" & Target.Row).Value = Format(Date, "YYYYDDMM")
            End If
        End If
    're-enable events
    Application.EnableEvents = True
    End If
Exit Sub


'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

 

[Krister]

Hello. this was happening when i tryed that :/

 

[Logit]

.
What about this ?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
    'disable events to stop changes made by this macro re-trigering it
    Application.EnableEvents = False
    'test if changed cell is a number
        If IsNumeric(Target.Value) Then
        Target.Offset(, 6).Value = Target.Offset(, 5).Value
        'only add a date if no date in column I
            If Not IsDate(Range("B" & Target.Row).Value) Then
                Range("b" & Target.Row).Value = Format(Date, "YYYYDDMM")
            End If
        End If
    're-enable events
    Application.EnableEvents = True
    End If
Exit Sub


'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

 

[Krister]

.
What about this ?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
    'disable events to stop changes made by this macro re-trigering it
    Application.EnableEvents = False
    'test if changed cell is a number
        If IsNumeric(Target.Value) Then
        Target.Offset(, 6).Value = Target.Offset(, 5).Value
        'only add a date if no date in column I
            If Not IsDate(Range("B" & Target.Row).Value) Then
                Range("b" & Target.Row).Value = Format(Date, "YYYYDDMM")
            End If
        End If
    're-enable events
    Application.EnableEvents = True
    End If
Exit Sub


'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

i`m really happy for helping me!! just to make that clear!:D but now it autoposts the Macro`s when i click on a cell in column A, but now it does not recive anything from the barcode reader :P

 

[Logit]

.

Here is my last attempt :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A:A")
    On Error GoTo ErrHnd:
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        
        'disable events to stop changes made by this macro re-trigering it
        Application.EnableEvents = False
        
        'test if changed cell is a number
        If IsNumeric(Target.Value) Then
            Target.Offset(, 6).Value = Target.Offset(, 5).Value
                'only add a date if no date in column I
                If Not IsDate(Range("B" & Target.Row).Value) Then
                    Range("b" & Target.Row).Value = Format(Date, "YYYYDDMM")
                End If
        're-enable events
        Application.EnableEvents = True
        End If
        Exit Sub
        
        'error handler
ErrHnd:
        Err.Clear
        're-enable events
        Application.EnableEvents = True
    
    
    End If
End Sub

 

[Krister]

.

Here is my last attempt :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A:A")
    On Error GoTo ErrHnd:
   
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
       
        'disable events to stop changes made by this macro re-trigering it
        Application.EnableEvents = False
       
        'test if changed cell is a number
        If IsNumeric(Target.Value) Then
            Target.Offset(, 6).Value = Target.Offset(, 5).Value
                'only add a date if no date in column I
                If Not IsDate(Range("B" & Target.Row).Value) Then
                    Range("b" & Target.Row).Value = Format(Date, "YYYYDDMM")
                End If
        're-enable events
        Application.EnableEvents = True
        End If
        Exit Sub
       
        'error handler
ErrHnd:
        Err.Clear
        're-enable events
        Application.EnableEvents = True
   
   
    End If
End Sub

Same as first time Only formula`s wrtiting, not the macro`s but thank you soo much for all)

 

[Logit]

I'm sorry I couldn't get the code for you. Hopefully someone else can provide some input.

Best wishes.

 

[Krister]

I'm sorry I couldn't get the code for you. Hopefully someone else can provide some input.

Best wishes.

Thank you soo much again!!

 

[NoSparks]

@ Krister

if you try this, do you get the message when you scan in a barcode or just when you enter a number manually or what ?

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "Change Event has been triggered"    'for test purposes only

If Target.Column = 1 Then
    'disable events to stop changes made by this macro re-trigering it
    Application.EnableEvents = False
    'test if changed cell is a number
    If IsNumeric(Target.Value) Then
    Target.Offset(, 6).Value = Target.Offset(, 5).Value
        'only add a date if no date in column I     ' ??? shouldn't this be B ???
        If Not IsDate(Range("B" & Target.Row).Value) Then
            Range("b" & Target.Row).Value = Format(Date, "YYYYDDMM")
        End If
    End If
    're-enable events
    Application.EnableEvents = True
End If
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub