Filtre "automatique" dans listbox d'un userform

bonjour, j'ai un tableau que je fais remonter dans un userform. code vba récupéré qui correspond bien à ce que je veux faire. faire remonter à l'utilisateur des informations d'un tableau et leurs permettre d'effectuer des modifications.

le hic, si on peut dire, c'est que dans mon tableau d'origine, dans la colonne motif (colonne 4), j'aimerai pas que l'utilisateur dans l'userform est accès aux données "supp", "modif" et vide "".

je trouve des moyens de filtrer en cascade (qui implique que l'utilisateur sélectionne des choses), mais je ne trouve pas une solution pour juste exclure ses données de la listbox de l'userform sans intervention de l'utilisateur.

je ne sais pas et surtout comment faire, s'il faut introduire ce filtre "automatique" lors de l'initialisation de l'userform ou lors de l'affichage de la listbox.

code initialize :

Private Sub UserForm_Initialize()
Set f = Sheets("Saisie")
Set Rng = f.Range("A2:J" & f.[A65000].End(xlUp).Row) ' BD (1 colonne de plus)
colInterro = Array(1, 2, 3, 4, 9) ' colonnes à interroger (adapter)
colVisu = Array(1, 2, 3, 4, 9) ' colonnes à visualiser (adapter)
Decal = Rng.Row - 1 ' début de la BD
BD = Rng.Value
Col = UBound(BD, 2): For i = LBound(BD) To UBound(BD): BD(i, Col) = i + Decal: Next i 'no enreg
NcolInt = UBound(colInterro) + 1
Ncol = UBound(colVisu) + 1 ' : ReDim ancien(1 To 1, 1 To Ncol)
'-- en têtes de colonne ListBox
x = Me.ListBox1.Left + 8
Y = Me.ListBox1.Top - 20
For Each K In colVisu
If K < 16 Then
Set Lab = Me.Controls.Add("Forms.Label.1")
Lab.Caption = f.Cells(Decal, K)
Lab.Top = Y
Lab.Left = x
Lab.Height = 24
Lab.Width = f.Columns(K).Width * 1.2
x = x + f.Columns(K).Width * 1.2
temp = temp & f.Columns(K).Width * 1.2 & ";"
End If
Next
temp = Left(temp, Len(temp) - 1)
Me.ListBox1.ColumnCount = UBound(colVisu) + 2
Me.ListBox1.ColumnWidths = temp & ";20"
'-- labels textbox
For i = 1 To Ncol
Me("textbox" & i).Width = f.Columns(colVisu(i - 1)).Width * 1.1
Set Lab = Me.Controls.Add("Forms.Label.1")
K = colVisu(i - 1)
Lab.Caption = f.Cells(Decal, K)
Lab.Top = Me("textbox" & i).Top + 5
Lab.Left = Me("textbox" & i).Left - 75
x = x + f.Columns(K).Width * 0.5
Next
For i = Ncol + 1 To 35: Me("textbox" & i).Visible = False: Next i
'-- génération de choix()
ReDim choix(1 To UBound(BD))
Col = UBound(BD, 2)
For i = LBound(BD) To UBound(BD)
For Each K In colInterro
choix(i) = choix(i) & BD(i, K) & "|"
'If IsDate(BD(i, k)) Then BD(i, k) = Format(BD(i, k), "dd/mm/yyyy")
Next K
choix(i) = choix(i) & BD(i, Col) & "|" ' no enreg
Next i
TriS choix, 1, UBound(choix)
'--- valeurs initiales dans ListBox
Dim TBL(): ReDim TBL(1 To UBound(BD), 1 To Ncol + 1)
For i = 1 To UBound(BD)
c = 0
For Each K In colVisu
c = c + 1: TBL(i, c) = BD(i, K)
Next K
c = c + 1: TBL(i, c) = i + Decal
Next i
TriMultiCol TBL, 1, LBound(TBL), UBound(TBL)
Me.ListBox1.List = TBL
Me.ListBox1.ListIndex = -1
b_ajout_Click
Me.TextBoxRech.SetFocus
End Sub

code listbox

Private Sub ListBox1_Click()
For i = 0 To Ncol - 1
Me("TextBox" & i + 1) = Me.ListBox1.Column(i)
Next i
Me.Enreg = Me.ListBox1.Column(Ncol)
mode = "Modif"
End Sub

d'avance merci de votre aide

Bonjour

Pensez à mettre vos codes entre balises en cliquant sur l'icone </> dans la barre de menu

le hic, si on peut dire, c'est que dans mon tableau d'origine, dans la colonne motif (colonne 4), j'aimerai pas que l'utilisateur dans l'userform est accès aux données "supp", "modif" et vide "".

La textbox pour Motif je vois mais les autres ???
A quoi servent toutes les textbox6 à 35 ?

Pour ne pas donner accès à la textbox4 (motif), dans la subPrivate Sub ListBox1_Click()peut être ajouter cette ligne juste en dessous de FOR i=0 to...

 If i = 3 Then Me("TextBox" & i + 1).Locked = True

Si ok et terminé

Cordialement

Merci Dan, je n'avais pas pensé aux balises codes </> dans le menu ;)

pour les textbox de 6 à 34, elles ne servent à rien, mais figuraient dans le code et je n'ai pas voulu les enlever (ayant eu peur de faire des bêtises).

je les ai supprimer en modifiant :

 For i = Ncol + 1 To 3: Me("textbox" & i).Visible = False: Next i

pour la textbox, j'aimerai juste appliquer un filtre pour ne pas faire apparaître dans la liste proposée dans l'userform les données avec un motif supp, modif ou vide que elles doivent rester dans le tableau source d'origine.

le code que tu m'indiques est pour empêcher la saisie, mais ne permet pas de ne pas afficher des données

If i = 3 Then Me("TextBox" & i + 1).Locked = True

j'aimerai que le tableau source reste :

et que dans l'userform on ne voit que

pour les textbox de 6 à 34, elles ne servent à rien, mais figuraient dans le code et je n'ai pas voulu les enlever (ayant eu peur de faire des bêtises).

je les ai supprimer en modifiant :

Hum c'est un peu compliqué vos codes sachant ce que vous devez afficher. Et surtout pourquoi masquer des textbox qui ne serviront pas
il y aurait eu moyen de faire plus simple et surtout de profiter du tableau structuré que vous avez en feuille Saisie

j'aimerai que le tableau source reste :...... et que dans l'userform on ne voit que

Sinon je ne comprends pas ce que vous voulez faire. c'est la colonne Par qui que vous ne voulez pas voir dans la listbox ou la colonne motif

Edit : ok j'ai compris ce que vous voulez avoir

ne maitrisant pas l'userform, j'ai préféré partir de quelque chose qui ressemble à mon besoin.

pour le coté simple lié a un tableau structuré, je ne sais pas je n'ai jamais pratiqué l'userform, mais je reste ouvert à l'idée

pour mon besoin, je souhaite que tout apparaisse dans l'userform (les 5 colonnes) et qu'on puisse faire les modifications, suppression, mais en enlevant les lignes avec un motif "Supp", "Modif" ou vide "" de la colonne Motif de la feuille Saisie apparaissent dans le résultat de l'Userform, par contre je veux les conserver dans le tableau structuré (source) de la feuille Saisie.

on ne doit pas les voir et pouvoir les modifier via le formulaire userform

(j'espère que c'est un peu plus clair

Remplacez le code initialize par celui ci-dessous

Private Sub UserForm_Initialize()
Dim TS As ListObject
Dim rng As Range
Dim i As Integer

Set TS = Sheets("Saisie").ListObjects(1)
Set rng = TS.DataBodyRange
ncol = 5

With ListBox1
    .ColumnWidths = "60 pt;60 pt;90 pt;50 pt;50 pt"
    .ColumnCount = ncol
End With

For i = 1 To TS.ListRows.Count
    If TS.DataBodyRange(i, 4) <> "supp" And TS.DataBodyRange(i, 4) <> "modif" And TS.DataBodyRange(i, 4) <> "" Then
        With ListBox1
            .AddItem TS.DataBodyRange(i, 1)
            .List(.ListCount - 1, 1) = TS.DataBodyRange(i, 2)
            .List(.ListCount - 1, 2) = TS.DataBodyRange(i, 3)
            .List(.ListCount - 1, 3) = TS.DataBodyRange(i, 4)
            .List(.ListCount - 1, 4) = TS.DataBodyRange(i, 9)
        End With
    End If
Next i
End Sub

Ensuite, corrigez ceci :
- désactivez la ligne qui se trouve juste en dessous de la ligne Option compare text
- toujours en dessous d'option compare text, rajoutez cette ligne :

Dim ncol as byte

- Vous pouvez supprimer toutes les textbox qui ne servent à rien

Dites moi si ok. Après je regarde pour le click dans la listbox

Edit : après je dois savoir si vous conservez la textbox4 et si elle doit être accessible ou non

Vous pouvez aussi rajouter ces lignes en les plaçant juste avant la boucle For i = 1 to TS.listrows.count

For i = 1 To ncol
    Me.Controls("Textbox" & i).AutoSize = True
Next i

cela permettra d'adapter la largeur des textbox en fonction du texte lorsque vous allez cliquer dans la listbox

nickel, effectivement il s'agit des données qui doivent s'afficher dans l'userform ;) sans toucher aux données du tableau.

c'est top, merci

on doit pouvoir pour le reste du formulaire modifier les dates et le motif (pas le reste) ou supprimer la donnée, le tout en selectionnant la ligne et en cliquant sur le bouton adéquate pour que ça fasse les modifications/suppression dans la base source de la feuille Saisie.

(le rng de l'ancien code, si j'ai tout compris permettait de localiser les donner et d'effectuer les modifications ou suppression ?)

cette partie permet de filtrer le tableau structuré définit en amont (?)

If TS.DataBodyRange(i, 4) <> "supp" And TS.DataBodyRange(i, 4) <> "modif" And TS.DataBodyRange(i, 4) <> "" Then
        With ListBox1

et cette partie d'afficher les données colonnes souhaitées (?)

            .AddItem TS.DataBodyRange(i, 1)
            .List(.ListCount - 1, 1) = TS.DataBodyRange(i, 2)
            .List(.ListCount - 1, 2) = TS.DataBodyRange(i, 3)
            .List(.ListCount - 1, 3) = TS.DataBodyRange(i, 4)
            .List(.ListCount - 1, 4) = TS.DataBodyRange(i, 9)

effectivement il s'agit des données qui doivent s'afficher dans l'userform

A quoi sert la textbox ENREG ?

on doit pouvoir pour le reste du formulaire modifier les dates et le motif (pas le reste)

Ok. mais vous devez avoir un bouton Modifier sur l'USF ?

Là plusieurs solutions,

Soit vous bloquez toutes les textbox à l'ouverture de l'USF au départ et le click sur "modifier" autorise l'accès aux textbox pouvant être modifiées

ou on ne bloque rien au départ et le click sur modifier va directement modifier les données

A ce sujet pourquoi ne pas avoir utilisé les boutons proposés par excel ? Je vois que ce sont des images dont il ne semble pas possible de modifier le contenu

le rng de l'ancien code, si j'ai tout compris permettait de localiser les donner et d'effectuer les modifications ou suppression

définie comme elle l'était la variable pouvait être réutilisée dans d'autres codes mais là ce sera à voir plus tard car de ce que j'ai vu, elle n'est utilisée qu'à l'ouverture.

cette partie permet de filtrer le tableau structuré définit en amont (?)

Cela vérifie avant de charger la listbox que dans la colonne 4 du tableau vous n'avez pas motif, supp ou vide

et cette partie d'afficher les données colonnes souhaitées (?)

Oui. Vous avez les colonnes concernées dans l'instruction databodyrange (i, 1) où i est la ligne et 1 est la colonne. Idem pour les autres lignes du code

merci pour ma compréhension du code proposé.

La textbox ENREG pour moi permet de renvoyer la ligne sélectionnée, mais plus je ne saurai vous dire. comme indiqué j'ai récupérer le code sur le/un forum et adapté le visuel de l'userform.

le bouton modifer l'userform s'appelle b_valid et fait parti des boutons du fichier (moi je vais agrémenter avec des images pour un meilleur rendu).

aucun besoin de bloquer les textbox à l'ouverture, puisque le but c'est de modifier, supprimer ou abandonner.

La textbox ENREG pour moi permet de renvoyer la ligne sélectionnée, mais plus je ne saurai vous dire. comme indiqué j'ai récupérer le code sur le/un forum et adapté le visuel de l'userform.

Ok vous pouvez supprimer car je peux ajouter cela dans la listbox. Il faut juste adapter le code Initialize que je peux vous donner quand vous voulez

le bouton modifer l'userform s'appelle b_valid et fait parti des boutons du fichier (moi je vais agrémenter avec des images pour un meilleur rendu).

Non moi j'ai le nom Image12...

aucun besoin de bloquer les textbox à l'ouverture, puisque le but c'est de modifier, supprimer ou abandonner.

Ok. c'était juste que ce que j'ai déjà fait c'est que lorsque vous passer en modification le bouton "Valider" changeait de nom en "modifier". Du coup on utilisait un seul code. Si l'USF sert uniquement pour consulter, alors on peut laisser tomber

Bonjour Dan,

désolé de ma réponse tardive, j'étais parti en weekend et sur mon téléphone portable, ce n'était pas possible de répondre ou de jouer avec excel.

oui vous pouvez me donner le code initialize svp.

les boutons seront en image pour un meilleur rendu, c'est pour ça que que j'ai rajouté les images mais sans adapter de code. le but de l'USF c'est de modifier des données présentes ou de supprimer.

Bonjour,

désolé de ma réponse tardive, j'étais parti en weekend

Pas de souci j'étais aussi en WE...

Voici les changements à effectuer :
1. En dessous de la ligne Option Compare Text, donc au dessus de tous les codes, mettez ceci

Dim ncol As Byte
Dim TS As ListObject
Dim encours As Boolean

2. Code initialize : à remplacer par celui ci-dessous

Private Sub UserForm_Initialize()
Dim rng As Range
Dim i As Integer

Set TS = Sheets("Saisie").ListObjects(1)
Set rng = TS.DataBodyRange
ncol = 6

With ListBox1
    .ColumnWidths = "60 pt;60 pt;90 pt;50 pt;50 pt;50 pt"
    .ColumnCount = ncol
End With

For i = 1 To ncol - 1
    Me.Controls("Textbox" & i).AutoSize = True
Next i

For i = 1 To TS.ListRows.Count
    If TS.DataBodyRange(i, 4) <> "supp" And TS.DataBodyRange(i, 4) <> "modif" And TS.DataBodyRange(i, 4) <> "" Then
        With ListBox1
            .AddItem TS.DataBodyRange(i, 1)
            .List(.ListCount - 1, 1) = TS.DataBodyRange(i, 2)
            .List(.ListCount - 1, 2) = TS.DataBodyRange(i, 3)
            .List(.ListCount - 1, 3) = TS.DataBodyRange(i, 4)
            .List(.ListCount - 1, 4) = TS.DataBodyRange(i, 9)
            .List(.ListCount - 1, 5) = i
        End With
    End If
Next i
End Sub

3. Code pour la sélection dans la lisbox

Private Sub ListBox1_Click()
Dim i As Byte

If encours = True Then Exit Sub
For i = 0 To ncol - 2
    Me("TextBox" & i + 1) = Me.ListBox1.Column(i)
Next i
End Sub

Faites quelques tests
restera le code pour la suppression que je peux vous donner après

Crdlt

re,

j'ai modifié les codes.

j'accède bien aux lignes voulues et je peux faire des modifications dans les textbox, par contre pas de validation de la saisie (?) ou je n'ai pas vu et rien ne se fait.

les différentes modifications, ne me permettent plus de faire des recherches intuitives sur des critères noms, date ou période, est ce normal ?

retour du weekend dur, je pense que je passe peut être à coté de quelque chose ;)

re

par contre pas de validation de la saisie (?) ou je n'ai pas vu et rien ne se fait.

Non je n'ai pas encore regardé ce point là.

les différentes modifications, ne me permettent plus de faire des recherches intuitives sur des critères noms, date ou période, est ce normal ?

Oui c'est aussi à voir. Là pour la recherche je voudrais que vous confirmiez les colonnes : Colonnes I, E.
Pour les dates il y a 3 colonnes. On prend la colonne F ?

oufff j'ai eu peur

donc les colonnes qui apparaissent dans le rendu du code son bonnes.

colonne : A,B,C,D et I(i). tout colle. il s'agit bien de ces colonnes pour la recherche intuitive et la modification.

Pour la suppression, si on peut faire : laisser la ligne mais modifier le motif par Supp ce qui automatiquement laissera dans le tableau source et ne figurera plus dans l'USF et si on peut indiquer la date de suppression en colonne M ?

Pour la suppression, si on peut faire : laisser la ligne mais modifier le motif par Supp ce qui automatiquement laissera dans le tableau source et ne figurera plus dans l'USF et si on peut indiquer la date de suppression en colonne M ?

Ce que j'avais prévu c'est la suppression dans la listbox et dans la feuille. Si je vous comprends on garde la ligne dans la feuille avec ajout de date en colonne M.
Si oui, voici le code à ajouter pour cette suppression

Private Sub CommandButton1_Click() 'supprimer
Dim lig As Integer

With ListBox1
    On Error Resume Next
    lig = .List(.ListIndex, 5)
    If lig = 0 Then MsgBox "Veuillez sélectionner la ligne à supprimer", vbCritical, "Suppression": Exit Sub
    encours = True
    .RemoveItem (.ListIndex)
    encours = False
End With
TS.DataBodyRange(lig, 13) = Now 'date et heure
End Sub

Il faudrait que je connaisse le nom du bouton supprimer. Faites un double click dessus pour savoir
Après remplacer Commanbutton1_click par le nom de votre bouton

Bonjour Dan,

c'est l'image14 ;) merci, le timestamp (jour+heure) ce met bien en place sur la ligne sélectionnée.

est ce que pour aussi modifier le motif il faut rajouter (?) :

TS.DataBodyRange(lig, 4) = "supp" 'modification du mot

j'ai fait le test et ça marche . je vous remets le fichier avec les modifications que vous m'avez proposé

c'est top, merci

Bonjour,

Vous devriez redimensionner l'USF qui est trop grande. Il a pas mal d'espace vide. A vous de voir mais il y a trop d'espace perdu et selon la dimension de l'écran, on est obligé de "scroller".

Voici quelques commentaires :
- En dessous de Option compare text, je vous l'avais déjà écrit, désactivez ou supprimez la ligne Dim f ....... Elle ne sert pas et vous déclarez des variables inutilement
- Ok pour le code Image14. Là je pense que vous pourriez plutot donner un nom à votre bouton (ex : Valider) et modifier Image14 par Valider ou alors mettre un commentaire à coté de Private Sub Image14_Click() pour que l'on sache de quoi il s'agit.
Si oui donnez moi le nom, j'adapterai dans votre fichier. Si non, je laisse tel quel.
- Désactivez tous les codes Private Sub b_xxx_Click() et les codes Private Sub Tri. Ils ne servent plus

Je passe au code Valider (actuellement image12)
- Doit-on effacer les Textbox après validation
- Doit-on remettre à jour la listbox une fois la validation effectuée

Merci de ne pas me poster un fichier. J'ai votre fichier de travail sur lequel je fais les tests

re,

je redimensionnerai l'USF à la fin mais c'est prévu. au début je voulais supprimer tous les boutons et les codes qui existaient avant que vous me donniez le nouveau code mais je n'ai pas osé.

oui le nom plus parlant sera mis aussi à la fin (ou au pire une explication complémentaire dans le code par ' )

dans l'idée c'est de "valider" la modification et fermer l'USF (donc pas d'actualisation et effacement de données, puis l'Initialize le gère ;))

peut on imaginer comme pour la suppression se baser sur la ligne sélectionner dans l'USF en modification, effectuer la modification et à la validation, que sur la ligne d'origine le motif passe à modif et qu'une nouvelle ligne soit créer avec les données modifiées :

ex : car je ne sais pas si mon idée est bien formulée :

je sélectionne la ligne 3

je modifie le motif M par CP.

lors de la validation, ça génère une nouvelle ligne pour l'ensemble des données et passe la ligne 3 du motif "M" à "modif" avec la date en colonne H

l'idée étant un style d'historisation.

re,

lors de la validation, ça génère une nouvelle ligne pour l'ensemble des données et passe la ligne 3 du motif "M" à "modif" avec la date en colonne H

- Heu vous voulez donc que si on valide cela ajoute une nouvelle ligne à la base ? waouh.... là cela va nécessiter de vérifier qu'une des données de la ligne a été modifiée... à réfléchir...
Vous n'aviez pas expliqué cela au départ donc je pensais que vous alliez modifier uniquement la ligne concernée.

- Ensuite après validation vous fermez l'USF ? C'est le plus simple mais si vous devez modifier deux lignes ?

Si j'étais vous, je changerais la textbox modifie en combobox pour faire un choix dans une liste plutôt que d'ajouter manuellement