CODEWITHSUNDEEP
excelvba
user1551817
user1551817

Reputation: 7451

I want my vba code to lock my sheet after a certain time has elapsed

I am trying to write some VBA code that will lock a sheet if a cell (A1) has a certain value. What I have currently:

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

The problem is that when I enter the password to unprotect, it immediately protects again. I can see that this is just doing what I have asked. But what I want is when the password is correctly entered, something else should change so that the locking condition is no longer satisfied. Could someone help please?

Edit: Once I put in the password correctly, I just want this whole piece of code to be ignored after that.

Upvotes: 0

Views: 789

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

You need to make the condition evaluate whether sheet content is protected. Worksheet.ProtectContent returns True when that is the case:

If Not Me.ProtectContent And Me.Range("A1").Value < Date - 1 + (7/24) Then

That condition block wouldn't be entered when the sheet is protected.

Consider using the many specialized functions in the VBA.DateTime module to work with dates and times instead of doing potentially confusing date math with the underlying values.

Upvotes: 2

Related Questions