Check For valid Range names?

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

a function would be my best suggestion
Code:
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

Board Regular
Joined
Sep 8, 2006
Messages
89
Ok thanks :biggrin: 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? :oops:

Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
first part is a SUB not a function

"Rangeselected" should be a sheet !?

then add something like
Code:
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
 

Forum statistics

Threads
1,141,588
Messages
5,707,264
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top