VBA code to pop up multiple messages from multiple rows

R

rvvan

New Member
Joined
Jun 22, 2016
Messages
14
Hello,

So i will have data updating in mutlple rows everyday. What i want to do is when the value "5" pops up in a column (k), a message will pop up saying something like "tote #49 (the tote number is in the same row, column H) will expire in 5 days". I want to do this also for 4, 3, and 2 values. But I also need it to loop so it checks every row until nothing is in the cell. I think it would be easier to have one dialog box maybe saying something in this format

Totes Expiring in 5 days : x, x, x
Totes Expiring in 4 days : x, x, x
Totes Expiring in 3 days : x, x, x

Here is my code I have tried .....



Public Sub Msgbox_for_notification()

Range("k4").Select



For i = 1 To num

num = ActiveCell.Value



If num = "test" Then MsgBox " Tote in production area will expire in 7 days or less "

Next i

End Sub





Any feedback would be GREATLY appreciated! Thanks!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
try something like this: (untested)
sub skipthrough()
lastRow = ActiveSheet.Range("K" & Rows.Count).End(xlUp).Row ' this finds the last row in column k with anything in it
fivemsg="Totes Expiring in 5 days :"
fourmsg="Totes Expiring in 4 days :"
threemsg="Totes Expiring in 5 days :"
for i=1 to LastRow
if cells(i,11).value="5" then fivemsg=fivemsg+cells(i,8).text+"," ' adds to relevant message text
if cells(i,11).value="4" then fourmsg=fourmsg+cells(i,8).text+","
if cells(i,11).value="3" then threemsg=threemsg+cells(i,8).text+","
next i
fivemsg=Left(fivemsg, Len(fivemsg) - 1)+vbCr ' removes last comma and adds carriage return
fourmsg=Left(fourmsg, Len(fourmsg) - 1)+vbCr
threemsg=Left(threemsg, Len(threemsg) - 1)
msgbox fivemsg+fourmsg+threemsg
end sub


Regards
 
Upvote 0
rvvan said:
Hello,

So i will have data updating in mutlple rows everyday. What i want to do is when the value "5" pops up in a column (k), a message will pop up saying something like "tote #49 (the tote number is in the same row, column H) will expire in 5 days". I want to do this also for 4, 3, and 2 values. But I also need it to loop so it checks every row until nothing is in the cell. I think it would be easier to have one dialog box maybe saying something in this format

Totes Expiring in 5 days : x, x, x
Totes Expiring in 4 days : x, x, x
Totes Expiring in 3 days : x, x, x

Here is my code I have tried .....



Public Sub Msgbox_for_notification()

Range("k4").Select



For i = 1 To num

num = ActiveCell.Value



If num = "test" Then MsgBox " Tote in production area will expire in 7 days or less "

Next i

End Sub





Any feedback would be GREATLY appreciated! Thanks!
Click to expand...
Hi rvvan, welcome to the boards.

My solution is similar however a little more detailed than the one above. It checks all cells in column K and will pipe out 5 messages at the end, one for each count of days til expiration:

Code: 
Sub CheckCells()
' Defines variables
Dim Cell As Range, cRange As Range
Dim LastRow As Long
Dim Days5 As String, Days4 As String, Days3 As String, Days2 As String, Days1 As String


' Defines LastRow as the last row of data based on column K
LastRow = ActiveSheet.Cells(Rows.Count, "K").End(xlUp).Row
' Sets the check range as K1 to the last row of K
Set cRange = Range("K1:K" & LastRow)


' Defines the default states for all 5 message boxes
Days5 = "Totes expiring in 5 days: "
Days4 = "Totes expiring in 4 days: "
Days3 = "Totes expiring in 3 days: "
Days2 = "Totes expiring in 2 days: "
Days1 = "Totes expiring in 1 day: "


' For each cell in the check range
For Each Cell In cRange
    ' Select a case based on the cell value
    Select Case Cell.Value
        ' Cell value of 5
        Case 5
            ' If no totes have been added to the message box list yet then...
            If Len(Days5) = 26 Then
                ' Add the tote number from column H to the message box list
                Days5 = Days5 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days5 = Days5 & ", " & Range("H" & Cell.Row).Value
            End If
        ' Cell value of 4
        Case 4
            ' If no totes have been added to the message box list yet then...
            If Len(Days4) = 26 Then
                ' Add the tote number from column H to the message box list
                Days4 = Days4 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days4 = Days4 & ", " & Range("H" & Cell.Row).Value
            End If
        ' Cell value of 3
        Case 3
            ' If no totes have been added to the message box list yet then...
            If Len(Days3) = 26 Then
                 ' Add the tote number from column H to the message box list
                Days3 = Days3 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days3 = Days3 & ", " & Range("H" & Cell.Row).Value
            End If
        ' Cell value of 2
        Case 2
            ' If no totes have been added to the message box list yet then...
            If Len(Days2) = 26 Then
                ' Add the tote number from column H to the message box list
                Days2 = Days2 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days2 = Days2 & ", " & Range("H" & Cell.Row).Value
            End If
        ' Cell value of 1
        Case 1
            ' If no totes have been added to the message box list yet then...
            If Len(Days1) = 25 Then
                ' Add the tote number from column H to the message box list
                Days1 = Days1 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days1 = Days1 & ", " & Range("H" & Cell.Row).Value
            End If
    End Select
' Check next cell in check range
Next Cell


' If no totes have been added to the Days5 message box list then...
If Len(Days5) = 26 Then
    ' Display a Days5 message reporting "None"
    MsgBox Days5 & "None", vbOKOnly, "5 Days"
' Else if totes have been added then...
Else
    ' Display the full Days5 message
    MsgBox Days5, vbOKOnly, "5 Days"
End If


' If no totes have been added to the Days4 message box list then...
If Len(Days4) = 26 Then
    ' Display a Days4 message reporting "None"
    MsgBox Days4 & "None", vbOKOnly, "4 Days"
' Else if totes have been added then...
Else
    ' Display the full Days4 message
    MsgBox Days4, vbOKOnly, "4 Days"
End If


' If no totes have been added to the Days3 message box list then...
If Len(Days3) = 26 Then
    ' Display a Days3 message reporting "None"
    MsgBox Days3 & "None", vbOKOnly, "3 Days"
' Else if totes have been added then...
Else
    ' Display the full Days3 message
    MsgBox Days3, vbOKOnly, "3 Days"
End If


' If no totes have been added to the Days2 message box list then...
If Len(Days2) = 26 Then
    ' Display a Days2 message reporting "None"
    MsgBox Days2 & "None", vbOKOnly, "2 Days"
' Else if totes have been added then...
Else
    ' Display the full Days2 message
    MsgBox Days2, vbOKOnly, "2 Days"
End If


' If no totes have been added to the Days1 message box list then...
If Len(Days1) = 25 Then
    ' Display a Days1 message reporting "None"
    MsgBox Days1 & "None", vbOKOnly, "1 Day"
' Else if totes have been added then...
Else
    ' Display the full Days1 message
    MsgBox Days1, vbOKOnly, "1 Day"
End If
    
End Sub
 
Upvote 0
Fishboy said:
Hi rvvan, welcome to the boards.

My solution is similar however a little more detailed than the one above. It checks all cells in column K and will pipe out 5 messages at the end, one for each count of days til expiration:

Code: 
Sub CheckCells()
' Defines variables
Dim Cell As Range, cRange As Range
Dim LastRow As Long
Dim Days5 As String, Days4 As String, Days3 As String, Days2 As String, Days1 As String


' Defines LastRow as the last row of data based on column K
LastRow = ActiveSheet.Cells(Rows.Count, "K").End(xlUp).Row
' Sets the check range as K1 to the last row of K
Set cRange = Range("K1:K" & LastRow)


' Defines the default states for all 5 message boxes
Days5 = "Totes expiring in 5 days: "
Days4 = "Totes expiring in 4 days: "
Days3 = "Totes expiring in 3 days: "
Days2 = "Totes expiring in 2 days: "
Days1 = "Totes expiring in 1 day: "


' For each cell in the check range
For Each Cell In cRange
    ' Select a case based on the cell value
    Select Case Cell.Value
        ' Cell value of 5
        Case 5
            ' If no totes have been added to the message box list yet then...
            If Len(Days5) = 26 Then
                ' Add the tote number from column H to the message box list
                Days5 = Days5 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days5 = Days5 & ", " & Range("H" & Cell.Row).Value
            End If
        ' Cell value of 4
        Case 4
            ' If no totes have been added to the message box list yet then...
            If Len(Days4) = 26 Then
                ' Add the tote number from column H to the message box list
                Days4 = Days4 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days4 = Days4 & ", " & Range("H" & Cell.Row).Value
            End If
        ' Cell value of 3
        Case 3
            ' If no totes have been added to the message box list yet then...
            If Len(Days3) = 26 Then
                 ' Add the tote number from column H to the message box list
                Days3 = Days3 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days3 = Days3 & ", " & Range("H" & Cell.Row).Value
            End If
        ' Cell value of 2
        Case 2
            ' If no totes have been added to the message box list yet then...
            If Len(Days2) = 26 Then
                ' Add the tote number from column H to the message box list
                Days2 = Days2 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days2 = Days2 & ", " & Range("H" & Cell.Row).Value
            End If
        ' Cell value of 1
        Case 1
            ' If no totes have been added to the message box list yet then...
            If Len(Days1) = 25 Then
                ' Add the tote number from column H to the message box list
                Days1 = Days1 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days1 = Days1 & ", " & Range("H" & Cell.Row).Value
            End If
    End Select
' Check next cell in check range
Next Cell


' If no totes have been added to the Days5 message box list then...
If Len(Days5) = 26 Then
    ' Display a Days5 message reporting "None"
    MsgBox Days5 & "None", vbOKOnly, "5 Days"
' Else if totes have been added then...
Else
    ' Display the full Days5 message
    MsgBox Days5, vbOKOnly, "5 Days"
End If


' If no totes have been added to the Days4 message box list then...
If Len(Days4) = 26 Then
    ' Display a Days4 message reporting "None"
    MsgBox Days4 & "None", vbOKOnly, "4 Days"
' Else if totes have been added then...
Else
    ' Display the full Days4 message
    MsgBox Days4, vbOKOnly, "4 Days"
End If


' If no totes have been added to the Days3 message box list then...
If Len(Days3) = 26 Then
    ' Display a Days3 message reporting "None"
    MsgBox Days3 & "None", vbOKOnly, "3 Days"
' Else if totes have been added then...
Else
    ' Display the full Days3 message
    MsgBox Days3, vbOKOnly, "3 Days"
End If


' If no totes have been added to the Days2 message box list then...
If Len(Days2) = 26 Then
    ' Display a Days2 message reporting "None"
    MsgBox Days2 & "None", vbOKOnly, "2 Days"
' Else if totes have been added then...
Else
    ' Display the full Days2 message
    MsgBox Days2, vbOKOnly, "2 Days"
End If


' If no totes have been added to the Days1 message box list then...
If Len(Days1) = 25 Then
    ' Display a Days1 message reporting "None"
    MsgBox Days1 & "None", vbOKOnly, "1 Day"
' Else if totes have been added then...
Else
    ' Display the full Days1 message
    MsgBox Days1, vbOKOnly, "1 Day"
End If
    
End Sub
Click to expand...


This worked great!! thank you so much. One question, is there anywat to combine all of these notification boxes into one? so the user does not have to select ok multple times? Thank you again!!
 
Upvote 0
Hi rvvan, glad to hear that it worked for you.

With regards to combining all the message boxes, yes that is doable, however due to the way I have currently got it set up to work out whether the number of totes expiring are either "some" or "none" I will have to go back to the drawing board briefly and re-evaluate.

Leave it with me a little longer mate.
 
Upvote 0
rvvan said:
This worked great!! thank you so much. One question, is there anywat to combine all of these notification boxes into one? so the user does not have to select ok multple times? Thank you again!!
Click to expand...

Fishboy said:
Hi rvvan, glad to hear that it worked for you.

With regards to combining all the message boxes, yes that is doable, however due to the way I have currently got it set up to work out whether the number of totes expiring are either "some" or "none" I will have to go back to the drawing board briefly and re-evaluate.

Leave it with me a little longer mate.
Click to expand...
Actually, that was a little easier than I thought it would be. Try this out:

Code: 
Sub CheckCells()
' Defines variables
Dim Cell As Range, cRange As Range
Dim LastRow As Long
Dim Days5 As String, Days4 As String, Days3 As String, Days2 As String, Days1 As String




' Defines LastRow as the last row of data based on column K
LastRow = ActiveSheet.Cells(Rows.Count, "K").End(xlUp).Row
' Sets the check range as K1 to the last row of K
Set cRange = Range("K1:K" & LastRow)




' Defines the default states for all 5 message boxes
Days5 = "Totes expiring in 5 days: "
Days4 = "Totes expiring in 4 days: "
Days3 = "Totes expiring in 3 days: "
Days2 = "Totes expiring in 2 days: "
Days1 = "Totes expiring in 1 day: "




' For each cell in the check range
For Each Cell In cRange
    ' Select a case based on the cell value
    Select Case Cell.Value
        ' Cell value of 5
        Case 5
            ' If no totes have been added to the message box list yet then...
            If Len(Days5) = 26 Then
                ' Add the tote number from column H to the message box list
                Days5 = Days5 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days5 = Days5 & ", " & Range("H" & Cell.Row).Value
            End If
        ' Cell value of 4
        Case 4
            ' If no totes have been added to the message box list yet then...
            If Len(Days4) = 26 Then
                ' Add the tote number from column H to the message box list
                Days4 = Days4 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days4 = Days4 & ", " & Range("H" & Cell.Row).Value
            End If
        ' Cell value of 3
        Case 3
            ' If no totes have been added to the message box list yet then...
            If Len(Days3) = 26 Then
                 ' Add the tote number from column H to the message box list
                Days3 = Days3 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days3 = Days3 & ", " & Range("H" & Cell.Row).Value
            End If
        ' Cell value of 2
        Case 2
            ' If no totes have been added to the message box list yet then...
            If Len(Days2) = 26 Then
                ' Add the tote number from column H to the message box list
                Days2 = Days2 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days2 = Days2 & ", " & Range("H" & Cell.Row).Value
            End If
        ' Cell value of 1
        Case 1
            ' If no totes have been added to the message box list yet then...
            If Len(Days1) = 25 Then
                ' Add the tote number from column H to the message box list
                Days1 = Days1 & Range("H" & Cell.Row).Value
            ' Else if there are already totes in the message box list then...
            Else
                ' Add a comma and the tote number from column H to the message box list
                Days1 = Days1 & ", " & Range("H" & Cell.Row).Value
            End If
    End Select
' Check next cell in check range
Next Cell




' If no totes have been added to the Days5 message box list then...
If Len(Days5) = 26 Then
    ' Update the Days5 string accordingly
    Days5 = Days5 & "None"
End If




' If no totes have been added to the Days4 message box list then...
If Len(Days4) = 26 Then
    ' Update the Days4 string accordingly
    Days4 = Days4 & "None"
End If




' If no totes have been added to the Days3 message box list then...
If Len(Days3) = 26 Then
    ' Update the Days3 string accordingly
    Days3 = Days3 & "None"
End If




' If no totes have been added to the Days2 message box list then...
If Len(Days2) = 26 Then
    ' Update the Days2 string accordingly
    Days2 = Days2 & "None"
End If




' If no totes have been added to the Days1 message box list then...
If Len(Days1) = 25 Then
    ' Update the Days1 string accordingly
    Days1 = Days1 & "None"
End If


' Display a final output message with a summary of the findings
MsgBox Days5 & vbCr & Days4 & vbCr & Days3 & vbCr & Days2 & vbCr & Days1, vbOKOnly, "Summary"


End Sub
 
Upvote 0
rvvan said:
Fishboy,

You are an excel WIZARD level 100. Thank you.
Click to expand...
Happy to help, and thanks for the positive feedback.

As much as this may seem like wizardry to you, the real gurus on these forums often leave me absolutely gobsmacked with some of the code they can produce! ;)
 
Upvote 0
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
VBA Pop Up Message Using Moving Cell Values
Replies
5
Views
233
Joe4
Joe4
M
  • Question
VBA pop up using multiple ranges/rows
2
Replies
19
Views
544
Cubist
Cubist
franklin_m
  • Question
VBA Search row for instances of pattern in two adjacent cells
Replies
3
Views
390
franklin_m
franklin_m
O
  • Question
Code to generate a "Success" Pop Up Box after macro runs
Replies
8
Views
189
Joe4
Joe4
B
  • Question
Event code to populate message upon workbook open at specified dates
Replies
2
Views
142
bh24524
B

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

Share this page

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