VBA code - have 1 woking, need to add another one

[jgold20]

I would like to add the below code to a worksheet1. It is only to execute when the user clicks on a shape (I know how to do this). I already have the code (see red) defined to sheet1. How do I accomplish this?

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





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

 

[mumps]

Place your "Clearselected" macro in a regular module and assign it to the shape you want to click. I've tidied up the other macro for you.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G3")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "YES"
            Range("E9:E12").Value = Range("J3:J6").Value
        Case "NO"
            Range("E9:E12").ClearContents
    End Select
End Sub

 

[jgold20]

Just to confirm:
1) Change value is assigned to a sheet.
2) Clearselected is defined in a module.

If this is the case, the cleaned up code is not copying J3:J6 - E9:E12

 

[mumps]

That's correct. I tried the macro on a dummy sheet and it copied the range properly.

 

[jgold20]

I am running on a Mac w/Excel2016. Did you try it with the clearselected? When I remove the module for clearselected, it also works for me.

 

[mumps]

I modified the Clearselected macro to this:

Sub Clearselected()
    Range("E3,E9,E12,H9,H12,I3,I6,K9,K12").ClearContents
End Sub

and they both worked properly. I'm using a PC and have no experience with a Mac. Although the code is very simple, I would think that it should work on a Mac but that is not always the case.

 

[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

 

[mumps]

So is everything working properly now?

 

[jgold20]

Not sure if clicking clear contents and then toggling between YES & NO on the drop-down what I would call working properly, but it get me the results