Ignoring Error's no longer working

[eli_m]

Hi,

I've had this code working for over 3 years now:

' Ignore Errors when Worksheet Activated
Private Sub Worksheet_Activate()

Dim r As Range: Set r = Range("A2:AQ200")
Dim cel As Range

For Each cel In r
  With cel
    .Errors(8).Ignore = True 'Data Validation Error
    .Errors(9).Ignore = True 'Inconsistent Error
    .Errors(6).Ignore = True 'Lock Error
  End With
Next cel

End Sub

But today it is no longer working.

On my PC my excel version is:

and the code no longer works.

But on my laptop I have a slightly older version of excel (Version 2302 Build 16130.20868) and it works fine.

I am guessing the update has broken something or it has changed somehow.

Is there a few way to ignore errors in a certain part of a spreadsheet?

Thanks in advance

 

[nemmi69]

On error resume next. It is best to find out what the error is though.

 

[eli_m]

On error resume next. It is best to find out what the error is though.

sorry what do you mean?

 

[eli_m]

My laptop has just updated after a few days and now the code is not working on there also.

Is there another way to ignore errors for everyone who opens my spreadsheet?

Thanks!

 

[Alex Blakenburg]

Does it work if you change them all ?

' Ignore Errors when Worksheet Activated
Private Sub Worksheet_Activate()

Dim r As Range: Set r = Range("A2:AQ200")
Dim cel As Range
Dim i As Long
Dim arrErrortypes As Variant

arrErrortypes = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)

For i = 0 To UBound(arrErrortypes) - 1
    For Each cel In r
      With cel
        .Errors(arrErrortypes(i)).Ignore = True
      End With
    Next cel
Next i

End Sub

 

[eli_m]

Does it work if you change them all ?

' Ignore Errors when Worksheet Activated
Private Sub Worksheet_Activate()

Dim r As Range: Set r = Range("A2:AQ200")
Dim cel As Range
Dim i As Long
Dim arrErrortypes As Variant

arrErrortypes = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)

For i = 0 To UBound(arrErrortypes) - 1
    For Each cel In r
      With cel
        .Errors(arrErrortypes(i)).Ignore = True
      End With
    Next cel
Next i

End Sub

I tried your code and sadly not - I am wondering if this is a update issue or if they have updated the way to ignore errors but I can't seem to find anything.

 

[Alex Blakenburg]

Sorry I don't know much more about it, the code seems to work for me.

 

[eli_m]

Hi,

Is anyone able to help with my first post?

 

[eli_m]

Silly me!
I've added a few more errors to the list and now it works:

.Errors(6).Ignore = True 'Lock Error
.Errors(7).Ignore = True 'Unprotected Forumla Error
.Errors(8).Ignore = True 'Data Validation Error
.Errors(9).Ignore = True 'Inconsistent Error
.Errors(10).Ignore = True 'Inconsistent Calculated Column Formula Error