none
Printing Contents of an Textbox using Excel VBA RRS feed

  • Question

  • I need to use text boxes in Excel as the contents are very large. They also contain special characters that do not display correctly in an Excel cell. The text box works fine. But, despite my continued searches, I cannot find a way to print its contents. I'm sure there must be a way and I just missed it despite my research. If anyone has a way, I would appreciate you sharing.  Oh, and the text box contain vertical scroll bars, so I can not simply make it a print object as it will only print what is visible.

    Thank You!

    Monday, December 28, 2015 7:09 PM

Answers

  • Because you have said that the TextBox has vertical scroll bars then I am assuming it is an ActiveX TextBox. If this assumption is correct then the following VBA code method copies the TextBox contents to a Word Document and prints it.

    Not sure of your expertise with VBA code so if you create an ActiveX Command button and then while in Design Mode, right click the button and select View Code to open the VBA editor and it automatically creates the Sub name and End Sub for the button. Copy the code below (without the Sub and End Sub lines) and paste in between the Sub name and End Sub that were created.

    Save the workbook as macro enabled.

    Private Sub CommandButton1_Click()
        Dim ws As Worksheet
        Dim txtOleObj As OLEObject
        Dim objWord As Object
        Dim MyData As New DataObject
       
        Set ws = Worksheets("Sheet1")   'Edit "Sheet1" to your worksheet name
       
        Set txtOleObj = ws.OLEObjects("TextBox1")
       
        MyData.SetText txtOleObj.Object.Text
        MyData.PutInClipboard
       
        'Attempt to GetObject first in case Word Application already open.
        On Error Resume Next
        Set objWord = GetObject(, "Word.Application")
        If Err <> 0 Then
            'GetObject returns error if not already
            'open so use CreateObject
            On Error GoTo 0
            Set objWord = CreateObject("Word.Application")
        End If
       
        With objWord
            .Visible = True
            .Documents.Add
            .Selection.Paste
            .activedocument.PrintOut
            .activedocument.Close SaveChanges:=False   'Suppress ask to save document
            .Quit   'Quit Word
        End With

    End Sub


    Regards, OssieMac

    • Proposed as answer by David_JunFeng Tuesday, December 29, 2015 1:29 AM
    • Marked as answer by David_JunFeng Thursday, January 7, 2016 8:48 AM
    Tuesday, December 29, 2015 1:01 AM

All replies

  • Because you have said that the TextBox has vertical scroll bars then I am assuming it is an ActiveX TextBox. If this assumption is correct then the following VBA code method copies the TextBox contents to a Word Document and prints it.

    Not sure of your expertise with VBA code so if you create an ActiveX Command button and then while in Design Mode, right click the button and select View Code to open the VBA editor and it automatically creates the Sub name and End Sub for the button. Copy the code below (without the Sub and End Sub lines) and paste in between the Sub name and End Sub that were created.

    Save the workbook as macro enabled.

    Private Sub CommandButton1_Click()
        Dim ws As Worksheet
        Dim txtOleObj As OLEObject
        Dim objWord As Object
        Dim MyData As New DataObject
       
        Set ws = Worksheets("Sheet1")   'Edit "Sheet1" to your worksheet name
       
        Set txtOleObj = ws.OLEObjects("TextBox1")
       
        MyData.SetText txtOleObj.Object.Text
        MyData.PutInClipboard
       
        'Attempt to GetObject first in case Word Application already open.
        On Error Resume Next
        Set objWord = GetObject(, "Word.Application")
        If Err <> 0 Then
            'GetObject returns error if not already
            'open so use CreateObject
            On Error GoTo 0
            Set objWord = CreateObject("Word.Application")
        End If
       
        With objWord
            .Visible = True
            .Documents.Add
            .Selection.Paste
            .activedocument.PrintOut
            .activedocument.Close SaveChanges:=False   'Suppress ask to save document
            .Quit   'Quit Word
        End With

    End Sub


    Regards, OssieMac

    • Proposed as answer by David_JunFeng Tuesday, December 29, 2015 1:29 AM
    • Marked as answer by David_JunFeng Thursday, January 7, 2016 8:48 AM
    Tuesday, December 29, 2015 1:01 AM
  • It works great.  Thank you very much!   Just one more question though.  Is it possible to not show Word opening and closing?  I tried Application.screenupdating =false, but that did not work.

    Thanks Again!

    Tuesday, December 29, 2015 6:56 PM
  • >>>Is it possible to not show Word opening and closing?  I tried Application.screenupdating =false, but that did not work.

    According to your description, I suggest that you could use Application.Visible Property to hide Microsoft Word. You could modify like below:

    With objWord
         .Visible = False
         ......
         .Quit   'Quit Word
    End With

    Wednesday, December 30, 2015 8:09 AM
  • Yes, I just noticed that line.  I should have seen it before.  Thank you very much.   It truly works great now.
    Wednesday, December 30, 2015 2:13 PM