Displaying MsgBox Dependent on Column Value

maize

New Member
Joined
Apr 11, 2011
Messages
12
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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,513
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 11, 2011
Messages
12
Thank you!
A button I created on the spreadsheet will trigger the event.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this in a regular module

Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,513
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,513
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 11, 2011
Messages
12

ADVERTISEMENT

Try this in a regular module

Code:
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!
 
Last edited:

maize

New Member
Joined
Apr 11, 2011
Messages
12
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,513
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Apr 11, 2011
Messages
12
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. :confused:

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

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,173
Members
409,854
Latest member
rickcoba
Top