Userform textbox string to header causes unwanted double spacing
 

I have a userform that is used to adjust certain print parameters. One of the parameters is the header which is picked up from Textbox1. Textbox1 is preloaded on userform initialization with the following string:


str = Sheet12.Range("ship").Value & Chr(10) & "Total Cost" & Chr(10) & Sheet12.Range("clin").Value

TextBox1.Value = str


The string is entered into the header with


ActiveSheet.PageSetup.CenterHeader = TextBox1.Value

with the intent being the user is allowed to edit the header string from the form and the changes will be reflected in the header. My problem is that the header becomes double spaced. The double spacing shows up whether or not the user makes any changes to the textbox. Textbox1 is set to multi-line and looks fine with the three lines and appropriate line breaks. It seems like the chr(10) triggers a line break in the textbox, but stays in there somehow and causes a double space when the textbox.value is made into the centerheader. How can I stop that?


That is the same string that works fine when it is put in the centerheader, without passing through the userform textbox.

Thanks

Ken

PS sorry about the premature sending of the earlier incomplete vesion of this.

Userform textbox string to header causes unwanted double spacing
 

Hi Ken,

Am Mon, 17 Nov 2014 08:01:05 -0800 (PST) schrieb :

The string is entered into the header with

ActiveSheet.PageSetup.CenterHeader = TextBox1.Value

with some testing I came to this result:

Private Sub UserForm_Initialize()
Dim str As String
str = Sheets("Sheet12").Range("ship").Text & Chr(10) & _
Sheets("Sheet12").Range("clin").Text

Me.TextBox1.Text = str
End Sub

And then for the center header:

With UserForm1.TextBox1
ActiveSheet.PageSetup.CenterHeader = _
Left(.Text, InStr(.Text, Chr(10)) - 1) & "Total Cost" & _
Chr(10) & Mid(.Text, InStr(.Text, Chr(10)) + 1)
End With


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Userform textbox string to header causes unwanted double spacing
 

When dealing with PageSetup you need to speak its language...

Option Explicit

Private Sub CommandButton1_Click()
Dim vText
vText = Split(TextBox1.Text, vbLf)
ActiveSheet.PageSetup.CenterHeader = Join(vText, "&7")
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1.Text = "ship" & vbLf & "total cost" & vbLf & "clin.value"
End Sub

...where "&7" denotes a linefeed in the header!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Userform textbox string to header causes unwanted double spacing
 

Simplified approach...

ActiveSheet.PageSetup.CenterHeader = _
Replace(TextBox1.Text, vbCrLf, vbLf)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Userform textbox string to header causes unwanted double spacing
 

Wow. Three great answers. Thanks to all.
Ken