Méthode FIND et ListBox

Version imprimable

Bonjour à tous,

Je recherche à remplir une listbox, en fonction des valeurs sous une cellule déterminé par la méthode FIND

Je n'arrive pas à déterminer la cellule num.. ERREUR : Incompatibilité de type.

La méthode find ne renvoie pas une addresse ?
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Private Sub ComboBox1_Click() Dim num As Range ListBox1.Clear Dim j As Long With Worksheets("Liste") Set num = .Range("A1:AY1").Find(ComboBox1.Value, xlValues, xlWhole, , , True) MsgBox .Cells(num.Row, num.Column) For j = 20 To Worksheets("Liste").Cells(num.Row, num.Column).End(xlUp).Row Me.ListBox1.AddItem Worksheets("Liste").Cells(j, num.Column) Next j End With End Sub
Je vous remercie d'avance,

Gaétan

Salut, à quelle ligne se situe l'erreur ? essaye ca :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
Private Sub ComboBox1_Click()
Dim num As Range
ListBox1.Clear
 Dim j As Long
With Worksheets("Liste")
    Set num = .Range("A1:AY1").Find(ComboBox1.Value, xlValues, xlWhole, , , True)
MsgBox .Cells(num.Row, num.Column)
  For j = 20 To Worksheets("Liste").Cells(num.Row, num.Column).End(xlUp).Row
      Me.ListBox1.AddItem Worksheets("Liste").Cells(j, num.Column).value
   Next j
 
    End With
End Sub

Bonjour à vous, Bonjour au Forum,

Salut Christian (ravi de te retrouver)

Tu réponds de manière juste à notre ami, et c'est normal.

Pour ma part, on ne dira jamais assez la réelle efficacité des tableaux structurés.

Ici, plus de méthode Find. Il suffit d'appeler la colonne choisie.

Module Standard
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Option Explicit Public Sub test_alimctrl() Dim c As Range With Usf_monboformulaire .Lbx_malistbox.Clear With .Cbx_macombo .Clear For Each c In Sht_données.ListObjects("T_montablo").HeaderRowRange .AddItem pvargitem:=c.Value Next c 'Debug.Print .ListCount .SetFocus End With .Show End With End Sub
Module formulaire
Code:

1 2 3 4 5 Option Explicit Private Sub Cbx_macombo_Change() Me.Lbx_malistbox.List = Range("T_montablo[" & Me.Cbx_macombo.Text & "]").Value End Sub
Je m'interroge cependant sur un point.
Pour l'alimentation de la Combobox, j'étais parti sur

Code:

.List = Sht_données.ListObjects("T_montablo").HeaderRowRange.Value

Mais seule la 1ère cellule de la zone de titres de colonnes était retenue comme item. :koi:

Salut.

Sans voir une copie de ta feuille, il es malaisé de comprendre ce que tu souhaites réellement obtenir. Perso, je m'y prendrais différemment.

Par exemple, même si je comprends bien que cette ligne est là à des fins de vérification, je n'écrirais pas MsgBox .Cells(num.Row, num.Column), mais MsgBox num.value, .Cells(num.Row, num.Column) pointe forcément vers num.

Je me méfie de Find et préfère m'en passer lorsque c'est possible. Or ici, je crois comprendre que la combobox1 est remplie avec les valeurs des cellules A1:AY1, de sorte que lorsque tu cliques sur une entrée de la combo, tu peux connaître la colonne qui a en fait été choisie. En sachant que les lignes de la combo commencent à 0, la colonne choisie est l'index de la combo +1... Dès lors, pas besoin de FIND pour trouver la colonne. Pour remplir la combobox, j'utilise sa propriété Column qui permet de transposer en lignes les valeurs de la plage A1:AY1.

Tu peux remplir la listbox grâce à sa propriété List qui permet d'éviter la boucle en lui passant le tableau des valeurs de la plage souhaitée lorsque cette dernière a été déterminée.

Dès lors, le code de remplissage de la combobox pourrait être celui-ci, à mettre dans le initialize du userform ou dans la procédure d'amorçage:

Code:

ComboBox1.Column = Worksheets("Liste").Range("a1:ay1").Value

Au clic sur le combo, on remplit alors la listbox avec le code suivant:
Code:

1 2 3 4 5 6 7 8 9 10 Private Sub ComboBox1_Click() Dim rng As Range Dim LastRow As Long ListBox1.Clear Set rng = Worksheets("Liste").Cells(20, ComboBox1.ListIndex + 1) LastRow = Worksheets("Liste").Cells(1048576, ComboBox1.ListIndex + 1).End(xlUp).Row Set rng = rng.Resize(LastRow - rng.Row + 1) ListBox1.List = rng.Value End Sub
N'hésite pas à avancer en pas à pas pour comprendre chaque ligne. Sans savoir ce que tu cherches exactement, je n'ai pu que supputer une solution qui est plus une illustration de la démarche à mettre en place plutôt qu'une solution fonctionnelle.

N'hésite pas à renommer tes contrôles dès le début de la création du userform ;)

11/08/2021, 10h33
Pierre Fauconnier

Salut Marcel

Citation:

Envoyé par MarcelG

[...]
Pour l'alimentation de la Combobox, j'étais parti sur

Code:

.List = Sht_données.ListObjects("T_montablo").HeaderRowRange.Value

Mais seul la 1ère cellule de la zone de titres de colonnes était retenue comme item. :koi:

Tu dois utiliser la propriété .Column puisque ton Header ne contient qu'une ligne...

Ici, sans voir la configuration de la feuille, je ne suis pas certain qu'il puisse s'agir d'un tableau structuré, puisque l'on part de la 20ième ligne pour remplir la listbox alors que "l'entête" est sur la ligne 1. A voir donc si on a une copie d'écran. Mais bien entendu, je ne peux qu'encourager l'utilisation des tableaux structurés, car le code est alors encore beaucoup plus concis et lisible.

Bonjour Pierre,

Merci
Je viens de le voir par ailleurs et allais reporter le code
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Option Explicit Public Sub test_alimctrl() With Usf_monboformulaire .Lbx_malistbox.Clear With .Cbx_macombo .Clear .Column = Sht_données.ListObjects("T_montablo").HeaderRowRange.Value .SetFocus End With .Show End With End Sub
Quand même plus simple, car la colonne choisie est celle dont l'entête est sélectionné par la Combobox.

Je ne dis pas qu'il s'agit là d'un tableau structuré.
Mon but était de mettre en évidence la facilité (parmi tant d'autres) que permet l'emploi de cet objet.

Citation:

Envoyé par MarcelG

[...]

Quand même plus simple, car la colonne choisie est celle dont l'entête est sélectionné par la Combobox.[...]

Oui, c'est ce que j'illustrais avec le code que j'ai donné: Set rng = Worksheets("Liste").Cells(20, ComboBox1.ListIndex + 1) qui évite le Find.

Avec un tableau structuré, le code est d'une concision inégalée (quand je dis que l'utilisation des tableaux structurés facilite l'écriture du code...)
Code:

1 2 3 4 5 6 7 Private Sub ComboBox1_Click() ListBox1.List = Range("tableau1").ListObject.ListColumns(ComboBox1.ListIndex + 1).DataBodyRange.Value End Sub Private Sub UserForm_Initialize() ComboBox1.Column = Range("tableau1[#headers]").Value End Sub
Au passage, il faut noter que préfixer la plage d'un tableau structuré de la feuille qui le contient est contreproductif. Les noms des tableaux structurés étant uniques, il est plus concis et surtout plus stable d'utiliser Range("MonTableau").ListObject que MaFeuille.ListObject("MonTableau") puisqu'en cas de déplacement sur une autre feuille, le premier code continuera à fonctionner alors que le second plantera.

D'accord.

Pour ce qui concerne la ligne de départ, on peut par ailleurs cacher les enregistrements et utliiser les cellules visibles pour alimenter la listbox.

Ce qui donnerait

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Option Explicit
 
Public Sub test_alimctrl()
 
Range("T_montablo[leparent]").EntireRow.Hidden = False
 
Dim i As Byte
 
For i = 1 To 2
      Range("T_montablo[leparent]")(i).EntireRow.Hidden = True
Next i
 
With Usf_monboformulaire
      .Lbx_malistbox.Clear
      With .Cbx_macombo
            .Clear
            .Column = Range("T_montablo[#Headers]").Value
            .SetFocus
      End With
      .Show
End With
 
End Sub

(Peut-être, pourrait-on se passer de boucle)

et

Code:

1
2
3
Private Sub Cbx_macombo_Change()
Me.Lbx_malistbox.List = Range("T_montablo[" & Me.Cbx_macombo.Text & "]").SpecialCells(xlCellTypeVisible).Value
End Sub

Ou bien donc

Code:

1
2
3
Private Sub Cbx_macombo_Change()
Me.Lbx_malistbox.List = Range("T_montablo").ListObject.ListColumns(Me.Cbx_macombo.ListIndex + 1).DataBodyRange.SpecialCells(xlCellTypeVisible).Value
End Sub

11/08/2021, 11h33
Pierre Fauconnier

Oui (sans toutefois trop voir le lien avec la question de départ), mais alors si les lignes à masquer se suivent, autant se passer de la boucle (On pourra bien sûr variabiliser les index)

Masquer x lignes à partir de la première

Code:

range("tableau1").Resize(2).EntireRow.Hidden=true

Masquer x lignes à partir d'une ligne précise

Code:

range("tableau1").Resize(1).Offset(4).Resize(10).EntireRow.Hidden=true

Perso, je préfère attendre que Gaëtan nous dise pourquoi il commençait à 20 et qu'il nous montre une copie d'écran, pour éviter de trop digresser.
11/08/2021, 11h37
MarcelG

OK, on attend

J'avais juste (en utilisant un Range de Range)

Code:

Range("T_montablo[leparent]").Range("A1:A2").EntireRow.Hidden = True
11/08/2021, 11h42
Pierre Fauconnier

Code:

Range("T_montablo[leparent]").Range("A1:A2")

C'est intéressant car ça illustre que l'on peut définir la cellule A1 d'une plage (ce qui est assez méconnu), ce qui évite dans certains cas de devoir calculer des décalages... :ccool:
11/08/2021, 20h11
Edge38

Citation:

Envoyé par Pierre Fauconnier

Salut Marcel

Tu dois utiliser la propriété .Column puisque ton Header ne contient qu'une ligne...

Ici, sans voir la configuration de la feuille, je ne suis pas certain qu'il puisse s'agir d'un tableau structuré, puisque l'on part de la 20ième ligne pour remplir la listbox alors que "l'entête" est sur la ligne 1. A voir donc si on a une copie d'écran. Mais bien entendu, je ne peux qu'encourager l'utilisation des tableaux structurés, car le code est alors encore beaucoup plus concis et lisible.

Merci je dois m'améliorer sur les tableaux, je reviens ce soir vers toi si je n'y arrive pas. Encore merci