excelvba

Change event result


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

My work module


Solution

  • 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
    

    enter image description here