Autoprint when cell is 10 characters long Excel

Question

I'm setting up a mini program so an operator scans a barcode, it creates one in excel and prints it on a label printer.

I need it to automatically print as soon as he's scanned in the 10 digit code.

What I have so far is -

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Len(Sheet1!A2) = 10 Then
ActiveWorkbook.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Sheets("Sheet1").Range("A2").ClearContents
End If
End Sub

This doesn't appear to work though. I have this code in Sheet1. The error message I get is

Run-time error '438' Object doesn't support this property or method

It highlights the If Len bit as the issue.

Can anyone help please?

Steven Martin · Answer 1 · 2014-11-28T17:27:38.943

1

Generally when you scan a barcode with a scanner, it sends the number and also sends an "enter" symbol or carriage return so I would use that as the trigger(Worksheet_SelectionChange) instead of any change on the sheet...

but regardless the error seems you didn't reference the cell correctly

If Len(Sheet1!A2) = 10 Then

should be

if Len(Sheets("Sheet1").Range("A2").Value) = 10 Then

edited Nov 28 '14 at 17:27

answered Nov 28 '14 at 17:21

Steven Martin

3,150
1
20
27

score 0 · Answer 2 · edited May 23 '17 at 11:57

If you are going to have it trigger on a specific cell changing then use this code:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
        'Checks if A2 was the changed cell
        If Intersect(Target, Sheets("Sheet1").Range("A2")) Is Nothing Then Exit Sub
            Else
        Application.EnableEvents = False 'to prevent endless loop
            If Len(Target.Value) = 10 Then
                ActiveWorkbook.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
                Target.ClearContents
            Else
            End If
        End If
        Application.EnableEvents = True
    End Sub

Used Peter's code from here: excel VBA run macro automatically whenever a cell is changed

Autoprint when cell is 10 characters long Excel

2 Answers2