Formatting a MsgBox to have two columns

JoshuaD

Board Regular
Joined
Dec 27, 2016
Messages
54
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.

Code:
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.

Code:
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top