Check For valid Range names?

[jjyxk845]

Hi,

What i want to do is check the contents of a cell to make sure it can be a valid range name and not clash with a posible cel ref. Ie

Name should be longer than 2 characters so it won't clash with a cell ref "C1" or a column ref "GI"

also is it possible to check for refences like "CB4" or "IV65536" incase of larger spead sheets?

cheers in advance
J

 

[erik.van.geit]

Hi,

a function would be my best suggestion

Sub test()
Dim CheckThisOne As String
CheckThisOne = Sheets(1).Range("A1").Text
If IsValidName(CheckThisOne) Then MsgBox CheckThisOne & " is a valid name" Else MsgBox CheckThisOne & " is NOT a valid name"
End Sub

Function IsValidName(nm As String) As Boolean
'Erik Van Geit
'060920

    On Error Resume Next
    
    With ActiveWorkbook.Names
    .Add Name:=nm, RefersTo:=Range("A1")
    .Item(nm).Delete
    End With
    
    IsValidName = IIf(Err, False, True)
    
    On Error GoTo 0

End Function

kind regards,
Erik

 

[jjyxk845]

Ok thanks for that if works a treat. But I've Changed it slightly and i Cant get it to work in my add in can you point out where I've gone wrong?

function checkName()

Dim RangeName As String

RangeName = Rangeselected.Cells(1, 1).Value
   
    If Check_Cell_ref(RangeName) = False Then
    MsgBox RangeName & " is NOT a valid name"
    Exit Function
    End If

end function

Function Check_Cell_ref(nm As String) As Boolean
'Erik Van Geit 
'060920 
    On Error Resume Next
    
    With ActiveWorkbook.Names
    .Add Name:=nm, RefersTo:=Rangeselected.Cells(1, 1)
    .Item(nm).Delete
    End With
    
    IsValidName = IIf(Err, False, True)
    
    On Error GoTo 0

End Function

 

[erik.van.geit]

first part is a SUB not a function

"Rangeselected" should be a sheet !?

then add something like

Dim Rangeselected as WorkSheet
Set Rangeselected = Sheets(1)

there is no reason to alter the "refersto"part in the function
the name is deleted just after it is defined, so what sheet is active doesn't matter
wrong:
RefersTo:=Rangeselected.Cells(1, 1)
it bugs because Rangeselected is not defined within the function

to my sense "Check_Cell_ref" is not the correct description, because a name can point to other items than ranges
if you want anyway to change the function's name, change it everywhere, else it won't work

best regards,
Erik