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.

 

[Skyybot]

Where is the Macro stored?

 

[VARD]

Where is the Macro stored?

In the sheet I work on

 

[Skyybot]

In the sheet I work on

Copy that. Try changing the value before you lock it. Let me know what happens.

Range("B14:B15").Value = 0
Range("B14:B15").Locked = True

 

[Skyybot]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$13" Then

Dim cellValue1 As String
cellValue1 = Target.Value

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

Else

Sheet1.Unprotect

End If

End If
End Sub

 

[VARD]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$13" Then

Dim cellValue1 As String
cellValue1 = Target.Value

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

Else

Sheet1.Unprotect

End If

End If
End Sub

Nothing changed. It won't lock the cell, neither it resets the values.

 

[Peter_SSs]

If I try to use it on a new book, sometimes it work, sometimes it doesn't.

If that is the case then you have not given us your actual code since Target.Address will never be "B13", but it could be $B$13 as seen in @Skyybot's code.

I also have other Workshee_Change events for the same sheet.

So, if we don't get the current issue resolved soon, given this and my first point above you may need to give us your full worksheet change event code.

If your sheet does not have a password then remove Password:="abc" where it twice occurs. (Of course if you do have a password, change abc to your password )
I have also change the message box text a little since it will only show if they have entered "No" at B13

This is my suggestion to try

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$13" Then
    ActiveSheet.Unprotect Password:="abc"
    If LCase(Target.Value) = "no" Then
      Application.EnableEvents = False
      Range("B14:B15").Value = 0
      Application.EnableEvents = True
      Range("B14:B15").Locked = True
      MsgBox "You have selected ""No"" in the previous question so you can't enter a value here."
    Else
      Range("B14:B15").Locked = False
    End If
    ActiveSheet.Protect Password:="abc"
  End If
End Sub

 

[VARD]

If that is the case then you have not given us your actual code since Target.Address will never be "B13", but it could be $B$13 as seen in @Skyybot's code.


So, if we don't get the current issue resolved soon, given this and my first point above you may need to give us your full worksheet change event code.

If your sheet does not have a password then remove Password:="abc" where it twice occurs. (Of course if you do have a password, change abc to your password )
I have also change the message box text a little since it will only show if they have entered "No" at B13

This is my suggestion to try

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$13" Then
    ActiveSheet.Unprotect Password:="abc"
    If LCase(Target.Value) = "no" Then
      Application.EnableEvents = False
      Range("B14:B15").Value = 0
      Application.EnableEvents = True
      Range("B14:B15").Locked = True
      MsgBox "You have selected ""No"" in the previous question so you can't enter a value here."
    Else
      Range("B14:B15").Locked = False
    End If
    ActiveSheet.Protect Password:="abc"
  End If
End Sub

Thank you for helping @Peter_SSs and @Skyybot . Actually, adding absolute references helped. I don't have a password, neither I have locked cells. The working code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$13" Then

        Dim cellValue1 As String
        cellValue1 = Target.Value
       
        If cellValue1 = "No" Then
           
            Range("B14:B15").Value = 0
            Range("B14:B15").Locked = True
            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

Now, if I need to use defined cell names instead of addresses, how can I modify the code? If instead of "$B$13" I type "match_yn" it stops working again. Should it be Target.Name? I am still adding and removing rows, so the addresses keep changing.

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Now, if I need to use defined cell names instead of addresses,

Let's step back from that for a moment as there are still some issues with your "working" code above.

1. Locking cells does nothing if you do not also protect the worksheet (with or without a password). For example, with your code if I put "No" in B13, it certainly resets B14:B15 to 0 and tells me that I cannot enter a value because I chose no in B13. However, it does not actually stop me entering values in B14 and/or B15
   
   
2. You have also removed the two 'Application.EnableEvents' lines that I put in the code. Having removed those lines, when the code puts 0 in B14:B15 that is a worksheet change so the whole Worksheet_Change event code is (needlessly) run again in the middle of the previous running.
   
   
3. There is absolutely no need to introduce that cellValue1 variable and put the B13 value into it. If we get to that line we have already established that 'Target' is B13 so instead of the 3 red lines below, the single blue one will do the same thing more efficiently.

Dim cellValue1 As String
cellValue1 = Target.Value

If cellValue1 = "No" Then

If Target.Value = "No" Then

Note also that in my previous code I actually used this line

If LCase(Target.Value) = "no" Then

I did that deliberately and would still actually recommend it. Whilst you have Data Validation in B13 with a choice of Yes or No that Data Validation would not stop a user entering any of the following values
No
NO
no
nO

If they do, your line of code would correctly pick up the "No" but would not reset or lock anything if the user happened to enter any of the other 3 possibilities. So in my mind a worthwhile safety feature to employ.

 

[VARD]

Dear @Peter_SSs,

I highly appreciate all your help and suggestions. I have been using data validation to restrict users from entering values is the answer is "No". Also, I tried to reply "no" or "nO" but it wouldn't let me with my current mediocre code. I have 8 such cases with similar code where I am restricting the answers to Yes No and if No locking the cells. Would you recommend using your code for all of them? I am planning to add a password and lock certain cells in future. Will I need to modify my whole code then? I need some cells to always be locked for the user, while the other cells should remain editable without the user having to enter a password. Basically, the user should never have to enter a password and should never be able to edit some cells. What is the right way to achieve my goal?