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!

Thanks for your help Jake. I copied and pasted the entire
thread hoping to eliminate confusion, but apparently
caused more by doing so. Sorry :-(
-----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!

.

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!

.

No problem, Randy. One of the main reasons to keep it in the same thread is
that many newsreaders will allow you to check if there are any replies to
your posts. So when I checked to see if there were any replies to my post
in the original thread, I didn't see any. It was just luck that I happened
upon your second post. Another reason is that users that have your same
question in the future may not find the answer as easily if you start a new
thread.

Glad we were able to solve your problem!

--
Regards,

Jake Marx
www.longhead.com


Randy wrote:
Thanks for your help Jake. I copied and pasted the entire
thread hoping to eliminate confusion, but apparently
caused more by doing so. Sorry :-(
-----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!

.

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!

.

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!

.

.

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!

.

.

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!

.

.

.