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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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".
 
Upvote 0
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
 
Upvote 0
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. :biggrin:
 
Upvote 0

Forum statistics

Threads
1,224,525
Messages
6,179,319
Members
452,905
Latest member
deadwings

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
Back
Top