1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
| Dim AdressesCellulesC As Variant
Dim AdressesCellulesD As Variant
Dim NomsFeuilles As Variant
Dim AddressePecedante As Variant
Private Sub Workbook_Open()
NomsFeuilles = Array("Feuil1", "Feuil2") ' En fait, tu mets les noms des feuilles que tu veux traiter
AddressePecedante = Array("A1", "A1") ' Autant d'élements que dans NomsFeuilles
AdressesCellulesC = Array("C2", "C5", "C10", "C13", "C19", "C24")
AdressesCellulesD = Array("D2", "D5", "D10", "D13", "D19", "D24")
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim lNom As Long
Dim lCellule As Long
If TypeName(Sh) = "Worksheet" Then
For lNom = LBound(NomsFeuilles) To UBound(NomsFeuilles)
If Sh.Name = NomsFeuilles(lNom) Then
' La feuille fait partie des feuilles gérées...
' Tester pour la colonne C
For lCellule = LBound(AdressesCellulesC) To UBound(AdressesCellulesC)
If AddressePecedante(lNom) = AdressesCellulesC(lCellule) Then
If Not lCellule = UBound(AdressesCellulesC) Then
Application.EnableEvents = False
Sh.Range(AdressesCellulesC(lCellule + 1)).Select
Application.EnableEvents = True
AddressePecedante(lNom) = Selection.Address(RowAbsolute:=False, ColumnAbsolute:=False)
Exit Sub
End If
End If
Next lCellule
' Tester pour la colonne D
For lCellule = LBound(AdressesCellulesD) To UBound(AdressesCellulesD)
If AddressePecedante(lNom) = AdressesCellulesD(lCellule) Then
If Not lCellule = UBound(AdressesCellulesD) Then
Application.EnableEvents = False
Sh.Range(AdressesCellulesD(lCellule + 1)).Select
Application.EnableEvents = True
AddressePecedante(lNom) = Selection.Address(RowAbsolute:=False, ColumnAbsolute:=False)
Exit Sub
End If
End If
Next lCellule
' Bonne feuille mais pas une cellule gérée donc,
AddressePecedante(lNom) = Selection.Address(RowAbsolute:=False, ColumnAbsolute:=False)
Exit Sub
End If
Next lNom
End If
End Sub |
Partager