Excel 97 (RESOLVED)

[Robbo]

Quick one, hopefully

how do i customiser the Header?

want &[Date] function but as Month year like FEB 05

remember this is in the custom header bit not on the sheet , just todays date displayed as this, cheers

[westconn1]

Dim dd As String
dd = Format(Date, "mmm yy")

hope this helps pete

[TheBionicOrange]

I think it takes the date format from your PCs regional settings for the Header/Footer.

[Robbo]

cant use VB, its in the Header footer part??, can i not use a format command or anything??

can anyone help

had a quick look, tried to change reginal settings however, it still does date month year, where i want month and year? as FEB 05, must be a way to do this??

thanks for your help

[Robbo]

hope this helps pete

Whos Pete?

[agmorgan]

Whos Pete?

Unless I missed the sarcasm and a joke....
Very probably westconn1

As in

Hope this helps.
Best Regards,

Pete

Rather than

Pete, I hope this helps you.

[Robbo]

anyway back to my problem

it still does date month year, where i want month and year? as FEB 05, must be a way to do this?? in the custom header??

thanks for your help

[dglienna]

I looked into it and didn't find anything. Maybe you could use another program to do the reports? Seems like a lot of work. I'd have thought that it was possible, but it doesn't seem like it.

[Robbo]

I looked into it and didn't find anything. Maybe you could use another program to do the reports? Seems like a lot of work. I'd have thought that it was possible, but it doesn't seem like it.

Excel? its a spreadsheet that need printing out every month, with the date on the top

if you cant help doesnt matter, is there anyone out there that has done this, im sure ive seen a spreadsheet with a bit of formatting before but cant find it

cheers

[dglienna]

Maybe you could record a macro that used SendKeys to type a date in the heading before it is printed?

[Robbo]

how do i do that?

[dglienna]

record a macro of you manually typing in the code, and then edit the macro (which will be VBA code). where you are typing, substitute the sendkey commands.

i think you'd be better off computing the formatted data into a cell, then setting it into a string, so that you could send the whole string at once.

I'm pretty sure that you can tell sendkeys to send a string, but I've only seen it used to send quoted text. Yup. You can. I just looked at an example that sent the numbers 1-100 to calculator to add up.

That should work well.

[Robbo]

record a macro of you manually typing in the code, and then edit the macro (which will be VBA code). where you are typing, substitute the sendkey commands.

i think you'd be better off computing the formatted data into a cell, then setting it into a string, so that you could send the whole string at once.

I'm pretty sure that you can tell sendkeys to send a string, but I've only seen it used to send quoted text. Yup. You can. I just looked at an example that sent the numbers 1-100 to calculator to add up.

That should work well.

havent a clue what your talking about, yeah know about macros but unsure what you mean about sendkeys?, want the date to appear in the customer header automatcally, if you can do that would u do on a template and zip up? so i have something to work with?

[dglienna]

Phase I - Record Macro to type it in

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/3/2005 by David Glienna
'
 
'
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "Feb 05"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = -3
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With
End Sub

You may be able to change the .Center Heading right there, before you run the macro. Now I'm thinking that it can be done programmatically. If not, just use send keys to enter the information that I typed. That is not hard.

You may have to use a VB program to alter the worksheet before you print it, but you could even print it from your VB program

[dglienna]

I couldn't get it to run as a VBS script, but that's not my area of expertise.
We have to do some work to get it to run from outside of Excel.

One thing that I noticed is as soon as I typed it in the header advanced, then it became a drop down in the normal header selection form.
Feb 05 was there right under Page # Date and the rest.
If you could find *that* you could change it easier.

I would post it in the VBA forum. RobDog888 prolly has a solution.

[Robbo]

ok we are getting somewhere now

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/3/2005 by David Glienna

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.RightHeader = "FEB 05"
End With
End Sub

need this to display the month it currently is, so how do i do that?

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/3/2005 by David Glienna
Dim CurrentDate as String

CurrentDate = Format(DATE, "MMM YY")

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.RightHeader = CurrentDate
End With
End Sub

how do i set up the font and size stuff, needs to be the same as the others which will be default in left and centre?

thanks

oh and how do i get to run when the sheet is opened, baring in mind that there are 4 other sheets in the workbook

[Robbo]

I couldn't get it to run as a VBS script, but that's not my area of expertise.
We have to do some work to get it to run from outside of Excel.

One thing that I noticed is as soon as I typed it in the header advanced, then it became a drop down in the normal header selection form.
Feb 05 was there right under Page # Date and the rest.
If you could find *that* you could change it easier.

I would post it in the VBA forum. RobDog888 prolly has a solution.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/3/2005 by David Glienna
Dim CurrentDate as String
 
CurrentDate = Format(DATE, "MMM YY")
 
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.RightHeader = CurrentDate
End With
End Sub

this is working but need to auto start?, also there are 4 sheets in the workbook?

[dglienna]

Just name it Auto_Open and it will auto-start when the WorkBook is opened.

My workbook had 3 sheets in it, so I don't think you have to do anything for 4 sheets.

If it doesn't work for all sheets, just re-record it for all 4 sheets, and name that the Auto_Start macro that does it all!

Hope that wraps things up. I guess we did it ourselves!

[Robbo]

Just name it Auto_Open and it will auto-start when the WorkBook is opened.

My workbook had 3 sheets in it, so I don't think you have to do anything for 4 sheets.

If it doesn't work for all sheets, just re-record it for all 4 sheets, and name that the Auto_Start macro that does it all!

Hope that wraps things up. I guess we did it ourselves!

Auto_Open didnt work, still have to run macro, also it only does the header on the active sheet want on each of the 4 sheets with formating of the font, so not quite finished yet?

[dglienna]

Can't find anything definitive in Excel. Now I saw Auto_Activate. Going to search on the web.

[dglienna]

Ok. It seems that you have to call it from VBA. Here is a link.

http://msdn.microsoft.com/library/de...HV03076909.asp

[Robbo]

ok

Sub Auto_Open()
'
' Macro1 Macro
' Macro recorded 03.02.2005 by John.Robinson
'
 
'
Dim CurrentDate As String
 
CurrentDate = Format(Date, "MMM YY")
 
Sheets("D1 Survey").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .RightHeader = CurrentDate
    End With
    
Sheets("D2 Survey").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .RightHeader = CurrentDate
    End With
 
Sheets("D4 Survey").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .RightHeader = CurrentDate
    End With
    
Sheets("D5 Survey").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .RightHeader = CurrentDate
    End With
    
Sheets("D1 Survey").Select
End Sub

got this working for all the sheets, just need the font bit sorting out and the auto start and then we are done

[dglienna]

Workbooks.Open "ANALYSIS.XLS"
ActiveWorkbook.RunAutoMacros xlAutoOpen

[Robbo]

ok

got this now

Sub Auto_Open()
'
' Macro1 Macro
' Macro recorded 03.02.2005 by John.Robinson
'
 
'.RightHeader = "&""Arial,Bold""&14FEB 05"
Dim CurrentDate As String
 
CurrentDate = Format(Date, "MMM YY")
 
Sheets("D1 Survey").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .RightHeader = "&""Arial,Bold""&14" & CurrentDate
    End With
    
Sheets("D2 Survey").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .RightHeader = "&""Arial,Bold""&14" & CurrentDate
    End With
 
Sheets("D4 Survey").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .RightHeader = "&""Arial,Bold""&14" & CurrentDate
    End With
    
Sheets("D5 Survey").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .RightHeader = "&""Arial,Bold""&14" & CurrentDate
    End With
    
Sheets("D1 Survey").Select
End Sub

its formatting how i want it, just need to run when workbook is opened?

will have a try with

Workbooks.Open "ANALYSIS.XLS"
ActiveWorkbook.RunAutoMacros xlAutoOpen

as example

[dglienna]

Hope it works...

[Robbo]

cant get any of that to run from open of spreadsheet

Workbooks.Open "ANALYSIS.XLS"
ActiveWorkbook.RunAutoMacros xlAutoOpen

is more to do with Workbook Object from VB6, need excel VBA

[dglienna]

I think I found something...

http://support.microsoft.com/kb/157346/en-us

If an Auto_Open, Auto_Close, or other automatically-running subroutine is stored "behind" a worksheet or ThisWorkbook, it may not function correctly when you open or close your workbook, or when you perform an action that should cause the subroutine to run.

[Robbo]

ive put it in a module and it works great, thanks alot, will put resolved in the header

thanks again bye

[dglienna]

Great. Glad it works.