liste des noms définis et leur utilisation

[gadget]

Bonjour tout le monde,

J'ai des problèmes avec certaines liaisons de mon classeur; pour les résoudre, je souhaite vérifier les formules ou listes de validation qui utilisent ces noms définis.

Après recherche, j'ai trouvé comment créer une liste de tous les noms définis pour un classeur (menu Insertion, puis Nom, puis coller puis coller une liste).

Maintenant j'aurais souhaité faire une liste de toutes les cellules contenant une liste de validation d'un classeur, et si possible la formule utilisée pour cette liste de validation (ce qui me permet de savoir où est utilisé un nom défini, puisque j'utilise des noms comme plage de cellules).

Est-ce que l'un ou l'une d'entre vous a déjà eu le problème et saurait comment le résoudre???

En vous remerciant par avance de votre aide...

gadget

 

[Guest]

Re : liste des noms définis et leur utilisation

Bonjour,

Voici une prcédure qui créee une nouvelle feuille avec la liste de toutes les cellules ayant une liste de validation dans le classeur, avec leur adresse et formule.

'---------------------------------------------------------------------------------------
Sub ValidationListe()
'Hasco: 21/12/2009
'http://www.excel-downloads.com/forum/newreply.php?do=newreply&noquote=1&p=782634
'---------------------------------------------------------------------------------------
    Dim plg As Range, c As Range
    Dim sh As Worksheet, shDest As Worksheet
    Dim i As Integer
    For Each sh In Worksheets
        On Error Resume Next
        Set plg = sh.UsedRange.SpecialCells(xlCellTypeAllValidation)
        If Not plg Is Nothing Then
            If shDest Is Nothing Then
                Set shDest = Sheets.Add
                shDest.Range("A1:C1").Value = Array("Feuille", "Cellule", "Validation")
            End If
            For Each c In plg
                If c.Validation.Type = xlValidateList Then
                    With shDest.Range("A" & Rows.Count).End(xlUp).Offset(1)
                       .Value = sh.Name
                       .Offset(, 1) = c.Address
                       .Offset(, 2) = Replace(c.Validation.Formula1, "=", "")
                     End With
                End If
            Next
        End If
        Set plg = Nothing
    Next sh   
End Sub

A+

 

[JNP]

Re : liste des noms définis et leur utilisation

Bonjour Gadget, Hasco ,
Vu que j'étais en train de plancher, je n'avais pas vu ton post Hasco ...
Comme j'ai raisonné en TXT, je propose quand même mon code

Sub test()
Dim Feuille As Worksheet, Cellule As Range
Open ThisWorkbook.Path & "\Validations.txt" For Output As #1
On Error Resume Next
For Each Feuille In ThisWorkbook.Worksheets
For Each Cellule In Feuille.Cells.SpecialCells(xlCellTypeConstants)
Print #1, "Feuille " & Feuille.Name & " : cellule " & Cellule.Address & _
    " : " & Cellule.Validation.Formula1
Next
Next
On Error GoTo 0
Close #1
End Sub

Bonne soirée

 

[gadget]

Re : liste des noms définis et leur utilisation

merci beaucoup JNP pour ton aide,
après une rapide vérification, cela ne me donne que la liste de la dernière feuille...
mais ça me done déjà une base pour travailler sur le reste du classeur.
je verrais ça demain
merci encore pour ton aide
gadget

 

[gadget]

Re : liste des noms définis et leur utilisation

merci hasco, je n'ai pas le temps d'essayer ce soir, j'essayerais demain...
gadget

 

[JNP]

Re : liste des noms définis et leur utilisation

Re ,
C'est bizarre, je vient de mettre des validations dans 3 feuilles, et je les récupère toutes ...
A suivre ...
Bonne soirée

 

[Guest]

Re : liste des noms définis et leur utilisation

Re,

@JNP tu récupères toutes (quoi?) toutes les cellules?

C'est normal avec Feuille.Cells.SpecialCells(xlCellTypeConstants) toutes les cellules contenant une constante.

UsedRange.SpecialCells(xlCellTypeAllValidation) est plus sélectif?

A+

 

[gadget]

Re : liste des noms définis et leur utilisation

bonjour les amis,

après essais:
la procédure "test" (de JNP) ouvre un fichier "Validations.txt" toujours vide...
la procédure "Validationliste (de Hasco) fonctionne mais oublie certaines feuilles (notamment celle qui contient les noms définis...bizarre...

en plus, pour faire compliqué, mes noms définis sont sur un autre classeur, mais je suppose que ça n'a rien à voir?

en tout cas, je vous remercie pour votre aide, je vais voir pour me débrouiller avec ces macros pour résoudre mon problème, et je ne manquerais pas de mettre la réponse ici pour aider les suivants...

merci encore

gadget

 

[JNP]

Re : liste des noms définis et leur utilisation

Re ,

@JNP tu récupères toutes (quoi?) toutes les cellules?
C'est normal avec Feuille.Cells.SpecialCells(xlCellTypeConstants) toutes les cellules contenant une constante.
UsedRange.SpecialCells(xlCellTypeAllValidation) est plus sélectif?

@ Hasco : Non, je parlais de toutes les feuilles, vu que je boucle sur toutes...
Il m'apparaissait logique que seule une constante puisse être issue d'une validation, mais c'est vrai que je n'avais pas bien lu l'aide au niveau de xlCellTypeAllValidation qui effectivement est parfaitement sélectif ...
@ Gadget : Par contre, sachant qu'une liste de validation fait en principe référence à une plage nommée de la même feuille, si tu dit que tes plages nommées sont dans un autre classeur, cela signifie que tu les ramènes par formules, donc il faudrait (je pense, à tester...) utiliser

Sub test()
Dim Feuille As Worksheet, Cellule As Range
Open ThisWorkbook.Path & "\Validations.txt" For Output As #1
On Error Resume Next
For Each Feuille In ThisWorkbook.Worksheets
For Each Cellule In Feuille.Cells.SpecialCells([B][COLOR=red]xlCellTypeAllValidation[/COLOR][/B])
Print #1, "Feuille " & Feuille.Name & " : cellule " & Cellule.Address & _
    " : " & Cellule.Validation.[B][COLOR=red]Formula1[/COLOR][/B]
Print #1, "Feuille " & Feuille.Name & " : cellule " & Cellule.Address & _
    " : " & Cellule.Validation.[B][COLOR=red]Formula2[/COLOR][/B]
Next
Next
On Error GoTo 0
Close #1
End Sub

car Hasco et moi, nous avons mis un On Error Resume Next qui passe l'instruction quand elle n'est pas valide, or, si ta validation est dans Formula2 au lieu de Formula1, Formula1 n'est pas valide d'où mon fichier vide...
Dis nous

 

[gadget]

Re : liste des noms définis et leur utilisation

je ne sais pas pourquoi, mais sur un classeur d'essais, ça fonctionne, et sur mon classeur ça ne fonctionne pas.........
je vais continuer à chercher mais je vous remercie pour votre aide

bon, j'ai trouvé!!
il se trouve que mon classeur est un modèle (.xlt) et protégé par mot de passe (pour éviter les conneries).
après avoir enlevé la protection, je retrouve bien une liste apparemment complète de mes listes de validations!

je vous remercie pour tout, je n'aurais jamais imaginé que la protection empêche une partie seulement des listes de validations maiis pas toutes!!!

merci encore pour votre aide.
finalement, j'ai choisi d'utiliser la version en fichier txt, cela me permet de ne pas toucher à mon fichier excel d'origine (ça m'arrange )

bonne journée à vous et bonne continuation à ce forum qui décidement m'a enlevé plusieurs épines des pieds :-D

gadget