Locking worksheet

    Re: Locking worksheet


    It would be something similar to:


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Me.Protect "Password"
    End If
End Sub


    in the worksheet module. Change the address to your cell address, but make sure to keep the $A$1 format because a simple A1 won't work. You have to keep the "$" signs.

    Re: Locking worksheet


    Thanks for that but it dosent seem to be working, the worksheet has a level of protection on it to start with to prevent formulas being altered so only giving the user acess to certian cells. what I need to achive is when say cell K2 has the word Approved in it then the sheet unlocks and then re-locks but making all cells and object unselectable.. Not sure if that makes sense

    Re: Locking worksheet


    Ok, I think I understand now. This will unprotect the sheet when A1 is changed, lock all cell in the entire worksheet, and reprotect the sheet.


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Me.Unprotect "Password"
            Cells.Locked = True
        Me.Protect "Password"
    End If
End Sub

    Re: Locking worksheet


    Thanks Think I have it working now


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$J$83" Then
            Me.Unprotect "Password"
            
    Me.Protect "Password", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Me.EnableSelection = xlNoSelection
    End If
End Sub

    Re: Locking worksheet


    Up to you. If you use that, you don't need the line:


    Cells.Locked = True


    because it doesn't matter if they're locked or not...the user can't do anything on the sheet as long as it's protected. And it looks like you figured that one out anyhow :)

Participate now!

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

Register Yourself Login