2 Vba Routines - stop working

I have 2 VBA routines. 1 is assigned to a button to clear cells the user has placed values in at the sheet level. The 2nd is to copy cells to other cells based on a drop down menus selection is in a macro.

I am having the following issues:

1. After the 1st time the clear cells button is clicked, the cells stop copying (macro1)

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)


  Dim cell As Range
 


'  Check to see if cell G3 updated
  If Target.Address = Range("G3").Address Then
      If Target.Value = "YES" Then
        Application.EnableEvents = False
        Range("E9:E12").Value = Range("J3:J6").Value
        Application.EnableEvents = True
  Else
  If Target.Address = Range("G3").Address Then
      If Target.Value = "NO" Then
        Application.EnableEvents = False
        Range("E9:E12").ClearContents
        Application.EnableEvents = True
      End If
    End If
    End If
  End If
       
End Sub

---

Code:

---

Sub Clearselected()
Range("e3").ClearContents
Range("e9", "e12").ClearContents
Range("H9", "H12").ClearContents
Range("i3", "i6").ClearContents
Range("K9", "K12").ClearContents
End Sub

---

First Run this

Code:

---

Sub Reset_Events
'Keep this sub around. EnableEvents is tricky
'When in doubt, Run this.
Application.EnableEvents = True
End Sub

---

Then see if this works better

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
' Check to see if cell G3 updated
If Target.Address <> "$G$3" Then Exit Sub

Application.EnableEvents = False

If Target.Value = "YES" Then
  Range("E9:E12").Value = Range("J3:J6").Value
ElseIf Target.Value = "NO" Then
  Range("E9:E12").ClearContents
End If

Application.EnableEvents = True

End Sub

---

Also

Code:

---

Sub Clearselected()
Application.EnableEvents = False
    Range("e3").ClearContents
    Range("e9", "e12").ClearContents
    Range("H9", "H12").ClearContents
    Range("i3", "i6").ClearContents
    Range("K9", "K12").ClearContents
Application.EnableEvents = True
End Sub

---

I am not sure if I understand:
1) Run Reset Events 1 time only as a module?

If that is the case, I did that. However, after doing the clearselected (button/module) the copy (sheet1) of the cells stops working (j3:j6 - e9:e12)

I usually run Reset_Events whenever anything stops working. Application.Events is tricky like The Joker. Reset_Events can be in any Code Page. I keep it in MyPersonal.xls so it's always available. You'd be amazed at how often people write bad code that fails after Application.EnableEvents = false but before Application.EnableEvents = True.

Try using

Code:

---

If UCase(Target) =

---

Is J9:J12 formulas? If not, try

Code:

---

Range("J9:J12").Copy Range("E9")

---

Quote:

---

Originally Posted by SamT

I usually run Reset_Events whenever anything stops working. Application.Events is tricky like The Joker. Reset_Events can be in any Code Page. I keep it in MyPersonal.xls so it's always available. You'd be amazed at how often people write bad code that fails after Application.EnableEvents = false but before Application.EnableEvents = True.Try using

Code:

---

If UCase(Target) =

---

Is J9:J12 formulas? If not, try

Code:

---

Range("J9:J12").Copy Range("E9")

---

---

I made the code changes. J9:J12 is defined as accounting. The first time I ran it, the bottom grid line of E12 disappeared. I submitted reset_events after that happened. I reran and the results were the same. E9 - E12 did not copy to J9 - J12. Below is my code:

UPDATE - PLEASE READ BELOW THEN LOOK AT THE RED BELOW THIS

Private Sub Worksheet_Change(ByVal Target As Range)
' Check to see if cell G3 updated
If Target.Address <> "$G$3" Then Exit Sub


Application.EnableEvents = False


If UCase(Target) = "YES" Then
Range("J9:J12").Copy Range("E9")
ElseIf UCase(Target) = "NO" Then
Range("E9:E12").ClearContents
End If


Application.EnableEvents = True


End Sub

UPDATE
The original code you sent to me (below) will work if I toggle between YES and NO after doing the CLEAR

Private Sub Worksheet_Change(ByVal Target As Range)
' Check to see if cell G3 updated
If Target.Address <> "$G$3" Then Exit Sub


Application.EnableEvents = False


If Target.Value = "YES" Then
Range("E9:E12").Value = Range("J3:J6").Value
ElseIf Target.Value = "NO" Then
Range("E9:E12").ClearContents
End If


Application.EnableEvents = True


End Sub

Quote:

---

The original code you sent to me (below) will work if I toggle between YES and NO after doing the CLEAR

---

Well, of course. The Change Event Sub only runs after you change G3.

Add this line as the first line in the sub

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox"Running the Change sub."
'etc
'

---

Then Clear the cells

The code you supplied does work following the below procedure:
1) Click clear contents.
2) Make sure drop down = no
3) Enter info.
4) Change drop down = yes - fields are updated

or

If I do not click clear contents, I am able to toggle back and forth between YES & NO without any problems - this is the part that puzzles me