Displaying MsgBox Dependent on Column Value

[maize]

I'm needing to be able to display a message box dependent on a column-value. I have a 'verification' column that contains true/false values and a corresponding 'name' column. I want to be able to display a message box notifying the user of the names that have FALSE verification.

Example setup:

NAME VERIFIED
Steve TRUE
George FALSE
Bob FALSE

A message box is needed to let the user know that George and Bob need verification.

I assume a FindAll method could be used, but need further direction on how to accomplish my goal with VBA.

 

[Joe4]

Welcome to the Board!

What is the "triggering" event that you want to cause this message box to be displayed (in other words, when should it run or be displayed? upon opening the file, or some other event happening?).

 

[maize]

Thank you!
A button I created on the spreadsheet will trigger the event.

 

[VoG]

Try this in a regular module

Sub test()
Dim LR As Long, i As Long, msg As String
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If Not Range("B" & i).Value Then msg = msg & vbNewLine & Range("A" & i).Value
Next i
If msg <> "" Then MsgBox msg, vbInformation, "Need confirmation"
End Sub

 

[Joe4]

Here is what I came up with. Note that if you entry is literal text and not boolean, you will need to enclose FALSE in quotes:

Sub MyVerify()
 
    Dim i As Long
    Dim myCol As String
    Dim myLastRow As Long
    
'   Indicate which column to search
    myCol = "B"
    
'   Find last row with data
    myLastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through all rows, starting in row 2
    For i = 2 To myLastRow
'   If entry is false, return entry from previous column
        If Cells(i, myCol) = FALSE Then
            MsgBox Cells(i, myCol).Offset(0, -1) & " needs verification", vbOKOnly
        End If
    Next i
 
End Sub

 

[Joe4]

I see Peter responded too. Note that his code will list all the names in one Message Box, where mine has a separate one for each person.
Keep that in mind in case one way works better for you.

 

[maize]

Try this in a regular module

Sub test()
Dim LR As Long, i As Long, msg As String
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If Not Range("B" & i).Value Then msg = msg & vbNewLine & Range("A" & i).Value
Next i
If msg <> "" Then MsgBox msg, vbInformation, "Need confirmation"
End Sub

I tried this and it works great, but something I failed to mention in the original post is that there are multiple rows belonging to the same person and also blank rows in between each person.

In other words, it can look like this:

NAME VERIFIED
Bob FALSE
Bob FALSE
George TRUE
Steve TRUE
Bill FALSE

Is there way to, in a sense, select the distinct value rather than having Bob show up twice along with the BLANK in the MsgBox? Other than that, it works as I had hoped. Thanks!

 

[maize]

I see Peter responded too. Note that his code will list all the names in one Message Box, where mine has a separate one for each person.
Keep that in mind in case one way works better for you.

Yours works as well Joe, but of course am still having similar problems to that of the code Peter wrote.

It's my fault. I should have mentioned that more than 1 row can belong to the same person/name and that there were blank rows separating each distinct name.

The MsgBox keeps catching the blanks and duplicating the name "warnings".

 

[Joe4]

Since you liked Peter's code better, I updated that one. As long as your list is sorted so that all your duplicate names appear in succession, this updated code should take care of both blanks and consecutive duplicates.

Sub test()
 
    Dim LR As Long, i As Long, msg As String, PrevName As String
    
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        If (Not Range("B" & i).Value) And (Len(Range("B" & i).Value) > 0) Then
            If Range("A" & i).Value <> PrevName Then msg = msg & vbNewLine & Range("A" & i).Value
            PrevName = Range("A" & i).Value
        End If
    Next i
    If msg <> "" Then MsgBox msg, vbInformation, "Need confirmation"
    
End Sub

 

[maize]

Thanks Joe!
Both worked great. I actually really liked your original as well and concatenated it with another column in my spreadsheet that provides the user with some benefit from the duplicated return results. Now I don't know which I like better.

Oh well...that's a good problem to have. Thanks to both of you for helping me out with this.