Timestamp stops working when a workbook is protected

[zombiemaster]

Hi, All... I've been away for quite some time, but finally have another question to ask.

I have an Excel workbook that we're developing for our call center to use to input call details, so the production area can go in and grab work when needed. This file is on a SharePoint site, and we want to have the ability to have the call center reps input their initials into column A and the date and time will appear in column B automatically. We tried using a circular-reference-formula in column B but it didn't really work very well and each user would need to turn on iterative calculations, which could be troublesome when you're talking hundreds of people. The testing results were less than promising and generated some errors on some of the testers machines. So, I did some research online and found this VBA code to do the same thing without having to worry about the formula:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
If Target.Column = 1 And Target.Value <> "" Then
Application.EnableEvents = False
Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss")
Application.EnableEvents = True
End If
Handler:
End Sub

It seems to work pretty well for what I need, but when I tried to protect the sheet so hidden rows couldn't be accidentally corrupted, the timestamp stopped working entirely. ALSO, when I saved the file (unprotected as a test) and had another tester go in, the timestamp didn't work for her AT ALL.

Does anyone know if there is a way to do what we're looking for, and be able to have the file be available to a lot of people at the same time within SharePoint?

I know it's a long shot, but if anyone knows, it will be this group!

Thanks in advance,
~ZM~

 

[Fluff]

Are all users opening the workbook in the desktop app, or are some opening it with excel online?

 

[zombiemaster]

Are all users opening the workbook in the desktop app, or are some opening it with excel online?

Good thinking! I just checked with the other user, and it was because she opened the file in the Online version of Excel. I had her try it through the desktop application and it worked for her. Now, we just need to convince an entire department that they need to open this one file in Excel and not use the browser version...lol

Thank you, Fluff!
~ZM~

 

[Fluff]

Now, we just need to convince an entire department that they need to open this one file in Excel and not use the browser version...lol

Not only that, but you will have to ensure that all users allow macros to run.

 

[zombiemaster]

Not only that, but you will have to ensure that all users allow macros to run.

Again, good thinking and I will make sure to keep that in mind if we go ahead with discussions on implementation!
~ZM~

 

[Fluff]

In answer to the other part of your original question, you can use it on protected sheets like

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 1 And Target.Value <> "" Then
      Me.Unprotect "Pword"
      Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss")
      Me.Protect "Pword"
   End If
End Sub

Just change the password to suit.