2 Column messagebox spacing of data

[Barryj]

I have a userform which shows data from 2 columns, due to the varing length of the names displayed, the second column info which is a constant length of characters.

The info is displayed as the attached picture Messagebox. I would like the Expiry date info to always be on the right side of the messagebox as is on the attached Messagebox 2.

Is this possible with my current code?

Below is the code for the message box


Sub Message()
Dim msg As String, i As Long, a As Variant
msg = ""

For i = 1 To 2
For Each a In Array("AC")
msg = msg & Cells(i, a).Value & vbTab
Next a
msg = msg & vbCrLf
Next i

For i = 1 To 10
For Each a In Array("U", "V")
msg = msg & Cells(i, a).Value & vbTab
Next a
msg = msg & vbCrLf
Next i

For i = 1 To 10
For Each a In Array("W", "X")
msg = msg & Cells(i, a).Value & vbTab
Next a
msg = msg & vbCrLf
Next i

For i = 1 To 10
For Each a In Array("Y", "Z")
msg = msg & Cells(i, a).Value & vbTab
Next a
msg = msg & vbCrLf
Next i

For i = 1 To 10
For Each a In Array("AA", "AB")
msg = msg & Cells(i, a).Value & vbTab
Next a
msg = msg & vbCrLf
Next I
End Sub

Thanks for any advice or assistance

[mancubus]

can you post your workbook?

[Barryj]

I have attached a sample work book, the information in the sheet is populated from another macro, but the attached shows some sample data.

[mancubus]

perhaps...



Sub vbax_61210()

Dim i As Long, MaxLength As Long
Dim msg As String

MaxLength = Application.Max(Evaluate("=MAX(LEN(U2:U2000))"), Evaluate("=MAX(LEN(W2:W2000))"), Evaluate("=MAX(LEN(Y2:Y2000))"), Evaluate("=MAX(LEN(AA2:AA2000))"), Evaluate("=MAX(LEN(w:w))"))

msg = Range("AC1").Value & vbTab & Range("AC2").Value & vbCrLf & vbCrLf

For i = 1 To 10
msg = msg & Range("U" & i) & Space(MaxLength - Len(Range("U" & i))) & vbTab & Range("V" & i) & vbCrLf
Next i

For i = 1 To 10
msg = msg & Range("W" & i) & Space(MaxLength - Len(Range("W" & i))) & vbTab & Range("X" & i) & vbCrLf
Next i

For i = 1 To 10
msg = msg & Range("Y" & i) & Space(MaxLength - Len(Range("Y" & i))) & vbTab & Range("Z" & i) & vbCrLf
Next i

For i = 1 To 10
msg = msg & Range("AA" & i) & Space(MaxLength - Len(Range("AA" & i))) & vbTab & Range("AB" & i) & vbCrLf
Next i

MsgBox msg, , "Staff Information"

End Sub


you should consider displaying the text on a userform when the length of the string exceeds 1024 chars limit. and in your case it exceeds...

[Barryj]

Thanks Mancubus for your assistance, I should be able to work with your solution, will mark this as solved.

[mancubus]

you are welcome.

thans for the feedback and marking the thread as solved.