Cannot remove extra carriage return in headers / footers

civilmonkey

New Member
Joined
Feb 20, 2008
Messages
14
Hello all,

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"
(http://google.com/groups?selm=VA.00000b2f.0028c7e5@mara9)


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:
http://img199.imageshack.us/img199/8956/extraspace.jpg

Did I explain my problem enough and if so do you have any suggestions?

Thanks very very much in advance,

Paul Hurst
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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:
Code:
dim hl as string
hl = remChr10(.text_header_left.Text)
PageSetupXL4M LeftHead:=hl

Where remChr10 removes all linefeeds from a string and PageSetupXL4M comes from: http://www.mcgimpsey.com/excel/udfs/pagesetup.html
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top