Put MsgBox text in a worksheet for printing

[Mark Tangard]

I have an Excel macro that finishes by displaying several lines of
results in a MsgBox. The output is arranged with the positioning
characters vbTab and vbCr so it aligns neatly.

When I do this in Word, I often give the user the option of printing the
MsgBox's text. It's easy to code -- just add a new document, set its
contents equal to the string that went into the MsgBox, and print. Word
interprets the vbCr and vbTab characters the same way in the MsgBox as
in the document.

Is there an easy way to do the same thing in Excel? -- to create a sheet
of printable results from formated MsgBox content? I know I could
parsing the MsgBox string into cell-shaped units and painstakingly
arrange them on a worksheet, but these messages can have several dozen
vbTab's and vbCR's, and the # of lines and tabs is never constant, so
that could be a huge job.

I've tried loading the MsgBox text into a new sheet's text box
(substituting vbLf's of vbCr's), and also into cell A1 after enlarging
it to fill the screen, but the vbTab characters of course remain ignored
in both cases. Other than a screenshot (which is what we're doing now),
is there a simpler way to handle this with code?

TIA

 

[Zoo]

Try followings:

Sub Main1()
Dim sMsg As String
Dim l1 As Long, l2 As Long
'Create a message.
For l1 = 0 To 9
For l2 = 0 To 9
sMsg = sMsg & (l1 * 10 + l2) & vbTab
Next
sMsg = sMsg & vbCr
Next
MsgBox sMsg

Dim x As Long
Dim y As Long
Dim vY As Variant
Dim vX As Variant
vY = Split(sMsg, vbCr)

On Error Resume Next
For y = LBound(vY) To UBound(vY)
vX = Split(vY(y), vbTab)
Sheet1.Range(Sheet1.Cells(y + 1, LBound(vX) + 1), Sheet1.Cells(y +
1, UBound(vX) + 1)) = vX
Next

End Sub

Sub Main2()
Dim sMsg As String
Dim l1 As Long, l2 As Long
'Create a message.
For l1 = 0 To 9
For l2 = 0 To 9
sMsg = sMsg & (l1 * 10 + l2) & vbTab
Next
sMsg = sMsg & vbCr
Next
MsgBox sMsg

Cells(1, 1) = Replace(Replace(sMsg, vbTab, Space(4)), vbCr, vbLf)
Columns(1).ColumnWidth = 100
End Sub