Footer Macro

[Wrathor]

I am trying to make a footer say what is in a cell on a different page and make the font size 8. When I run my macro nothing happens and I can't figure out why...

Code:

Sub Font()
'
' font Macro
'
'

Sheets("Market Overview").Select

Dim strFtr As String
strFtr = Sheets("Inputs").Range("A6") & " Report"

With ActiveSheet.PageSetup.RightFooter = "&8strFtr"

End With
End Sub

 

[ukmikeb]

Hi

Welcome to the forum.

Possibly this -

With ActiveSheet.PageSetup
    .RightFooter = "&8" & strFtr
End With

hth

 

[CharlesChuckieCharles]

Try

With ActiveSheet.PageSetup.RightFooter = strFtr

 

[cjcobra]

I used to use this code to update the statusbar before it was built into 2010. Basically put the SUM, Avg, etc of the highlighted cells into the status bar. You could make it point somewhere else to display whatever you wanted.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

On Error Resume Next
Application.StatusBar = " Sum= " & Format(Application.WorksheetFunction.Sum(Selection), "#,##0.00") & " Avg= " & Format(Application.WorksheetFunction.Average(Selection), "#,##0.00") & " Count= " & Format(Application.WorksheetFunction.Count(Selection), "#,##0") & " Min= " & Format(Application.WorksheetFunction.Min(Selection), "#,##0.00") & " Max= " & Format(Application.WorksheetFunction.Max(Selection), "#,##0.00")
If Err <> 0 Then Application.StatusBar = ""

End Sub

 

[Wrathor]

I ended up recruiting another guy where i work to help figure this out.

The issue that was occurring, was that A6 of the inputs sheet was "2011-Q1"

When adding the font size of 8 it did not seperate the 8 and 2011, thus trying to make the font size 82011. this caused a silent error and just defaulted the font size to 10.

We had to find a way to break up the 8 and 2011. The first thing we tried to do was just add an ' after the 8. this resulted in the footer showing as size 8 font, but "'2011-Q1 Report". We eventually figured out that we could also seperate the 8 and 2011 by declaring a font color so there wouldn't be an ' in the footer.

The final code we ended up using is:

Dim strRightFooterText As String
strRightFooterText = "&8&K000000" & Sheets("Inputs").Range("A6") & " Report"
Sheets("Market Overview").Select
With ActiveSheet.PageSetup
.RightFooter = strRightFooterText
End With
End Sub