Alert when entering data in the workbook

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
306
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,274
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

BatCoder
Joined
Feb 28, 2002
Messages
881
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.

Code:
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
 
Last edited:

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
306
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

BatCoder
Joined
Feb 28, 2002
Messages
881
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.

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

Forum statistics

Threads
1,081,984
Messages
5,362,556
Members
400,680
Latest member
Wild Child

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top