Macro pops up when certain value is entered

**sigmund_domingo** · 11-25-2010, 02:32 AM

Hi all,

This is my first post in this forum. I was able to find answers in past discussions so I am confident I can get an answer to this tricky problem that I have regarding pop ups.

I have attached a sample of the worksheet I have been working on. It basically consists of two sheets: the first sheet is where data is entered (Database), in this case you can see that the Customer Name and Address fields are empty. The second sheet (Checklist) is automatically updated everyday by someone else. What I would like Excel to do is pop up a message when any value entered in the Customer Name and Address fields in the Database sheet is equal to the value on the Checklist sheet.

For example, when the name "Edward Adam" is entered on the Customer column, it pops up a message saying "This information has already been entered", or something to that effect.

I have limited background in Macros, but I am fairly certain that a macro is the best way to make this happen. I would appreciate any help with this from the awesome forum members!

**DonkeyOte** · 11-25-2010, 04:08 AM

Would a validation rule not suffice ? (rather than VBA)

Using your sample file

Step 1: create a Named Range re: Checklist

Name: _DVCheckList
RefersTo: =Checklist!$A:$A
(you could use a Dynamic Construct pending other requirements)

Step 2: apply a Data Validation rule to Database sheet

Highlight C2:Dn (where n is last row you wish to apply the logic to) and apply a Custom Data Validation rule of:

=1-COUNTIF(_DVChecklist,$C2)

Set Error Alert message as necessary - change Style from "Stop" to "Warning" etc if duplicates are still to be permitted.

**sigmund_domingo** · 11-25-2010, 09:38 AM

Simplicity really is the key, isn't it?

I had a feeling Validation could be enough. I used the steps you have provided and it had proved quite useful. I just had a bit of trouble with the Countif function working with Custom in the allow field so I used Between instead. Worked like a charm!

Thanks for your help DonkeyOte. You guys are amazing!

**sigmund_domingo** · 11-25-2010, 11:36 PM

Hi all,

I'm sorry I had to post again regarding the issue.

The data validation worked well with the Customer name field since those texts are entered manually. The problem begins on the Address column of the database, using the same procedure, the error alert pops up flawlessly when addresses are entered manually. Unfortunately, my colleagues informed me that they copy-paste the data entered in this column from another file and as such, the error alert does not trigger automatically. Is there any way that the validation error alert can still be trigerred even if the data provided is pasted?

Also, (and perhaps this was the reason I was looking into macros) the address may not be exactly the same as the one entered in the Address column of the Database sheet. This way, even if just the Street name or suburb matches the address entered, the alert should still pop up so that the encoder can be prompted to take a closer look.

Any thoughts?