Alert when entering data in the workbook

[monkeyharris]

Hi all,

I have a problem, i want a message to flash up when i enter specific data anywhere in a workbook (regardless of the sheet)

IE. If i enter ME1 1XY in a cell i may want this to say: "Delivery probems, check list". The purpose is to alert delivery drivers if we have had problems delivering to a certain postcode. I can create a table of the issues in one worksheet and use the same column housing the keyword.

Please help

 

[My Aswer Is This]

You said:

I can create a table of the issues in one worksheet and use the same column housing the keyword.

Do you mean on sheet "Master" you will enter values that will cause this alert. And in say Column A and the message you want to popup if that value is entered in any cell in any sheet.

So in Range("A"1) of sheet named Master you will have

​

Hello and in in range ("B1") of sheet named Master you will have Go Home

So if any place on any sheet the value Hello is entered you want a Message Box to Popup saying Go Home


And these values like Hello will be entered Manually correct?

Not as a result of a formula or being loaded in from some outside source

 

[smozgur]

What I sent here is a very resource intensive solution as it will check every single cell content change in any worksheet. So I am not sure if I like it. If the entry cell was a specific cell then perhaps Data Validation might be used. However, this is how it could be done by using VBA.

Create two columns data range in a separate worksheet.
First column has the keywords, second column has the keyword specific messages - I thought like there might be different messages for different keywords.

Name this two columns range as "keywords".

Then go to VBE, double click on ThisWorkbook Excel Object in the Project Explorer, and copy and paste the following code into the module opened in the right pane.

This is supposed to catch all changes in all worksheets in the workbook, except the worksheet that has the "keywords" range, and search for the entered value in the keywords. If it finds the keywords, then it shows a message with the keyword and the corresponding message.

Hope it I understood what you need correctly, and this helps.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng As Range
Dim fnd As Range

    'Set the keyword range
    'Make sure you have first column with keywords,
    'and second column with desired message corresponding to each keyword
    Set rng = Range("keywords")
    
    'Make sure that this is not the sheet
    'that we store the keywords
    'You can use "Is" to compare objects
    If Sh Is rng.Worksheet Then Exit Sub
    
    'Use "Find" method to search in the first column of keyword range
    Set fnd = rng.Columns(1).Find(Target.Value, LookIn:=xlValues)
    If Not fnd Is Nothing Then
        MsgBox fnd.Cells(, 1).Value & " - " & fnd.Cells(, 2).Value, vbOKOnly + vbExclamation
        'Following line can be enabled if it is necessary to remove the entered value
        'Target.Clear
    End If
End Sub

 

[monkeyharris]

Hi Smozgur,
Many thanks for the solution which almost works. The problem is if one of my keywords in the first column is ME12 4JY, when i type ME12 and press enter it gives me the comment in column two. Is there a way to match the exact word or cell contents (IE, ME12 4JY, not ME12).

 

[smozgur]

Sure, there is a way. In fact, I am not sure why I didn't code it like that in the first place.

Find method has another parameter called LookAt. Simply set it to be xlWhole, then it will look for the whole cell value.

Change the related line as shown below.

...
Set fnd = rng.Columns(1).Find(Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
...