I don't understand.

Maybe someone else will jump in.

Hasan wrote:

On Sep 30, 6:37 am, Dave Peterson wrote:
Maybe there's a difference in the name of the sheet you're changing and what you
typed into the table in Sheet3.

I'd add:

msgbox "***" & sh.name & "***" & vblf & "***" & res & "***"

to see if I could see a difference.





Hasan wrote:

On Sep 30, 1:35 am, Dave Peterson wrote:
if lcase(sh.name) = lcase(res) then
'no message required
else
'show the message
end if

Hasan wrote:

On Sep 29, 5:20 am, Dave Peterson wrote:
Add target.clearcontents to clear the cell that had the value entered.

Hasan wrote:

On Sep 29, 2:23 am, Dave Peterson wrote:
I'm not sure if this gets incorporated into the earlier code or if it's for a
single sheet, but maybe this will get you started:

You could use something like:

Dim res as variant

res _
= application.vlookup(target.value,worksheets("Sheet 3").range("A:B"),2,false)

if iserror(res) then
msgbox "Not found on sheet3"
else
msgbox "This Number should go in " & res & "worksheet."
end if

Hasan wrote:

Coloum A data validation list in all worksheets is from sheets3 column
A. And Sheet3 has following data

Column A ColumnB
123456 Apple
456789 Orange
147894 Pineapple
159357 Orange

If the user is in appleworksheetand select value "456789"(which is a
new value in the workbook) from drop down a message box should pop up
saying "this Number should go in Orangeworksheet"

--

Dave Peterson

I have pasted this in the earlier code.

- Even though the value selected is for the correctworksheet, i am
getting the message which i should not

- Its not clearing the data after clicking "OK" on message box"

- As the Coloum A data validation list in all worksheets is from
sheets3 column A. Below code is not required

if iserror(res) then
msgbox "Not found on sheet3"

--

Dave Peterson- Hide quoted text -

- Show quoted text -

How do i aviod the message if the value selected is for the correct
sheet ?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

I have edited the code as shown below and pasted in "ThisWorkbook" but
still the same. Its poping up the message for the selected values.

Dim res as variant
res _
= application.vlookup(target.value,worksheets("Sheet 3").range("A:B"),
2,false)

if lcase(sh.name) = lcase(res) then
'no message required
else
msgbox "This Number should go in " & res & "worksheet."
Target.Clearcontents
end if

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Using below code is showing the acitive sheet name & the sheet3
columnB value

res _
= Application.VLookup(Target.Value, Worksheets("Sheet3").Range
("A:M"), 13, False)
If LCase(Sh.Name) = LCase(res) Then
'no message required
Else
MsgBox "***" & Sh.Name & "***" & vbLf & "***" & res & "***"
End If

But i want the macro to compare the columnA values(which is a data
validation drop down list) in sheets(apple, orage,pineapple) with
Sheet3 ColumnA value and if the selected value(in sheet "Orange")
shows "Apple" in Sheet3 Column B then message box "this belongs to
Apple worksheet" and clear contents(or insert the value in last row of
Apple worksheet) else if it shows "Orange" in Sheet3 Column B then no
message box

--

Dave Peterson