locking coloumns & cells before today's date

    Hello All,


    Looking for a way to lock all cells before today's date.


    In fact, the only cells I want people to be able to edit would be the ones with the drop-down option (data validation) already given to them.


    I've tried playing around with the following bit of code I got from searching on here, but cant seem to get it working properly.


    "Private Sub Workbook_Open()
    Dim i As Long
    With Sheets("Sheet1")
    .Unprotect "test"
    .Cells.Locked = False
    For i = 4 To .UsedRange.Columns.Count
    If .Cells(3, i) < Date Then .Columns(i).Locked = True
    Next
    .Protect "test"
    End With
    End Sub"


    I'd greatly appreciate it, if someone could help me out with this.


    The password for the file is "[email protected]"


    Thanking you in advance,


    Nat
    [ATTACH]n1205186[/ATTACH]

    Try to use the code below, it will help you


    Private Sub Workbook_Open()
    Dim i As Long
    With Sheets("Drivezy")
    .Unprotect "test"
    For i = 5 To .UsedRange.Columns.Count
    If .Cells(5, i).Value >= Date Then
    .Columns(i).Locked = False
    Else
    .Columns(i).Locked = True
    End If
    Next i
    .Protect "test"
    End With
    End Sub

    [IMG2=JSON]{"alt":"01652845663","data-align":"none","data-size":"full","src":"https:\/\/www.ozgrid.com\/forum\/core\/image.php?userid=329077&thumb=1&dateline=1530858315"}[/IMG2]
    01652845663,

    Thank you for your reply. Much appreciated.


    While this does most of what I intended, it leaves the cells below E7:AI25 (below row 25, i take it?) open for editing. Any way of having these locked, as well?


    Sorry for being a bugbear, but I'm not very good at VB.


    Muchos Gracias!

    Hi Natrjc,


    Don't worry bro, practice makes perfect. I will help you at VB if I can


    Let try as my instruction below:
    1. Open the file and unprotect sheet(I think you know how)
    2. Choose all cells in the sheet(you can click the top left corner)-->right click and choose "format cell"-->at "protection"tab, make sure"clocked" is clicked-->click OK(what I need you is to clock all cells)
    3. Copy below code into you VBA


    Private Sub Workbook_Open()
    Dim i, j As Long
    With Sheets("Drivezy")
    .Unprotect "test"
    For i = 5 To 36
    If .Cells(5, i).Value >= Date Then
    For j = 7 To 25
    .Cells(j, i).Locked = False
    Next j
    Else
    For j = 7 To 25
    .Cells(j, i).Locked = True
    Next j
    End If
    Next i
    .Protect "test"
    End With
    End Sub


    4. Save and re-run your excel file


    Let's try and see the result, let me know if you have concern


    ---Viet

    Hi 01652845663,


    Your solution worked like a charm!


    Sorry for the late post, but a server issue at work kept me on my toes, and I could not respond.


    Thank you, once again, for the help. I spent some time trying to figure out what the code does, and it makes a little more sense to me now. I'm just realising just HOW much there is I don't know. But you've given me the push I needed, to spend some more time trying to get my head around VBA.


    Great forum, great people! Hats off to all those contributing so selflessly.

    You are welcome. Glad to hear that the code worked at your site.
    If you have other concerns, you can post it on this forum or send me a message, I'll help you when I'm free. :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login