Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel
Previous Topic Next Topic

Fix The Code [Solved/Closed]

Report
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
 -
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
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

1 reply

Answer 1 / 1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
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
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
 1
Hi
I am very sorry Thanks for your so much contribution I am Grateful to you
Please accept my apology
Thanks
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
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
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!