PDA

View Full Version : Sleeper: Header Drop Down Box



Jane
05-13-2005, 11:44 AM
Hello, Everyone:

Could anyone please tell me how to add new information in the Header drop down box in the Header/Footer of Page Setup.

Thanks!

Bob Phillips
05-13-2005, 12:52 PM
Here is some code that puts the current date in the left header


Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftHeader = Format(Date, "dd mmmm yyyy")
End With
End Sub

This is workbook event code. To input this code, right click on the Excel icon on the worksheet (or next to the File menu if you maximise your workbooks), select View Code from the menu, and paste the code

Jane
05-13-2005, 01:50 PM
Here is some code that puts the current date in the left header


Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftHeader = Format(Date, "dd mmmm yyyy")
End With
End Sub

This is workbook event code. To input this code, right click on the Excel icon on the worksheet (or next to the File menu if you maximise your workbooks), select View Code from the menu, and paste the code

Hello, Xld:

Thank you for your answer.

As a matter of fact, I wand to repeat a logo picture, which is in the first row, on my report. The report is a quite long one with landscape orientation.

Since I cannot do it by using the function ? repeat rows at the top, I tried to add a new information (the logo picture) into the header drop down box.

Attached please find the test spreadsheet.

By the way, how could I attach a document with quick reply function.


Thank you!

Bob Phillips
05-13-2005, 02:01 PM
Since I cannot do it by using the function ? repeat rows at the top,

Why not? That is the standard way of adding logos in headers in Excel.

Jane
05-16-2005, 06:51 AM
Why not? That is the standard way of adding logos in headers in Excel.

Hello, Xld:

I tried again but it didn't show up on print copy. I attached one spreadsheet with information copied from your reply and the date doesn't show up either.

Would you please check to see what I did wrongly?

Thank you very much!

Bob Phillips
05-16-2005, 07:04 AM
I tried again but it didn't show up on print copy. I attached one spreadsheet with information copied from your reply and the date doesn't show up either.

Hi Jane,

I opened your book and noticed no repeating rows were setup. You need to
File>Page Setup>Sheet and insert $1:$1 in Rows to repeat.

I also noticed that you have breaks in the columns, so you need to copy the logo to the head of those pages, E1 in the example.

Jane
05-16-2005, 08:10 AM
Hello, Xld:

I understand what you said. I know the function of repeat the same row at the top. Since my spreadsheet is quite long with landspread orientation and I don't like to use the copy and paste function to repeat the same logo picture on the first row in different cells: E1, I1, M1 and etc; I am seeking some other options to get the same result.

I tried the visual basic and put date format you suggested in the spreadsheet attached. Just want to see if there is date showing on the print copy. It seems no.

Would please send me a sample of spreadsheet with a logo or picture (any kind)?

Thank you very much

Bob Phillips
05-16-2005, 09:09 AM
my spreadsheet is quite long with landspread orientation and I don't like to use the copy and paste function to repeat the same logo picture on the first row in different cells: E1, I1, M1 and etc; I am seeking some other options to get the same result.

Don't understand.


I tried the visual basic and put date format you suggested in the spreadsheet attached. Just want to see if there is date showing on the print copy.

What visual basic? What date? Are we talking about the same thing?

When I copied th logo to E1, and set the repetaing heading rows, I got the logo on each page.

Jane
05-16-2005, 10:01 AM
Don't understand.



What visual basic? What date? Are we talking about the same thing?

When I copied th logo to E1, and set the repetaing heading rows, I got the logo on each page.

Hello, Xld:

I know how to do the function of repeat the same row at the top. But I don't want to use this function on my spreadsheet, which is very long with landscape (20 pages). I am trying to find other option to get the same result.

Please open my attachment and press Alt+F11, you will see I tried your suggestion to see if the date will show on the print copy. But it doesn't. If it does. I might save the logo in a file to do it.

So I asked if you could send me a sample with the other option which could solve my problem.

Thank you very much!

Bob Phillips
05-16-2005, 10:48 AM
You could try this.

Delete the logo on the worksheet.

run this code



With ActiveSheet.PageSetup
.LeftHeaderPicture.Filename = _
"C:\Documents and Settings\bob\My Documents\My Pictures\ES-2003-02-01%20Front.jpeg"
.LeftHeader = "&G - &D"
End With

with the filename of your logo file

Jane
05-16-2005, 12:00 PM
You could try this.

Delete the logo on the worksheet.

run this code


With ActiveSheet.PageSetup
.LeftHeaderPicture.Filename = _
"C:\Documents and Settings\bob\My Documents\My Pictures\ES-2003-02-01%20Front.jpeg"
.LeftHeader = "&G - &D"
End With


with the filename of your logo file

Hello, Xld:

I tried your vba suggestion, but it doesn't work. Please see the attachment.

Thank you very much!

Bob Phillips
05-16-2005, 12:33 PM
I tried your vba suggestion, but it doesn't work. Please see the attachment.

Do you have Excel 2000 or earlier? If so, it won't work I am afraid.