Timelocking a Worksheet

StephE

New Member
Joined
Mar 31, 2021
Messages
3
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi

I have the following VBA which I am using to lock a worksheet at 7am each morning. It appears to be working well but the issue I have is when I come to unlock the document using the password the sheet automatically locks again.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Value < Date - 1 + (7 / 24) Then
ActiveSheet.Protect Password:="password"
MsgBox "This workbook is locked, please contact ********", vbInformation, "Protected Document"
Else
End If
End Sub

I need the sheet to remain locked until a password is entered. Once the password is entered the sheet is to be edited and then the password protection applied again.

Any help would be much appreciated

Thanks
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,012
Is the value in cell A1 constantly changing due to a TIMER ? The value displayed is like a constantly updating clock ?

Or ... Are you manually entering a DATE/TIME into cell A1 ?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Also note that users can easily get around this simply by not enabling VBA when opening the workbook, so that the code never runs.
 

StephE

New Member
Joined
Mar 31, 2021
Messages
3
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I am manually entering a date
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,012

ADVERTISEMENT

Ok ... great.

The reason the workbook is immediately locking up again is due to the macro being placed in the
Worksheet_SelectionChange.

By entering the password to unlock the workbook for changes creates a "change event" and the macro kicks in by locking
the workbook again.

Explain locking the workbook at 7 am each day. Why only at 7 am ? Shouldn't it be locked at all times unless you want to make
an edit ?

???
 

StephE

New Member
Joined
Mar 31, 2021
Messages
3
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi

That makes sense.

It is a sheet that can be edited by multiple users throughout the day.

The users work shifts which are 7am to 7pm and 7pm to 7am, in order to stop users making amendments to the sheet after both shifts are completed each day I need to lock the previous days sheet at 7am each morning. Once locked only the manager will have the password and will be authorised to make amendments.

The workbook contains a new sheet for each day so users will move to the next worksheet once the current worksheet locks, i.e. Monday, Tuesday, Wednesday etc.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,012
Here is a method to lock a sheet at a specific time : Protect Sheet After Certain Time

However, you will have a challenge due to the sheet name. A workbook can only have a unique name for each sheet. So .... the sheet you are locking will need to be renamed,
then a new sheet for the new day will need to be renamed as the old sheet.

Does that make sense ?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,012
Another approach (and probably easier) would be to copy/paste the sheet's data to a new sheet.

Delete all the data on the original sheet and let the workers use that for the new day.

Repeat the process at 7 am each day.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,931
Messages
5,639,064
Members
417,067
Latest member
rohitbabshet

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