I am trying to launch a macro uppon the change of the value of a cell which contains a choice list and i tried first a simple change event to see how it works but nothing happens when i chose a value from the choice list:
Private Sub RunMacroForDropdown(ByVal Target As Range)
Dim Synthese_Global As Worksheet
Set Synthese_Global = ThisWorkbook.Worksheets("Synthèse_Globale")
If Not Intersect(Target, Synthese_Global.Range("B1")) Is Nothing Then
Call MyMacro
End If
End Sub
Sub MyMacro()
MsgBox "Cell B1 has changed!"
End Sub
the code for the choice list is:
Dim str As String 'Create string of actual values
Synthese_Global.Range("B1").Validation.Delete
Synthese_Global.Range("B1") = Empty
For i = 2 To Synthese_Global.Range("A2").End(xlDown).Row
If Synthese_Global.Cells(i, 1) = "N/A" Then
If Len(str) = 0 Then
str = Synthese_Global.Cells(i, 1)
Else
str = str & "," & Synthese_Global.Cells(i, 1)
End If
End If
Next i
Synthese_Global.Range("B1").Validation.Add xlValidateList, , , str
You need to handle the Worksheet_Change event of the "Synthèse_Globale" worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [B1]) Is Nothing Then
Call MyMacro
End If
End Sub
Sub MyMacro()
MsgBox "Cell B1 has changed!"
End Sub