Barcode scanner

Krister

New Member
Joined
Nov 23, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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


VBA Code:
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
 

Attachments

  • excel.png
    excel.png
    64.2 KB · Views: 46
Last edited by a moderator:

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,818
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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):
VBA Code:
' 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)
VBA 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)
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Krister

New Member
Joined
Nov 23, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
@ 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 ?
VBA Code:
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.
new error.png
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,818
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Mar 15, 2013
Messages
1,051
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Aug 31, 2016
Messages
4,016

ADVERTISEMENT

Would implementing a command for ENTER into the macro suffice ?

SENDKEYS ?
 

Krister

New Member
Joined
Nov 23, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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 :/
test 4.png
 

Krister

New Member
Joined
Nov 23, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Would implementing a command for ENTER into the macro suffice ?

SENDKEYS ?
how will i write that? :)
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,818
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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

New Member
Joined
Nov 23, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,818
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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:
VBA Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,278
Messages
5,641,281
Members
417,202
Latest member
AndyVBA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top