Solved

EXCEL 97-VBA - CUSTOM HEADER/FOOTER

Posted on 2000-02-18
6
580 Views
Last Modified: 2012-06-27
Finally related to 2 earlier q.
is there some neat code to get the current path/filename directly into a custom header/footer ?
0
Comment
Question by:mikerees
  • 4
  • 2
6 Comments
 
LVL 5

Accepted Solution

by:
TigerMan earned 50 total points
ID: 2536824
mikerees,


[Sorry, but I forgot to reply to this question in its own thread].

The full code needed to answer this, andn your other question, can be undertaken by:

Sub ShowPath()

   ActiveSheet.Range("A1").Value = ThisWorkbook.FullName

End Sub


Then:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   ShowPath
   ActiveSheet.PageSetup.CenterHeader = Range("A1")
End Sub

Also please note that the ShowPath routine will only work on Saved files.

Hope that answers both your questions.

0
 

Author Comment

by:mikerees
ID: 2537020
Thanks for code, but how do I execute it at Print or Print Preview stage and see results in the View Header/Footer and Custom Header???
I would like your code to place the path&file into the custom header/footer such that it can be seen in the View Header/Footer and Custom Header/Footer menu options.
Ideally the user presses an icon which will insert the path&file into the custom/header l/c/r section and can view the results from the Print Preview options.
Before you get carried away - I can cope with assigning a macro to a button!!
p.s. you have to work hard for the points!!!!!
0
 
LVL 5

Expert Comment

by:TigerMan
ID: 2537209
mikerees,

The code as specified, i.e. with the call in the Before_Print routine, will automatically update both the cell (A1) and the header/footer to which you direct it.  Therefore, when you select Print, it occurs automatically.

If you wish to see how it looks at Print Preview, and assuming you want to automate this for your users, it is probably quickest to:

Write/Record your own macro and attach it to a button.  In that macro you should include:

ShowPath  'update A1 on sheet
ActiveWindow.SelectedSheets.PrintPreview


This is assuming you have declared the sub from our previous correspondence:

Sub ShowPath()
   ActiveSheet.Range("A1").Value = ThisWorkbook.FullName
End Sub

Regarding the work for points q.

If I work on the assumption that I am merely a student in the art of Excel programming, I am sure to learn and acquire a bunch of skills over time.  I have taken that approach for several years now.  Therefore in one sense, the knowledge to answer questions takes a life-time - yet the actual answering is sometimes only a few minutes work.

Dave
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:mikerees
ID: 2540582
I have tried all of the above and assigned to a custome icon.
It appears to work so far, in that the path is shown in A1 and therefore printed as the 1st row.
However, it neither appears as a Header in Print Preview, nor in the View Custom Header centre box.
Have I "lost the plot"?
Here is the code -

Option Explicit

   
Sub ShowPath()

ActiveSheet.Range("A1").Value = ThisWorkbook.FullName

End Sub

Sub Workbook_BeforePrint(Cancel As Boolean)
ShowPath
ActiveSheet.PageSetup.CenterHeader = Range("A1")
End Sub

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 20/02/00 by mikres
'
'

    ShowPath 'update A1 on sheet
    Range("A1:B12").Select
    ActiveWindow.SelectedSheets.PrintPreview
    End Sub

0
 
LVL 5

Expert Comment

by:TigerMan
ID: 2545690
Hi Mikerees,

The code you have pasted is accurate for the purpose.  In fact, I just implemented a rather complex bunch of similar code today to prevent students copying each other's work in assessment.  It works fine using the code we have been working with.

I would suggest either of two related scenarios:

1. Try toggling a break point on the first line of code in your ShowPath sub, and the first line in your _BeforePrint sub.  Then run your macro from the workbook.  If it fails to 'hit' both break points then you may have a scope problem.

2. Is the macro called from the Sheet to which the ShowPath sub is called?  Generally speaking it may be better to put ShowPath in a Module.  That would ensure its 'publicness' and likely fix any scope problems from 1 above.

NB I note the withdrawel of the other question.  That poses no problem to me.

If you still find no resultion, you could email a copy of your workbook, or else I could rip a quickie together and forward to you.  Whatever.

d64471023a@hotmail.com

Dave
0
 
LVL 5

Expert Comment

by:TigerMan
ID: 2546511
Mikerees,

Just spotted the problem with your last lot of code.  If you want the FileName to appear in your PrintPreview you will need to add:

ActiveSheet.PageSetup.CenterHeader = Range("A1")

to your Macro1() sub.  i.e:

Sub Macro1()

    ShowPath
    ActiveSheet.PageSetup.CenterHeader = Range("A1")
ActiveWindow.SelectedSheets.PrintPreview
End Sub


I need more caffeine when answering qs this late!

Dave
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question