Prevent data entry and set value to zero dependent on value of a different cell

New Contributor

Hi there,

I want to put some restrictions on a cell but I'm unsure how to do it.


If the word "Yes" has been typed into cell B2, i want to allow the user to input any numerical value in cell B5. However if the word "No" has been typed into cell B2, I want to prevent the user from entering any value to cell B5 and also set the value of B5 to zero.


I'm struggling a little to work out how to merge the commands together so if anyone has any ideas they can offer, they would be greatly appreciated.


Many thanks

2 Replies


This requires several steps.


1) Set up data validation for cell B5 to allow whole numbers or decimal numbers; specify a wide enough range.


2) Select all cells that the user should be able to edit, including B2 and B5.

Press Ctrl+1 to activate the Format Cells dialog.

Activate the Protection tab.

Clear the Locked check box.

Click OK.


3) On the Review tab of the ribbon, click Protect Sheet.

Use the check boxes to specify what users will be allowed to do. At the very least, leave the check box "Select unlocked cells" ticked.

If you wish, specify a password that will be needed to unprotect the sheet. Do not forget it!

Click OK. If you entered a password, you will have to re-enter it as confirmation.

In the step below, I will use "secret" as password.


4) Right-click the sheet tab and select "View Code" from the context menu.

Copy the following code into the worksheet module:


Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B2"), Target) Is Nothing Then
        Me.Unprotect Password:="secret"
        Application.EnableEvents = False
        Select Case Range("B2").Value
            Case "Yes"
                Range("B5").Locked = False
            Case Else
                Range("B5").Locked = True
                Range("B5").Value = 0
        End Select
        Application.EnableEvents = True
        Me.Protect Password:="secret"
    End If
End Sub

5) Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that users allow macros when they open it.

@Hans Vogelaar Wow amazing, I'll give that a try. Thanks so much for your quick reply