VBA Solution - POP UP/DATA VALIDATION

[shemayisroel]

Hi,

Sample data is below

What I'm trying to do is when a name is selected from a drop list (referring to sheet 1 in column A below) in sheet 2 say range A2:A20 if in column B of the below table has a YES in it then display a POP UP message with the contents in column C below...

If in column B has NO then do nothing...

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">NAMES</TD><TD style="FONT-WEIGHT: bold">POP UP NOTE?</TD><TD style="FONT-WEIGHT: bold">POP UP NOTE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>John</TD><TD>YES</TD><TD>Go Home</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Peter</TD><TD>NO</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Rachael</TD><TD>YES</TD><TD>On a Break</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Noah</TD><TD>YES</TD><TD>Try again later</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Sarah</TD><TD>YES</TD><TD>Almost there</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Isaac</TD><TD>NO</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Joshua</TD><TD>NO</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Gabriella</TD><TD>NO</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>Jacoob</TD><TD>YES</TD><TD>Got It</TD></TR></TBODY></TABLE>

 

[shemayisroel]

BUMP - sorry just need this part of the solution and then I'm done

Thanks

 

[Michael M]

Hi
How are you going to determine what activates the different comments.
Yes means yes, so what will trigger the different pop ups.

 

[shemayisroel]

Hi
How are you going to determine what activates the different comments.
Yes means yes, so what will trigger the different pop ups.

It's driven by the name in Column A

So a user will select a name in Sheet 2 in Column A then the POP UP will appear if the name has a YES next to it...

Does that make sense?

 

[Michael M]

So, it needs a VLOOKUP to get the comment and then place it in VBA ??

Geez, that sounded simple, I'm guessing it's not gonna be.

 

[shemayisroel]

So, it needs a VLOOKUP to get the comment and then place it in VBA ??

Geez, that sounded simple, I'm guessing it's not gonna be.

That's correct, exactly what I wanted but it's a VLOOKUP only if Column B = Yes.

Doable?

 

[Michael M]

Ok, so if you put your Lookup table in G1:H20 and then in C1 and copy down

=IF(B1="","",VLOOKUP(A1,G1:H20,2,FALSE))

 

[shemayisroel]

Ok, so if you put your Lookup table in G1:H20 and then in C1 and copy down

=IF(B1="","",VLOOKUP(A1,G1:H20,2,FALSE))

Thanks though I was not looking for a LOOKUP solution, the solution I was looking for was have the LOOKUP result in a POP UP window like when you use DATA VALIDATION it shows a warning message...

I'm pretty sure this will need to be a VBA solution not formula.

 

[Sandeep Warrier]

Probably something like this???

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()<br>    <SPAN style="color:#00007F">Dim</SPAN> TableRng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> LookupVal <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>        LR = .Range("A" & .Rows.Count).End(xlUp).Row<br>        <SPAN style="color:#00007F">Set</SPAN> TableRng = .Range("A1:C" & LR)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> LookupVal <SPAN style="color:#00007F">In</SPAN> Sheets("Sheet2").Range("A1:A20")<br>        <SPAN style="color:#00007F">With</SPAN> WorksheetFunction<br>            <SPAN style="color:#00007F">If</SPAN> LCase(.VLookup(LookupVal.Value, TableRng, 2, 0)) = "yes" <SPAN style="color:#00007F">Then</SPAN><br>                MsgBox .VLookup(LookupVal.Value, TableRng, 3, 0)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> LookupVal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[shemayisroel]

Thanks Sandeep, where would I paste this code?