Is there a way to auto date stamp anytime cells are edited in a protected sheet?

[tobink]

Hello,

I created a spreadsheet that needs to allow multiple users to add, edit, delete data in ceratin cells, but not be able to change the formatting. In order to accomplish this, I unlocked the cells that can be manipulated, then protected each sheet. Now, however, the auto date macro is not working and keeps popping up a debug option. Is there a way to have this macro run in a protected sheet? Here is the code I have (it works when not protected):

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A4:W155")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("A1").Value = Now
Application.EnableEvents = True
End Sub

Thanks for the help!

 

[p45cal]

Just realized after working in the spreadsheet a few minutes, that without the password added when I protect the sheet, I can't insert/delete rows or sort?? In the formatting for the unlocked cells, i have the boxes checked for insert row, delete row, sort...but it doesn't work with it protected. Sooo frustrating!

I don't think it's whether the password applied or not that affects what you can do, nor is it the format of unlocked cells, more it's the choices you apply in the Protect Sheet dialogue box when you're protecting the sheet.
If you record yourself a macro of you protecting the sheet the way you want with the options you want you'll end up with a variation of the following line:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True

Now you need to tweak that line for use in the worksheet_change event handler above to a bit like:

[COLOR=#ff0000]Me[/COLOR].Protect [COLOR=#ff0000]Password:="verysecretpassword"[/COLOR], DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, [COLOR=#ff0000]userinterfaceonly:=True[/COLOR]

And obviously, adjust the password before you run the code.
This line will execute whether the sheet is already protected or not.