Data Validation - Mulitiple Tabs
 

Good day,

I was wondering if the data validation works in terms of duplicates
across multiple tabs. I.E If the user enters the Unique ID in column A
on Sheet1 but the SSN is already entered in column A on Sheet2, but I
just wanted a warning to Appear letting the user know it exists
elsewhere in the file. I assume I would have to do this in vba but not
really sure how I could start it.

Thanks

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Data Validation - Mulitiple Tabs
 

Right click on the sheet tab where the entry is being made and select view
code:

Private Sub Worksheet_Change(ByVal Target As Range)
If target.Column = 1 and Target.Row 2 then
With Worksheets("Sheet2")
res = Application.Countif(.Columns(1),Target.value)
if res 0 then
msgbox "This is a duplicate of an entry in Sheet2"
end if
End with
End if
End Sub

--
Regards,
Tom Ogilvy


"TheLeafs" wrote:


Good day,

I was wondering if the data validation works in terms of duplicates
across multiple tabs. I.E If the user enters the Unique ID in column A
on Sheet1 but the SSN is already entered in column A on Sheet2, but I
just wanted a warning to Appear letting the user know it exists
elsewhere in the file. I assume I would have to do this in vba but not
really sure how I could start it.

Thanks

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Data Validation - Mulitiple Tabs
 

Hi Tom,

Thank you for your reply, but I should have explained the entire
situation. I will have 30 Sheets/Tabs. I have to validate an Unique
Identifier when an entry is made in colomn A, including the sheet in
which it is being worked on. Do you know if this is possible?

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Data Validation - Mulitiple Tabs
 

Hi Tom,

Thank you for your reply, but I should have explained the entire
situation. I will have 30 Sheets/Tabs. I have to validate an Unique
Identifier when an entry is made in colomn A, including the sheet in
which it is being worked on. Do you know if this is possible?

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Data Validation - Mulitiple Tabs
 

Private Sub Worksheet_Change(ByVal Target As Range)
If target.Column = 1 and Target.Row 2 then
for each sh in Worksheets
res = Application.Countif(sh.Columns(1),Target.value)
if res 0 then
msgbox "This is a duplicate of an entry in " & sh.Name
exit sub
end if
Next
End if
End Sub

--

Regards,
Tom Ogilvy


"TheLeafs" wrote:


Hi Tom,

Thank you for your reply, but I should have explained the entire
situation. I will have 30 Sheets/Tabs. I have to validate an Unique
Identifier when an entry is made in colomn A, including the sheet in
which it is being worked on. Do you know if this is possible?

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Data Validation - Mulitiple Tabs
 

Thanks Tom, would I have to put this code in every sheet correct?

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Data Validation - Mulitiple Tabs
 

that would be one option. Another would be to use the workbook level
equivalent event: (found in the ThisWorkbook module)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If target.Column = 1 and Target.Row 2 then
for each sh1 in Worksheets
res = Application.Countif(sh1.Columns(1),Target.value)
if res 0 then
msgbox "This is a duplicate of an entry in " & sh1.Name
exit sub
end if
Next
End if

End Sub

--
Regards,
Tom Ogilvy


"TheLeafs" wrote:


Thanks Tom, would I have to put this code in every sheet correct?

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Data Validation - Mulitiple Tabs
 

Hi again Tom,

This code works great but the only problem I am noticing is that when
you enter say the number 131 in call A6 on the Student1 tab, it brings
up the error message, although that number hasn't been in any of other
tabs. Do you know how to get this fixed? Also, but not a big deal but
the validation does not work until you hit Row 3, do you know why that
is? Would have to change
Target.Row 2 to Target.Row = 2?

Thank you for you time
Chris


+-------------------------------------------------------------------+
|Filename: MultiSheet.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4812 |
+-------------------------------------------------------------------+

--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Data Validation - Mulitiple Tabs
 

Since we evolved this code from a much simpler premis, I never stepped back
and fully analyzed it.

But first, yes, I intentionally restricted it to row 3 or later in column 1
as an example. At the time I originally wrote it you had given no
specifications other than column A on another workbook, so that was an
example of putting in some conditions for row. Adjust to fit your
situation.

Now the problem at hand is that it checks the worksheet where the entry is
made. Naturally, it counts the entry you just made, so it raises the error.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim jj as Long, sh1 as Worksheet, res as Long
If target.Column = 1 and Target.Row = 2 then
for each sh1 in Worksheets
res = Application.Countif(sh1.Columns(1),Target.value)
if sh1.Name = sh.Name then
jj = 1
else
jj = 0
end if
if res jj then
msgbox "This is a duplicate of an entry in " & sh1.Name
exit sub
end if
Next
End if

End Sub


--
Regards,
Tom Ogilvy



"TheLeafs" wrote in
message ...

Hi again Tom,

This code works great but the only problem I am noticing is that when
you enter say the number 131 in call A6 on the Student1 tab, it brings
up the error message, although that number hasn't been in any of other
tabs. Do you know how to get this fixed? Also, but not a big deal but
the validation does not work until you hit Row 3, do you know why that
is? Would have to change
Target.Row 2 to Target.Row = 2?

Thank you for you time
Chris


+-------------------------------------------------------------------+
|Filename: MultiSheet.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4812 |
+-------------------------------------------------------------------+

--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile:
http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Data Validation - Mulitiple Tabs
 

Good day Tom,

It would be great if you could answer just have one more question i
you have time. Bascially what I was looking to do is have an share
excel file over a network that was going to be used by 22 people. Eac
person will be using there respective tab where they will be enterin
SSN's and other information. What I needed to happen is that i
PersonA entered an SSN and a few minutes later PersonB entered the sam
SSN then I wanted an message to appear warning the user of this. Bu
when I tested the file attached it only works for a single user. I.E I
I enter in the same SSN then the message appears. Do you know of a wa
or a solution to this, if you do it would be a great help.

Thank you for your tim

+-------------------------------------------------------------------
|Filename: Duplicate_Call_Test_Project.zip
|Download: http://www.excelforum.com/attachment.php?postid=4866
+-------------------------------------------------------------------

--
TheLeaf
-----------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...fo&userid=1013
View this thread: http://www.excelforum.com/showthread.php?threadid=54552

Data Validation - Mulitiple Tabs
 

Sorry about that I posted the wrong file. Attached is the correct one

+-------------------------------------------------------------------
|Filename: Duplicate_Call_Test_Project.zip
|Download: http://www.excelforum.com/attachment.php?postid=4867
+-------------------------------------------------------------------

--
TheLeaf
-----------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...fo&userid=1013
View this thread: http://www.excelforum.com/showthread.php?threadid=54552