Results 1 to 2 of 2

Cannot remove extra carriage return in headers / footers

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. ...

  1. #1
    New Member
    Join Date
    Feb 2008
    Posts
    14

    Default Cannot remove extra carriage return in headers / footers

    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.000...28c7e5%40mara9)


    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

  2. #2
    New Member
    Join Date
    Feb 2008
    Posts
    14

    Default Re: Cannot remove extra carriage return in headers / footers

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com