Saving the revised date for any workbook.

[Still_learning]

Hi,

I know by using a before save event you can update the revised date. The
revised date is held in the left footer.

Is there a way to have the before save event placed some where, so that any
workbook opened (new or old) would trigger the event but would update the
revised date in only the workbook that was being saved.

 

[Patrick Molloy]

how about using the workbook's OPEN event to revise the last edited date?

 

[Still_learning]

Hi Molloy,

Revising the date is not the problem. Let me explain what I’m doing first. I
made a global workbook that works like VBAProject (PERSONAL.XLS). It allows
any excel workbook that is open by the user (new or old) to access it stored
macro for uses. I made a button that actives a given macro for user convent
(and to keep them from the code). One of the steps in this macro retrieves
and places the revised date in the left footer of one 1 or all sheet (on the
day the user clicks the button). Now I need something that will update the
revised date but only when a given user saves the workbook, I have found that
the before save event is perfect except that it is stored to a local
workbook. Thus before save event works for only given workbook that it has
manual been placed in and I can’t have the users doing this.

I’m thinking 1 of 2 things:
1 There is some where to place the before save event to make it a global
event.
2 A condition that will be trigger when the footer button is clicked to
setup or create the before save event in the workbook that the above footer
as being placed in.

If any one knows a solution pleases help.

 

[Patrick Molloy]

yes, there is a "global" event that you can use - and it appertains to the
Excel application.
Let's build it here:

so go to the development environment

to Personal.xls add a CLASS MODULE and name it XLclass
(menu Insert / Class Module)

place this code into the class module

Option Explicit
Public WithEvents xl As Excel.Application
Private Sub Class_Terminate()
Set xl = Nothing
End Sub
Private Sub Class_Initialize()
Set xl = Excel.Application
End Sub
Private Sub xl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
Boolean, Cancel As Boolean)
MsgBox "Saving " & Wb.Name
End Sub

we need to add some code to initialise this object, so add a standard code
module (Insert/Module) and add this code:

Option Explicit
Public xl As xlClass
Sub InvokeXL()
Set xl = New xlClass
End Sub

and now we need to run this as soon as Personal.xls opens, so we'll use the
workbook's open event to do this.
Go to the code page for ThisWorkbook (in the personal.xls project) and add
this code:

Private Sub Workbook_Open()
InvokeXL
End Sub



How does it hang together?
When the workbook is opened, the open event call InvokeXL.
InvokeXL creates the xlclass object and by doing to, the object, as its
global, is now ready and running. whenever a workbook is saved, the
application level WorkbookBeforeSave event fires, and you'll get the message
for which workbook is being saved. Thats just for demo ...but you know that
the workbook object, WB refers to that particular workbook.
You can add code like
WB.Worksheets("main").Range("A1")=format$(Date,"dd-mmm-yyyy")

using the application class is a nice "tool" for this kind of trapping -
though I've not often seen it used in business. shame