Footer Entries

[Guest]

I would like to insert the contents of a certiain cell into the footer of a
spreadsheet. I have inserted the file name and the tab name, but i need to
have a certain cell inserted in the footer as well. Is this possible? If so I
would need the code/formula to make it work.

 

[Guest]

Hi
not possible without VBA. You have to use the BeforePrint event of your
workbook. So try putting the following type of code in your workbook
module 'ThisWorkbook' (don't put it in a standard module):
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.CenterHeader = wkSht.range("A1").value
End With
Next wkSht
End Sub

 

[Chuckles123]

Frank,
Thanks for your code.
How does it need to be modified to handle a situation when all of th
macros are stored in a separate workbook? Also, is the answer an
different if the macro-hosting file is an .xlA file (not set-up as a
AddIn within Excel)?

Thanks in advance for your response.

Chuckles12

 

[Chuckles123]

In a ClassModule (named 'Class1):

Option Explicit
Public WithEvents xlApp As Application
_________________________________________________________________
Private Sub Class_Initialize()
Set xlApp = Application
End Sub
_________________________________________________________________
Private Sub xlApp_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel A
Boolean)
With PageSetup
.CenterHeader = "A HIT LIST - " & Range("A2").Value
End With
End Sub


In 'ThisWorkbook':

Option Explicit
_________________________________________________________________
Private Sub Workbook_Open()
Set gxlApp = New Class1
Set gxlApp.xlApp = Application
End Sub


In Module1 (there is only one module), as a separate procedure at th
top (along with other 'Public ...' statements):

Public gxlApp As Class1



In addition, 'Option Explicit' is the only coding for Sheet1, Sheet2
and for Sheet3.

All of the above is typically in an .xlA file (as I said, not as a
AddIn in Excel), but I am currently testing with all of the above in a
.xlS file. When I open a new workbook and execute a print macro (vi
clicking on a custom button), my custom header is not being printed.

Help!
Chuckles12

 

[Chuckles123]

This seems to work:

Under ‘ThisWorkbook’:

Option Explicit
Dim gxlApp As New Class1
______________________________________________________________________
Private Sub Workbook_Open()
Set gxlApp = New Class1
Set gxlApp.xlApp = Application
End Sub


Under Class1 (a named ‘Class Module’):

Option Explicit
Public WithEvents xlApp As Application
______________________________________________________________________
Private Sub xlApp_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel A
Boolean)
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""&16A ""BLENDED"" HIT LIST - "
Range("A2").Value
End With
End Sub

Chuckles12