Help with cell protection

[JonWhite123]

Hi, I really hope someone can help me with this one.

I'm trying to invent a spreadsheet where the users enter a timestamp when they have done their task. Everything works fine, and the spreadsheet is shared. Problem is, I think they are cheating! So I protected each sheet with a password and locked the cells. I changed my macro so it unprotects the sheet, does the timestamp and then protects it again - everything works.

The problem: It doesn't work when shared. The spreadsheet needs to be shared as more than one user update it.

Help! Anyone know what I can do? I need to protect the cells in the shared workbook so they cannot go back and change the time afterwards, but keep it shared.

Thank you in advance, you kind and wise bunch!

 

[TK_Hussar]

Hi Jon
If you are using a Web Folder to share your spreadsheet then this may be the problem. If you use a Web Folder for sharing spreadsheets then you lose some functionality within Excel. I have been unable to find out exactly what functionality is affected or lost.
I have had similar problems in the past.
However if you are using a normal folder on a server then what you are trying to do should work fine I believe.
TK_Hussar

 

[JonWhite123]

Thanks for your reply TK. I am hosting the 2003 s/s on a sharepoint site, then copied to a shared network drive and renamed.

I think the is simply that Excel 2003 won't allow you to unprotect a sheet within a workbook if the workbook is shared. It works fine if you unshare it first. The problem I have is the s/s needs to be shared to allow more than one user to update it.

It may be I'm trying to do something that Excel 2003 is incapable of doing OR.. there is a much better way than how I'm currently doing it.

Any thoughts much appreciated.

 

[TK_Hussar]

Hi Jon
Is this not something you could use MS Project for if you are looking to find out when people have finished their tasks.
There is a freely available routine in MS Project that creates a spreadsheet for each resource in a projecty plan. They update the spreadsheet at regular intervals and then you can automatically update project and print out a report. The beauty of this routine is you cannot update the same date range twice.
Let me if this will help and I can send you further details.
TK_Hussar

 

[JonWhite123]

Thanks again for your reply. I had a feeling that maybe excel wasn't the best way to do this, and from what you say it isn't!

I'll have a look into MS Project. As it's a work thing there may be costs and the like, but I think it would be worth it.

Thank again.
Jon

 

[TK_Hussar]

Hi Jon
You would only need one single user copy of Project standard to achieve this functionality as separate excel spreadsheets would be created for each user.
TK_Hussar

 

[Jonmo1]

Yes it's true, you can't use code to protect/unprotect sheets on a shared book. However, I think I saw a thread a while ago where someone figured a way around that. It involved using some advanced code to Unshare the sheet - Unprotect it - let user do stuff - reprotect it - reshare it.

I don't remember when or where, but if you search the board you might find it. Search for Shared and Protection.

But, besides that difficulty. Consider putting your timestamps in a different book.

Other than that, I'm of the general opinion that, Cheaters are going to cheat no matter what you do. You may be better off devising a way to catch the cheaters (rather then prevent them from cheating). And if they continute to cheat, remove them from your project.