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

killaej6

New Member
Joined
May 22, 2021
Messages
5
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
Platform
  1. Windows
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
 

Attachments

  • Mrexcel help1.PNG
    Mrexcel help1.PNG
    203 KB · Views: 15

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,833
Office Version
  1. 2019
Platform
  1. Windows
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

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

killaej6

New Member
Joined
May 22, 2021
Messages
5
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
Platform
  1. Windows
Hi dmt32...thanks for the suggestion...will give it a try and come back....
 

killaej6

New Member
Joined
May 22, 2021
Messages
5
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
Platform
  1. Windows
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

New Member
Joined
May 22, 2021
Messages
5
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,833
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
May 22, 2021
Messages
5
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,833
Office Version
  1. 2019
Platform
  1. Windows
if you now have something that works then yes - mark suggestion as solution

Dave
 

Forum statistics

Threads
1,143,687
Messages
5,720,295
Members
422,275
Latest member
Maria95

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