What i have are cells, that when you type say "AAA" into the cell, it
will do a worksheet change event, and add "AAA" as a name to my
worksheet.

What I'd like to do is, if the user types in a name into one of these
cells, and the 'name' already exists as a name, it will return a pop up
box error telling them to choose a different name and 'End' my name
naming 'Sub'

Cheers.
Kevin.

Dim nm as Name
On Error Resume Next
set nm = thisworkbooks.names(target.value)
On Error goto 0
if not nm is nothing then
msgbox "Name " & target.Value & " already exists"
exit sub
end if

--
Regards,
Tom Ogilvy

"Kevin O'Neill" wrote in message
ups.com...
What i have are cells, that when you type say "AAA" into the cell, it
will do a worksheet change event, and add "AAA" as a name to my
worksheet.

What I'd like to do is, if the user types in a name into one of these
cells, and the 'name' already exists as a name, it will return a pop up
box error telling them to choose a different name and 'End' my name
naming 'Sub'

Cheers.
Kevin.

Hey Tom

I used this, which is what you suggested inside of a Sub. It gives a
compile error Variable not defined on the first "Target"

My plan was to insert this code in before my other code that creates
the name. To do the check prior to creating the name.

Sub checknames()

Dim nm As name
On Error Resume Next
Set nm = thisworkbooks.Names(Target.Value)
On Error GoTo 0
If Not nm Is Nothing Then
MsgBox "Name " & Target.Value & " already exists"
Exit Sub
End If

End Sub

it will do a worksheet change event,

Target is defined in a worksheet change event. Using code to check if the
name exists anywhere else would not seem consistent with the functionality
you described?

--
Regards,
Tom Ogilvy


"Kevin O'Neill" wrote in message
oups.com...
Hey Tom

I used this, which is what you suggested inside of a Sub. It gives a
compile error Variable not defined on the first "Target"

My plan was to insert this code in before my other code that creates
the name. To do the check prior to creating the name.

Sub checknames()

Dim nm As name
On Error Resume Next
Set nm = thisworkbooks.Names(Target.Value)
On Error GoTo 0
If Not nm Is Nothing Then
MsgBox "Name " & Target.Value & " already exists"
Exit Sub
End If

End Sub

Hey Tom, that would be very true. Being as new as I am to to VBA, that
didn't cross my mind (1st proj. ever...) I suppose the easiest way is
to just show you all the code. This worksheet change event is triggered
when an "add" button is hit, and creats 4 rows worth of info, 1 of the
cells in 4 rows is the 'name' to be added and checked.

Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim myrow&, myCol&
myrow = Target.Row
myCol = Target.Column

If myrow 0 And myrow < 100 Then
If myCol 0 And myCol < 100 Then
Application.EnableEvents = False

'Load Case Name Range adder.

Dim bubba As Integer, bubba2 As Integer, i As Integer
bubba = Range("Bookmark").Offset(2, 0).Row
bubba2 = Range("B" & Rows.Count).End(xlUp).Row

For i = bubba To bubba2 Step 4
Dim myValue
myValue = Cells(i, 2).Value

'start your code
Dim nm As name
On Error Resume Next
Set nm = thisworkbooks.Names(Target.Value)
On Error GoTo 0
If Not nm Is Nothing Then
MsgBox "Name " & Target.Value & " already exists"
Exit Sub
End If
'end your code

ActiveWorkbook.Names.Add name:=myValue,
RefersTo:="='Loads'!$B$" & i
Next i

Application.EnableEvents = True
End If
End If


End Sub

thisworkbooks is a typo on my part. it should be ThisWorkbook (singular)

Since the proposed name is held in MyValue, then we should use that instead
of target.

Since your code appears to have nothing to do with the cell that is being
changed other than to run if there is any change in A1 to CV100. Then it
seems like you will get many messages for every run after the first time.
Not sure the Change event is where you want this, but you know what your
doing and I don't.

Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim myrow&, myCol&
myrow = Target.Row
myCol = Target.Column

If myrow 0 And myrow < 100 Then
If myCol 0 And myCol < 100 Then
Application.EnableEvents = False

'Load Case Name Range adder.

Dim bubba As Integer, bubba2 As Integer, i As Integer
bubba = Range("Bookmark").Offset(2, 0).Row
bubba2 = Range("B" & Rows.Count).End(xlUp).Row

For i = bubba To bubba2 Step 4
Dim myValue
myValue = Cells(i, 2).Value

'start your code
Dim nm As name
On Error Resume Next
Set nm = thisworkbook.Names(MyValue)
On Error GoTo 0
If Not nm Is Nothing Then
MsgBox "Name " & MyValue & " already exists"
Exit Sub
End If
'end your code

ActiveWorkbook.Names.Add name:=myValue, _
RefersTo:="='Loads'!$B$" & i
Next i

Application.EnableEvents = True
End If
End If


End Sub

--
Regards,
Tom Ogilvy

"Kevin O'Neill" wrote in message
oups.com...
Hey Tom, that would be very true. Being as new as I am to to VBA, that
didn't cross my mind (1st proj. ever...) I suppose the easiest way is
to just show you all the code. This worksheet change event is triggered
when an "add" button is hit, and creats 4 rows worth of info, 1 of the
cells in 4 rows is the 'name' to be added and checked.

Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim myrow&, myCol&
myrow = Target.Row
myCol = Target.Column

If myrow 0 And myrow < 100 Then
If myCol 0 And myCol < 100 Then
Application.EnableEvents = False

'Load Case Name Range adder.

Dim bubba As Integer, bubba2 As Integer, i As Integer
bubba = Range("Bookmark").Offset(2, 0).Row
bubba2 = Range("B" & Rows.Count).End(xlUp).Row

For i = bubba To bubba2 Step 4
Dim myValue
myValue = Cells(i, 2).Value

'start your code
Dim nm As name
On Error Resume Next
Set nm = thisworkbooks.Names(Target.Value)
On Error GoTo 0
If Not nm Is Nothing Then
MsgBox "Name " & Target.Value & " already exists"
Exit Sub
End If
'end your code

ActiveWorkbook.Names.Add name:=myValue,
RefersTo:="='Loads'!$B$" & i
Next i

Application.EnableEvents = True
End If
End If


End Sub

Hey Tom, that would be very true. Being as new as I am to to VBA, that
didn't cross my mind (1st proj. ever...) I suppose the easiest way is
to just show you all the code. This worksheet change event is triggered
when an "add" button is hit, and creats 4 rows worth of info, 1 of the
cells in 4 rows is the 'name' to be added and checked.

As of now, I get a 'variable not defined' on "thisworkbooks" is that
too suppose to be passed?



Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim myrow&, myCol&
myrow = Target.Row
myCol = Target.Column

If myrow 0 And myrow < 100 Then
If myCol 0 And myCol < 100 Then
Application.EnableEvents = False

'Load Case Name Range adder.

Dim bubba As Integer, bubba2 As Integer, i As Integer
bubba = Range("Bookmark").Offset(2, 0).Row
bubba2 = Range("B" & Rows.Count).End(xlUp).Row

For i = bubba To bubba2 Step 4
Dim myValue
myValue = Cells(i, 2).Value

'start your code
Dim nm As name
On Error Resume Next
Set nm = thisworkbooks.Names(Target.Value)
On Error GoTo 0
If Not nm Is Nothing Then
MsgBox "Name " & Target.Value & " already exists"
Exit Sub
End If
'end your code

ActiveWorkbook.Names.Add name:=myValue,
RefersTo:="='Loads'!$B$" & i
Next i

Application.EnableEvents = True
End If
End If


End Sub