| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Pop Up Message
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 |
|
#2
|
|||
|
|||
|
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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "AK" wrote in message ... 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 |
|
#3
|
|||
|
|||
|
Neat function Earl -- what with the OR() and a single argument...
How so? "Earl Kiosterud" wrote in message ... 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "AK" wrote in message ... 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 |
|
#4
|
|||
|
|||
|
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, "Earl Kiosterud" wrote: 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "AK" wrote in message ... 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 |
|
#5
|
|||
|
|||
|
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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jim May" wrote in message news:i2CXd.57526$%U2.25620@lakeread01... Neat function Earl -- what with the OR() and a single argument... How so? "Earl Kiosterud" wrote in message ... 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "AK" wrote in message ... 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 |
|
#6
|
|||
|
|||
|
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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "AK" wrote in message ... 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, "Earl Kiosterud" wrote: 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "AK" wrote in message ... 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 |
|
#7
|
|||
|
|||
|
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 "Earl Kiosterud" wrote in message ... 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jim May" wrote in message news:i2CXd.57526$%U2.25620@lakeread01... Neat function Earl -- what with the OR() and a single argument... How so? "Earl Kiosterud" wrote in message ... 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "AK" wrote in message ... 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 |
|
#8
|
|||
|
|||
|
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 "Earl Kiosterud" wrote: 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "AK" wrote in message ... 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, "Earl Kiosterud" wrote: 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "AK" wrote in message ... 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 |
|
#9
|
|||
|
|||
|
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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jim May" wrote in message news:KOWXd.57795$%U2.20084@lakeread01... 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 "Earl Kiosterud" wrote in message ... 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jim May" wrote in message news:i2CXd.57526$%U2.25620@lakeread01... Neat function Earl -- what with the OR() and a single argument... How so? "Earl Kiosterud" wrote in message ... 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "AK" wrote in message ... 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 |
|
#10
|
|||
|
|||
|
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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "AK" wrote in message ... 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 "Earl Kiosterud" wrote: 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "AK" wrote in message ... 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, "Earl Kiosterud" wrote: 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "AK" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Error Message | Excel Worksheet Functions | |||
| when opening excel I receive a message that says file can't be fo. | Excel Discussion (Misc queries) | |||
| changing the message in an error message | Excel Worksheet Functions | |||
| Pivot Table not valid error message when formatting data 'button'. | Excel Discussion (Misc queries) | |||
| Error message opening Excel97 workbook | Excel Discussion (Misc queries) | |||
Linear Mode
