Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

EXCEL 97-VBA - CUSTOM HEADER/FOOTER

Posted on 2000-02-18
6
Medium Priority
?
587 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 5

Accepted Solution

by:
TigerMan earned 150 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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 …
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…

718 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