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]

check out protecting the sheet with userinterfaceonly:= true
This property holds only as long as the file is open and needs doing only once after the file is opened. It allows vba to make changes to a protected sheet.

 

[tobink]

Thanks for the reply! I am very new to this, so could you tell me where I am suppossed to enter the line item you are suggesting? Much appreciated!

 

[p45cal]

You could put it in the workbook open event, or just as a line before you want the code to change something on that sheet, it matters not that the line is executed multiple times once a workbook is open.
In your code above, perhaps:
me.protect userinterface:=true
Do you protect the sheet in the code? If so, use a similar line but add userinterfaceonly:=True to it.

 

[tobink]

I'm sure it is "user error", but I can't seem to get any of what you are telling me to work?! I am a newbie, so I'm sure I am trying to input in the wrong place or something.

I do not have a protect written in the code. If I were to do that, would I still have to unlock cells and protect sheets like I described above?

 

[p45cal]

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

 

[tobink]

Okay...so I entered as you showed above, but when you go to edit a cell, it pops up the "unprotect sheet" box asking for a password. If is enter the password, the auto date does work; however, I do not want to unprotect sheet. Does this mean that I need to add an unprotect and password to the code for it to work?

 

[p45cal]

Well, the cell you want a user to adjust HAS to be unlocked. It's the cells that you want the user not to be able to adjust, but still want the code to adjust that must be locked.
So in your example code, A4:W155 have to be unlocked.
By implication, at least A1 has to be locked, so that the code can put the timestamp in it, but not allow the user to adjust that time directly himself.

 

[tobink]

That's exactly how I have it done. So I just tried protecting the sheet without a password and it worked! Thanks so much for your help (and patience)! Have a great night!!!!

 

[tobink]

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!