Code stops working properly after 80 to 90th scans using barcode scanner

[killaej6]

Hello everyone....

I am in need of some advice.

I have a code ( Worksheet_Selection Change) where i scan the barcode into the application.inputbox and then the value in the application.inputbox is returned to a fixed cell column. ( Column E).

If the user clicks "Cancel" then sub exits. the code is reactivated through the command button which i have placed.

The code runs fine until around the 80th-ish, 90th-ish, entry. On these entries the return value jumps to Column A.

The command button also stops responding after the 80th -ishh, 90th-ishh entry.

On top of this, all other excel functions like ( "File", "Edit", Closing the workbook becomes unresponsive). I will then need to shutdown the workbook using Task manager and restart the workbook. The code then runs fine until another 90++ entry which the problem occurs again.

Hope my explanation of the issue is clear to all. Do let me know if need any other info.

Appreciate the support guys..

P/S : My code as shown below. and i have attached an image to visualize my problem.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Scan4 As Variant
Dim lr As Long

On Error GoTo presscancel

Sheets("Scan Here").Select
ActiveSheet.Unprotect Password:="henry"

Application.EnableEvents = False

Scan4 = Application.InputBox("Please scan the barcode")

If Scan4 = False Then

Application.EnableEvents = False

Else

Application.EnableEvents = True

lr = Workbooks("BATBarcode v1.xlsm").Sheets("Scan Here").Range("E" & Rows.Count).End(xlUp).Row + 1

Range("E" & lr).Value = Scan4

End If

Sheets("Scan Here").Select
ActiveSheet.Unprotect Password:="henry"
Range("A1:L500").Select
Selection.Locked = True
Selection.FormulaHidden = False
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Locked = False
Selection.FormulaHidden = False
Sheets("Scan Here").Select
ActiveSheet.Protect Password:="henry"

presscancel:
Exit Sub
End Sub

 

[dmt32]

Hi,
quick glance suggest you may, using Selection Change Event, be getting recursion (code keeps calling itself) and if so, may explain why your code stop working & excel becomes unresponsive.

Try placing updated code in a separate module & call it from your button & see if helps

Sub ScanBarCodes()
    Dim Scan4       As Variant
    Dim lr          As Long
    
    On Error GoTo presscancel
    
    Sheets("Scan Here").Select
    ActiveSheet.Unprotect Password:="henry"
    
    Do
    Scan4 = Application.InputBox("Please scan the barcode")
    If Scan4 = False Then Exit Do
    
        Application.EnableEvents = False
        
        lr = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row + 1
        
        ActiveSheet.Range("E" & lr).Value = Scan4
        
        With ActiveSheet.Range("A1:L500")
            .Locked = True
            .FormulaHidden = False
        With .SpecialCells(xlCellTypeBlanks)
            .Locked = False
            .FormulaHidden = False
        End With
        End With
    Loop
    
presscancel:
    ActiveSheet.Protect Password:="henry"
    Application.EnableEvents = True
    
End Sub

Dave

 

[killaej6]

Hi dmt32...thanks for the suggestion...will give it a try and come back....

 

[killaej6]

Hi dmt32...it worked like a charm...thank you again...

I did make some minor tweaks. Please see the code below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Scan4 As Variant
Dim lr As Long

Sheets("Scan Here").Select
ActiveSheet.Unprotect Password:="henry"

Do
Scan4 = Application.InputBox("Please scan the barcode")
If Scan4 = False Then Exit Do


Application.EnableEvents = False

lr = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row + 1

ActiveSheet.Range("E" & lr).Value = Scan4

With ActiveSheet.Range("A1:L500")
.Locked = True
.FormulaHidden = False
With .SpecialCells(xlCellTypeBlanks)
.Locked = False
.FormulaHidden = False
End With
End With
Loop

Application.EnableEvents = False
Sheets("Scan Here").Select
ActiveSheet.Unprotect Password:="henry"
Range("A1:L500").Select
Selection.Locked = True
Selection.FormulaHidden = False
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Locked = False
Selection.FormulaHidden = False
Sheets("Scan Here").Select
ActiveSheet.Protect Password:="henry"



End Sub

 

[killaej6]

hi dmt32......just for me to understand further, if you dont mind, could you give a brief explanation on why did my original code kept recalling itself ( recursion) and how do i (and other people reading this post) can avoid this in our future codes?

thanks again...

 

[dmt32]

Hi dmt32...it worked like a charm...thank you again...

I did make some minor tweaks. Please see the code below:

You are still using the selection change event which is not what I suggested.

Place code I posted in a standard module & call it from your command button.
Selection change event code should be deleted.

To answer your last question - You were not disabling events while making range selections in your code. Each change triggers another event of the Selection_Change to run itself over and over until it crashes.

Dave

 

[killaej6]

hi dmt32....i need to use the Selection Change because the scanning process has to be continuous.....the inputbox should appear automatically after each scan so that the user can continuously perform the scanning....sorry, looks like i missed to inform this in my original post....

In summary :

1. Scanning has to be continuous. When a batch of scanning is done, then user clicks cancel which stops the code and the Event.
2. When user clicks cancel, the Event turns to false. This is needed so that the user can enter other details into columns A to D ( Manually).
3. When user wants to scan 2nd batch, i have created a command button which the user will click to restart the code.

Using your help, i was able to continuously scan almost 250 entries without any issues vs only coming up to 80, 90 scans previously....I guess it should be safe to say that the issue is solved yes?

 

[dmt32]

if you now have something that works then yes - mark suggestion as solution

Dave