Search code examples
vbaexcelexcel-2003

Msgbox With If statement

I'm trying to get a box to pop up listing a number of values that could be blank.

MsgBox (res1 & vbNewLine & _
res2 & vbNewLine & _
res3 & vbNewLine & _
res4 & vbNewLine & _
res5 & vbNewLine & _
res6 & vbNewLine & _
res7 & vbNewLine & _
res8 & vbNewLine & _
res9 & vbNewLine & _
res10 & vbNewLine & _
res11 & vbNewLine & _
res12 & vbNewLine)

what I want to do is have is something like this: 

if res1 <> "" then
    res1 & vbNewLine 
else
    ""
end if

Because what shows at the moment is load of blank lines:

https://i.sstatic.net/M4PZf.png

Solution

  • You could declare a String, and construct the string to display the characters. Something like,

    Dim tmpRes As String

If Len(res1) > 0 Then _
    tmpRes = tmpRes & res1 & vbCrLf 

If Len(res2) > 0 Then _
    tmpRes = tmpRes & res2 & vbCrLf

If Len(res3) > 0 Then _
    tmpRes = tmpRes & res3 & vbCrLf

If Len(res4) > 0 Then _
    tmpRes = tmpRes & res4 & vbCrLf

If Len(res5) > 0 Then _
    tmpRes = tmpRes & res5 & vbCrLf

If Len(res6) > 0 Then _
    tmpRes = tmpRes & res6 & vbCrLf

If Len(res7) > 0 Then _
    tmpRes = tmpRes & res7 & vbCrLf

If Len(res8) > 0 Then _
    tmpRes = tmpRes & res8 & vbCrLf

If Len(res9) > 0 Then _
    tmpRes = tmpRes & res9 & vbCrLf

If Len(res10) > 0 Then _
    tmpRes = tmpRes & res10 & vbCrLf

MsgBox tmpRes