jgold20
05-26-2018, 03:43 AM
I just want to make sure what I am trying to accomplish will not work:
I have a drop down box & when you select "YES" it works.
I have added code to take a different action if the response is "NO".
When it is "NO" and you enter a number in A3 & B10, the result no longer shows in C10 (it is blank - the same holds true for b11 - b14 & c11 - c14), however, the multiplication does happen as the other cells have the correct results from the calculation. If I remove the code for ClearContents, and I enter a number in b10 - b14, id I select "NO", c10 - c14 does have the intended results.
Is there a way to get this to work without populating b10 - b14 and than selecting "NO"? Or should I just use IF logic in the cell?
Private Sub Worksheet_Change(ByVal Target As Range)
' Check to see if cell I3 updated
If Target.Address <> "$I$3" Then Exit Sub
Application.EnableEvents = False
If Target.Value = "YES" Then
Range("b10:b14").Value = Range("g3:g7").Value
Range("c10:c14").Value = Range("f3:f7").Value
ElseIf Target.Value = "NO" Then
'Range("b10:b14").ClearContents
'Range("c10:c14").ClearContents
Range("c10") = Range("a3") * Range("b10")
Range("c11") = Range("a3") * Range("b11")
Range("c12") = Range("a3") * Range("b12")
Range("c13") = Range("a3") * Range("b13")
Range("c14") = Range("a3") * Range("b14")
End If
Application.EnableEvents = True
End Sub
I have a drop down box & when you select "YES" it works.
I have added code to take a different action if the response is "NO".
When it is "NO" and you enter a number in A3 & B10, the result no longer shows in C10 (it is blank - the same holds true for b11 - b14 & c11 - c14), however, the multiplication does happen as the other cells have the correct results from the calculation. If I remove the code for ClearContents, and I enter a number in b10 - b14, id I select "NO", c10 - c14 does have the intended results.
Is there a way to get this to work without populating b10 - b14 and than selecting "NO"? Or should I just use IF logic in the cell?
Private Sub Worksheet_Change(ByVal Target As Range)
' Check to see if cell I3 updated
If Target.Address <> "$I$3" Then Exit Sub
Application.EnableEvents = False
If Target.Value = "YES" Then
Range("b10:b14").Value = Range("g3:g7").Value
Range("c10:c14").Value = Range("f3:f7").Value
ElseIf Target.Value = "NO" Then
'Range("b10:b14").ClearContents
'Range("c10:c14").ClearContents
Range("c10") = Range("a3") * Range("b10")
Range("c11") = Range("a3") * Range("b11")
Range("c12") = Range("a3") * Range("b12")
Range("c13") = Range("a3") * Range("b13")
Range("c14") = Range("a3") * Range("b14")
End If
Application.EnableEvents = True
End Sub