Automatically Check Each Worksheet For Duplicate Entry
 

Is it this line:
Windows("excel_sheet[1].xls").Activate
If yes, then you don't have a window by that name.

I'd go through the workbook collection (but you still have to spell the workbook
name correctly):

Workbooks("excel_sheet[1].xls").Activate

Those square brackets don't look legal to me.

If you didn't make a typing error, close your file.
Use windows explorer to change the name (use () instead of [])
Open your workbook
fix your code
and try again.

Hasan wrote:

On Oct 6, 2:48 am, Dave Peterson wrote:
When you change subjects, you should start a new thread.

If you used the button from the Forms toolbar placed on aworksheet, just
rightclick on the button and choose assign macro.



Hasan wrote:

On Oct 6, 1:26 am, Hasan wrote:
On Oct 3, 2:30 am, Hasan wrote:

On Oct 3, 1:42 am, Dave Peterson wrote:

checkto see what's in the cell first:

if target.value = "" then
'do nothing
else
'do everything
end if

Hasan wrote:

On Oct 2, 3:20 am, Dave Peterson wrote:
You need all those checks.

if iserror(res) then
'no message
else
if lcase(sh.name) = lcase(res) then
'do nothing
else
'do that other stuff
end if
end if

Hasan wrote:

On Oct 2, 1:28 am, Dave Peterson wrote:
Stop the events from firing your worksheet_change:

application.enableevents = false
target.value = ""
application.enableevents = true

Hasan wrote:

On Oct 1, 11:25 pm, Dave Peterson wrote:
When you deleted the line that checked for an error, you lost thatcheck.

Add thatcheckback and you'll see that you don't have a match in that table.

Hasan wrote:

On Oct 1, 5:41 am, Dave Peterson wrote:
If you're using mergedcells, then .clearcontents won't work.

try:

Target.value = ""

Hasan wrote:

On Oct 1, 3:10 am, Dave Peterson wrote:
I would have guessed that changing this line:

MsgBox "***" & Sh.Name & "***" & vbLf & "***" & res & "***"
to
MsgBox target.value & " should be on " & res

would have worked.

Hasan wrote:

On Oct 1, 1:14 am, Dave Peterson wrote:
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
Appleworksheet" and clear contents(or insert the value in last row of
Appleworksheet) else if it shows "Orange" in Sheet3 Column B then no
message box

--

Dave Peterson- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -...

read more »

Sorry for not starting the new thread.

I have assigned and recorded macro to button to refresh data. But i
get error "Subscript out of range" whenever i click the refresh
button. Below is the recorded macro.

Sub Button2_Click()

Columns("A:Q").Select
Selection.ClearContents
Windows("excel_sheet[1].xls").Activate
Range("A1:Q34000").Select
Selection.Copy
Windows("Sheet1.xls").Activate
Range("A4").Select
ActiveSheet.Paste
Range("A4").Select
Application.CutCopyMode = False
End Sub

--

Dave Peterson