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

 

[ZVI]

Hi,

Looks like scanner uses DDE server application.
Have no any barcode scanners, therefore it is just an idea:

1. Put this code into standard module (VBE-Insert-Module):

' Code in standard module
Option Explicit

Public Cell As Range

Function MyTrigger(rng As Range) As String
  If ActiveCell.Column = 1 Then
    Set Cell = ActiveCell
  End If
End Function

2. The below code goes to the sheet's code module (Right Click on sheet's tab - View Code)

' Code in the sheet module
Option Explicit

Private Sub Worksheet_Calculate()
  If Cell Is Nothing Then Exit Sub
  If Cell.Column = 1 Then
    If IsNumeric(Cell.Value) Then
      Application.EnableEvents = False
      Cell.Offset(, 6).Value = Cell.Offset(, 5).Value
      If Not IsDate(Cell.Offset(, 1)) Then
        Cell.Offset(, 1).Value = Now
      End If
      Application.EnableEvents = True
    End If
  End If
  Set Cell = Nothing
End Sub

3. Put this formula into any empty cell of the sheet:
=MyTrigger(A:A)

 

[Krister]

@ 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

Hello I will get this message in the picture i added when i write manually. and nothing from the macro when i scna with barcode.. i can upload and send the excel sheet if someone want to try to figure out this? i am using scan it to office on my phone, and on the sheet.

 

[ZVI]

I will get this message in the picture i added when i write manually. and nothing from the macro when i scna with barcode..

This is typical behavior for DDE Server application usage. Try the code shown in the post #11

 

[NoSparks]

I don't have any barcode scanner either.
The scanner is not triggering the change event.
Have seen other threads with this issue and its been rectified by settings of the scanner to include something like Enter or Return along with the scan.
Don't know if that applies to your scanner or not.
Maybe ZVI's suggestion is what you need.

 

[Logit]

Would implementing a command for ENTER into the macro suffice ?

SENDKEYS ?

 

[Krister]

This is typical behavior for DDE Server application usage. Try the code shown in the post #11

i tryed this now, but it still does not work the macro did not post anything now :/

 

[Krister]

Would implementing a command for ENTER into the macro suffice ?

SENDKEYS ?

how will i write that?

 

[ZVI]

i tryed this now, but it still does not work the macro did not post anything now :/

In your picture the active cell is in B-column.
But the code expects that scanner types numeric value into cells of A-column.
Does scanner change an active cell at all?

The debugging is required.
Set Breakpoint (via F9 or VBE - Debug - Toggle Breakpoint) on the code line with Function MyTrigger() to see if that code is triggered.

 

[Krister]

In your picture the active cell is in B-column.
But the code expects that scanner types numeric value into cells of A-column.
Does scanner change an active cell at all?

The debugging is required.
Set Breakpoint (via F9 or VBE - Debug - Toggle Breakpoint) on the code line with Function MyTrigger() to see if that code is triggered.

to be honest, i did not think i was going to end up with any macro`s in my sheet(Atlest not a problem that i cannot understand). cause i only know formulas.. i have used shamefull amount of time for the little macro i got now. and i only used excel for around 4 month.. first excel experience i had was make a storage program.. and that`s working .. but now i`m at copy pasting when i`m at this point.. so, i would love to get an explaining of why that will work, and where to put it. so i can learn it right away. or a copy past ( then i can look at it for some days and then understand why it`s working) ! the reason i`m making this is because i made the storage Excell sheet for the company.. and they wanted me to make a error logging system, and a tv screen with everything from the production.. and i`ve made everything working.. exept this thing..

 

[ZVI]

Well, the problem can be in saving that workbook at Application.Calculation = xlCalculationManual.
Turn manually the calculation to automatic via File - Options - Formulas - Workbook calculation - Automatic
Then save that workbook and try the code.
Or add that code to standard module of that workbook, save and reload it:

Sub Auto_Open()
  ' This code runs automatically at this workbook loading
  Application.Calculation = xlCalculationAutomatic
End Sub

You may also just run that sub mannually without reloading.