user box options if cell entry is a duplicate found in a table column

**humboldtguy** · 08-23-2009, 03:25 PM

Hi, I have a table that i use for a customer database. and the end user adds new customers to this table, what i would like is for a message box to pop up whenever cell B2 matches an entry in a column in the customers table. the table starts on row 25, and the column i would like to check for duplicates is column B. I would like the message box to give the user the message "A customer by this name already exists, Would you like to load this customers file?" If the answer is yes, then the row that the match was found on would be copied and pasted onto row 1. if the answer is no, then nothing else happens. I hope this makes sense, i am posting this sheet of my workbook for reference. hope it makes sense. Thanks for any help guys, the help i have gotten on this forum already is just incredible, thanks again.

excel help forum.xlsx

**Leith Ross** · 08-24-2009, 01:15 AM

Hello humboldtguy,

Here is macro that will search worksheet "Sheet1" starting at cell "B25" down to the last entry in column "B" for the contents of cell "B2". If the customer exists, a message box will prompt the user. Copy this macro into a standard VBA module.

Please Login or Register  to view this content.

**humboldtguy** · 08-24-2009, 01:55 AM

Hey Leith thanks for the quick response. That works exactly like i want, however i need for this macro to run automatically as soon as a match occurs...can that be done?

**trucker10** · 08-24-2009, 02:31 AM

Originally Posted by humboldtguy

Hey Leith thanks for the quick response. That works exactly like i want, however i need for this macro to run automatically as soon as a match occurs...can that be done?

Please Login or Register  to view this content.

**humboldtguy** · 08-24-2009, 02:56 AM

Trucker, thanks for the response...I dont have very much knowledge of VBA, just trying to learn....i'm not sure how to edit the code that you put up...i have been tinkering with it, trying to figure it out, but am not having any luck....can you tell me what i need to change?

**trucker10** · 08-24-2009, 03:55 AM

Replace the old code by this one , from the Belgium highway

Please Login or Register  to view this content.

**humboldtguy** · 08-24-2009, 11:25 AM

Hey Trucker, Thanks for the help, unfortunately it still isnt working for me. Not sure what I am doing wrong. Any idea's? Here is the code i ended up with....note that on my original workbook the sheet name is "Customers"

Please Login or Register  to view this content.

**Leith Ross** · 08-24-2009, 11:40 AM

Hello humboldtguy,

If you want the macro to run whenever "B2" changes then you need to use the Worksheet_Change event. Leave the macro in the VBA module and call it from the "Customer" worksheet event like this...

Customer Worksheet

Please Login or Register  to view this content.

Module Code

Please Login or Register  to view this content.

**humboldtguy** · 08-24-2009, 02:04 PM

Alright now that is perfect Leith....Thank you Leith and Trucker...you guys are awesome! I can't thank the people on this forum enough for all the help.