XL 2016 VBA - Une manière rapide de trouver les cellules verrouillées ?

[Dudu2]

Bonjour,
Je n'y crois pas trop, mais à part parcourir les cellules une à une (ce qui n'est envisageable que sur un Range limité) y a-t-il un moyen de trouver le Range des cellules verrouillées d'une feuille ?
Merci.

 

[TooFatBoy]

J'utilise très souvent et depuis longtemps des noms de portée feuille, je n'ai jamais de problème.

Je demande ça à Dudu car je ne comprends pas les problèmes dont il parle. 😔

Je ne comprends pas non plus comment il se retrouve avec deux Nom identiques faisant référence, l'un à une feuille, l'autre au classeur.

J'avoue que je ne comprends pas la plupart des phrases qui me dépassent complètement car bien trop techniques, et je n'ai même pas les bases pour les comprendre... 😢

 

[laurent950]

Bonsoir Le forum,

Sub TrouverCellulesVerrouilleesSansBoucle()
    Dim rng As Range
    Dim firstLockedCell As Range
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    
    ' Recherche de la première cellule verrouillée dans la plage utilisée de la feuille
    Set firstLockedCell = ws.Cells.Find(What:="", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    ' Si une cellule verrouillée est trouvée, utilisez SpecialCells pour obtenir toutes les cellules verrouillées dans la feuille
    If Not firstLockedCell Is Nothing Then
        Set rng = ws.Cells.SpecialCells(xlCellTypeConstants)
        
        ' Si des cellules verrouillées sont trouvées, affichez leurs adresses
        If Not rng Is Nothing Then
            Debug.Print "Cellules verrouillées trouvées aux emplacements : " & rng.Address
        Else
            Debug.Print "Aucune cellule verrouillée trouvée sur la feuille."
        End If
    Else
        Debug.Print "Aucune cellule vide trouvée sur la feuille."
    End If
End Sub

 

[Dudu2]

Il faut dire que le vocabulaire utilisé est parfois sophistiqué. En fait c'est très simple.

Si on considère le classeur ThisWorkbook...
Chaque Objet Name du classeur a un Name et un Parent.
Tous les Objets Name sont dans la Collection Thisworkbook.Names.
Ceux spécifiques d'une feuille K sont dans la Collection Thisworkbook.Worksheets(K).Names.

Un nom "LeNom" défini dans le Gestionnaire de noms (ou en VBA) au niveau (étendue) classeur:

• GUI Gestionnaire de noms = "LeNom", étendue "Classeur"
• ObjName.Name = "LeNom"
• ObjName.Value = "=Feuil1!A1:A10"
• ObjName.Parent = ThisWorkbook

Un nom "LeNom" défini dans le Gestionnaire de noms (ou en VBA) au niveau (étendue) feuille "Feuil2":

• GUI Gestionnaire de noms = "LeNom", étendue "Feuil2"
• ObjName.Name = "Feuil2!LeNom" (ce que @Dranreb appelle un "nom de portée feuille")
• ObjName.Value = "=Feuil2!B1:B10"
• ObjName.Parent = Feuil2

Ne faut pas confondre le Nom qui apparait dans le Gestionnaire de noms et l'ObjName.Name qui certes sont directement liés mais diffèrent si l'étendue est une feuille.

Ne pas confondre non plus l'ObjName.Name et l'ObjName.Value pour un Range qui pour ObjName.Name d'étendue feuille commencent tous les deux par le nom de la feuille (au signe "=" près).

Et pour éviter les ennuis, ne pas donner un nom similaire à un l'ObjName d'étendue feuille et un ObjName d'étendue classeur.

 

[TooFatBoy]

Il faut dire que le vocabulaire utilisé est parfois sophistiqué. En fait c'est très simple.

Waouh ! Merci pour cette explication parfaitement limpide !👍

Effectivement, expliqué comme ça, ça semble simple.
Merci monsieur le Professeur ! 🤩 🤩 🤩


Si les Nom posent problème dans certains cas, peut-être peux-tu simplement stocker les données dans une feuille masquée ?

 

[Dudu2]

@TooFatBoy,
Tu sais je n'y connaissais rien sur les Objets Name avant de ramer sur le code que j'essaie de terminer en vain à cause de ce foutu Delete qui pourtant est aussi simple que le reste... sauf dans mon code !

 

[TooFatBoy]

ObjName.Name = "Feuil1!LeNom" (ce que @Dranreb appelle un "nom de portée feuille")

J'avoue que naturellement j'ai plus tendance à parler de portée du Nom au niveau de la feuille ou du classeur, plutôt que de parler de "référence à" comme le fait Microsoft.
Mais comme je ne voyais personne d'autre que moi utiliser cette "terminologie", je pensais que j'avais mal compris l'utilisation des Nom.

 

[Dranreb]

La portée et la référence sont deux choses différentes
La porté indique notamment dans quel cas le nom est reconnu dans une formule si on ne précise pas de feuille! devant. La référence décrit ce que le nom représente.

 

[Dudu2]

Alors j'ai enfin trouvé pourquoi je galère avec le Delete.

J'ai 2 Objets Name:
ObjetName1 = "LeNom" (étendue classeur)
ObjetName2 = "Feuil2!LeNom" (étendue Feuil2)

En principe si je fais référence à ObjetName1 pour une action (ObjectName1.Delete par exemple), il ne devrait pas y avoir d'ambigüité. Et bien pour Excel c'est selon... !!!

Si l'ActiveSheet est Feuil2, Excel va considérer que j'ai fait ObjetName2.Delete
Si l'ActiveSheet est Feuil1, Excel va considérer que j'ai fait ObjetName1.Delete (comme spécifié car je n'ai pas d'ObjetName d'étendue feuille sur Feuil1, sinon je suppose qu'il l'aurait utilisé -> confirmé par un test.)

Alors ça, c'est très embêtant si l'ActiveSheet (une circonstance aléatoire) vient altérer l'ObjetName désigné dans le code, ce n'est plus du code mais de la roulette. Et pour l'instant je n'ai pas de solution.

Ce qui me fait dire que pour éviter les ennuis avec les Noms d'étendue feuille, il faut éviter de les mettre en concurrence avec des Noms similaires d'étendue classeur. A cette condition, c'est effectivement utilisable.

 

[Dudu2]

A titre d'information, voilà les fonctions utilitaires que j'utilise maintenant pour stoker et restituer soit un String soit un Range dans des Noms du Gestionnaire de noms. A l'exclusion des formules et autres contenus.

Les paramètres des fonctions s'appliquent au Nom tel qu'il apparaît dans le Gestionnaire de noms avec un indicateur booléen précisant si le Nom a une étendue Feuille ou Classeur.

Edit: fichier modifié le 04/05/2024 à 14h29

 

[Dudu2]

@laurent950,
Ta macro a besoin de données pour fonctionner.

 

[TooFatBoy]

Dim Range As Range
Dim Name As Name

Tu aimes bien chercher la difficulté toi. 😅

 

[Dudu2]

Et oui, pourquoi chercher midi à quatorze heures ? 😎

 

[Dudu2]

J'ai modifié le fichier du Post #130 ci-dessus pour être 100% sûr du Range récupéré, notamment par rapport au classeur qui n'était pas explicitement désigné et donc implicitement était l'ActiveWorkbook peut-être à tort.

J'ai finalement renoncé au préfixage des noms par le CodeName pour n'utiliser que des Noms d'étendue feuille. Puisque ça marche bien si on évite les conflits avec des noms similaires d'étendue classeur.

 

[Dudu2]

La solution au problème du Delete lorsqu'il y a conflit de noms, est d'activer temporairement, sous ScreenUpdating False, la feuille de la valeur de Range d'un Nom d'étendue Classeur pour qu'Excel ne fasse pas la substitution avec le Nom similaire d'étendue Feuille de l'Activesheet au moment du Delete.

Si ces feuilles sont les mêmes, alors on sait qu'Excel, que ce soit pour une lecture de valeur ou un Delete va donner la priorité au Nom d'étendue feuille. Ce qui est incontournable.

Alors certes, c'est pour un cas très marginal, mais j'ai quand même codé ce cas dans le code modifié du Post #130.

 

[eriiic]

Bonjour à tous,
il y a qq années j'ai été confronté à ces incompréhensions sur les noms de niveau feuille.
J'ai fait un bilan exhaustif (je pense) des comportements de VBA sur ces noms qui varient en fait selon le contexte et la syntaxe.

Je commence par les constats et la conclusion simple :
1) un nouveau nom est toujours créé au niveau classeur si pas de spécification particulière
2) il ne faut pas de ce nom au niveau classeur (sauf cas voulu et maîtrisé) car si VBA ne trouve pas au niveau feuille il cherchera au niveau classeur.
3) si la feuille n'est pas spécifiée dans le nom VBA utilisera la feuille active

Conclusion : toujours spécifier la feuille lors de la création ou utilisation de noms de niveau feuille.
Création :
[A1].Name = "'Feuil1'!nom_11" (ne pas spécifier la feuille crée un nouveau nom au niveau classeur)
Utilisation :
[B1] = ['Feuil1'!nom_11]
Names("'Feuil1'!nom_11").Delete
Si besoin de spécifier le classeur : '[Classeur 1.xlsm]Copie 1'!nom_11

Synthèse des tests d'utilisation pour les curieux :

eric