Error if 'name' already exists
 

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.

Error if 'name' already exists
 

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.

Error if 'name' already exists
 

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

Error if 'name' already exists
 

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

Error if 'name' already exists
 

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

Error if 'name' already exists
 

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

Error if 'name' already exists
 

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

Error if 'name' already exists
 

Tom thanks I got it working. I wanted it inside the worsksheet change
event, but I needed it outside of the Loop. Thanks a million.

Cheers.
Kevin.

Error if 'name' already exists
 

Lol no your right, I think I maybe don't want this in my worksheet
change event. There's a selection change event right? I think I need it
in there.

Error if 'name' already exists
 

That woud be worse. How about the Sheet activate event. It really
depends on when you need to run.

If you wanted to just react to names that would be affected by the a cell
entry, then the change event would be appropriate, but you would need to
change the macro not to process the entire sheet, but just those cells/names
that would be affected.

--
Regards,
Tom Ogilvy


"Kevin O'Neill" wrote in message
oups.com...
Lol no your right, I think I maybe don't want this in my worksheet
change event. There's a selection change event right? I think I need it
in there.