Results 1 to 3 of 3

Set footer font size in VBA code

This is a discussion on Set footer font size in VBA code within the Excel Questions forums, part of the Question Forums category; I have the following code that adds an NT Users loggon name to the right footer when printing. The printed ...

  1. #1
    Board Regular Glaswegian's Avatar
    Join Date
    Oct 2003
    Location
    Glasgow, Scotland
    Posts
    1,482

    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 - XL2002 on Win XP - 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

    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,482

    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 - XL2002 on Win XP - 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

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