Lock the sheet once date is passed

**anwitha** · 12-08-2011, 01:23 AM

Hi,

I need to lock the sheet automatically once the date is passed. I have attached the sample sheet.

Please give your input and guide me about this.

**Fotis1991** · 12-08-2011, 03:17 AM

Hi anwitha

You can do this, using Validation(Data>Validation>Custom)

You can not use NOW() function,in B2,
because in this case, the result(a2-b2), changes every second!!

The formula that you have to use in Validation(marking all the Sheet) is:

=SUM($A$2-$B$2)<2)

Hope to helps you.

Regards

**anwitha** · 12-08-2011, 06:09 AM

Hi Fotis

Thanks for your reply but I want when that date is crossed after 2 days entire sheet should get locked...any idea how to do that?

Regards,
Anwitha

**john55** · 12-08-2011, 06:38 AM

hi Anwitha,
as an option try this one and see if it helps you

Private Sub Workbook_Open()
If [b2] - [a2] >= 2 Then Sheets(1).Protect "123"
End Sub

**anwitha** · 12-10-2011, 12:46 AM

Hi Joh,

Thanks for your help. but I have given specified cell access to all the users I want that given specified cell should lock once the date is passed 2 days. The code which you have provided is not working in this scenario. Is there any other option availabe to do that?

**john55** · 12-10-2011, 01:37 AM

hi,
add the code in the workbook_open event, if the date in cell a2 is < or = with 2 days than the date in cell b2 when you open the file the sheet is protected.
the password is 123. go to main meniu, Review, Unprotect Sheet, password 123.
see the file attached.
hope it helps you

**anwitha** · 12-10-2011, 02:57 AM

Hi John,

Please refer to the newly attached sheet. I have given the access to the users to update only for the column F apart from that they are not able to edit any other cells. My concern is if the date in A2 cell is more than 2 days to B2 Cell then that F column also should get locked.

Attached the sample sheet for your reference.

Thanks.
Sambashiva

**john55** · 12-10-2011, 03:53 AM

I am sorry, perhaps someone else has another approach.

**nilem** · 12-10-2011, 04:45 AM

maybe so

Sub Button2_Click()
With Sheets(1)
    .Unprotect "123"
    .Columns("F:F").Locked = .[b2] - .[a2] > 2
    .Protect "123"
End With
End Sub

**anwitha** · 12-11-2011, 12:55 AM

Hi nilem.

Its working fine Thanks for your help.

Thread: Lock the sheet once date is passed

Thread Tools

Display

Lock the sheet once date is passed

Re: Lock the sheet once date is passed

Re: Lock the sheet once date is passed

Re: Lock the sheet once date is passed

Re: Lock the sheet once date is passed

Re: Lock the sheet once date is passed

Re: Lock the sheet once date is passed

Re: Lock the sheet once date is passed

Re: Lock the sheet once date is passed

Re: Lock the sheet once date is passed

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions