Auto-run a VBA function when a cell value is changed

[mcruse]

Hi,

I have a function made up of if-thens that adds different text to a cell when the value of another cell is changed through a drop down menu. But it only runs when I hit the play button in VBA. I've trying searching for the worksheet change event but I can't figure out how to incorporate it into my code. Any help would be greatly appreciated!

The basic code is
Function Optimum()
Range("C2").Select
If Range("B2") = "2.5" Then
Range("C2").FormulaR1C1 = "Use 1 2kg and 1 .5kg"
Else
If Range("B2") = 5 Then
Range("C2").FormulaR1C1 = "Use 1 5kg"
Else
.
.
.
End If
.
.
.
End Function

 

[Glory]

I'm assuming you've already got your listbox/combobox linked to the B2? Put this into your worksheet module. Change combobox1 to the name of your control.

Private Sub [COLOR=darkgreen][B]ComboBox1[/B][/COLOR]_Change()
 
If [COLOR=darkgreen][B]ComboBox1[/B][/COLOR] = "2.5" Then
    Range("C2").Select
    Range("C2") = "Use 1 2kg and 1 .5kg"
 
ElseIf [COLOR=darkgreen][B]ComboBox1[/B][/COLOR] = 5 Then
    Range("C2").Select
    Range("C2") = "Use 1 5kg"
 
End If
 
End Sub