Hard return in textbox for header text

[Dylan]

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

 

[Keith74]

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

 

[DDawson]

Keith

I think it maybe something to do with the way excel headers work.

Thanks for trying - see inline comments, below

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

 

[DDawson]

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

 

[DDawson]

Steve

Thanks very much, this solves the problem.

Dylan