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

 

[Incidental]

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

 

[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

 

[Incidental]

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

 

[DDawson]

Steve

Thanks very much, this solves the problem.

Dylan