Set footer font size in VBA code

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
Thanks Mudface - sorted!

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

Appreciate your help.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
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