Programmatically change Excel footer problem

[PeDa]

I want to let an Excel footer depend on the value of a particular cell (to differentiate between an "original" and a later print). However the following does not work in the WorkBook_BeforePrint event:

If ... Then
  Worksheets("Audit").PageSetup.LeftFooter = "A" 
Else
  Worksheets("Audit").PageSetup.LeftFooter = "B"
End If

Any suggestions?

 

[macropod]

Hi PeDa,

The basic statements work OK, so problem most likely lies in your 'If ... Then' test, but you haven't shared that with us.

Cheers

[MS MVP - Word]

 

[PeDa]

Hello Macropod,

Ah yes, but it doesn't set the footer to either "A" or "B"! The WorkBook_BeforePrint event is running (why shouldn't it), because if I insert a Msgbox "Hello" I see that.

Here is the actual event (excuse my Dutch!)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim MyDif_d As Date
MyDif_d = Abs(CDate(Worksheets("Audit").Cells(11, 1)) - Now())
Worksheets("Audit").PageSetup.RightFooter = "Pagina &P van &N"
If Hour(MyDif_d) > 0 Or Minute(MyDif_d) > 0 Then
  Worksheets("Audit").PageSetup.LeftFooter = "Afgedrukt op " & Format(Now, "d mmmm yyyy") & " om " & Format(Now, "hh:nn:ss")
  Else
  Worksheets("Audit").PageSetup.LeftFooter = "Origineel afdruk, Paraaf: _____________"
End If
End Sub

 

[macropod]

Hi PeDa,

Your code works for me.

Cheers

[MS MVP - Word]

 

[PeDa]

Hello again Macropod,

Thank you for this. It should work for me too; very strange (and frustrating) ;-((

 

[firefytr]

PeDa,

If you put a breakpoint on your first line (select the Private Sub Workbook_BeforePrint line and press F9) and then run your code (print it), step through your code via F8 and tell us what line it errors on and what the error is exactly.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci