CODEWITHSUNDEEP
vbaexcel
1990Mustafa
1990Mustafa

Reputation: 115

Lock Cell in Excel VBA

The code below doesn't work when I have my workbook protected (it does work when nothing is protected). What I am trying to do is having a workbook and sheets protected except for few cells and after a specific data I want all cells to be protected, please help.

Sub Workbook_Open()

    If Date >= DateSerial(2016, 1, 19) Then
       Worksheets("Jan").Protect Password:="123"

    ElseIf Date >= DateSerial(2016, 2, 28) Then
       Worksheets("FEB").Protect Password:="123"

    ElseIf Date >= DateSerial(2016, 3, 31) Then
       Worksheets("MAR").Protect Password:="123"

    ElseIf Date >= DateSerial(2016, 4, 30) Then
       Worksheets("Apr").Protect Password:="123"

    End If
End Sub

Upvotes: 1

Views: 195

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

Appears to work when I tested it - could it be that it isn't protecting each sheet, just the first sheet that meets the test. So, in February If Date >= DateSerial(2016, 1, 19) will be TRUE, so it'll protect January again and exit the procedure - it'll never even look at the FEB sheet.

I'm assuming the January date should be 31 rather than 19.

Try:

Private Sub Workbook_Open()

    Dim wrkSht As Worksheet
    Dim dSheet As Date

    'Look at each sheet.
    For Each wrkSht In ThisWorkbook.Worksheets
        'Create first of month date from sheet name.
        dSheet = CDate("1-" & wrkSht.Name & "-" & Year(dSheet))
        'Calculate last day of month (day zero of the following month).
        dSheet = DateSerial(Year(dSheet), Month(dSheet) + 1, 0)

        'Check the date against todays date and protect.
        If Date >= dSheet Then
            wrkSht.Protect Password:="123"
        End If

    Next wrkSht

End Sub

To test change dSheet = CDate("1-" & wrkSht.Name & "-" & Year(dSheet)) to dSheet = CDate("1-" & wrkSht.Name & "-" & 2015)

Upvotes: 2

Related Questions