Customizing Header

[Tasha]

Hi,
I am applying Date Range filter to my file, but i want to change the line of
the Header where the Filter name is shown, to show the Dates that I used in
the filter. How would i do that?

Thanks

 

[John]

Hi,
I am applying Date Range filter to my file, but i want to change the line of
the Header where the Filter name is shown, to show the Dates that I used in
the filter. How would i do that?

Thanks

Tasha,
I don't understand what you mean by, "the line of the header where the
filter name is shown". Do you mean the caption of the interactive filter
window itself? If so, I know of no way to edit that caption. You could
create your own userform or message box in VBA - then you can specify
the window caption text.

If you are referring to some other 'header", what is it?

John
Project MVP

 

[Tasha]

John,
The file i am working with has a header and footer on it for when it is
printed out. A use of the header in this report is to show what filter is
used on the file (which is great). So when I use the 'Date Range..' filter,
'Date Range..' is part of the header. What i want is for it to show the Dates
selected for the Date Range in the Header; not the filter name.

I hope that made a little more sense?

Tatum

 

[John]

John,
The file i am working with has a header and footer on it for when it is
printed out. A use of the header in this report is to show what filter is
used on the file (which is great). So when I use the 'Date Range..' filter,
'Date Range..' is part of the header. What i want is for it to show the Dates
selected for the Date Range in the Header; not the filter name.

I hope that made a little more sense?

Tatum

Tatum,
Yes, now it makes sense. Other than manually typing in the header
information for the filter range, the only way to get there is through a
VBA macro. The Date Range filter could be incorporated into the code
along with the FilePageSetupHeader Method.

Hope this helps.
John
Project MVP

 

[Tasha]

John
I know this is a stupid question but how would i go about making VBA macro
incorporated into the code along with the FilePageSetupHeader Method.

Thanks
Tatum

 

[John]

John
I know this is a stupid question but how would i go about making VBA macro
incorporated into the code along with the FilePageSetupHeader Method.

Thanks
Tatum

Tasha,
First, there are no stupid questions. Second, do you have any experience
with VBA? If you do not and would like some help, I can either direct
you to some information on learning VBA or I may even be persuaded to
write the code for you although I wouldn't be able to look at it until
later today or tomorrow (priorities).

Hope this helps.
John
Project MVP

 

[John]

Tatum,
Ok, it's later in the day. Here is the macro code to set up your print
header. If you have limited experience setting up and running a macro,
just follow these steps.
1. First copy the code below to your clipboard
2. Go to Tools/Macro/Macros.
3. Overwrite whatever might be in the Macro Name box with the name,
"DateRangeHeader"
4. At the bottom of the Macros Window pick "Global template" from the
selection list of "Macros in".
5. Hit the "Create" button
6. When the editor window opens, delete the two default lines that are
in the window and paste the below code into the window. Note that line
continuation characters are a space followed by an underscore (" _")
7. Go to File/Save Global.mpt
8. The macro can now be run on any file by going to Tools/Macro/Macros
and selecting the macro name. Just make sure you are looking at macros
in the Global. Then hit "run".

If you want to try the macro without actually printing, comment out by
putting an apostrophe (comment indicator) in front of the line
"FilePrint" and remove the comment line indicator from the
"FilePrintPreview" line.

Sub DateRangeHeader()
Dim StRngDate As Date, FinRngDate As Date
'Define the date range limits and filter
StRngDate = InputBox("Enter date for start of date range", "Date Range
Beginning")
FinRngDate = InputBox("Enter date for end of date range", "Date Range
End")
FilterEdit Name:="TatumDR", taskfilter:=True, Create:=True, _
OverwriteExisting:=True, FieldName:="start", Test:="is less than or
equal to", _
Value:=FinRngDate, showinmenu:=False, showsummarytasks:=True
FilterEdit Name:="TatumDR", taskfilter:=True, operation:="And",
newFieldName:="finish", _
Test:="is greater than or equal to", Value:=StRngDate
FilterApply Name:="TatumDR"

'set up print option
ans = MsgBox("Ready to print?", vbYesNo, "Print option")
If ans = vbYes Then
FilePageSetupHeader Alignment:=pjLeft, Text:=ActiveProject.Name _
& " filtered for Date Range of: " & StRngDate & " to " & FinRngDate
FilePrint
'fileprintpreview
End If
End Sub

Hope this helps.
John
Project MVP

 

[Tasha]

Hey John,

I did everything you told me to, but when i try to run it a 'Syntax Error'
comes up. Its says it for Ln 4

Tatum

 

[John]

Hey John,

I did everything you told me to, but when i try to run it a 'Syntax Error'
comes up. Its says it for Ln 4

Tatum

Tatum,
Line 4 doesn't tell me much since that depends on how the copy/paste
process entered the code in the editor. But, I can guess that the
problem is with a misplaced line continuation character sequence.
Sometimes what happens when code is copied from a newsreader is that one
or more lines of code are given a line feed before the line continuation
character sequence. If a code line is continued on successive lines, the
end of each line (except the last) must be a space following by an
underscore (i.e. " _"). If you don't have that condition, or if a line
continuation sequence appears in the middle of a line, the line needs to
be corrected appropriately.

Hope this helps.
John
Project MVP

 

[Tasha]

Hi John

I did everything you said to do and it still isnt working. It keep showing
errors on different line. Is there something, anything else i can do?

Thanks
Tatum