Set footer font size in VBA code

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Set footer font size in VBA code

  1. #1
    Board Regular Glaswegian's Avatar
    Join Date
    Oct 2003
    Location
    Glasgow, Scotland
    Posts
    1,487
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Set footer font size in VBA code

     
    I have the following code that adds an NT Users loggon name to the right footer when printing. The printed name is appearing as font size 10 - which is the default size for all my worksheets. Other footer details are set at font size 8, but when I try to add the code to change the loggon name to 8 I get an 'Object required' error message and the line ".RightFooter.FontSize = 8" highlighted. Not quite sure what I'm doing wrong here - can anyone help?

    Code:
    Sub add_username_to_footer()
    'this adds the username from the NT loggon to the footer of a print
    'this routine is called each time user clicks on print checkbox and print button
    
    Dim strBuf As String, lngUser As Long, strUn As String
        strBuf = Space$(255) '//Clear buffer
        lngUser = WNetGetUser("", strBuf, 255)
        If lngUser = NO_ERROR Then
            strUn = Left(strBuf, InStr(strBuf, vbNullChar) - 1)
        Else
        End If
        
        With ActiveSheet.PageSetup
            .RightFooter = ""
            .RightFooter = "User: " & strUn
            .RightFooter.FontSize = 8
                          
        End With
    End Sub
    Many thanks.
    Iain - XL2010 on Win 7 Pro - A Bank is a place that will lend you money if you can prove you don't need it.

    Member of ASAP :: Member of UNITE

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Set footer font size in VBA code

    Try this-

    Code:
        With ActiveSheet.PageSetup
            .RightFooter = "&8User: " & strUn
        End With
    There is no fontsize property for a header/ footer so you have to use a formatting code as above.

  3. #3
    Board Regular Glaswegian's Avatar
    Join Date
    Oct 2003
    Location
    Glasgow, Scotland
    Posts
    1,487
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Set footer font size in VBA code

      
    Thanks Mudface - sorted!

    I didn't realise I had to use a formatting code - but I do now.

    Appreciate your help.
    Iain - XL2010 on Win 7 Pro - A Bank is a place that will lend you money if you can prove you don't need it.

    Member of ASAP :: Member of UNITE

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