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:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,977
I don't have a scanner here so cannot test this ... however, try placing your macro code into the :

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

New Member
Joined
Nov 23, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hello. this was happening when i tryed that :/





error.png
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,977
.
What about this ?

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

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

ADVERTISEMENT

.
What about this ?

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

Well-known Member
Joined
Aug 31, 2016
Messages
3,977
.

Here is my last attempt :


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

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

ADVERTISEMENT

.

Here is my last attempt :


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

Well-known Member
Joined
Aug 31, 2016
Messages
3,977
I'm sorry I couldn't get the code for you. Hopefully someone else can provide some input.

Best wishes.
 

NoSparks

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

Watch MrExcel Video

Forum statistics

Threads
1,128,099
Messages
5,628,678
Members
416,332
Latest member
blkbeltmjk

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