Lock excel cell based on criteria

[propolis]

Hi,

Link to my spreadsheet: Spreadsheet

In my spreadsheet cell B2 is a Yes No option field

If you select Yes the Cell B3 is clear and you can enter data.

If you select NO, the Cell in B3 gets a grey colour, BUT, when they select No and the cell is Grey, I would like to protect the cell B3 so that you cant enter any text in cell B3

Hope this makes sense

Propolis

EDIT:
There are about 30 plus Yes / No selection per worksheet

 

[DJFANDANGO]

Try this using the follwong VBA:

When/if data is in C2:C31 when yes is selected, when 'No' is selected then that data is deleted, greyed out and locked.

There is no password on the locked cell, so the 'end user' can select yes/no at their leisure, I hope it helps!!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim cell As Range
Dim i As Integer

Set ws = ThisWorkbook.Sheets("Sheet1") ' Adjust the sheet name if needed

' Only respond to changes in column B
If Not Intersect(Target, ws.Range("B2:B31")) Is Nothing Then
Application.EnableEvents = False
ws.Unprotect

' Loop through the specified range
For i = 2 To 31
If ws.Cells(i, 2).Value = "No" Then
ws.Cells(i, 3).Value = "" ' Clear the cell content
ws.Cells(i, 3).Locked = True
Else
ws.Cells(i, 3).Locked = False
End If
Next i

ws.Protect
Application.EnableEvents = True
End If
End Sub

Book2.xlsm
	A	B	C	D	E
1		Select	Information
2		No
3		No
4		Yes	Data
5		No
6		Select
7		No
8		Select
9		Select
10		Select
11		No
12		Yes	Data
13		No
14		Select
15		Select
16		Select
17		Select
18		No
19		Select
20		Select
21		Select
22		Select
23		Yes	Data
24		Select
25		Select
26		Select
27		Select
28		Select
29		Select
30		Select
31		Select
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C31	Expression	=$B2="No"	text	NO

 

[propolis]

Hi,

Thanks for the code.

Works as I wanted it but have one issue:

When I select 'No' the cell gets protected but the whole worksheet as well. I have to unprotect the worksheet, do the next line, and if 'No' the whole worksheet gets protected.

Any suggestions please?

Propolis

 

[DJFANDANGO]

Hi,

Go to the "Review" tab and click "Protect Sheet".
Ensure that the "Select locked cells" and "Select unlocked cells" options are checked. You may set a password if desired (probably not in your case).
Make sure you uncheck the "Edit objects" and "Edit scenarios" options to allow the VBA code to make changes to the locked status of cells.

See if that helps.

Dj

 

[DJFANDANGO]

Hi there,

I have done some testing...

1. Ctrl+a (select the whole sheet)
2. Right Click anywhere, select 'Format Cells' (you may need to unlock sheet first)
3. Make sure that in the 'Protection tab' 'Locked and Hidden' are UNCHECKED

4. Try the sheet again...

Dj

 

[mumps]

There are about 30 plus Yes / No selection per worksheet

Are you using Yes/No drop downs in column B? If you select Yes in B2, cell B3 is cleared and you can enter data. If you select No in B2, cell B3 is greyed out and locked. Is this pattern followed down column B? For example, if you select Yes in B3, cell B4 is cleared and you can enter data. If you select No in B3, cell B4 is greyed out and locked and so on down column B. Is this correct?

 

[propolis]

Hi,

Yes, this pattern is followed down the page.

Eddie

 

[mumps]

Start by unlocking all the cells in your sheet. You can do this by clicking on the cell to the left of column letter A and above the row number 1 in the upper left hand corner. This will select all the cells in the sheet. Next right click anywhere on your sheet and click Format Cells. Then click the Protection tab and remove the check mark beside 'Locked'. Next copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the password (in red) to suit your needs. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 2 Then Exit Sub
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect "MyPassword"
    Select Case Target.Value
        Case "Yes"
            With Target.Offset(1)
                .ClearContents
                .Select
            End With
        Case "No"
            With Target.Offset(1)
                .Interior.ColorIndex = 15
                .Locked = True
            End With
    End Select
    ActiveSheet.Protect "MyPassword"
    ActiveSheet.EnableSelection = xlUnlockedCells
    Application.ScreenUpdating = True
End Sub