On Error doesn't capture Error 13

[Proulxs]

Hi,
I have a file with several scripts running in which users enter various code using barcodes and for the most part it work fine but the code reader sometimes write random stuff. I added a check to discard that stuff but there's an issue when it write something that excel cannot accept like =dada

In that exemple Excel will accept what's written and simply display in the cell that it doesn't know what function it is, but then my script try to load the value and error 13 will appear. I'm trying to capture this error to reset and clear the cell. But On error Goto Retry fail to stop the error from being written on screen.
Here's the first few lines of script:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Errror GoTo Retry
If ActiveWorkbook.Name = "horodateur.xlsm" Then
If Target.Offset(-1, 0).Value = "QUITEXCEL" Then

When the error pop in it'll show on screen and debug send me to the 4th line because it tried to read the value of the cell.
The questions:
1.1) Why this error isn't captured.
1.2) Is there a way to put the on error somewhere else that would trigger? I assume no since it is this script that do cause the error (this is in sheet1 object)
2) Also is there a simple way to discard mathematical error too? Like if it type +1..1 that would cause excel to ask to confirm the formula?

 

[KOKOSEK]

Error probably shows when you select cell in row 1 because you are referencing offset(-1,0) what gives error because there is no row number 0 (just guessing).
Also I recommend to add:

If Target.Count > 1 Then Exit Sub

 

[Proulxs]

Error probably shows when you select cell in row 1 because you are referencing offset(-1,0) what gives error because there is no row number 0 (just guessing).
Also I recommend to add:

If Target.Count > 1 Then Exit Sub

No. The active cell is A2 and after typing something in it you end up in A3 and the script check the cell above (A2), if aaccepted it add a new line on 2nd line and select A2. When the error happen you are in A3 and it's looking correctly at A2's value.

 

[Alex Blakenburg]

Correct the spelling of your On Errror (currently has 3 r's and should be On Error) and I think you will find the issue disappears.

 

[Proulxs]

Correct the spelling of your On Errror (currently has 3 r's and should be On Error) and I think you will find the issue disappears.

That would indeed be a solution. For other reading this thread; if you try to load .value from an cell in error you will get a vba error but if you load .formula instead you will not. So if you are working mostly with text like me this can be a solution also.

@Alex do you have an idea about question 2?

 

[Alex Blakenburg]

What do you want Excel to do when there is a formula error that it doesn't know how to fix ?

 

[Proulxs]

Scrape the cell content. None of the barcode provided have formulas it's all text based. Only missfires from the reader would cause formula errors.

 

[Alex Blakenburg]

I would need to know about your process.

• The formula error we are talking about here only arises when you are entering data.
• This triggers a worksheet change event.
• Your are using a worksheet selection change event (when you move to another cell)
• The error / dialogue box occurs "before" either of the above event procedures run.
• "Only missfires from the reader would cause formula errors."
  How is this getting into the cell ? If it always in the same columns and you are not doing arithmetic operations with the barcode "number", if you preformat the columns as "Text" it should solve the issue of any entries being mistaken for formulas and triggering a formula error.

Anyway just food for thought.