Error handling for error 1004 only not other errors

Question

I have an Excel sheet with 3 sheets within. I do some code depending on the named range of a specific value. There are only a few cells that have a name, from the moment I edit a cell that has no name I get an error, that's why I (think I?) need an error handler:

This is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim targetName As String
    Dim iCurrentWeek As Integer
    Dim iPreviousWeek As Integer
    Dim nNamedRange As Name
    On Error GoTo noNamedrange
    'check if the changed range intersects with any named range
    For Each nNamedRange In Me.Names
        'check if the named range belongs to this worksheet
        If nNamedRange.RefersToRange.Worksheet Is Me Then
            targetName = Target.Name.Name
            'if the changed range intersects with the named range
            If Not Intersect(Target, nNamedRange.RefersToRange) Is Nothing Then
                'process the change based on the target name
                Select Case targetName
                                        (this is my specific code where another error might happen)
                End Select
                'exit the loop after processing the change
                Exit For
            End If
        End If
    Next nNamedRange
    
noNamedrange: Resume Next
    
End Sub

The code works well until there is another error somewhere else, and of course I don't get the error message, because of this error handling. Is there a way to handle ONLY the error that there might a range without any name on ? If you have an alternative way I'm also interested because I don't like using error handling things, they cover too much. Thanks for your time.

Answer 1

When an error occurs that is handled via On Error Goto xy or On Error Resume Next, you can check the error by using the global Err-object:

noNamedrange:
    If Err.Number = 1004 Then Resume Next
    MsgBox "An unexpected error occurred: " & vbCrLf & Err.Number & " " & Err.Description

However, there is a much cleaner way to solve your issue. As a rule of thumb, when you expect that a statement may fail, just enclose this single statement with an On Error Resume Next. Handle the result of the statement and Issue an On Error Goto 0 after that to switch on the regular error handling.

In your case, read the target name once at the top of the code. If there is no name set to target, jump out of the routine.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim targetName As String
    On Error Resume Next
    targetName = Target.Name.Name
    On Error GoTo 0
    If targetName = "" Then Exit Sub   ' No name set, nothing to do.