Replies

Thank you Hans

It seem to me that you should be using the Worksheet_Change event on each sheet fire your macro. Passing the Target properties to a macro in a standard module should do the trick.

My worksheet does have a Worksheet_change event to fire the macro (or a comment update macro for a cell that already has a comment), that part works fine; I did not show that macro as it is working as intended. The only problem is if the user enters new data in the cell then, without pressing Enter, uses the mouse to select another cell , the data appears in the cell that was changed alright but the comment appears in the newly selected cell. I think this must be because rowInt and colInt are set by reference to the ActiveCell, what I need is a way of setting rowInt and colInt so they refer to the ActiveCell at the start of the macro and do not alter when a new ActiveCell is selected.

Thanks to everyone for your help.

I am having trouble now in concatenating text and date whilst using the overwrite option; suppose I want to add “Cell updated on” and the date and also tell VBA not to overwrite the existing comment, what should the syntax be?

su

I had not thought of this as we want the usage log to see the workbook’s history, who has used it, who has altered it (I intend to add a column recording the last change made to the book, when I have the open and save bits working properly) etc. However I think you are right, it could be useful to know when someone has just looked at the book and then closed it without saving it, I added the following lines to the EnterOpenData macro after the writing of the opening date/time and username and before the reprotection of the sheet.

Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True

This seems to work OK, if the workbook is closed without saving there are blank cells alongside the opening data.

Thanks to you all for your advice.

I tried out the book on another machine at work with the same XL version as mine and it worked (should have thought of that earlier).

The problem was a utility called Saver that I installed years ago that puts the full path and filename in the Caption when a file is saved, I removed this and the problem went away.

Thanks again everyone for your efforts.

Hans

I have now tried your workbook in XL2000 SP3 and it crashes at the save step there too unless I manually unprotect the sheet first.

Hans

I am at home using XL2000 SR1 and your workbook opens alright for me but crashes when I try to save it.

Is there someone else using XL2000 who can try Hans’ workbook and see if they can save it?

Thanks to all.

Hans
You are right of course, I had used the colon to join the Protect call with its MsgBox so they could be remarked out together, without realising VB would think it indicated a nonexistant destination.

However here is a version with that error removed and the problem persists.

My theory that Protect does not work in XL2000 SP3 is wrong. Attached is my workbook with macros that Protect and Unprotect the worksheet, and they work fine as long as they are run alone. But when they are called from the other macros, FormatUsageLog, EnterOpenData, or EnterSaveData they seem to do nothing. If the worksheet is unprotected before it is saved everything is fine, but if the worksheet is protected I get Error 1004. Can anyone tell me what is going on?

Hans and Steve

The problem arose at work where I use XL2000 SP3; at home I have XL2000 SR1 and the code works fine.

I will not be at work again until next week but I will post the workbook then.

However suppose my theory is right and there is a glitch in 2000 SR3, if I send a workbook with a macro written with 2000 SR3 and someone with a different version of XL opens it and runs it will not the macro run with the VBA in that person’s version so if that version is glitch-free the problem will not show up? Or am I misunderstanding how macros, workbooks and versions interact?

Thank you Hans

I am not using a password so that is not the problem. What version of XL are you using? The only think I can think of is that Protect is one of those things that does not work in some versions of XL ( I think TRIM was another though I cannot remember which version it was). If anyone else is still using 2000 SP3 so they could check this.

Thanks again

Thanks to you both

Jan

It did not work when I first made the changes but now it seems to be doing so. I will experiment further.

I can see a further problem though, this is to be a template and if I use Workbooks(“UsageLogTest.XLS”) in writing in the time saved etc, this presumably will not work when the workbook is saved with a new name.

Many thanks

Jan

Thanks for your reply.

I do not think that is the problem as I tried what you suggested and the error persists. I also made a copy of SaveWithoutSaver, stored it in the test book and used that macro to save so I am not changing workbooks at any stage (my original SaveWithoutSaver was in Personal.xls) and that did not get rid of the error either

(Edited by HansV to make URL clickable – see Help 19)

Thank you Hans

I could not find a listing of FaceId’s in help, however this site has them http://www.j-walk.com/ss/excel/tips/tip67.htm%5B/url%5D