formatting codes for footer not being applied and page orientation data mismatch

Question

I have a worksheet(adHoc) where cell b28 contains

"&9 2014 YTD Financial Data for PP" & Chr(10) & " &D &T" & Chr(10) & " Version 1.0" & Chr(10) & " &F"

When I use the above to update the footer of another worksheet in a different workbook. I don't get the embedded formatting - it displays exactly what is contained in the cell b28.For example excel should see &9 and make the font 9 points.

I am also getting a datatype mismatch error with the page orientation. The contents of cell b36 is xlLandscape.

I posted a copy of this question last week on another board but did not get any answers. I hope someone here has answer.

http://www.mrexcel.com/forum/excel-questions/919033-updating-pagesetup-using-cells-master-worksheet-orientation-formatting-footer-excel-visual-basic-applications.html

This is the code I am using.

Sub page_setup()

  Dim reportWB As Excel.Workbook

  Dim sheet As Excel.Worksheet

  'open report workbook - name of workbook is in cell b4
  Set reportWB = Workbooks.Open(Workbooks("macros.xlsm").Sheets("adHoc").Range("b4").Value)

  Dim leftFooter

  leftFooter = Workbooks("macros.xlsm").Sheets("adHoc").Range("b28").Value

  For Each sheet In reportWB.Sheets

  With sheet

     .PageSetup.leftFooter = leftFooter

     .PageSetup.Orientation = Workbooks("macros.xlsm").Sheets("adHoc").Range("b36").Value

  End With     

Next

End Sub

Answer 1

Reading in your footer definitions like that they are treated as literal string, not code. You need to resolve the code to valid footer strings somehow.

For the LeftFooter string, you can use Evaluate to resolve it, but it will need to be written as if it's a Excel Formula, not VBA, so use

"&9 2014 YTD Financial Data for PP" & Char(10) & " &D &T" & Char(10) & " Version 1.0" & Char(10) & " &F"

Note the I use Char rather than Chr, the Excel formula equivalent.

For Orientation you are using a named constant, which won't work. Either put the value on your Excel sheet (2 in this case) or write your own code to resolve the name to its value

Working version (with corrected source data on sheet as descibed above)

Sub page_setup()
    Dim reportWB As Excel.Workbook
    Dim sheet As Excel.Worksheet
    Dim wsSource As Worksheet

    'open report workbook - name of workbook is in cell b4
    Set wsSource = Workbooks("macros.xlsm").Sheets("adHoc")
    Set reportWB = Workbooks.Open(wsSource.Range("b4").Value)

    Dim leftFooter
    leftFooter = wsSource.Range("b28").Value

    For Each sheet In reportWB.Sheets
        With sheet
            .PageSetup.leftFooter = Evaluate(leftFooter)
            .PageSetup.Orientation = wsSource.Range("b36").Value
        End With
    Next
End Sub

To handle the constants you could add a UDF that resolves the string names to values and call that from your settings sheet

Eg

Function GetConst(s As String) As Variant
    Select Case s
        Case "xlLandscape"
            GetConst = xlLandscape
        Case "xlPortrait"
            GetConst = xlPortrait
        ' etc
    End Select

End Function

Put in cell B36 GetConst("xlLandscape") (as a string, not formula), and change your Orientation line of code to

.PageSetup.Orientation = Evaluate(wsSource.Range("b36").Value)

Add any other named constants you want to the Select Case statement.

Answer 2

AFAIK, there is no (straightforward) way to do what you're trying to do. When you put code in a cell, and then call that cell's value in place of actual code, what VBA is trying to run is not:

.PageSetup.Orientation = xlLandscape

but rather:

.PageSetup.Orientation = "xlLandscape"

which will produce the errors and behavior you're seeing.

As rule of thumb, if your VBA code needs a string (ie, something in ""), or a number, you can do that calculation on the sheet and have the code pull in the value.

For everything else (there's Mastercard) put it in the code. For example:

leftfooter = cell1.value & Chr(10) & cell2.value & Chr(10) & cell3.value

(As a side note, I'm not familiar with the formatting it seems you're trying to do in that string... Those are generally set up through things like

With sheet.PageSetup.leftFooter.
    .Font.Size = 9
    'etc...

)