Liste de validation depuis un tableau structuré (listobject)

Version imprimable

Bonsoir,

dans une feuille, j'ai plusieurs tableaux structurés (listobject).
Dans une autre feuille, je souhaite, par macro, charger des listes de validation depuis les tableaux structurés.
Ci-dessous mon code de module, suivi par la procédure qui doit s'occuper de charger les listes de validation.
Je voudrais éviter d'utiliser des noms fixes de tableau, c'est pourquoi j'utilise "Listobjects(cpt1)".
Merci pour vos éclairages :)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Sub tables_to_table()
   Dim namingOfferSheet As Worksheet, newOfferSheet As Worksheet
   Dim nbtab As Long, cpt1 As Long
   Dim offer As String, nameOfSheet As String
 
   selectSheet nameOfSheet
   Set namingOfferSheet = ActiveWorkbook.Worksheets(nameOfSheet)
   offer = namingOfferSheet.ListObjects(1).DataBodyRange(1, 1)
   addNewNamedSheet offer     'creates a new sheet with the name of the offer
   Set newOfferSheet = ActiveWorkbook.Worksheets(offer)
   nbtab = namingOfferSheet.ListObjects.Count      'counts the number of tables to "import"
   'Create a new table with the validation lists
   newOfferSheet.ListObjects.Add SourceType:=xlSrcRange, Source:=newOfferSheet.Range("B5")
   newOfferSheet.Cells(6, 2) = offer      'copy the name of the Offer "imported"
   For cpt1 = 1 To nbtab
      newOfferSheet.Cells(5, (cpt1 + 1)) = namingOfferSheet.ListObjects(cpt1).HeaderRowRange(1)
   Next cpt1
   For cpt1 = 2 To nbtab
      addListsToTable newOfferSheet, cpt1, namingOfferSheet
   Next cpt1
End Sub

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
Sub addListsToTable(newOfferSheet As Worksheet, cpt1 As Long, namingOfferSheet As Worksheet)
   With newOfferSheet.ListObjects(1).DataBodyRange(cpt1).Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=indirect(""namingOfferSheet.ListObjects(cpt1).ListColumns(1).DataBodyRange"")"    'JE COINCE ICI
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
   End With
End Sub

13/05/2019, 23h22
78chris

Bonjour

De façon générale on nomme les colonnes de tableau (hors en tête) à utiliser dans les validations.

Les noms permettent de s'y retrouver et de bénéficier de l'élasticité des tableaux et donc de la maintenance automatisée des listes.
13/05/2019, 23h38
benoitdevries

Merci :D
Je suis donc obligé d'utiliser les plages nommées ?
Zut alors ... les tableaux structurés ne sont pas parfaits ? :mouarf:
14/05/2019, 01h17
78chris

Re

Citation:

Envoyé par benoitdevries

Merci :D
Je suis donc obligé d'utiliser les plages nommées ?
Zut alors ... les tableaux structurés ne sont pas parfaits ? :mouarf:

On peut utiliser INDIRECT mais je ne suis pas fan comme nombre d'entre nous...

La syntaxe est

Code:

Formula1:="=INDIRECT(""NomTableau[NomColonne]"")"

donc il te faut récupérer les name de chaque et non le databodyrange pour faire ta concaténation

Une plage nommée basée sur une colonne de Tableau structuré garde les avantages de celui-ci. Notamment si on change a postériori le titre de la colonne, la plage nommée suit mais pas INDIRECT...
14/05/2019, 08h57
benoitdevries

Merci à nouveau pour ta réponse.

Nommer les plages au sein d'un tableau structuré, alors qu'avec le tableau structuré on peut normalement indiquer le numéro de colonne et s'affranchir des noms d'en-tête, c'est pour moi une étape supplémentaire que je ne comprends vraiment pas.

Je voudrais simplement que ma procédure :
- utilise le n° de colonne que je lui passe en variable
- récupère tout le contenu de la colonne ciblée (hors en-tête)
- mette cette récupération dans une liste de validation.

Bien penser pour bien panser

Bonjour

Je partage (si elle le permet) la première réponse donnée par 78Chris ;) !

Citation:

Je suis donc obligé d'utiliser les plages nommées ?

Non mais tu te prives de tous les avantages procurés ainsi !

Citation:

Zut alors ... les tableaux structurés ne sont pas parfaits ?

Zut alors … rien ne l’est en ce bas monde donc exiger cela …

Citation:

Nommer les plages au sein d'un tableau structuré, alors qu'avec le tableau structuré on peut normalement (??) indiquer le numéro de colonne et s'affranchir des noms d'en-tête

Hé non, cela dépend du type de travail *!
C’est un problème de connaissances (même 80% n’est pas suffisant pour toujours réussir).

Chaque chose a ses défauts et ses qualités, ses inconvénients et ses avantages !
Beaucoup ne prennent pas en compte que les défauts sont mis en exergue quand on ignore des qualités, que les inconvénients prédominent, quand on n’utilise pas des avantages connus !
C’est tout un Art que de gérer ces états et ce ne sont pas les Professionnels qui sont toujours les plus aptes à y exceller !
On peut réduire les défaillances d’un outil en utilisant les réussites d’un autre.

* Contexte : un tableau nommé Tableau, Titre1 est le nom du premier titre et la première colonne contient des entiers inférieurs à 100.
Encore un défaut (seconde partie de la macro) ?
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Sub e() Dim C As Range, S As String For Each C In [Tableau[Titre1]] If C < 60 Then S = S & C & vbLf Next MsgBox S, , "" S=”” For Each R In [Tableau].Columns(1) ‘Oups If R < 60 Then T = T & C.Address & vbLf Next MsgBox S, , "" End Sub
Corrigé ainsi
Code:

1 2 3 4 5 6 7 Sub Oui() Dim C As Range, S As String For Each C In [Tableau].Columns(1).Cells ‘merci Pierre If C < 60 Then S = S & C & vbLf Next MsgBox S, , "" End Sub