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

nrog5 

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.