Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving the revised date for any workbook.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving the revised date for any workbook.
how about using the workbook's OPEN event to revise the last edited date?
"Still_learning" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving the revised date for any workbook.
Hi Molloy,
Revising the date is not the problem. Let me explain what Im 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 cant have the users doing this. Im 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" wrote: how about using the workbook's OPEN event to revise the last edited date? "Still_learning" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving the revised date for any workbook.
yes, there is a "global" event that you can use - and it appertains to the
Excel application. Let's build it he 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 "Still_learning" wrote in message ... Hi Molloy, Revising the date is not the problem. Let me explain what Im 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 cant have the users doing this. Im 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" wrote: how about using the workbook's OPEN event to revise the last edited date? "Still_learning" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3rd and last Revised WorkBook | Excel Discussion (Misc queries) | |||
pulling information from a closed workbook (revised) | Excel Programming | |||
Exporting Only Filled Rows To Another Workbook (revised) | Excel Programming | |||
Format excel to revised date automatically when revised | Excel Programming | |||
IF Function with Date revised question | Excel Worksheet Functions |