Loop through cells in a range and lock cells based on their value

0
votes

OK say i have a range of cells from A1:B10. The cells in the range A1 to A10 contain a dropdown list with 2 options (lock, dont_lock). The cells in the range B1 to B10 are empty cells which allow the user to enter data. what i want to do is lock individual cells based on values in the adjacent cell. so if cell A1 is set to "lock" then i lock cell B1. IF cell A2 is set to "dont_lock" then B2 is not locked and so on. I have tried using a for loop shown in my code below but it does not work. Can Anyone help ? 

Dim rCell As Range
Dim rRng As Range

Set rRng = Sheet1.Range("A1:B10")

For Each rCell In rRng.Cells
    If rCell = "Lock" Then
    Range("B1").Locked = True

End If

Next rCell
2
vbaexcel
can you attach a screen print of your sheet? :) You are very close to what you want to achieve anyway. When you say Column A has drop down menu, do you mean a drop down list in Excel cell? – bonCodigo
It's fine you do not have to attach any images. Please look at my comments in the answer :) – bonCodigo
Yes i mean drop down list option – derek

2 Answers

4
votes

In Excel, cells are locked by default. If you want cells to be unlocked you have to actually specify that behavior. However, it doesn't matter if the cell is locked or not if the worksheet isn't currently protected. So if you want the locked behavior, make sure to call Sheet1.Protect.

Now if I understand your explanation correctly you would do better with something like this:

Dim rCell As Range
Dim rRng As Range

Set rRng = Sheet1.Range("A1:A10") 'Exclude B as you are only checking A.
Sheet1.Unprotect
For Each rCell In rRng.Cells
    'Avoid if else structure since locking depends on result of evaluation only.
    rcell.Offset(0,1).locked = rcell.Value = "Lock"
Next rCell
Sheet1.Protect
1
votes

Try the following code. Only issue with your code is that rCell.value = "Lock" is missing. You are only locking Cell B1 for all cells, you are not locking the adjacent all cells :)

** THIS IS A SAMPLE CODE**

Option Explicit

Sub lockNextCell()
Dim wkSheet As Worksheet
Dim rng As Range, rCell As Range

Set wkSheet = Sheets("Sheets1") '-- use your own sheet
Set rng = wkSheet.Range("A3:A12") '-- use your own range

For Each rCell In rng
    If rCell.Offset(0, 0).Value = "Lock" Then
        rCell.Offset(0, 1).Locked = True '-- here we are locking the adjacent cell
    else
        rCell.Offset(0, 1).Locked = False
    End If
Next rCell

End Sub

Just a note as Daniel talks about protecting sheet:

Here is a reference from MSDN : "If you lock a cell and protect the worksheet, then you cannot type data into the cell, modify the data currently in the cell, or change other attributes of the cell (such as cell formatting)"