Formatting a MsgBox to have two columns

[JoshuaD]

Good Afternoon!

I have written the below code to work with a collection and then display the collection as a message box. The problem is that when the string name is to large it throws off all of my alignment. Any help you can give would be awesome. I just want a way to space out the strings so it looks like columns. The max any of the values would ever be is 32 characters.


This is the code that aligns nicely.

Sub Collection_Test()
    
    Dim collSheet As New Collection
    Dim ShtMsg As String
    Dim ShtMsgC As String
    Dim ColCount As Single
    Dim i As Long
    
'Ensures collection is empty
Set collSheet = Nothing
    
    ' Add items
    collSheet.Add "Red"
    collSheet.Add "Blue"
    collSheet.Add "Green"
    collSheet.Add "Purple"
    collSheet.Add "Orange"
    collSheet.Add "Yellow"
    collSheet.Add "Maroon"
    collSheet.Add "Brown"
    
'returns how many are stored
ColCount = collSheet.Count
'Configure the Message Box
For Each colval In collSheet
i = 1
If i Mod 2 = 0 Then
SpcString = vbTab
Else
SpcString = vbTab & vbTab
End If
  
ShtMsgC = ShtMsgC & colval & SpcString
    i = i + 1
Next colval
ShtMsg = "The following sheets were not calculated due to not having a spreadsheet available to send:" & vbNewLine & vbNewLine
MsgBox ShtMsg & ShtMsgC

'Emptys the collection
Set collSheet = Nothing
End Sub

This is the code that causes the Message box to dis-align.

Sub Collection_Test()

    Dim collSheet As New Collection
    Dim ShtMsg As String
    Dim ShtMsgC As String
    Dim ColCount As Single
    Dim i As Long
    
'Ensures collection is empty
Set collSheet = Nothing
    
    ' Add items
    collSheet.Add "Red"
    collSheet.Add "Blue"
    collSheet.Add "Green"
    collSheet.Add "Purple"
    collSheet.Add "This length breaks it" [B]'this is the only change between the two codes[/B]
    collSheet.Add "Yellow"
    collSheet.Add "Maroon"
    collSheet.Add "Brown"
    
'returns how many are stored
ColCount = collSheet.Count
'Configure the Message Box
For Each colval In collSheet
i = 1
If i Mod 2 = 0 Then
SpcString = vbTab
Else
SpcString = vbTab & vbTab
End If
  
ShtMsgC = ShtMsgC & colval & SpcString
    i = i + 1
Next colval
ShtMsg = "The following sheets were not calculated due to not having a spreadsheet available to send:" & vbNewLine & vbNewLine
MsgBox ShtMsg & ShtMsgC

'Emptys the collection
Set collSheet = Nothing
End Sub

 

[Joe4]

Perhaps the workaround mentioned in this thread will help you: https://www.mrexcel.com/forum/excel-questions/781657-aligning-multiple-columns-msgbox.html

 

[JoshuaD]

This still does not work if the larger string is on the left. I have the same issue. For some reason the String(20- LEN(colval)," ") is not working. I replaced the space with x's and they don't even line up. I did try and incorporate that work around but it is the same thing.