Private Sub Worksheet_Activate()
'création de la liste des dossiers
Dim w As Worksheet, n As Integer
[Liste].ClearContents 'RAZ
For Each w In Worksheets
If IsNumeric(w.Name) Then
n = n + 1
[Liste].Cells(n, 1) = w.Name
[Liste].Cells(n, 2) = w.[B2]
End If
Next
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'tri de la liste
If Target.Address = "$A$9" Then
[Liste].Sort [Liste].Columns(1), Header:=xlNo
ElseIf Target.Address = "$B$9" Then
[Liste].Sort [Liste].Columns(2), Header:=xlNo
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
If Target.Address = "$A$9" Then
[B9] = ""
[B9] = Sheets(Target.Text).[B2]
ElseIf Target.Address = "$B$9" Then
Dim i
[A9] = ""
i = Application.Match(Target, [Liste].Columns(2), 0)
[A9] = [Liste].Cells(i, 1)
End If
Application.EnableEvents = True
End Sub