2 Vba Routines - stop working

[jgold20]

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:

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




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


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

[SamT]

First Run this

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

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

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

[jgold20]

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)

[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
If UCase(Target) =

Is J9:J12 formulas? If not, try

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

[jgold20]

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
If UCase(Target) = Is J9:J12 formulas? If not, try
Range("J9:J12").Copy Range("E9")

[jgold20]

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

[SamT]

The original code you sent to me (below) will work if I toggle between YES and NO after doing the CLEARWell, of course. The Change Event Sub only runs after you change G3.

Add this line as the first line in the sub

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

[jgold20]

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