PDA

View Full Version : Results changing from a drop down selection - understanding VBA code



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

Paul_Hossler
05-27-2018, 11:39 AM
If it's NO and you want a change in A3 to update C10 etc, you might might need to use formulas




Option Explicit
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").Formula = "=G3"
Range("c10:c14").Formula = "=F3"

ElseIf Target.Value = "NO" Then
'Range("b10:b14").ClearContents
'Range("c10:c14").ClearContents
Range("c10").Formula = "=$A$3*B10"
Range("c11").Formula = "=$A$3*B11"
Range("c12").Formula = "=$A$3*B12"
Range("c13").Formula = "=$A$3*B13"
Range("c14").Formula = "=$A$3*B14"
End If

Application.EnableEvents = True
End Sub