Verrouillage/déverrouillage automatique

Bonjour,

Je me débrouille pas trop mal avec excel, mais actuellement je bute sur un fonctionnement que j'aimerais mettre en place. Je m'explique en m'appuyant sur le fichier joint dans lequel aucun code ne se trouve, mais sert à illustrer ma demande.

En effet, je dispose d'un grand tableau avec passablement de personnes, dont certaines donnent de la formation. Le but recherché est que si le formateur n'est pas inscrit un des jours à disposition, la colonne de la date soit grisée et verrouillée. En ce qui concerne la visibilité par la mis en conditionnelle, je sais le faire grâce à une réponse précédente donnée sur ce forum. Je cherche comment améliorer cela en bloquant l'accès à ces cellules si la date n'a pas été "ouverte par le formateur".

En résumé : le formateur met un "X" pour marquer sa disponibilité, ça enlève la mise en forme conditionnelle et ça déverrouille la cellule.

Malheureusement je n'arrive pas à mettre cette idée par formule ou VBA. Est-il possible de m'aider ?

Je précise qu'il est clair que cette page excel sera protégée au final.

Bonjour,

Voici un essai à adapter :

private sub worksheet_change(byval target as range)
with me
    with .usedrange
        set rmodif = intersect(.rows(1), target)
        set rscroll = .rows(1)
        if not rmodif is nothing then
            for k = 1 to .columns.count
                if .cells(1, k) = "X" then set rscroll = union(rscroll, .columns(k))
            next k
        end if
    end with
    .scrollarea = rscroll.address
end with
end sub

A chaque changement de valeur sur la première ligne de la plage utilisée, on met à jour la plage modifiable pour qu'elle n'intègre que les colonnes dont la première cellule contient "X".

Cdlt,

Bonjour et merci pour votre réponse.

Bon, soit je n'ai pas réussi à adapter code, soit il n’interagit pas comme je le voudrais, mais toujours est-il que je n'arrive pas à faire fonctionner. Le problème vient certainement de mon adaptation su code.

Bonjour,

Je n'ai pas testé le code et j'ai un petit doute sur l'utilisation de .scrollarea. Quel problème avez-vous rencontré ?

Je ne l'ai pas précisé mais ce code est à coller dans le module de la feuille concernée par les changements. La macro est évènementielle et se déclenche lors d'un changement sur la 1ère ligne de la zone utlisée sur cette même feuille.

Si vous rencontrez un bug, essayez ceci sinon :

private sub worksheet_change(byval target as range)
with me
    .unprotect
    with .usedrange
        .locked = true
        set rmodif = intersect(.rows(1), target)
        set rscroll = .rows(1)
        if not rmodif is nothing then
            for k = 1 to .columns.count
                if .cells(1, k) = "X" then set rscroll = union(rscroll, .columns(k))
            next k
        end if
    end with
    rscroll.locked = false
    .protect
end with
end sub

Cdlt,

Bonjour,

Bonjour,

En effet, vous avez raison. J'ai donc changé l'ordre des instructions, je pense que ça devrait aller mieux.

private sub worksheet_change(byval target as range)
with me
    .unprotect
    with .usedrange
        set rscroll = .rows(1)
        if not intersect(.rows(1), target) is nothing then
            .locked = true
            for k = 1 to .columns.count
                if .cells(1, k) = "X" then set rscroll = union(rscroll, .columns(k))
            next k
            rscroll.locked = false
        end if
    end with
    .protect
end with
end sub

Cdlt,

Bonjour 3GB,

Cela fonctionne parfaitement bien. Pour parfaire le truc, serait-il possible de ne déverrouiller que les cellules des colonnes qui se trouvent en-dessous de la ligne sélectionnée qui ne sera pas la 1 ? Histoire que toute la mise en page au-dessus ne soit pas modifiable par les multiples non-informaticien qui vont accéder au fichier.

En tout cas un grand merci pour la réactivité et l'utilité des infos, c'est la classe.

Ah c'est cool !

Je me doutais bien qu'il fallait laisser d'autres cellules déverrouillées. Est-ce que le tableau est un tableau structuré ?

Sinon, quel est le numéro de la ligne des en-têtes du tableau ?

En effet, d'autres cellules seront déverrouillées, notamment celles des dates et horaires (voir photo jointe) et d'autres verrouillées qui font partie du squelette du tableau.

Par rapport à la photo jointe, je vais utiliser les 3-4 premières lignes du tableau (12 à 15) pour insérer les fameux formateurs. En ligne 16, qui sera la ligne référence de votre code VBA, je vais récupérer si l'un ou l'autre formateur à mis un "X" et le code doit réellement agir à partir de la ligne 17 et au-dessous que le code doit agir.

J'ai fait un test sur ma feuille, comme décrit dans mon message précédent et comme attendu, tout le squelette qui doit rester verrouillé se déverrouille. Faudrait vraiment que je puisse déverrouiller ce qui se trouve en-dessous. De l'aide ?

Bonjour Daniboom,

Voici une nouvelle tentative :

private sub worksheet_change(byval target as range)
dim rlock as range
with me
    .unprotect
    with .usedrange
        if not intersect(.rows(16), target) is nothing then
            .locked = false
            for k = 1 to .columns.count
                if .cells(16, k) <> "X" then
                    if rlock is nothing then
                        set rlock = .columns(k).offset(16, 0).resize(.rows.count - 16)
                    else
                        set rlock = union(rlock, .columns(k).offset(16, 0).resize(.rows.count - 16))
                    end if
                end if
            next k
            if not rlock is nothing then rlock.locked = true
        end if
    end with
    .protect
end with
end sub

Cette fois-ci, on prend le problème à l'envers, on déverrouille toute la zone utilisée lors d'un changement sur la ligne 16, puis on verrouille les colonnes (à partir de la ligne 17) lorsque la valeur en ligne 16 est différente de "X".

Cdlt,

Bonjour 3GB,

Alors malheureusement le code amène une erreur de compilation "Next sans For", 7 lignes avant la fin du code.

Je viens de modifier le code, il manquait un end if^^.

Malgré tous vos efforts, les lignes au-dessus de la 16 se déverrouillent tout de même à l'application du code. Je pense que je vais procéder différemment car cela devient complexe et non-fonctionnel. Un grand merci pour le coup de main, il manquait pas grand chose pour que cela fonctionne.

Bonjour Daniboom,

J'ai mal dû comprendre ce que tu cherchais à faire alors. Je pensais que tu souhaitais n'avoir que les cellules en dessous de la ligne 16 éventuellement verrouillées.

Voici un autre essai reposant sur une méthode différente :

private sub worksheet_change(byval target as range)
with me
    .unprotect
    with .usedrange
        set r = intersect(.rows(16), target)
        if not r is nothing then
            for each cell in r.cells
                if cell.value = "X" then
                    .columns(cell.column).offset(16, 0).resize(.rows.count - 16).locked = false
                else
                    .columns(cell.column).offset(16, 0).resize(.rows.count - 16).locked = true
                end if
            next cell
        end if
    end with
    .protect
end with
end sub

Ici, on ne s'occupe de verrouiller ou déverrouiller que les cellules en dessous de la ligne 16 lorsque qu'il y a un X ou non en ligne 16. De cette façon, tu peux agir comme tu l'entends sur le verrouillage des autres cellules.

Bonjour 3GB,

C'est grandiose, on approche du but. J'ai pu tester sur une feuille blanche et cela fonctionne parfaitement. Toutefois sur mon classeur à moi dont j'ai posté la photo l'un de mes posts précédents, j'ai une "erreur d'exécution '1004' : Impossible de définir la propriété Locked de la classe Range."Cette erreur renvoie à la ligne

.Columns(cell.Column).Offset(16, 0).Resize(.Rows.Count - 16).Locked = False

Est-ce que cela pourrait être en rapport à des cellules fusionnées plus haut ? Malheureusement je ne peux pas mettre mon classeur ici, mais je pourrais vous le transmettre en MP au besoin.

Bonjour Daniboom,

Déjà, si ça fonctionne à moitié, c'est pas mal.

Oui, ça pourrait être dû à des fusions ou, d'intuition, à un format de cellule (bordures ou couleur de fond) se prolongeant jusqu'à la dernière ligne de la feuille, entrainant par conséquent un échec de la méthode .offset, incapable d'aller au-delà de l'espace disponible.

En tout cas, ce sont 2 pratiques interdites par la Loi^^.

Mais ce serait étonnant car les essais précédents auraient normalement dû aboutir au même résultat en principe...

On pourrait le tester en intervertissant les 2 méthodes sur chacune des lignes :

.columns(cell.column).offset(16, 0).resize(.rows.count - 16).locked = false 'ou = true

devient

.columns(cell.column).resize(.rows.count - 16).offset(16, 0).locked = false 'ou = true

A voir... Mais il faut limiter le format si possible.

Edit : Après essai, les cellules fusionnées provoquent effectivement un bug et le format ne pose pas de problème ! Il faut donc les défusionner et les laisser ainsi ou éventuellement les remplacer par un format de cellule particulier : Format de cellule/Alignement/centré sur plusieurs colonnes.

Merci pour toutes les informations, cela fonctionne désormais à merveille!