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.