userform avec listes déroulantes et cases à cocher

[Vilain]

Salut à tous,

Je fais actuellement une macro et bloque sur un élément que j'ai déjà fait il y a quelques temps mais que je ne parviens pas à reproduire...
C'est très simple : j'ai une colonne avec un titre et des données. Je souhaite que mon userform s'ouvre avec une liste déroulante sans doublons et qu'on puisse cocher plusieurs cases (exactement comme un filtre automatique en fait) et qu'une fois validé, la colonne soit filtrée uniquement sur ces données.
Voilà, pas bien sorcier, mais n'ayant plus du tout fait de vba depuis plus de 6 mois, je suis complétement perdu....

D'avance merci.

A plus

 

[Papou-net]

Re : userform avec listes déroulantes et cases à cocher

... pour le reste, je ne souhaire en rien faire "concurrence" à Papou-net et donc ma proposition peut "rester en retrait"

Bonjour Modeste,

Tu es fidèle à ton pseudo, mais il n'y a pas lieu de se faire concurrence sur Xldown. Bien au contraire, surtout quand la réponse ajoute à la créativité. En fait, tu as écrit la solution que je pensais chercher sans en avoir le temps pour le moment.

Toutes mes félicitations, car je ne sais pas si j'aurais pu faire aussi simple. Grâce à toi, j'ai découvert un nouveau mot-clé "Action" que je ne connaissais pas.

Bonne soirée.

Amicalement.

 

[Modeste]

Re : userform avec listes déroulantes et cases à cocher

Bonsoir,

Alors si Papou pas fâché, moi continuer

Un essai en pièce jointe: j'ai (un peu) commenté la partie du code que j'ai "commis", mais pas le code de Papou-net. Je ne voudrais pas le dénaturer (le code, hein, pas Papou-net!)
Je ne sais pas si tu tenais au "Tableau" version 2007-2010 qui figurait dans la feuille "Base" (avec un fichier ".xls", je n'étais pas certain). Je "nettoie" donc la feuille "Base" à chaque exécution de la macro et j'ai ajouté deux lignes à la fin du code pour re-créer la table avec la même (je crois) mise en forme.

Tu testeras ... et tu nous diras si c'est ok?

 

[Papou-net]

Re : userform avec listes déroulantes et cases à cocher

Bonsoir,

Moi pas fâché, alors moi envoyer explications code à moi :

Private Sub UserForm_Activate()
Sheets("Donnees").Range("I2:" & Sheets("Donnees").Range("I" & Rows.Count).End(xlUp).Offset(1, 0).Address).Clear 'effacer les critères en feuil1, colonne I
' La variable chaîne récupère les noms de service, séparés par une virgule
tabl = ""  ' Initialise la variable chaîne
With ListBox1 ' Les actions suivantes s'appliquent à la liste déroulante
  .Height = 12 ' Initialisation de la hauteur du contrôle ListBox1 à 12
  For n = 2 To Feuil1.Range("B" & Rows.Count).End(xlUp).Row ' Boucle qui lit les cellules de la colonne B
    If InStr(tabl, Feuil1.Cells(n, 2).Value) = 0 Then ' Si le nom dans la cellule en cours de lecture n'est pas encore stocké dans tabl
      tabl = tabl & "," & Feuil1.Cells(n, 2).Value ' Elle s'y inscrit à la suite
      .AddItem Feuil1.Cells(n, 2).Value ' et elle s'ajoute dans la liste déroulante
      ' La condition suivante teste la position en pixels du bas de la liste déroulante
      ' Si elle est inférieure au bas du formulaire - 40, la hauteur de la liste augmente de 12 points
      ' (Si le nombre d'éléments de la liste augmente, les flèches de défilement verticales s'affichent)
      If .Top + .Height < UserForm1.Height - 40 Then
        .Height = .Height + 12 ' augmente la hauteur de la liste de 12 points
      End If
    End If
  Next
End With
End Sub

A +

Cordialement.

 

[Vilain]

Re : userform avec listes déroulantes et cases à cocher

Re,
Merci à vous deux pour l'aide que vous m'apportez, j'apprécie réellement le temps que vous m'accordez.
J'avance, mais c'est pas encore ça.
Je suis toujours confrontez à quelques soucis :
Quand je lance ma macro, elle copie ma base entière sur l'onglet BasePourAnalyse et en dessous elle copie uniquement les données que j'ai cocher. Moi je souhaiterais qu'elle copie tout le tableau selon les données que j'ai filtrées !
J'ai un temps réussis à adapter le tout pour que tout soit supprimé avant de copier, mais ça ne marche plus , désormais ça copie en dessous.
Bon, je crois que c'est à peu près tout, mais ce n'est pas très propre.
Je vous joins mon fichier réél, vidé de toute données confidentielles.
Moi qui croyais en avoir pour quelques minutes voir quelques heures sur ce petit bout de code, me voila complétement bloqué depuis 2 jours et mon boss va finir par pas être contente...
Merci d'avance pour toutes les réponses qui seront apportées.

A plus

 

[Modeste]

Re : userform avec listes déroulantes et cases à cocher

Salut tout le monde,

Pour que le filtre élaboré fonctionne, la colonne BI (dans ce cas d'espèce!) devait contenir le même titre que la colonne de ta liste, sur laquelle le critère est vérifié.
Il y avait par ailleurs un endroit (au moins ... je n'ai pas fait le relevé ), dans le code où une colonne I était renseignée en lieu et place de BI.
J'ai supprimé, à la fin du code les lignes qui permettaient de convertir en table, puisque tes "données source" ont déjà une mise en forme (laquelle est recopiée automatiquement, via le filtre)

Les deux-trois petits tests que je viens de faire semblent encourageants ... à toi de voir ce qu'il en sera avec ton fichier réel.

 

[Vilain]

Re : userform avec listes déroulantes et cases à cocher

Re,

Youpi, Joie, bonheur, tout ça tout ça !
J'ai réussi à adapter ton code à mon fichier !
Merci beaucoup à toi, ma santé mentale commencait à décliner

A plus

 

[Vilain]

Re : userform avec listes déroulantes et cases à cocher

Me revoila, quand c'est fini y'en a encore !
J'ai une petite question :
Dans la colonne ou j'ai toutes mes info, j'ai des intitulés qui se ressemblent (4-B et 4-B+ par exemple). Dans ce cas précis, j'ai bien les 2 informations dans l'userform, mais si je ne sélectionne que l'une des deux, dans l'onglet ou la sélection est copiée, j'ai tous les éléments correspondants aux 2 choix (4-B et 4-B+ dans ce cas précis).
Je ne parviens pas à l'éviter. Sais tu comment faire ?

Encore merci pour tout.

A plus

Edit : Enfer et damnation !
J'ai un autre souci !
Si je coche toutes les cases de mon usf, il me manque des lignes dans la copie qui est faite !
Après analyse, j'ai des chiffres dans cette colonne qui vont de 3 à 20. Pour une raison que j'ignore, le 3 n'est pas dans l'usf !
J'ai adapté ce bout de code sur d'autres colonnes et je rencontre le même souci
D'ou ça peut bien venir ?

 

[Vilain]

Re : userform avec listes déroulantes et cases à cocher

Re,

Pour illustrer mon propos, je te joins ton fichier exemple avec simplement le contenu de la colonne incriminée (je n'ai rien changé d'autre, j'ai juste copier coller le contenu de ma colonne). Tu verras que si tu coches tout, il te manque 4 lignes (qui correspondent au chiffre 3).
J'ai beau me creuser la tête...

A plus

 

[Modeste]

Re : userform avec listes déroulantes et cases à cocher

Bonsoir,

Je préférais le message où on lisait "Bonheur, Joie, etc..."

Sauf erreur de ma part, l'absence des '3' dans la ListBox serait liée à la fonction "Instr" qui compare deux chaînes si '13' figure déjà dans la liste, la fonction considère que '3' y figure déjà.
Si Papou-net (ou qui que ce soit d'autre) passe par ici et connaît une solution "propre", qu'il se lève ... et ne se taise jamais ... à défaut, je peux proposer un "bricolage" pour tester l'existence de chaque valeur "encadrée" de 2 virgules. Ca règle ce premier problème. L'autre solution serait d'utiliser un objet "Dictionary", mais demanderait de réécrire le début du code.

Pour ce qui est des "4-B" et "4-B+" je pense savoir où se trouve le souci et entrevois une solution à tester, mais pas sûr d'avoir du temps dans l'immédiat (plus tard en soirée ... ou demain!?)

Voyons déjà si le premier point soulève une nuée de réactions

 

[Papou-net]

Re : userform avec listes déroulantes et cases à cocher

Bonsoir Gillus69,
Bonsoir Modeste,

Sauf erreur de ma part, l'absence des '3' dans la ListBox serait liée à la fonction "Instr" qui compare deux chaînes si '13' figure déjà dans la liste, la fonction considère que '3' y figure déjà.
Si Papou-net (ou qui que ce soit d'autre) passe par ici et connaît une solution "propre", qu'il se lève ... et ne se taise jamais ... à défaut, je peux proposer un "bricolage" pour tester l'existence de chaque valeur "encadrée" de 2 virgules.

Ce n'est pas du bricolage, il suffit effectivement d'ajouter une (et non deux) virgule de séparation dans la fonction Instr, comme par exemple :

Private Sub UserForm_Activate()
Sheets("EmployeesDetails").Range("BI2:" & Sheets("EmployeesDetails").Range("BI" & Rows.Count).End(xlUp).Offset(1, 0).Address).Clear 'effacer les critères en feuil1, colonne BI
tabl = ""
With ListBox1
  .Height = 12
  For n = 2 To Feuil1.Range("T" & Rows.Count).End(xlUp).Row
    If InStr(tabl, "," & Feuil1.Cells(n, 20).Value) = 0 Then
      tabl = tabl & "," & Feuil1.Cells(n, 20).Value
      .AddItem Feuil1.Cells(n, 20).Value
      If .Top + .Height < UserForm1.Height - 40 Then
        .Height = .Height + 20
      End If
    End If
  Next
End With
End Sub

Ainsi, le 3 apparaîtra dans la liste.

Concernant le premier point (4-B, 4-B+) je ne vois pas pourquoi les deux s'affichent : chez moi, j'obtiens bien la liste de l'une ou de l'autre sélection. A moins que quelque chose m'échappe ?

Cordialement.

EDIT : oupsn concernant le premier point, j'ai parlé trop vite : les deux s'affichent effectivement, mais seulement si on choisit "4-B". Affaire à suivre.

 

[Modeste]

Re : userform avec listes déroulantes et cases à cocher

Re-bonsoir,

Ce n'est plus une nuée, c'est un raz-de-marée (moi qui espérais qu'un Père Noël avant l'heure m'aurait évité des recherches ...)
Voici donc une proposition un peu remaniée, testée avec des chiffres seuls, des '10+', des '4-B' et '4-B+' ... à nouveau 2-3 tests encourageants (reste à voir si tu n'as plus d'autres surprises en stock )

Bref, comme d'hab ... à tester soigneusement!

Bonne nuit,

Edit: Aaaargh, pas vu que Papou-net repassait justement (salut et merci à lui ) ... je regarderai la proposition demain: maintenant dodo!

Re-Edit: ... et re-Aaaargh, pressé d'aller rejoindre Morphée, j'ai oublié le fichier ... le voici

 

[Modeste]

Re : userform avec listes déroulantes et cases à cocher

Re-bonjour (oui, je sais: encore! ... mais ça me tarabuste)

En essayant de comprendre la dernière proposition de Papou-net, il me semble que le résultat fourni par la fonction "Instr" dépend (en partie) de l'ordre dans lequel les différents éléments apparaissent dans la colonne T: si '1' figure avant '10', tout se passe normalement. Si '10' a été encodé avant '1' (et donc si 10 est présent dans la chaîne "tabl" avant que le '1' soit testé, Instr renvoit la position du '1' en tant que premier caractère de la chaîne '10' et considère la valeur comme présente)
... Vous me suivez??
Par exemple, si on place, en colonne T (et dans cet ordre) les valeurs suivantes: '4-B+', '4-B', '4-' et '4', seule la première des valeurs est présente dans la ListBox ... ou alors je n'ai pas les yeux en face des trous!? Ma dernière suggestion semble contourner cet écueil (mais s'échoue peut-être lamentablement sur un banc de sable, ailleurs )

 

[job75]

Re : userform avec listes déroulantes et cases à cocher

Bonjour les amis,

Comme vous êtes cool, je viens jouer le 3ème larron :

Option Explicit
Dim plage As Range, n& 'mémorisation des variables

Private Sub ListBox1_Change()
Dim t, cel As Range, filtre As Range
With ListBox1
  If n = 0 Then plage.EntireRow.Hidden = True
  n = n + IIf(.Selected(.ListIndex), 1, -1)
  If n = 0 Then plage.EntireRow.Hidden = False: Exit Sub
  t = .List(.ListIndex)
  For Each cel In plage
    If cel = t Then Set filtre = _
      Union(cel, IIf(filtre Is Nothing, cel, filtre))
  Next
  filtre.EntireRow.Hidden = Not .Selected(.ListIndex)
End With
End Sub

Private Sub UserForm_Initialize()
Dim d As Object, cel As Range
ActiveSheet.AutoFilterMode = False
Set plage = Range("A2", [A65536].End(xlUp))
If plage.Row = 1 Then Exit Sub
n = 0
Set d = CreateObject("Scripting.Dictionary")
For Each cel In plage
  d(cel.Value) = cel.Value
Next
With ListBox1
  .List = d.keys
  .Height = 13.3 * d.Count
  Me.Height = .Height + 25
End With
End Sub

Fichier joint sur Excel 2003.

A+

 

[job75]

Re : userform avec listes déroulantes et cases à cocher

Re,

Il vaut mieux afficher les lignes masquées à l'ouverture de l'USF.

Fichier (2).

Edit : je viens de tester sur Excel 2010.

Il faut remplacer 25 par 15 dans UserForm_Initialize.

A+

 

[job75]

Re : userform avec listes déroulantes et cases à cocher

Re,

Sur mon ordi avec Excel 2010, il faut dimensionner la ListBox avant de la remplir :

With ListBox1
  .Height = 15 + 10 * d.Count
  Me.Height = .Height + 15
  .List = d.keys
End With

Fichier .xlsm joint.

A+