Month and Year in Excel Header Mac

[excellence]

I currently have this &[Date] &[Time] in the right part of a footer on an Excel spreadsheet. I am trying to put a similar "formula" in the top left header of the same spreadsheet, but would like it to put in the current month and year automatically in the format Mmmm yyyy January 2024.

I have tried to modify my footer without success, though I believe this should be able to be done.
Thanks

 

[Peter_SSs]

I think that you will need to use vba to do that. You could try this in the ThisWorkbook module. Each time the workbook is opened the headers for each worksheet would be updated. Another option would be to put similar code in a Private Sub Workbook_BeforePrint procedure.

Private Sub Workbook_Open()
  Dim ws As Worksheet
  
  For Each ws In ThisWorkbook.Worksheets
    ws.PageSetup.LeftHeader = Format(Date, "mmmm yyyy")
  Next ws
End Sub

 

[excellence]

I think that you will need to use vba to do that. You could try this in the ThisWorkbook module. Each time the workbook is opened the headers for each worksheet would be updated. Another option would be to put similar code in a Private Sub Workbook_BeforePrint procedure.

Private Sub Workbook_Open()
  Dim ws As Worksheet
 
  For Each ws In ThisWorkbook.Worksheets
    ws.PageSetup.LeftHeader = Format(Date, "mmmm yyyy")
  Next ws
End Sub

Just wonder why it cannot be done the same way as the footer or is that just Excel doing what Excel does ) ?
As amateur VBAer, what addition code is necessary to bold the date?

I think that you will need to use vba to do that. You could try this in the ThisWorkbook module. Each time the workbook is opened the headers for each worksheet would be updated. Another option would be to put similar code in a Private Sub Workbook_BeforePrint procedure.

Private Sub Workbook_Open()
  Dim ws As Worksheet
 
  For Each ws In ThisWorkbook.Worksheets
    ws.PageSetup.LeftHeader = Format(Date, "mmmm yyyy")
  Next ws
End Sub

 

[Peter_SSs]

Just wonder why it cannot be done the same way as the footer

The built-in header/footer date formats are taken from your system date format settings.

what addition code is necessary to bold the date?

Private Sub Workbook_Open()
  Dim ws As Worksheet
  
  For Each ws In ThisWorkbook.Worksheets
    ws.PageSetup.LeftHeader = "&B" & Format(Date, "mmmm yyyy")
  Next ws
End Sub

 

[excellence]

many thanks

 

[excellence]

One additional point if I may.
I assume that after running this VBA it causes Excel to ask if I want to save the changes..that is the VBA puts in the month and date each time no matter if it was there when the Excel file was closed.

Is there a way for the VBA to ignore the date change, even if it were another month?
The reason for this is it makes me wonder if other changes were made that I should be concerned about. There would no way way of me knowing if the only change is the date or if I made other changes intentionally or unintentionally.
No concerns here if the date changes.

I guess. simply put, could the VBA ignore changes in the header date of this VBA?
Or more simply, have Excel ignore changes made by the VBA !!

 

[Peter_SSs]

Is there a way for the VBA to ignore the date change, even if it were another month?

No, because if the month has changed then the workbook has changed. If you close it without saving the changes then there is no point changing the header in the first place.

However, there are a few other options that might be considered. The most obvious one to me is that the above code sets the header immediately the workbook opens. If we then also immediately save the workbook then the only change saved (if any) would be if the headers changed. Then if nothing else was changed in the workbook it would close without you being asked about changes. Try replacing the code with this one, save the changes then ty opening/closing the workbook with or without making any changes yourself.

Private Sub Workbook_Open()
  Dim ws As Worksheet
  
  For Each ws In ThisWorkbook.Worksheets
    ws.PageSetup.LeftHeader = "&B" & Format(Date, "mmmm yyyy")
  Next ws
  ThisWorkbook.Save
End Sub

 

[excellence]

No, because if the month has changed then the workbook has changed. If you close it without saving the changes then there is no point changing the header in the first place.

However, there are a few other options that might be considered. The most obvious one to me is that the above code sets the header immediately the workbook opens. If we then also immediately save the workbook then the only change saved (if any) would be if the headers changed. Then if nothing else was changed in the workbook it would close without you being asked about changes. Try replacing the code with this one, save the changes then ty opening/closing the workbook with or without making any changes yourself.

Private Sub Workbook_Open()
  Dim ws As Worksheet
 
  For Each ws In ThisWorkbook.Worksheets
    ws.PageSetup.LeftHeader = "&B" & Format(Date, "mmmm yyyy")
  Next ws
  ThisWorkbook.Save
End Sub

Simply perfect
So many thanks again
I hope I checked off the correct boxes. If no please let me know the best way to get kudos for all your work.
Happy New Year

 

[Peter_SSs]

So many thanks again

You're welcome.

I hope I checked off the correct boxes.

Just marking the solution that suited you best by marking the tick at the right of the post to help future readers, as you have done, is all that is required.

 

[excellence]

Hi,
Was checking this and it doesn't seem to run automatically when the .xlsm file is opened. It will run if I put the cursor to the left of Private and click the blue arrow key in the toolbar to "run sub". Is there a way to make this run automatically on opening?
Must be something I haven't done.

Thanks

Private Sub Workbook_Open()
  Dim ws As Worksheet
 
  For Each ws In ThisWorkbook.Worksheets
    ws.PageSetup.LeftHeader = "&B" & Format(Date, "mmmm yyyy")
  Next ws
  ThisWorkbook.Save
End Sub