Message when exiting

[marcellosanto]

I added a cell with current time and date. When I close the spreadsheet I want the current time and date to be displayed and fixed (not change when reopened) on a message box and the contents of this box be embedded somewhere in the spreadsheet. I tried several ways and could not do it. If a VBA macro is needed, please give me the step by step operation for I am not an expert user like others here. Thanks.

[Richard Buttrey]

I added a cell with current time and date. When I close the spreadsheet I want the current time and date to be displayed and fixed (not change when reopened) on a message box and the contents of this box be embedded somewhere in the spreadsheet. I tried several ways and could not do it. If a VBA macro is needed, please give me the step by step operation for I am not an expert user like others here. Thanks.

Yes a simple bit of VBA macro code will be required. We'll need to include this in the Workbook save event - I assume you want to keep the date/time when you save rather than just close.

Open up the Visual Basic Environment (VBE) by pressing the ALT-F11 keys.

Over on the left hand side you should see a window headed "Project - VBAProject. If you don't, just click the View Menu and choose the 'View Project Explorer option.

Now in the list of open workbooks, sheets etc, find your workbook name, (the one with an Excel icon next to it) and double click it.

You will find that a big window opens on the right, (if it's not already open).

In the top left of that window there is a drop down box which is defaulting to the word 'General'. Click the drop down arrow and select the Workbook object. A couple of lines of code will open Private Sub Workbook_Open(), and an End sub. You don't want this default Subroutine so in the right hand drop down box, choose the 'Before Save' event.

This will open another couple of lines of code and you should enter the following code in between the two lines Private....End Sub

This assumes your Date/Time formula is in B5, and you want the saved Date/Time to be in C5. Change and format as appropriate.

Please Login or Register  to view this content.

Rgds