How to Lock Cells

skamat · Feb 1st 2013

I need some help on the following 2 points

1. How to Lock Row 4 in sheet3, lock means even we cannot put our cursor over there. (I do not want to hide the row)

I need another lock where we cannot put our cursor also on the following occasion

2. How to lock entire Coloumn A, only when someone puts some data on roloumn B then only the adjecent cell in coloumn A becomes unlocked.
Further if we deletes the data in coloumn B then adjecent data on coloumn A gets deleted automaticaly and again gets locked.

Mumps · Feb 1st 2013

Re: How to Lock Cells

Could you post a copy of your file? It would make it easier to help.

skamat · Feb 2nd 2013

Re: How to Lock Cells

Please see the sample file

Row 4 should be locked means the cursor should also should not go there (Dont hide the Row)
Coloumn contains the dropdown list, only when in coloumn B I write the date the adjecent cell on Coloumn A should get activated.
When coloumn B data is deleted the automatically adjecent cell in coloumn gets deleted and locked.

Mumps · Feb 2nd 2013

Re: How to Lock Cells

Hello skamat. There are a few things that you must do before you use the macro below. Start by unlocking all the cells in the range B5 to C23. If you have data below C23, then unlock those cells as well. Then protect your worksheet leaving only 'Select unlocked cells' checked. All other options must be unchecked. You can protect the sheet using a password if you wish, but if you use a password, the code will need to be modified. If you want to use a password and are unsure of how to do it, please let me know. Next copy and paste the code below in the worksheet module for the sheet that contains your data (not into a regular module). Now you are ready to go!

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
        If Target <> "" Then
            Target.Offset(0, -1).Locked = False
        ElseIf Target = "" Then
            Target.Offset(0, -1).ClearContents
            Target.Offset(0, -1).Locked = True
        End If
    ActiveSheet.Protect
End Sub

Display More

skamat · Feb 2nd 2013

Re: How to Lock Cells

Sir I tried but it is not working, please see the attached sheet wherein I have applied the formula.
I do not want to apply any password.
Please let me know if I have made any mistake after seeing the worksheet

Mumps · Feb 2nd 2013

Re: How to Lock Cells

Hi skamat. You had placed the macro into a regular module. I moved it into the worksheet module as mentioned in Post #4. If you want to see where the code is located, do the following:
Hold down the ALT key and press the F11 key. This will display the Visual Basic Editor. On the left hand side, right click Sheet1 and then click 'View Code" and there it is. This is the worksheet module as opposed to a regular module. Try the attached file and let me know how it turns out.

skamat · Feb 3rd 2013

Re: How to Lock Cells

No sir I am still able to put my cursor on Row 4

Also without filling anything I am able to put text in coloumn A

I want row 4 locked and cursor should also not go there
Col A can be activated only if Col B has data

rollis13 · Feb 3rd 2013

Re: How to Lock Cells

If I may be of some help with a minor change to Mumps work, try this in the sheets code module:

Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub            '<== added
    ActiveSheet.Unprotect                               '<== moved down
    If Target <> "" Then
        Target.Offset(0, -1).Locked = False
    ElseIf Target = "" Then
        Target.Offset(0, -1).ClearContents
        Target.Offset(0, -1).Locked = True
    End If
    ActiveSheet.Protect
    ActiveSheet.EnableSelection = xlUnlockedCells       '<== added
End Sub

Display More

Mumps · Feb 3rd 2013

Re: How to Lock Cells

Hi skamat. I'm not sure what happened but try the attached file. Everything worked properly when I tested it. Remember that for the locking and unlocking to take place, the sheet must remain protected.

How to Lock Cells

Participate now!

Share