Adding custom Header/Footer to Default Listbox (Excel 97)

Topic

New Reply

Hi all,

I have a workbook in which I add every week a new sheet. Contents of each sheet differs, so, in order to print it, I have to do a File|Page Setup every week again.
The Footer I want to have on each sheet has the same structure : some standard text (same each week) + the info that is on the TAB of the sheet. This goes on the left of the page, on the right of the page it has to be : Page + “Pagenumer” of “Number of pages”.

But this Footer is not one that appears in the “Default Footers Listbox”. How can I add my “custom footer” to that “Default List” ?

I have looked through the other threads, but none gave my the answer.
Can anyone help me ?

Best regards,

Walter

Reply | Quote

Replies

in the page-setup dialog, where you see the list of default options, you should also see a “Custom” Button – click on this and you’ll be able to add your own – sample attached (from 2K but from memory it is the same)

Reply | Quote

in the page-setup dialog, where you see the list of default options, you should also see a “Custom” Button – click on this and you’ll be able to add your own – sample attached (from 2K but from memory it is the same)

Reply | Quote

Did you try this post 271544

Steve

Reply | Quote

Did you try this post 271544

Steve

Reply | Quote

My apologies Walter – looking at Steve’s response and the post He links to, I believe I may have misinterpreted your question !

Reply | Quote

Well, Steve’s response and the post he links to, is actually what I mean.

I really appreciate your answer (post 363124) , but I knew about the possibility of “making” a custom Header/Footer.
But what I really want is to “save” my “custom-built” Header/Footer, so that it is available in the “drop-down list” the next time I want to setup a page for printing.

Looking at the thread Steve’s answer is in, I must admit I overlooked this one (most probably because it referres to Excel 2000 SR1, and I still use Excel 97).

In Steve’s thread, the post (275206) from Satiria describes what I want, but that thread ends with a post (275248) pointing to a VBA solution. This VBA solution was not posted, so I am still stuck. Anyone who can give me a step-by-step guide-through with a VBA solution ?

Thanks and best regards,

Walter

Reply | Quote

The post I referenced tells how you can add the footer to the default book/sheet and then that default footer would be in the custome list.

The VB solution will not add it to the custom list. Based on what you want it to do you could add this to your personal.xls file, add it to a toolbar and call it anytime
Change the “standard text as desired

Sub CustomFooter()
    With ActiveSheet.PageSetup
        .LeftFooter = "Standard Text &A"
        .RightFooter = "Page &P of &N"
    End With
End Sub

For info on Personal.xls see the LegareColeman‘s starpost 118382

Steve

Reply | Quote

Thank you Steve,

I added the macro to my Personal.xls and also added a button to my “private toolbar”.
Now I just have to click the button and the Footer I want is inserted.

But this made me thinking: are there more printsettings that I could add to the macro ? As all the sheets in this specific workbook always have to be printed in Landscape, can I put this in the same macro ? What is the code ?

Can I define a range in each sheet that I can use to indicate the printrange, and only use the range name in the print settings ? And can this be added to the macro ?

Yep, one solution leads to more questions …..

Can you put me on the right track ?

Best regards,

Walter

Reply | Quote

The best way to learn the objects is to use the macrorecorder (tools – macro – record new macro)

Then while recording, run page setup, and set the options. when done, stop the macro and look at the code in VB. This will give you the code (and more than you want/need!)

Note, when you use the macro recorder, any dialog you open with the recorder running, all the options (whether you change them or not) will be recorded. It should be “relatively obvious” to you what the items are, dlete the onew you do not want to change/define and keep the things you do.

If you have questions on them, while in VB select the property/method name and press and you will go to help on that item. You may also post more questions here for additional clarification if required.

Once you understand, you can even add to the code to generalize it, prompt the user for items (you could use a message box to ask about landcape/portrait directly at runtime.

Hope this helps,

Steve

Reply | Quote

Thanks again Steve,

I just recorded a macro with the page-setup settings I usually do for this specific workbook. I looked at the code that was generated, and I think I can use quite a lot of it in my application.

What you said about “Once you understand, you can even add to the code to generalize it, prompt the user for items (you could use a message box to ask about landcape/portrait directly at runtime.” is not clear to me. Could you give me a hint here ?

Best regards,

Walter

Reply | Quote

I was making a general statement. If you want to do something with the code that the macrorecorder does not handle, you will have to be specific about your want/need and we can try to answer it.

I wanted you to be aware that the recorder was only a starting point. We can answer any specific questions, but without an idea of what you want to do with it, we can’t give any general solutions.

Steve

Reply | Quote

OK, Steve!

When I have a more specific question, I will come back with a new post.
In the meantime, thanks for your help.

Best regards,

Walter

Reply | Quote

OK, Steve!

When I have a more specific question, I will come back with a new post.
In the meantime, thanks for your help.

Best regards,

Walter

Reply | Quote

I was making a general statement. If you want to do something with the code that the macrorecorder does not handle, you will have to be specific about your want/need and we can try to answer it.

I wanted you to be aware that the recorder was only a starting point. We can answer any specific questions, but without an idea of what you want to do with it, we can’t give any general solutions.

Steve

Reply | Quote

Thanks again Steve,

I just recorded a macro with the page-setup settings I usually do for this specific workbook. I looked at the code that was generated, and I think I can use quite a lot of it in my application.

What you said about “Once you understand, you can even add to the code to generalize it, prompt the user for items (you could use a message box to ask about landcape/portrait directly at runtime.” is not clear to me. Could you give me a hint here ?

Best regards,

Walter

Reply | Quote

The best way to learn the objects is to use the macrorecorder (tools – macro – record new macro)

Then while recording, run page setup, and set the options. when done, stop the macro and look at the code in VB. This will give you the code (and more than you want/need!)

Note, when you use the macro recorder, any dialog you open with the recorder running, all the options (whether you change them or not) will be recorded. It should be “relatively obvious” to you what the items are, dlete the onew you do not want to change/define and keep the things you do.

If you have questions on them, while in VB select the property/method name and press and you will go to help on that item. You may also post more questions here for additional clarification if required.

Once you understand, you can even add to the code to generalize it, prompt the user for items (you could use a message box to ask about landcape/portrait directly at runtime.

Hope this helps,

Steve

Reply | Quote

Thank you Steve,

I added the macro to my Personal.xls and also added a button to my “private toolbar”.
Now I just have to click the button and the Footer I want is inserted.

But this made me thinking: are there more printsettings that I could add to the macro ? As all the sheets in this specific workbook always have to be printed in Landscape, can I put this in the same macro ? What is the code ?

Can I define a range in each sheet that I can use to indicate the printrange, and only use the range name in the print settings ? And can this be added to the macro ?

Yep, one solution leads to more questions …..

Can you put me on the right track ?

Best regards,

Walter

Reply | Quote

The post I referenced tells how you can add the footer to the default book/sheet and then that default footer would be in the custome list.

The VB solution will not add it to the custom list. Based on what you want it to do you could add this to your personal.xls file, add it to a toolbar and call it anytime
Change the “standard text as desired

Sub CustomFooter()
    With ActiveSheet.PageSetup
        .LeftFooter = "Standard Text &A"
        .RightFooter = "Page &P of &N"
    End With
End Sub

For info on Personal.xls see the LegareColeman‘s starpost 118382

Steve

Reply | Quote

Well, Steve’s response and the post he links to, is actually what I mean.

I really appreciate your answer (post 363124) , but I knew about the possibility of “making” a custom Header/Footer.
But what I really want is to “save” my “custom-built” Header/Footer, so that it is available in the “drop-down list” the next time I want to setup a page for printing.

Looking at the thread Steve’s answer is in, I must admit I overlooked this one (most probably because it referres to Excel 2000 SR1, and I still use Excel 97).

In Steve’s thread, the post (275206) from Satiria describes what I want, but that thread ends with a post (275248) pointing to a VBA solution. This VBA solution was not posted, so I am still stuck. Anyone who can give me a step-by-step guide-through with a VBA solution ?

Thanks and best regards,

Walter

Reply | Quote

My apologies Walter – looking at Steve’s response and the post He links to, I believe I may have misinterpreted your question !

Reply | Quote