Unusual Header Title via VBA

[JackDanIce]

Hi,

Have the following code attached to a button in my sheet:

Private Sub CommandButton1_Click()

Dim msg As String

msg = "Enter date tracking error is for: (dd mmmm yyyy):"

With ActiveSheet.PageSetup
    .CenterHeader = "European Focus: " & InputBox(msg, "Date of Tracking Error", Range("TEDate"))
    .LeftFooter = "Path: " & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
End With

End Sub

Range("TEDate") has formula: =TEXT(WORKDAY(TODAY(),-1, "DD MMMM YYYY")

Which today would give a value of 12 August 2011, yet after I run the above code, when I click on print preview, the central header shows:
"European Focus: 14 August 2011"

I tried including msgbox CenterHeader after the .CenterHeader line and this gave a message of "European Focus: 12 August 2011" so why is it when I click on print preview it shows 14 August?

Thank you for suggestions in advance,
Jack

 

[GlennUK]

It works fine for me!?!?!? What do you get for ActiveSheet.Name in debugging? Is it the sheet you expect?

 

[JackDanIce]

Hi Glenn,

Don't know, haven't checked that! But the button is on (and hence) clicked from the activesheet I'm having this problem on.. I'll check and post back.

Ta,
Jack

PS I'll be in your sunny part of the world in October, indoor surfing up in Milton Keynes, great fun, highly recommend it if you've not done it before!

 

[JackDanIce]

Proper weird, changed the code to:

Private Sub CommandButton1_Click()

Dim msg As String

msg = "Enter date tracking error is for: (dd mmmm yyyy):"

With Sheets("Summary")
    MsgBox ActiveSheet.Name 'This, as expected gives the right sheet name
    With .PageSetup
        .CenterHeader = "European Focus: " & InputBox(msg, "Date of Tracking Error", Range("TEDate"))
        .LeftFooter = "Path: " & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
    End With
End With

End Sub

It's definitely on the sheet I want but print preview still shows the header as 14 August rather than 12th..

 

[GlennUK]

Indoor surfing? Now that does sound weird. Only joking ... I might give that a try one day.

So, the sheet being processed is the right one ... I don't see how it could not work, sorry

 

[JackDanIce]

It's really good fun and the water's warm!

No idea regarding this issue, see if anyone else replies or I'll just find a more manual way to do it..