Problem with text toolbox in connection with printing

Copper Contributor

I have created an excel form in O365.
There i have set a text toolbox with (steuerelement) a scrollbar. to be able to enter a lot of text.
If I enter now much text, so that the scrollbar becomes active, and i print then only the displayed text is printed but not the text actually contained.
I would like to print the entire text!

About help i would be happy !

19 Replies

@SteVer1880 Suppose your textbox is named TextBox1 and the codename of the worksheet it is on is Sheet1. Enter this code in the ThisWorkbook module:

Option Explicit

Public OldHeight As Double
Public OldWidth As Double

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If ActiveSheet.Name = "Sheet1" Then
        OldHeight = Sheet1.TextBox1.Height
        OldWidth = Sheet1.TextBox1.Width
        Sheet1.TextBox1.AutoSize = True
    End If
    Application.OnTime Now, "ReSizeBox"
End Sub

Insert a module and enter this code into it:

Option Explicit

Sub ReSizeBox()
    With Sheet1.TextBox1
        .AutoSize = False
        .Height = ThisWorkbook.OldHeight
        .Width = ThisWorkbook.OldWidth
    End With
End Sub

@Jan Karel Pieterse thank´s for your support.

Please check my VBA-config (attachment)- i'm not shure if it is correct.

VBA is not my speciality :(

@Jan Karel Pieterseand a second appendix with an error when compiling.

Relpace all "Sheet1." with "Tabelle1." (including the period but excluding the quotation marks).

@Jan Karel Pieterse 

 

Sorry, but it doesn't work. Unfortunately, not all content is printed yet.

In my case, the function with rezice before print does not seem to work.

 

I'am sorry !

WHat happens if you do a print preview, does it show all content of the textbox?

@Jan Karel Pieterse 

 

no, there will be only the visible text

@Jan Karel Pieterse 

can you help me once again?

Sure, I'll try to :)

@Jan Karel Pieterse 

 

So, now it works !!! Thx.


I have a further question:
How can i print more than one text boxes? I have 5 text boxes in the sheet.
Please tell me the correct vba-config.


Many greets

@SteVer1880 That is slightly more complicated as we have to record the heights and widths of all textboxes prior to changing them:

Option Explicit

Dim OldHeights() As Double
Dim OldWidths() As Double

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim Obj As Object
    Dim lCt As Long
    ReDim OldHeights(1 To 1)
    ReDim OldWidths(1 To 1)
    If ActiveSheet.Name = "Sheet1" Then
        For Each Obj In Sheet1.OLEObjects
            If TypeName(Obj.Object) = "TextBox" Then
                lCt = lCt + 1
                ReDim Preserve OldHeights(1 To lCt)
                ReDim Preserve OldWidths(1 To lCt)
                With Obj
                    OldHeights(lCt) = .Height
                    OldWidths(lCt) = .Width
                    .Object.AutoSize = True
                End With
            End If
        Next
    End If
    Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!thisworkbook.ReSizeBox"
End Sub

Public Sub ReSizeBox()
    Dim Obj As Object    'msforms.TextBox
    Dim lCt As Long
    For Each Obj In Sheet1.OLEObjects
        If TypeName(Obj.Object) = "TextBox" Then
            lCt = lCt + 1
            With Obj
                .Object.AutoSize = False
                .Height = OldHeights(lCt)
                .Width = OldWidths(lCt)
            End With
        End If
    Next
End Sub

@Jan Karel Pieterse 

 

Thanks for your use!

I have tested the current config, unfortunately it does not work as desired.

A screenshot of the application, the config and the printer result is in the appendix

Can you please post the workbook rather than a screenshot?

@Jan Karel Pieterse 

here it is :)

Move the code to the "Diesearbeitsmappe" module and in the code, replace "Thisworkbook" with "Diesearbeitsmappe".

@Jan Karel Pieterse 

 

Hi,

 

thx for the info! So far so good - but there is the next surprise.

 

The extendet textboxes are overlap. Is there a way to dynamically move the other text in the textboxes an the normal text (in this case "TextBox2")? -> See the Screenshot !

 

What do you mean - i'm on the right way to create a functionall solution or should i think about my idea idea to realise the form ?

To be honest, I don't know a really solid solution for this. We could:
1. Remember the tops of each textbox and move textboxes 2-n down if needed
2. If a textbox becomes taller than a given number, first remember its width and make it wider before setting it to autosize
Option 1 may mean the textbox spills to a next sheet of paper when printing (Don't even know if the last one will print if it spills over if your print area is too small?)
Option 2 will nog guarantee they don't overlap
Excel is not the best program to use when it comes to printing large amounts of texts. More than ~1000 characters quickly becomes cumbersome.

@Jan Karel Pieterse 

 

Good Morning,

 

thx for your great support. At the weekend i´m created a form with ms word. I think it is the better way to solve my problems - an what should i say -> it work`s.

 

I'am thankfull of your help.

 

Best regard´s

Steffen Verfürth

Excellent, glad you were able to solve the issue.