Fix The Code

Solved/Closed
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 - Nov 9, 2015 at 12:42 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Nov 9, 2015 at 01:25 PM
Hello,


Its being requested please fix the code given below
the problem in this code is that any one of the given condition execute

and once it executed then other is not execute or in other words its stop working
even same condition will also not run that was executed first time.

Private Sub Worksheet_Change(ByVal Target As Range)

Set rngCheck = Range("D2:D1000")
Set rngBlock = Range("E2:E1000")

If Intersect(Target, rngBlock) Is Nothing Then
Application.EnableEvents = False
For Each rngToChk In Target
Select Case rngToChk.Value
Case "Best", "Worst"
rngToChk.Offset(0, -1).ClearContents
rngToChk.Offset(0, -2).ClearContents
End Select
Next
Else
If Target.Offset(0, -1) = "Best" Or Target.Offset(0, -1) = "Worst" Then
Target.Select
MsgBox "You cannot enter any value as Student's Column contains" _
& vbNewLine & "Best or Worst.", vbOKOnly, "Warning!"

Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True

End If
End If
End Sub

Thanks & Regards
Related:

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Nov 9, 2015 at 12:59 PM
When you initially asked the question (https://ccm.net/forum/affich-853400-remove-or-clear-data#9 ) in the answer provided, I had put the comments Have you read the comments and understood what it means ?

' this disables the events
Application.EnableEvents = False

In you IF block you are disabling the event. You never enable it back again
1
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
Nov 9, 2015 at 01:10 PM
Hi
I am very sorry Thanks for your so much contribution I am Grateful to you
Please accept my apology
Thanks
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Nov 9, 2015 at 01:25 PM
No need for apology. The point that I was trying to made was one need to understand what each line of coding was doing. So I was not sure if you did not read the comments or there is some confusion in your understanding. Once you know what the code is doing you can then always change /reuse it otherwise you would never learn. Also one learn by mistake more than by having every thing falling in place. Like in this case, I am sure you would now always have a good understanding of enableevents purpose. I could have corrected it for you but that would not helped in longer run. So I purposefully directed you towards comments and issue
0