Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hard return in textbox for header text
I'm using the following code to enter text from a userform textbox into my
page header before printing. I have set both EnterKeyBehaviour and Multiline = True. When I print there is a space between each line of text. How do I remove the blank lines? Private Sub btnTitle_Click() Dim strTitle As String strTitle = frmPageTitle.txtTitle.Value ActiveSheet.PageSetup.PrintTitleRows = "$1:$1" ActiveSheet.PageSetup.CenterHeader = "&""Arial,Bold""&14 " & strTitle _ '& Chr(10) & (Date) & " " & (Time) ActiveSheet.PageSetup.RightHeaderPicture.Filename = _ "H:\SWS Logo sm.jpg" ActiveSheet.PageSetup.LeftHeader = "&""Arial,Bold""&14&D" 'Print Sheet ' ActiveWindow.SelectedSheets.PrintOut Copies:=1 frmPageTitle.Hide End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hard return in textbox for header text
Quick fix :- lose the chr(10)
better idea :- check what the value being returned from the textbox is using ?frmPageTitle.txtTitle.Value in the immediate window. Using .text instead of .value might help Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hard return in textbox for header text
Keith
I think it maybe something to do with the way excel headers work. Thanks for trying - see inline comments, below "Keith74" wrote: Quick fix :- lose the chr(10) I tried this, but it makes no difference better idea :- check what the value being returned from the textbox is using ?frmPageTitle.txtTitle.Value in the immediate window. Textbox value "1 2 3" Imediates window value ?frmPageTitle.txtTitle.Value "1 2 3 " Using .text instead of .value might help I tried this, but it makes no difference Keith |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hard return in textbox for header text
Hi Dylan
The code below is one way to do it. I came across a similar problem before removing address details from a multiline textbox that i needed in a single line, what i have done is use the split function to create an array of the data held in the lines of the textbox and then passed those lines back out of the array into the string. I hope this helps you out and sorts your problem, if you need help with the code let me know and i shall comment it for you. The only other thing i changed was i added a with statement to the end for the "ActiveSheet.PageSetup" sections as it is tidier and easier to read and of course less to type. ;D Option Explicit Dim ArrayCount, i As Integer Dim strTitle As String Dim SplitArray As Variant Private Sub btnTitle_Click() SplitArray = Split(txtTitle.Value, vbNewLine) ArrayCount = UBound(SplitArray) For i = 0 To ArrayCount strTitle = strTitle & SplitArray(i) & " " Next With ActiveSheet.PageSetup ..PrintTitleRows = "$1:$1" ..CenterHeader = "&""Arial,Bold""&14 " & _ strTitle & Chr(10) & (Date) & " " & (Time) ..RightHeaderPicture.Filename = "H:\SWS Logo sm.jpg" ..LeftHeader = "&""Arial,Bold""&14&D" End With 'Print Sheet ' ActiveWindow.SelectedSheets.PrintOut Copies:=1 frmPageTitle.Hide End Sub I hope this helps Steve |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hard return in textbox for header text
Thanks Steve
I had to add a line to the BeforePrint WorkBook Event so that the data is cleared before I print. strTitle = "" I get the following result when I add the following to the dialog "1 2 A B" Results on print preview is: "1 2 A B" Is this what you intended from the code - so that all text appears on one line? My original problem was that I get an extra blank line between the text when I print preview, so the result would look something like: "1 2 A B" I just wanted a solution that removes the blank lines so that it is as it appears in the dialog. E.g. "1 2 A B" Is there any way you can modify your code to solve this, please? Thanks Dylan "Incidental" wrote: Hi Dylan The code below is one way to do it. I came across a similar problem before removing address details from a multiline textbox that i needed in a single line, what i have done is use the split function to create an array of the data held in the lines of the textbox and then passed those lines back out of the array into the string. I hope this helps you out and sorts your problem, if you need help with the code let me know and i shall comment it for you. The only other thing i changed was i added a with statement to the end for the "ActiveSheet.PageSetup" sections as it is tidier and easier to read and of course less to type. ;D Option Explicit Dim ArrayCount, i As Integer Dim strTitle As String Dim SplitArray As Variant Private Sub btnTitle_Click() SplitArray = Split(txtTitle.Value, vbNewLine) ArrayCount = UBound(SplitArray) For i = 0 To ArrayCount strTitle = strTitle & SplitArray(i) & " " Next With ActiveSheet.PageSetup ..PrintTitleRows = "$1:$1" ..CenterHeader = "&""Arial,Bold""&14 " & _ strTitle & Chr(10) & (Date) & " " & (Time) ..RightHeaderPicture.Filename = "H:\SWS Logo sm.jpg" ..LeftHeader = "&""Arial,Bold""&14&D" End With 'Print Sheet ' ActiveWindow.SelectedSheets.PrintOut Copies:=1 frmPageTitle.Hide End Sub I hope this helps Steve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hard return in textbox for header text
Hi Dylan
Sorry i missunderstood what you wanted, the code below should be what you need. Option Explicit Dim strTitle As String Private Sub btnTitle_Click() strTitle = Replace(txtTitle.Value, Chr(10), "") With ActiveSheet.PageSetup ..PrintTitleRows = "$1:$1" ..CenterHeader = "&""Arial,Bold""&14 " & _ strTitle & Chr(10) & (Date) & " " & (Time) ..RightHeaderPicture.Filename = "H:\SWS Logo sm.jpg" ..LeftHeader = "&""Arial,Bold""&14&D" End With strTitle = "" 'Print Sheet ' ActiveWindow.SelectedSheets.PrintOut Copies:=1 'frmPageTitle.Hide End Sub I hope this sorts your problem out Steve |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hard return in textbox for header text
Steve
Thanks very much, this solves the problem. Dylan "Incidental" wrote: Hi Dylan Sorry i missunderstood what you wanted, the code below should be what you need. Option Explicit Dim strTitle As String Private Sub btnTitle_Click() strTitle = Replace(txtTitle.Value, Chr(10), "") With ActiveSheet.PageSetup ..PrintTitleRows = "$1:$1" ..CenterHeader = "&""Arial,Bold""&14 " & _ strTitle & Chr(10) & (Date) & " " & (Time) ..RightHeaderPicture.Filename = "H:\SWS Logo sm.jpg" ..LeftHeader = "&""Arial,Bold""&14&D" End With strTitle = "" 'Print Sheet ' ActiveWindow.SelectedSheets.PrintOut Copies:=1 'frmPageTitle.Hide End Sub I hope this sorts your problem out Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hard return | Excel Worksheet Functions | |||
use a hard return in a text box | Excel Programming | |||
hard return | New Users to Excel | |||
Msgbox text hard return | Excel Programming | |||
Hard Return in large textbox | Excel Programming |