Print contents of textbox (has scrollbars)

[Guest]

Hello -

I have a worksheet that includes a textbox. I need to be able to print the
entire contents of it. (It is a fixed size and uses scrollbars to display
all of the text.)

Is there some way I can code the printing of the document to do this?

Any suggestions will be greatly appreciated!

 

[Tom Ogilvy]

so it shows all the text just by printing it and not resizing the textbox
(text remains hidden in the box)? No.

moving the text somewhere else in a properly sized area or resizing the
textbox and then printing all the visible text? yes, you should be able to
write code to do that.

 

[LenB]

Here is a quick way that might help. (Tested on Win98 and Excel97.)
Calling this sub will just print the textbox contents. Note the changes
that you have to make if not using TextBox1 on Sheet1, and also the path
to notepad.exe if not c:\windows. You can make it run when printing the
worksheet by calling this sub in the Workbook_BeforePrint event (or
putting the code there). A bit crude, but it works. As Tom O. says,
there are other (longer) ways to put the text into a printable format on
a worksheet.

Sub PrintTextBox()
Dim intOutFile As Integer 'number for the output file

intOutFile = FreeFile 'get a file number

'change path and file name if required.
Open "c:\temp\temp.txt" For Output As intOutFile
'Change next line to use your text box. Could also pass a
'textbox object to this sub.
Print #intOutFile, Sheet1.TextBox1.Text
Close intOutFile

'Open notepad and print the file. Adjust the path to notepad.exe
'as required. This will print to the default printer without
'any dialog box.
Shell "c:\windows\notepad.exe /P c:\temp\temp.txt"

Kill "c:\temp\temp.txt" 'delete the file if desired
End Sub

 

[davidm]

LebB,

Brilliant!

Davi

 

[Guest]

Thanks, Len!
--
Sandy

Here is a quick way that might help. (Tested on Win98 and Excel97.)
Calling this sub will just print the textbox contents. Note the changes
that you have to make if not using TextBox1 on Sheet1, and also the path
to notepad.exe if not c:\windows. You can make it run when printing the
worksheet by calling this sub in the Workbook_BeforePrint event (or
putting the code there). A bit crude, but it works. As Tom O. says,
there are other (longer) ways to put the text into a printable format on
a worksheet.

Sub PrintTextBox()
Dim intOutFile As Integer 'number for the output file

intOutFile = FreeFile 'get a file number

'change path and file name if required.
Open "c:\temp\temp.txt" For Output As intOutFile
'Change next line to use your text box. Could also pass a
'textbox object to this sub.
Print #intOutFile, Sheet1.TextBox1.Text
Close intOutFile

'Open notepad and print the file. Adjust the path to notepad.exe
'as required. This will print to the default printer without
'any dialog box.
Shell "c:\windows\notepad.exe /P c:\temp\temp.txt"

Kill "c:\temp\temp.txt" 'delete the file if desired
End Sub

 

[LenB]

LebB,

Brilliant!

David

Thanks! I'll take that kind of feedback anytime.
Len

 

[Alberto Ast]

Great tip... I was looking for it... glad I found it on an old posts... it is
always good to dig into already posted questions.