Solved

EXCEL 97-VBA - CUSTOM HEADER/FOOTER

Posted on 2000-02-18
6
578 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:mikerees
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
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.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now