Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put MsgBox text in a worksheet for printing
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 ----------------------- Mark Tangard "Life is nothing if you're not obsessed." --John Waters |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put MsgBox text in a worksheet for printing
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 "Mark Tangard" wrote in message ... 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 ----------------------- Mark Tangard "Life is nothing if you're not obsessed." --John Waters |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bold Text In Msgbox | Excel Discussion (Misc queries) | |||
MsgBox text on several lines | Excel Discussion (Misc queries) | |||
printing a worksheet with VB buttons or text boxes | Excel Discussion (Misc queries) | |||
If a worksheet name is = to test then a msgbox appears | Excel Discussion (Misc queries) | |||
VBA MsgBox() Text | Excel Programming |