Message Box that loops and displays multiple records

Question

I'm trying to create a message box that displays various entries for an undetermined number of columns.

I believe I want a loop, but I want the message box to display all the records available in a range and not create a new message box for each item in the range.

I want the message box to look like

Loan Summary(Price, Range, Standard Deviation):
Loan 1: (100, 5, 2)
Loan 2: (102, 4, 3)

and so on but the number of records (loans) will change each time.

I have the below code. How do I add a new line for each record in a range?

For theRep = 1 To wsv.Range("J3").Value
    Average1 = Range("loanSummary").Offset(0, theRep)
    Range1 = Range("loanSummary").Offset(1, theRep)
    StdDev1 = Range("loanSummary").Offset(2, theRep)

    MsgBox "Loan Summary (Price, Range, Standard Deviation):" & vbCrLf & vbTab & "Loan 1: " & Format(Average1, "##0.00") & ", " & Format(Range1, "##0.00") & ", " & Format(StdDev1, "##0.00")
Next

I would suggest a MsgBox isn't the best solution for this; you'd be better creating a UserForm, which gives you much more flexibility. — Joe

Scott Craner Scott Craner · Accepted Answer · 2018-12-11T19:19:40

Use a string variable to hold the data then after the loop present the string in one MsgBox

Dim str As String
str = "Loan Summary (Price, Range, Standard Deviation):" & vbCrLf & vbTab
For theRep = 1 To wsv.Range("J3").Value
    Average1 = Range("loanSummary").Offset(0, theRep)
    Range1 = Range("loanSummary").Offset(1, theRep)
    StdDev1 = Range("loanSummary").Offset(2, theRep)


    str = str & "Loan " & theRep & ": (" & Format(Average1, "##0.00") & ", " & Format(Range1, "##0.00") & ", " & Format(StdDev1, "##0.00") & ")" & vbCrLf & vbTab
Next
MsgBox str

Message Box that loops and displays multiple records

1 Answers