Timestamp stops working when a workbook is protected

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
205
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:

VBA Code:
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~
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,279
Office Version
  1. 365
Platform
  1. Windows
Are all users opening the workbook in the desktop app, or are some opening it with excel online?
 
Solution

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
205
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~
:cool:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,279
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 27, 2009
Messages
205
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~
:cool:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,279
Office Version
  1. 365
Platform
  1. Windows
In answer to the other part of your original question, you can use it on protected sheets like
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,711
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top