Replies

Thanks Hans and Rudi

Having to put the code in each sheet would be a bit of a drag, but Rudi’s code works beautifully.

Will someone explain to me what the (ByVal Sh As Object, ByVal Target As Range) does?

Sorry people.

My attachment did not have a Date Accessed in cell A2, this was because my original development Workbook is at work and I will not have access to it for two weeks, so I had to start a new one and I forgot to put a date in A2, in my original book this is done with a workbook open macro.

Apologies. This attachment is correct

Thanks for the help everyone. I’ll deal with the points raised in turn.

The code is exactly as I put it in post 683787, which I believe is a ‘before save’. Also the macro is partly executing when I save as I explained in post 683809, it is putting the date into the cell to the right of the selected cell, it just is not moving the selected cell to the end of Column A

The code is in the ‘ThisWorkbook’ object; not having in there was my early error but Hans put me right on that in post 683772.

Legare, I am not clear what the ‘the module behind the ThisWorkbook object’ is, I double clicked on ThisWorkbook in the Project Window and pasted the code in there, if I try to add a module whilst in ThisWorkbook VBA adds a module into the Modules, not into theObjects/ThisWorkbook.

I think adding user information is a good idea too.

Thanks for that Steve.

I still find it puzzling that the selected cell does not change when the code is run as a WorkbookBeforeSave macro but it works perfectly as an ordinary macro.

Incidentally is it possible to step through a WorkbookBeforeSave macro?

This is to be a log of accesses and saves of this workbook, people will rarely be in the UsageLog sheet when they save so I made the macro go to the UsageLog sheet and the appropriate cell to enter the save date and time.

The offset is correct, the idea is to go to the last used cell in Column A, which is the list of accesses and contains today’s date from a workbook open macro, then to update Column B, which contains the Date Saved data, with the date and time of saving, overwriting any previous saves made today.

Yes it is in ThisWorkbook and it works in that it writes the date and time in the cell to the right of whatever cell is selected at the time the save is made (I have been in sheet UsageLog during all the testing, just selecting different cells before I save), it just does not move the selection to the last cell of Column A before doing the writing.

This code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo ErrorHandler

ActiveWorkbook.Sheets(“UsageLog”).Select
MsgBox (“UsageLog Sheet Exists”)

‘goto last used cell in col A, should be today’s date, inserted when the workbook was opened
Range(“A65536”).End(xlUp).Select
MsgBox (“Cell (A, Last) should be selected”)
ActiveCell.Offset(0, 1).Value = Date & ” ” & Time ‘overwrite any previous saves

ErrorHandler:

End Sub

Does not work, the selected cell remains whereever it was before the workbook is saved, but when the same code is run as an normal macro all proceeds as intended with the selected cell going to the end of Column A

Thank you Hans

Thanks to you all for your efforts. However I have put both Jan’s and Hans’s code into a module without success. So I tried the following

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox (“Saving File”)
End Sub

I put this in a code module in my workbook, then saved the file but my message box did not appear, is there somthing more I need to do?

TIA

Thank you Hans

Only being able to access the info when the file is closed is a very useful feature.

I am not familiar with user defined functions, am I doing this right, I copied the function into a module in my workbook then in a cell in my workbook I typed:

=GetProperty(ActiveWorkbook, 4)

and I get a type mismatch error (same when I put the full path instead of ActiveWorkbook)

Thank you Hans

I will check it out.

Thank you Jan and Andrew

Works perfectly

Many thanks Hans

Thank you John.

Seems like Microsoft doing what it does best.

Thats great.

Thanks yet again Hans

Playing around with the problem some more, I think the macro is working from the size of the original chart, which may not be identical for all my charts. Is it possible to specify the sizing to be 5 columns and 20 row, aligning with the gridlines in the new sheet.

Thanks.