[SIZE=2]Option [COLOR=navy]Explicit
Dim [/COLOR]RAZcell[COLOR=navy] As Boolean[/COLOR]
[COLOR=navy]Private Sub[/COLOR] Worksheet_Change([COLOR=navy]ByVal[/COLOR] Target[COLOR=navy] As [/COLOR]Range)
[COLOR=green]'myDearFriend! - Août 2006[/COLOR]
[COLOR=navy]Static[/COLOR] EnCours[COLOR=navy] As Boolean
Dim [/COLOR]F[COLOR=navy] As [/COLOR]Worksheet
[COLOR=navy]Dim [/COLOR]Deroul[COLOR=navy] As Boolean[/COLOR]
[COLOR=navy]If [/COLOR]EnCours [COLOR=navy]Then Exit Sub[/COLOR] [COLOR=green]'pour éviter boucle sur évènement[/COLOR]
[COLOR=navy]If Not [/COLOR]CelluleOK(ActiveCell) [COLOR=navy]Then Exit Sub[/COLOR] [COLOR=green]'ne gère que les cellules cibles[/COLOR]
[COLOR=navy]With[/COLOR] ActiveCell
[COLOR=navy]If [/COLOR].Validation.InCellDropdown = [COLOR=navy]True Then[/COLOR]
EnCours = [COLOR=navy]True[/COLOR]
[COLOR=navy]If [/COLOR]RAZcell [COLOR=navy]Then[/COLOR] [COLOR=green]'RAZ de la ligne[/COLOR]
.EntireRow.Range("B1:IV1").ClearContents
RAZcell = [COLOR=navy]False[/COLOR]
[COLOR=navy]End If[/COLOR]
[COLOR=navy]If [/COLOR]Application.CountIf(.EntireRow, .Value) < 2 [COLOR=navy]Then[/COLOR] [COLOR=green]'MAJ de la ligne[/COLOR]
.EntireRow.Range("IV1").[COLOR=navy]End[/COLOR](xlToLeft).Offset(0, 1).Value = .Value
[COLOR=navy]End If[/COLOR]
[COLOR=green]'Gestion du Dropdown[/COLOR]
[COLOR=navy]Set[/COLOR] F = Worksheets("[COLOR=navy]Base[/COLOR]")
[COLOR=navy]Select Case[/COLOR] ActiveCell.Validation.Formula1
[COLOR=navy]Case[/COLOR] "=SousMatière"
Deroul = Application.CountIf(F.Range("Matière"), .Value) = 1
[COLOR=navy]Case[/COLOR] "=Solution"
Deroul = Application.CountIf(F.Range("$J$1:$AV$8"), .Value) = 0
[COLOR=navy]End Select[/COLOR]
[COLOR=navy]If [/COLOR]Deroul [COLOR=navy]Then[/COLOR]
SendKeys "%{DOWN}"
[COLOR=navy]Else[/COLOR]
.Value = ""
[COLOR=navy]End If[/COLOR]
[COLOR=navy]End If[/COLOR]
[COLOR=navy]End With[/COLOR]
EnCours = [COLOR=navy]False
End Sub[/COLOR]
[COLOR=navy]Private Sub[/COLOR] Worksheet_[COLOR=navy]Select[/COLOR]ionChange([COLOR=navy]ByVal[/COLOR] Target[COLOR=navy] As [/COLOR]Range)
[COLOR=navy]If Not [/COLOR]CelluleOK(ActiveCell) [COLOR=navy]Then Exit Sub[/COLOR]
SendKeys "%{DOWN}"
RAZcell = Is[COLOR=navy]Empty[/COLOR](ActiveCell)
[COLOR=navy]End Sub[/COLOR]
[COLOR=navy]Private [COLOR=navy]Function[/COLOR] CelluleOK([COLOR=navy]ByVal[/COLOR] C[COLOR=navy] As [/COLOR]Range)[COLOR=navy] As Boolean[/COLOR]
[COLOR=green]' Vérifie si la cellule cible est située en colonne A et contient bien une liste de validation[/COLOR]
[COLOR=navy]If [/COLOR]Application.Intersect(C, Columns(1))[COLOR=navy] Is Nothing Then Exit Function[/COLOR]
[COLOR=navy]On Error Resume Next[/COLOR]
CelluleOK = C.Validation.[COLOR=navy]Type[/COLOR] = xlValidateList
[COLOR=navy]End[/COLOR] Function[/COLOR][/SIZE]