VBA reset user entered value to 0 if

[VARD]

Hi,
I am working on the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
  
If Target.Address = "B13" Then

        Dim cellValue1 As String
        cellValue1 = Target.Value
       
        If cellValue1 = "No" Then

            Range("B14:B15").Locked = True
            Range("B14:B15").Value = 0
            MsgBox "If you have selected ""No"" in the previous question, you can't enter a value here."
           
        Else

            Range("B14:B15").Locked = False
           
        End If

    End If
End Sub

In B13 user can choose from a validation list "Yes" or "No". If they choose "No" then B14 & B15 should lock (meaning they shouldn't be able to enter a value) and if there was a value other than 0, it should reset to 0. Sometimes users may first answer "Yes" enter values in B14 and B15 then decide to change B13 to "No". If they do so, I need to lock and reset B14 and B15. However, this code isn't doing what I need. Please note that I also have other Workshee_Change events for the same sheet. If I try to use it on a new book, sometimes it work, sometimes it doesn't. On the original file, it never works. The macros are enabled from the options. Any idea on what is wrong? Thank you.

 

[Peter_SSs]

I have been using data validation to restrict users from entering values is the answer is "No".

Do you mean Data Validation in B14 and B15? If so, there has been no mention of that up until now that I know of so I was not aware. Your continued use of the term "Lock" in relation to cells implies the concept of protecting the worksheet after using this feature in the format of cells:

If you are blocking entry to B14:B15 with data validation, that is fairly weak since the user can paste something in there and it will over-ride/remove the data validation

I tried to reply "no" or "nO" but it wouldn't let me with my current mediocre code.

Code? Do you mean vba code? Post 1 indicated the Yes/No was Data validation:

In B13 user can choose from a validation list "Yes" or "No".

the user should never have to enter a password and should never be able to edit some cells.

If the worksheet is protected, even with a password, the user should never have to enter that password provided you set up appropriate cells a "Locked" or "not Locked" before the worksheet is protected. You would do well to do some research/reading about protecting worksheets


Pretty hard to give much specific advice as I feel that I do not have a very good understanding of your worksheet and what you are trying to achieve.

 

[VARD]

Do you mean Data Validation in B14 and B15? If so, there has been no mention of that up until now that I know of so I was not aware. Your continued use of the term "Lock" in relation to cells implies the concept of protecting the worksheet after using this feature in the format of cells:

View attachment 86924

If you are blocking entry to B14:B15 with data validation, that is fairly weak since the user can paste something in there and it will over-ride/remove the data validation


Code? Do you mean vba code? Post 1 indicated the Yes/No was Data validation:




If the worksheet is protected, even with a password, the user should never have to enter that password provided you set up appropriate cells a "Locked" or "not Locked" before the worksheet is protected. You would do well to do some research/reading about protecting worksheets


Pretty hard to give much specific advice as I feel that I do not have a very good understanding of your worksheet and what you are trying to achieve.

Thanks again. I will read up on how to protect a worksheet. Meanwhile, could you suggest me how to use cell names for target as well as for range?
"
If Target.Name.Name = "match_yn" Then
... "
but
"
Range("contr").Value = 0
" didn't work

  If Target.Name.Name = "match_yn" Then
    
    If LCase(Target.Value) = "no" Then
      Application.EnableEvents = False
      Range("contr").Value = 0
      Application.EnableEvents = True
      Range("contr").Locked = True
      MsgBox "You have selected ""No"" in the previous question so you can't enter a value here."
    Else
      Range("contr").Locked = False
    End If
    
  End If

 

[Peter_SSs]

If Target.Address = Range("match_yn").Address Then

 

[Peter_SSs]

@VARD
To help future readers, please 'Mark as solution' the post that answered your original question, if there is one. You had marked post #13 but that has nothing to do with the original question so I have removed that mark.