Check if a Named Range exists (global scope) and tell the user about confilcting situation

[Romulo]

I saw some information on but I need to be a bit more specific.

I have a macros that names the headers of a region of data. It error checks if the proposed header names aren't valid names. I've attached.

I want to check if the a named range exists in the ActiveWorkbook. I've tried the code below, as an if to check if the names exist in the the workbook. It doesn't quite work. I've attached an example sheet for reference as well.

Any help would be much appreciate.

Sub example()


    Dim clnames As Range
    
    
    Dim checkednames As Range
    
    
    For Each clnames In Range(ActiveCell.CurrentRegion.Cells(1, 1), ActiveCell.CurrentRegion.Cells(1, 1).End(xlToRight))
        'rCol.Value = clnames


        'I want to tell the user not to start a name with a number
        If InStr(1, clnames, "1") Then


    MsgBox clnames.Address() & " contains an invalid header name because '" & clnames & "' begins with a number. Don't begin a name with a number, include a space, use characters (&, +, etc.), or use punctuation."

        ElseIf InStr(1, clnames, "2") Then

    MsgBox clnames.Address() & " contains an invalid header name because '" & clnames & "' begins with a number. Don't begin a name with a number, include a space, use characters (&, +, etc.), or use punctuation."

    'you get the idea about the numbers thing
    
        ElseIf clnames = "" Then

            MsgBox clnames.Address() & " is a blank header. You need to make sure that your header has value. Empty cells cannot be names."

        ElseIf InStr(1, clnames, " ", vbTextCompare) Then

            MsgBox clnames.Address() & " contains an invalid header name because '" & clnames & "' contains a space. Don't begin a name with a number, include a space, use characters (&, +, etc.), or use punctuation."
        ElseIf InStr(1, clnames, ",", vbTextCompare) Then

            MsgBox clnames.Address() & " contains an invalid header name because '" & clnames & "' contains a comma. Don't begin a name with a number, include a space, use characters (&, +, etc.), or use punctuation."

        End If
    Next clnames
    'that checks if the named range had one of the identificable errors, which would prevent a name from being made, errors are skipped
    
    On Error GoTo ErrorHandler
    
    'name the header
    
    For Each checkednames In ActiveCell.CurrentRegion.Columns
       'check if the values equal existing names in the workbook
        
        With checkednames
        If .Resize(.Rows.Count - (.Rows.Count - 1)).Name = ActiveWorkbook.Name Then
        MsgBox .Resize(.Rows.Count - (.Rows.Count - 1)).Address() & " contains a Name that already exists in this WorkBook. You must provide named range a unique name."
        
        Else
        
        .Resize(.Rows.Count - 1).Offset(1).Name = "'" & .Worksheet.Name & "'!" & .Cells(1).Value
        End If
        End With
        
    Next checkednames
    
    
    ' I want to check if the values inside the headers already exists, just to let the user know
    ' The  next step is to warn the user, and to check for locally scoped and global scope,
   
    
    
ErrorHandler:

    
    Resume Next
    


End Sub

So basically, I want to get a pop that says: hey wait, that name already exists, rename it. Eventually I want to put in an input box that allows for people to rename on the spot but thats the next step.