Pop Up Message

A

AK

Based on the entry in a cell in a column (B), how would a message box appear
based on the entry.

Example:
If B2 = "NY", message box would appear, "Talk to John"
or if B5000 = "NY" same message would appear

Message box should appear for "NY" in the column and not a specific cell.

Thank you in advance
 
E

Earl Kiosterud

AK,

An easy way is to put this in a cell:

=IF(OR(B2:B65536="NY"),"Talk to Bob", "")

Enter as an array formula (Use Ctrl - Shift - Enter, not just Enter).

It's best put at the top of the worksheet in the freeze pane area, if you
have one, so it doesn't get scrolled off the screen.
 
A

AK

Thanks Earl

However I need to know the VBA code for the MsgBox... The "helper" column
equation is not the best way in this instance to handle this. Do you know
the code to have a Message Box appear based on the "NY" in any cell in a
particular column?

Thanks,
 
E

Earl Kiosterud

Jim,

The OR is necessary to reduce the results of looking at B2:B65536="NY" to a
single TRUE (at least one was TRUE), or FALSE (all were FALSE). If we
wrote:

=IF(B2:B65536 = "NY", ...

it would want to give us an array of answers, for each of B2 through B65536.
Array formulas can give an array for a result. In this case, we'd see only
the results of the first one (B2). By reducing them all to one TRUE or
FALSE with the OR, we don't get an array for an answer -- we get one TRUE or
FALSE.

That's my story but I might not stick to it.
 
E

Earl Kiosterud

AK,

If a message box is to be posted, when should that happen? On the first
occurence of NY in the column? Or can more than one NY appear, and as long
as there's one there, "Talk to John" should show? You need to describe the
conditions, and when they happen, or we'll be guessing at stuff.
 
J

Jim May

Earl,
Thanks, - this is ground-breaking stuff. Your use of the Or() function
reminds me of the things we usually see Harland Grove come up with.
Appreciate your response and explanation.
Jim May
 
A

AK

Earl,

The message box should appear anytime "NY" is typed into any cell in a
particular column.

Hope that helps and thanks for the help

AK
 
E

Earl Kiosterud

Jim,

I forgot to mention: As I said, without the OR, the array formula wants to
give us an array for a result. The way you'd have to get that array result
is to have entered the formula into multiple contiguous cells, then do the
Ctrl - Shift - Enter. Array in -- array out. When you use something like
SUM, or OR, or anything that takes the array and produces one result, you
need only one array formula. Array in -- one value out.
 
E

Earl Kiosterud

AK,

This is an event-fired sub. PUt it in the Sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Const NYColumn = 6
If Target.Column = NYColumn And Target.Value = "NY" Then
MsgBox "Talk to John"
End If
End Sub

Change NYColumn to match the actual column you want it to monitor.
 
Top