CODEWITHSUNDEEP
vbaexcel
derek
derek

Reputation: 1055

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

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

Upvotes: 0

Views: 3767

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

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)"

Upvotes: 1

Daniel
Daniel

Reputation: 13142

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

Upvotes: 4

Related Questions