VBA Solution - POP UP/DATA VALIDATION

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
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>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
BUMP - sorry just need this part of the solution and then I'm done

Thanks
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,374
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Hi
How are you going to determine what activates the different comments.
Yes means yes, so what will trigger the different pop ups.
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,374
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,374
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Ok, so if you put your Lookup table in G1:H20 and then in C1 and copy down
Code:
=IF(B1="","",VLOOKUP(A1,G1:H20,2,FALSE))
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
Ok, so if you put your Lookup table in G1:H20 and then in C1 and copy down
Code:
=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

Well-known Member
Joined
Oct 31, 2008
Messages
2,672
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,133,163
Messages
5,657,176
Members
418,364
Latest member
Somebodycomegeetur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top