May 23 2019 05:33 AM - edited May 23 2019 05:35 AM
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 !
May 23 2019 09:44 AM
@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
May 23 2019 11:08 PM
@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 :(
May 23 2019 11:28 PM
@Jan Karel Pieterseand a second appendix with an error when compiling.
May 24 2019 05:24 AM
May 26 2019 11:57 PM
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 !
May 27 2019 04:59 AM
May 27 2019 07:08 AM
Jun 02 2019 11:40 PM
can you help me once again?
Jun 03 2019 01:01 AM
Jun 18 2019 03:05 AM
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
Jun 18 2019 05:43 AM
@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
Jun 18 2019 11:03 PM
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
Jun 19 2019 01:16 AM
Jun 19 2019 02:04 AM
here it is :)
Jun 19 2019 04:36 AM
Jun 21 2019 12:56 AM
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 ?
Jun 21 2019 01:47 AM
Jun 23 2019 10:18 PM
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
Jun 24 2019 01:22 AM