Userform textbox string to header causes unwanted double spacing

[ken23508]

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 ispreloaded 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 thatthe 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 thecenterheader. 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.

 

[Claus Busch]

Hi Ken,

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

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.

 

[GS]

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

 

[GS]

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

 

[ken23508]

Wow. Three great answers. Thanks to all.
Ken