Display Many Cell Result In Message Box

Simon Lloyd · Nov 8th 2006

Hi all, i have a routine which loops through a range looking for past dates and when it finds one display the result in a message box in my testbook i only use 25 rows but it gets annoying having to click ok for every find....is it possible to collect all results and display them in the message box at one time?

Code

Private Sub Workbook_Open()
Dim Mycell
Dim Rng
Set Rng = Sheets("Sheet1").Range("B1:B25")
For Each Mycell In Rng
If Mycell.Value < Date Then
MsgBox Mycell.Offset(0, -1).Value & " Is Overdue By " & Date - Mycell.Value & " Days, Take Action Now!", vbOKOnly, "Tasks Overdue"
End If
Next Mycell
End Sub

Regards,
Simon

**Andy Pope** · Nov 8th 2006

Re: Displaying More Than One Result In A Message Box?

[vba] Dim Mycell
Dim Rng
Dim strText As String

Set Rng = Sheets("Sheet1").Range("B1:B25")
For Each Mycell In Rng
If Mycell.Value < Date Then
strText = strText & vbLf & Mycell.Offset(0, -1).Value & " - " & Date - Mycell.Value & " Days"
End If
Next Mycell

If Len(strText) > 0 Then
MsgBox strText & vbLf & "Are Overdue. Take Action Now! ", vbOKOnly Or vbExclamation, "Tasks Overdue"
End If
[/vba]

Becareful though the maximum characters in a message box is about 1028

Simon Lloyd · Nov 8th 2006

Re: Displaying More Than One Result In A Message Box?

Andy thanks for the very quick response, just a couple of learning curve questions is vblf the same as Chr(13) and does Len mean length so where you state

Code

If Len(strText) > 0 Then

is it if length of string is > 0 then input string?

Regards,
Simon

**Andy Pope** · Nov 8th 2006

Re: Displaying More Than One Result In A Message Box?

To be precise vbLF is the same as CHAR(10). But the result is a new line.
Other options are vbCRLF = CHAR(13) & CHAR(10) or vbNewLine

The LEN() is indeed a test of the length of the string. If it has zero length then all items where in date so no msgbox required.

Simon Lloyd · Nov 8th 2006

Re: Displaying More Than One Result In A Message Box?

Thanks for the clearing those issues up and again thanks for the mods!

Regards,
Simon

Display Many Cell Result In Message Box

Participate now!

Share