Print a Message Box

[Tim]

Is there anyway to do this. I have a message Box that displays the results of a procedure and I would like to give the user an option to print the message box.

I don't want to pollute any worksheets with the data as a quickfix.

I found the method PrintForm, but it seems to only work with UserForms.

Tim.

[Damon Ostrander]

Hi Tim,

I don't believe there is a way to print a message box, but you can print the text contained in the messagebox using the Shell Print command:

Dim MessageText As String
Dim Answer As Variant

MessageText = "This is a message" & vbCrLf _
& "If you would like to print it, select Yes" & vbCrLf _
& "If not, select No"

Answer = MsgBox(MessageText, vbInformation + vbYesNo, _
"A Message From Our Sponsor")

If Answer = vbYes Then
Open "c:tempmsg.txt" For Output As #1
Print #1, MessageText
Close
Shell "print /d:\AST-P-SSB02ssna-418-lex c:tempmsg.txt"
Application.Wait Now + 2 / 86400 'wait two seconds for Shell completion
Kill "c:tempmsg.txt"
End If

where

\AST-P-SSB02ssna-418-lex

is the name of the print device (in my case, a network printer).

As you can see, the code writes the data to a temporary file, prints it, then deletes the file.

[Farah]

do you necessarily have to mention a printer name ? it might be different for users.

[Nimrod]

I found the method PrintForm, but it seems to only work with UserForms.

Farah...
Since you've already discovered the PrintForm method for a userform why not just make a userform that looks like a message box and then give the user the option to print it out ?

[parry]

Hi Tim, you've already got some useful answers to your question but if your wanting to provide feedback to users you could of course post the results into a sheet as part of your code. That way the results are in electronic form rather than a piece of paper.

A low tech alternative to print an object on the screen is to advise users to use ALT+PRTSCN (for the message box alone, as opposed to PRTSCN for the whole screen) and paste into Word and print.

hth