Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

EXCEL 97-VBA - CUSTOM HEADER/FOOTER

Posted on 2000-02-18
6
Medium Priority
?
589 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

782 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