Ned
Footballguy
Chemical X said:well, each choice has a probability.
ie - Ned = 20%
Chem X = 15%
so if both are chosen, that cell would be 35%. but likely only 1 choice will ever be chosen.
Assumption: the probabilities are in the column next to your list of dropdown values.Chemical X said:Dim Oldvalue As String
Dim Newvalue As String
[SIZE=12pt]Dim ProbVal, DDLoc
Dim DDrng As Range
Dim ProbRng As Range[/SIZE]
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 16 Or Target.Column = 17 Or Target.Column = 18 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Set DDrng = ActiveCell.Parent.Range(ActiveCell.Validation.Formula1)
DDLoc = Application.WorksheetFunction.Match(ActiveCell.Value2, DDrng, 0)
Set ProbRng = DDrng.Offset(0, 1)
ProbVal = ProbRng(DDLoc)
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
[SIZE=12pt] Range("AA1").Value = ProbVal[/SIZE]
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
[SIZE=12pt] Range("AA1").Value = Range("AA1").Value + ProbVal[/SIZE]
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
[SIZE=12pt]On Error GoTo 0[/SIZE]
Application.EnableEvents = True
End Sub
The code in orange will give you the corresponding probability for every selection chosen in the dropdown. If more than 1 value is selected, it will add the probabilities. Without knowing where you want the probability to be displayed, I stuffed it into cell AA1. Change the reference to wherever you want it.
The code you posted has a bug - if an error is encountered, the code won't execute again when the dropdown is changed. The error handling needs to be reset to 0 in - add the green code to fix the bug.