Formatting Headers through VBA
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Formatting Headers through VBA

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    In my code I copy a header from a differnt workbook to a new workbook. The format of the old header can vary. I then have to reformat the new header to be TimesNewRoman,Bold and 12 size. It seems the formatting of the old header gets caught in the text whenever I try to reformat it.

    Any ideas? Is it possible to store the old header in a variable, rip out the formatting, paste in the new formatting and then set the new header equal to this variable? I can't figure out a way to do this though.

    Thanks.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Little Italy
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-20 08:04, yussi1870 wrote:
    In my code I copy a header from a differnt workbook to a new workbook. The format of the old header can vary. I then have to reformat the new header to be TimesNewRoman,Bold and 12 size. It seems the formatting of the old header gets caught in the text whenever I try to reformat it.

    Any ideas? Is it possible to store the old header in a variable, rip out the formatting, paste in the new formatting and then set the new header equal to this variable? I can't figure out a way to do this though.

    Thanks.
    ActiveSheet.PageSetup.LeftHeader = "&""Times New Roman,Bold""&12" & your left Header

    same goes of course for CenterHeader and RightHeader

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I entered the code:

    ActiveSheet.PageSetup.CenterHeader = "&""Times New Roman,Bold""&12" & sHeader

    sHeader contains the header of another worksheet and is in Arial, Bold, 10. This is concatenated with a cell in the current worksheet. .CenterHeader code above doesn't even effect the formatting! The header from the other worksheet is still in Arial, Bold, 10 and the Cell value is still in its original formatting of Arial, Bold, 12.

    Any ideas?

    Thanks.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Little Italy
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oh yeah, I'm sorry, I didn't read your original post very well now did I? If the source of the new Header is an old one you do need to rip out the formatting. How to do it? Like the big man said I'll be back! I really gotto go now, but I dig the solution up for you. I've done it in one of my prior projects, it shouldn't be a problem to find it...

    Check me later, all right?



  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Little Italy
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-20 11:39, yussi1870 wrote:
    I entered the code:

    ActiveSheet.PageSetup.CenterHeader = "&""Times New Roman,Bold""&12" & sHeader

    sHeader contains the header of another worksheet and is in Arial, Bold, 10. This is concatenated with a cell in the current worksheet. .CenterHeader code above doesn't even effect the formatting! The header from the other worksheet is still in Arial, Bold, 10 and the Cell value is still in its original formatting of Arial, Bold, 12.

    Any ideas?

    Thanks.
    Sorry man it took so long, but here is something you can try:

    *********************************************

    Function sHeader(FullHeader As String)

    Dim FontStripped As String
    Dim StyleStripped As String

    On Error Resume Next

    StripIndex1 = InStr(1, FullHeader, ",")

    If StripIndex1 = 0 Then

    StyleStripped = FullHeader

    GoTo Size

    End If

    FontStripped = Mid(FullHeader, StripIndex1)

    StripIndex2 = InStr(1, FontStripped, Chr(34))

    StyleStripped = Mid(FontStripped, StripIndex2 + 1)

    Size:

    sHeader = StyleStripped

    If IsNumeric(Mid(StyleStripped, 2, 1)) Then
    sHeader = Mid(StyleStripped, 3)
    End If

    If IsNumeric(Mid(StyleStripped, 2, 2)) Then
    sHeader = Mid(StyleStripped, 4)
    End If

    If IsNumeric(Mid(StyleStripped, 2, 3)) Then
    sHeader = Mid(StyleStripped, 5)
    End If

    End Function

    *********************************************

    It has worked for me in a similar situation, It's not good programming, I know, but try it and change it better if you wish. Just call the function and it should return one stripped up header value.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thank you so much! This worked like a charm. Thanks!

User Tag List

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