Again thanks!
I think Ive gotten way out of my league here (or way over
my head) :( The existing code take the contents of sheet1
A1(cat) and creates a new sheet named cat. After Sheet
cat is created and some one enters Cat again nothing
happens which is fine for me but I wanted a pop up that
says 'Cat already exist please re-enter'.
Steve

-----Original Message-----
Hi Steve,

I don't think you need to check any range values on this
(or use the
Worksheet_Change event routine for that matter), but
maybe I'm missing
something. You could use a function similar to this if
you want to create a
new worksheet:

Public Function bCreateNewSheet(rsName As String) _
As Boolean
Dim sDummy As String

On Error Resume Next
sDummy = Sheets(rsName).Name

If Err.Number Then
'/ sheet didn't exist; create it
Worksheets.Add After:=Worksheets( _
Worksheets.Count)
Worksheets(Worksheets.Count).Name = rsName
bCreateNewSheet = True
Else
'/ already exists
End If

On Error GoTo 0
End Function

This function will attempt to add a new worksheet with
the specified name
(and return True on success). If a sheet already exists
with that name, it
will return a value of False and no sheet will be added.

The function assumes you're working with the active
workbook - if not, you
may want to add an argument to enable passing a reference
to a specific
workbook.

--
Regards,

Jake Marx
www.longhead.com


Steve wrote:
Hey Jake thanks for the response. Exsting routine
creates
a new sheet and renames based on cell content of the
existing sheet. If the new sheets matches one that was
previously created then POPUP 'duplicate number' I
suppose
this 'validation' should occur at the beginning of the
routine.
Steve
email shebertatscanadotcom

-----Original Message-----
Hi Steve,

What specifically are you trying to accomplish? You
could modify
the If statement to simply check the value against a
named range,
and pop up a messagebox if it doesn't match:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, _
Rows(2)) Is Nothing Then
If Target.Value < Range
("MyNamedRange").Value Then
MsgBox "No match - please try again."
Target.ClearContents
Target.Select
End If
End If
End Sub

If that's not what you were looking for, please post
back with some
more specifics.

--
Regards,

Jake Marx
www.longhead.com


Steve wrote:
Can this be modified to compare the cell contents to
existing worksheet names?

Thanks in advance!
-----Original Message-----
Hi Randy,

Please try to keep followup questions in the
original thread so
people know what you're referring to.

If you want to do this via VBA code, you can put it
in the
Worksheet_Change event. Here's an example:

Private Sub Worksheet_Change(ByVal Target As
Range)
If Not Application.Intersect(Target, _
Rows(2)) Is Nothing Then
If Application.WorksheetFunction.CountIf(
_
Rows(2), "=" & Target.Value) 1 Then
MsgBox "Please enter a unique value."
Target.ClearContents
Target.Select
End If
End If
End Sub

Just right-click the tab of the worksheet you'd like
to monitor,
then select View Code. Paste this code into the
resulting code
pane.

--
Regards,

Jake Marx
www.longhead.com


Randy wrote:
From a previous post.....

When entering a cell value and a dupilicate exists
in
the same cell different column, I would like a pop
up that
says "Please Enter a Unique Value"
All help would be appreciated
Thanks!


You can do this with Data | Validation. Just
select row
2, select Data |
Validation, then select Custom and enter this
formula:

=COUNTIF($2:$2,"=" & A2)=1

Under the Error Alert tab, you can enter the error
message you'd like
displayed when users enter bad data. Unfortunately,
copying/pasting will
destroy the data validation, so this will not stop
someone from
copying/pasting data onto row 2.


...I would like to add this to an existing macro
that I am
already using
Thanks!

.

.

.