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>

 

[Michael M]

Hmm, I knew I shouldn't have gone to lunch.....

Sub coment()
Dim r As Long, lrow As Long
lrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For r = lrow To 1 Step -1
    If Range("B" & r).Value = "yes" Then
        MsgBox Application.VLookup(Range("A" & r), Range("G1:H20"), 2, False)
    End If
Next r
End Sub

 

[Sandeep Warrier]

Do you want the pop-up to appear every time a value is selected from the drop down? Or do you want the code to run through all values in A1:A20 and display all pop-ups... The code I posted is for this scenario. If you want the first scenario it will need to be tweaked slightly...

 

[shemayisroel]

Do you want the pop-up to appear every time a value is selected from the drop down?

This is what I'm looking for.

 

[shemayisroel]

Hmm, I knew I shouldn't have gone to lunch.....

Sub coment()
Dim r As Long, lrow As Long
lrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For r = lrow To 1 Step -1
    If Range("B" & r).Value = "yes" Then
        MsgBox Application.VLookup(Range("A" & r), Range("G1:H20"), 2, False)
    End If
Next r
End Sub

Thanks Michael, where do I place this code and what's it supposed to do?

 

[Sandeep Warrier]

In the worksheet specific module for sheet 2

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> LookupVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> TableRng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Range("A1:A20"), Target) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        LookupVal = Target.Value<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">With</SPAN> WorksheetFunction<br>            <SPAN style="color:#00007F">If</SPAN> LCase(.VLookup(LookupVal, TableRng, 2, 0)) = "yes" <SPAN style="color:#00007F">Then</SPAN><br>                MsgBox .VLookup(LookupVal, 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">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[shemayisroel]

In the worksheet specific module for sheet 2

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LookupVal As String, LR As Long
Dim TableRng As Range
If Not Intersect(Range("A1:A20"), Target) Is Nothing Then
LookupVal = Target.Value
With Sheets("Sheet1")
LR = .Range("A" & .Rows.Count).End(xlUp).Row
Set TableRng = .Range("A1:C" & LR)
End With
With WorksheetFunction
If LCase(.VLookup(LookupVal, TableRng, 2, 0)) = "yes" Then
MsgBox .VLookup(LookupVal, TableRng, 3, 0)
End If
End With
End If
End Sub

Thank you very very very much, exactly what I was looking for. Perfect with a capital P

 

[Sandeep Warrier]

You're welcome! I'm glad it worked

 

[shemayisroel]

Sandeep, the solution works however when you enter a value then you delete a value an error message appears that says

"Runtime error 1004"

"Unable to get the Vlookup property of the worksheet function class"

Is there a way around this?

 

[Sandeep Warrier]

After the line

LookupVal = Target.Value

try inserting another line

If LookupVal = "" Then Exit Sub

 

[shemayisroel]

After the line

LookupVal = Target.Value

try inserting another line

If LookupVal = "" Then Exit Sub

Yes that worked. Thanks again.