This is a discussion on Cannot remove extra carriage return in headers / footers within the Excel Questions forums, part of the Question Forums category; Hello all, I have a macro that allows a user to edit a multi line text box from a form. ...
I have a macro that allows a user to edit a multi line text box from a form. The macro uses the XL4 PAGE.SETUP macro to modify the headers and footers as it's faster then using something like activesheet.pagesetup.leftheader = "new header"
My macro literally sets the left header (or whatever) equal to the .Text property of forms textbox. My problem is that if the user adds a carriage return using ctrl-enter or shift-enter, when you do a print preview excel shows a blank line in the header where the user added a carriage return.
I have tried removing the special characters 10 and/or 13 but it doesn't seem to help.
Even more funny is that if I remove the extra space in Excel's page setup dialog box the space stays removed, however if I populate a string in VBA with the pages header, then apply the extra string back to the header, the space re-appears.
Finally, I have looked at the header string character by character. In both cases, when my header has two seperate lines and when Excel shows the blank line between them and when it doesn't, the only special characters present in the string are 13 and 10.
Here is a small screen shot if that helps:
Did I explain my problem enough and if so do you have any suggestions?
Thanks very very much in advance,
For what it's worth:
I ended up determining the if you assign a multi line string containing Chr(13) (carriage return) to a textbox.Text, the control replaces Chr(13) with Chr(13)Chr(10) (carriage return + linefeed). Since I was assigning the header/footer value from the .Text property I ended up with multiple lines.
My workaround was this:
Where remChr10 removes all linefeeds from a string and PageSetupXL4M comes from: http://www.mcgimpsey.com/excel/udfs/pagesetup.htmlCode:dim hl as string hl = remChr10(.text_header_left.Text) PageSetupXL4M LeftHead:=hl