Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Can somebody help me with fixing my date modified code in VBA?

I have added a date modified code that was recommended by someone else in a forum somewhere.  It works... however now the month (mm) is missing, and when i formulated the font size, it shows up at 409pts??  Does anyone have an idea of what I might be doing wrong?
Code is shown below.

Or... is there a better way to do this?  My customer wants the date the file was modified to show up on the top of the sheet, in the header.  And it should only show the date that it was modified, not the current date that you open the file.

Thanks,
Toni
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    For Each ws In ActiveWorkbook.Worksheets
       ws.PageSetup.RightHeader = "&""Arial""&I&7" & Format(Now, "mm/dd/yy")
    Next
End Sub

Open in new window

0
herkie94
Asked:
herkie94
  • 4
  • 4
1 Solution
 
Patrick MatthewsCommented:
Hello herkie94,

I think you left out a space.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    For Each ws In ActiveWorkbook.Worksheets
       ws.PageSetup.RightHeader = "&""Arial,Italic""&7 " & Format(Now, "mm/dd/yyyy")
    Next
End Sub

Regards,

Patrick
0
 
herkie94Author Commented:
Patrick,

Where do you see the extra space?  I made the adjustments similar to yours, but did not see an extra space.

Should I post the document?

Thanks,
Toni
0
 
Patrick MatthewsCommented:
Toni,

>>Should I post the document?

Sure!

Patrick
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
herkie94Author Commented:
Here it is.  It also won't let me get to the footer now... this is weird.
Price-Sheet-Date-Modified.xls
0
 
herkie94Author Commented:
Another issue that came up after doing all of this... I can't access my header/footer anymore by going into a separate window.  Now Excel is showing it on the page rather than separately.  Is there something that I accidentally hit to make it do that? I want it to go back.
0
 
herkie94Author Commented:
Patrick,

Were you able to take a look at the file I uploaded?
0
 
Patrick MatthewsCommented:
herkie94,

My apologies for the lack of response.

Look at these two lines...

From your code:
       ws.PageSetup.RightHeader = "&""Arial""&I&7" & Format(Now, "mm/dd/yy")

from mine:
       ws.PageSetup.RightHeader = "&""Arial,Italic""&7 " & Format(Now, "mm/dd/yyyy")

Note how mine has a space following the 7, and yours does not.  When I make that correction in the code, it runs
just fine, as I indicated previously.  I just tested this against your sample file.

Patrick

Patrick
0
 
Patrick MatthewsCommented:
The suggestion I offered previously (http:#a24127036) answers the question.

Cheers,

Patrick
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now