XL 2010 Liste de validation avec plage nommée

[apt]

Bonjour à tous,

Suite à ce sujet :

Liste de validation de noms de feuilles sans macro

Je cherche à définir une liste de validation avec une plage nommée ListeFeuilles, qui liste tous les noms des feuilles d'un classeur, excepté le nom de la première feuilles, mais sans passer par une colonne intermédiaire.

=TRANSPOSE(REMPLACER(LIRE.CLASSEUR(1);1;TROUVE("]";LIRE.CLASSEUR(1));""))

Mais en affectant cette plage à la liste de validation, une erreur est affichée :

La source est reconnu comme erroné

Est-ce possible ?

Merci d'avance.

 

[job75]

Bonsoir apt,

Est-ce possible ?

Non car une liste de validation doit être définie par une plage de cellules ou une liste en dur.

Et pas par une matrice.

Ce que vous appelez "colonne intermédiaire" est donc indispensable.

A+

 

[job75]

Bonjour apt,

Si l'on ne veut pas utiliser une plage pour la liste il faut du VBA pour la créer en dur.

Voyez le fichier joint et ces macros dans ThisWorkbook :

Private Sub Workbook_Open()
Workbook_SheetActivate ActiveSheet
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If TypeName(Sh) <> "Worksheet" Then Exit Sub
Dim s As Object, liste$
For Each s In Me.Sheets
    If s.Name <> Sh.Name Then liste = liste & "," & s.Name
Next
With Sh.[G2].Validation
    .Delete
    .Add xlValidateList, Formula1:=Mid(liste, 2)
End With
End Sub

Notez que comme une formule est créée celle-ci ne doit pas dépasser 8192 caractères.

A+

 

[apt]

Bonsoir job75,

Merci pour le code

Peut-on simplement se contenter de créer la liste de validation dans la première feuille uniquement ?

 

[job75]

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name <> "Feuil1" Then Exit Sub
'----

 

[apt]

J'ai ajouté la nouvelle condition, mais la liste de validation continue d'apparaître dans les autres feuilles, même lorsque le classeur est rouvert !

Private Sub Workbook_Open()
Workbook_SheetActivate ActiveSheet
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If TypeName(Sh) <> "Worksheet" Then Exit Sub
If Sh.Name <> "Feuil1" Then Exit Sub
Dim s As Object, liste$
For Each s In Me.Sheets
    If s.Name <> Sh.Name Then liste = liste & "," & s.Name
Next
With Sh.[G2].Validation
    .Delete
    .Add xlValidateList, Formula1:=Mid(liste, 2)
End With
End Sub

 

[job75]

J'ai ajouté la nouvelle condition, mais la liste de validation continue d'apparaître dans les autres feuilles, même lorsque le classeur est rouvert

Parce qu'elles avaient été créées avant, supprimez-les !!!

If TypeName(Sh) <> "Worksheet" Then Exit Sub n'est plus nécessaire.

 

[apt]

Bonjour,

J'ai encore un petit souci :

Si je veux exclure certaines feuilles de la liste, que devrais-je faire ?

 

[job75]

Bonjour apt,

Si je veux exclure certaines feuilles de la liste, que devrais-je faire ?

Travailler un peu pour comprendre ce qu'on vous propose.

Il y a déjà une feuille d'exclue, en exclure plusieurs n'est pas difficile :

For Each s In Me.Sheets
    If s.Name <> "Feuil1" And s.Name <> "Feuil2" And s.Name <> "Feuil3" Then liste = liste & "," & s.Name
Next
With Sh.[G2].Validation
    .Delete
    If liste <> "" Then .Add xlValidateList, Formula1:=Mid(liste, 2)
End With

A+

 

[apt]

Travailler un peu pour comprendre ce qu'on vous propose.

J'ai suivi ton conseil, et voici ce que j'ai pu avoir :

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim s As Object, liste$
Dim nSheets As Variant
 
nSheets = Array("Feuil1", "Feuil3")

For Each s In Me.Sheets
    If UBound(Filter(nSheets, s.Name)) < 0 Then liste = liste & "," & s.Name
Next
With Sh.[B6].Validation
    .Delete
    .Add xlValidateList, Formula1:=Mid(liste, 2)
End With
End Sub

 

[job75]

Ou aussi ;

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim nSheets, s As Object, liste$
If Sh.Name <> "Feuil1" Then Exit Sub
nSheets = Array("Feuil1", "Feuil3")
For Each s In Me.Sheets
    If IsError(Application.Match(s.Name, nSheets, 0)) Then liste = liste & "," & s.Name
Next
With Sh.[B6].Validation
    .Delete
    If liste <> "" Then .Add xlValidateList, Formula1:=Mid(liste, 2)
End With
End Sub

 

[apt]

Ou aussi ;

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim nSheets, s As Object, liste$
If Sh.Name <> "Feuil1" Then Exit Sub
nSheets = Array("Feuil1", "Feuil3")
For Each s In Me.Sheets
    If IsError(Application.Match(s.Name, nSheets, 0)) Then liste = liste & "," & s.Name
Next
With Sh.[B6].Validation
    .Delete
    If liste <> "" Then .Add xlValidateList, Formula1:=Mid(liste, 2)
End With
End Sub

merci pour le code.