XL 2021 Verrouillage de cellules après exécution d'une macro

[MATLEA]

Problème de verrouillages de résultats issus d’une macro

Considérons un groupe de cellules (fichier joint) contenant des valeurs nominales (série des « tel »), colonnes AB et AC de 3 à 18 avec quelques trous exemple tel 21 (AC7), tel 28 (AC14), tel 30 (AC16).

L’objectif du travail est de créer un tirage aléatoire permettant de modifier l’ordre en éliminant les cellules vides.

Création de la colonne AG (de 3 à 34) reprenant les valeurs des colonnes AB et AC en utilisant la formule =""& et coordonnées de la cellule (exemple =""&AB3 qui donne tel 1 en AG3) pour éviter l’introduction d’un zéro (0) dans les cellules vides .

Génération de la colonne AK située obligatoirement 4 colonnes après la colonne AG reprenant les valeurs de la colonne AG (formule =) puis mise sous forme de tableau structuré de la colonne AK qui devient colonne1, les valeurs étant décalées vers le bas d’une cellule. Génération de la colonne2 (colonne2) par propagation de la cellule AK3 vers la droite ce qui active la macro (développée par job75 du forum) générant un nouvel ordre des cellules de la colonne AG sans prise en compte des cellules vides regroupées en bas de colonne2 (cellules AL33, 34,35).

Dans la colonne AN sont issues les valeurs transférées depuis la colonne AL après application de la formule =INDIRECT("AL"&LIGNE()) (AL étant la coordonnée de la colonne 2).(merci job75)

Mon problème tient au fait que toute tentative de récupération des valeurs de la colonne AN relance la macro et remodifie l’ordre (exemple en AR7 la saisie de AN6 qui aurait due être tel18 est devenue tel20). Toute tentative pour récupérer la valeur d’une cellule dans AN modifie l’ordre de la colonne entre AN4 et AN34.

Ma question est de savoir comment verrouiller les cellules de la colonne2 (AL) après exécution de la macro permettant de travailler sur les valeurs en AN sans craindre une nouvelle modification de l’ordre.

 

[job75]

Y aurait-il moyen d'éviter ce problème et de restreindre l'activation de la macro à la seule étape de la création de la colonne2 des tableaux structurés.

Bon OK vous en avez marre de la Worksheet_Change, alors utilisons le double-clic :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim pas&, LO As ListObject, P As Range, Q As Range, mem, h&
If Target.ListObject Is Nothing Then Exit Sub 'si le double-clic n'est pas dans un tableau structuré
Cancel = True
pas = 1 'modifiable
Application.ScreenUpdating = False
For Each LO In Me.ListObjects
    Set P = LO.Range
    If P.Columns.Count = 1 Then
        MsgBox "Le tableau " & LO.Name & "' doit avoir au moins 2 colonnes..."
    Else
        Set Q = Columns(P.Column - 4).Cells '4ème colonne à gauche
        Set Q = Range(Q(P.Row), Q(Rows.Count).End(xlUp))
        If Q.Row = P.Row Then
            If Not LO.DataBodyRange Is Nothing Then LO.DataBodyRange.Delete xlUp 'RAZ
            P(2, 1).Resize(Q.Rows.Count) = Q.Value 'copie les valeurs
            Set P = LO.Range
        End If
        mem = P.Columns(1) 'mémorise les valeurs
        P.Columns(2).Offset(1).ClearContents 'vide la 2ème colonne
        P.Sort P(1), xlAscending, Header:=xlYes 'tri de la 1ère colonne
        P(2, 2).Formula = "=OFFSET(" & P(2, 1).Address & "," & pas & "*(ROW()-" & P.Row + 1 & "),)"
        P.Columns(2) = P.Columns(2).Value 'supprime les formules
        h = Application.CountA(P.Columns(1))
        h = Application.Ceiling((h - 1) / pas, 1) + 1
        If h < P.Rows.Count Then P.Rows(h + 1).Resize(P.Rows.Count - h).ClearContents
        With P.Resize(h)
            .Columns(1) = "=RAND()" 'ALEA()
            .Sort .Columns(1), Header:=xlYes 'tri aléatoire
        End With
        P.Columns(1) = mem 'remise en l'état initial
    End If
Next
End Sub

Il faut faire le double-clic dans l'un des tableaux structurés, tous sont alors mis à jour.