Menu déroulant dans userforme

Private Sub UserForm_Initialize()
    Dim i%, n%, a
    With [BBato]
        For i = 1 To .Rows.Count
            If .Cells(i, 1) <> "" Then a = a & ";" & .Cells(i, 1)
        Next
    End With
    If a <> "" Then
        a = Replace(a, ";", "", 1, 1)
        a = Split(a, ";")
        If UBound(a) > 0 Then
            CB1.List = a
        ElseIf UBound(a) = 0 Then
            CB1.AddItem a(0)
        End If
    End If
    With [Opér1]
        For i = 1 To 5
            If .Cells(2, i) <> "" Then
                n = .Cells(1, i).End(xlDown).Row
                a = Range(.Cells(2, i), .Cells(n, i)).Value
                If UBound(a) > 1 Then
                    Controls("cbxOp" & i).List = a
                ElseIf UBound(a) = 1 Then
                    Controls("cbxOp" & i).AddItem a(1, 1)
                End If
            End If
        Next i
    End With
End Sub

La procédure Initialize reprend la constitution de la liste de CB1 qu'elle faisait déjà, et s'y ajoute la constitution des listes des ComboBox opérateurs.

Ce que je n'ai pas conservé du code antérieur, l'instruction End (cette instruction, y compris dans les autres procédures où elle était utilisée). Cette instruction met fin, mais à tout ! Elle force l'arrêt complet, et vide la mémoire, peut même fermer des fichiers ouverts en cours d'exécution... Bref, étant en situation normale, autant user des sorties normales, car là on risque d'avoir des surprises par la suite.

J'ai en outre apporté une modification, non déterminée par l'utilisation de nom pour pouvoir réutiliser les même variables dans les deux phases de la procédure. a sous forme tableau dynamique n'était pas réutilisable avec la méthode de constitution des liste opérateurs (ou en changeant de méthode mais qui aurait été moins rapide).

J'ai donc un peu modifié la méthode de constitution du tableau pour CB1. a est déclaré comme Variant (toute variable non typée est de type Variant), donc pouvant accueillir un tableau.

Le tableau est constitué en parcourant la plage BBato, comme précédemment, sauf qu'ici on ne fait plus référence à la feuille, la plage nommée suffit, en recueillant les éléments sous forme de chaîne en les séparant par des points-virgules, chaîne que l'on splitte pour obtenir le tableau final.

Autre modification, ou plus exactement ajout complémentaire, en raison du fait que la propriété List ne fonctionne pas s'il n'y a qu'un seul élément à affecter à la liste, il faut alors utiliser AddItem. Même si le cas de doit jamais se produire (comme le cas où l'on n'a pas d'élément, mais que l'on prévoit par sécurité), il convient de le prévoir, d'où la mise sous condition pour affecter List...

Pour les opérateurs, on utilise le nom Opér1 comme point d'appui. A partir de cette cellule, une boucle de 1 à 5 nous permet de parcourir les 5 colonnes opérateurs (sur la ligne 1). Pour chaque colonne on dimensionne la taille et on affecte les valeurs de la ligne 2 à la dernière au tableau a.

Cela se passe comme précédemment, mais multiplié par 5.

Les petites différences que l'on notera entre les deux phases tiennent au fait que Split produit un tableau de base 0 à une dimension, alors que l'affectation d'une plage (ses valeurs) produit un tableau de base 1 à deux dimensions.

A l'ouverture du Userform, CB1 sera munie de sa liste, de même que cbxOp1 à 5.

Pour ce qui est des procédures Change de CB1 et CB2, lesquelles mettent en place les listes de CB2 et CB3 respectivement, l'utilisation du nom BBato ne modifie pas la méthode de constitution.

Seul élément que j'ai rectifié, l'insertion d'un caractère 160 (espace insécable) lorsque le ListIndex de la Combo est à -1 (soit pas de sélection dans la liste), lequel pourrait poser problème et que rien ne vient justifier. On a intérêt au cas où une saisie non conforme serait effectuée, à la remplacer par rien (""), rien que l'on pourra facilement tester à la fin (alors que ce caractère aurait permis de valider une mention inexistante, le caractère 160 n'étant pas rien mais demeurant invisible).

    i = Application.Match(CB1.Value, [BBato], 0)
    n = [BBato].Cells(i, 1).MergeArea.Rows.Count

Pour CB1_Change, ci-dessus, on procède comme précédemment en recherchant l'occurence de la sélection dans la plage BBato, avec Match (c'est la fonction EQUIV d'Excel).

Puis on prend le nombre de lignes de la cellule fusionnée trouvée.

Partant du nom de plage nommée, on définit directement en référence à cette plage, la plage de recherche (qui avant était définie par l'intersection des lignes de la cellule fusionnée trouvée et de la colonne connue).

    With [BBato].Cells(i, 3).Resize(n)
        n = 0
        For i = 1 To .Rows.Count
            If .Cells(i, 1) <> "" Then
                ReDim Preserve a(n)
                a(n) = .Cells(i, 1)
                n = n + 1
            End If
        Next i
    End With

La suite le montre : on prend la cellule sur la ligne i trouvée et sur la colonne 3 à partir de la plage nommée qui est en A, que l'on redimensionne au nombre de lignes voulues. Le résultat est le même, le reste du code se déroule de la même façon...

Une petite remarque (pas inutile je pense), les variables i et n ont été utilisées en amont pour définir la ligne de départ et le nombre de lignes à parcourir. Une fois leur utilisation bouclée sur la ligne de code With... qui définit la plage à parcourir (VBA la met alors en mémoire), les variables i et n deviennent disponibles pour une nouvelle utilisation, ce qui est fait immédiatement à la suite, après avoir réinitialisée n à 0.

Même schéma pour CB2, à ceci près que l'occurrence de la valeur sélectionnée dans la Combo ne se trouve pas dans BBato mais dans une plage décalée de 2 colonnes :

    i = Application.Match(CB2.Value, [BBato].Offset(, 2), 0)
    n = [BBato].Offset(, 2).Cells(i, 1).MergeArea.Rows.Count

Mais pour ce qui est de la plage à parcourir pour constituer la liste de CB3, on peutplus simplement repartir de BBato, le numéro de colonne définissant la colonne qu'on cible.

    With [BBato].Cells(i, 6).Resize(n)

Les TB1 et TB2 sont servis selon la sélection de CB2...

Venons-en maintenant aux sélections dans les listes d'opérateurs :

Private Sub cbxOp1_Change()
    If cbxOp1.ListIndex = -1 Then cbxOp1.Value = ""
    NbOpér
End Sub

Une procédure Change renvoie à une proc. : NbOpér. Même chose pour les 4 autres Combo.

Pour TB3 (opérateur 6), évènement AfterUpdate plutôt que Change (qui se déclenche à chaque touche frappée...)

Private Sub TB3_AfterUpdate()
    NbOpér
End Sub

La procédure appelée calcule le nombre d'opérateurs :

Sub NbOpér()
    Dim i%, n%
    For i = 1 To 5
        If Controls("cbxOp" & i).Value <> "" Then n = n + 1
    Next i
    If TB3.Value <> "" Then n = n + 1
    TB4.Value = n
End Sub

Enfin la procédure d'affectation :

Private Sub CommandButton1_Click() 'Valider
    Dim Lgn(), Ctl, i%, n%
    If CB1.Value = "" Then CB1.DropDown: Exit Sub
    If CB2.Value = "" Then CB2.DropDown: Exit Sub
    If CB3.Value = "" Then CB3.DropDown: Exit Sub
    Ctl = Split("CB1 CB2 TB1 TB2 CB3 cbxOp1 cbxOp2 cbxOp3 cbxOp4 cbxOp5 TB3 TB4 TB5 TB6")
    ReDim Lgn(UBound(Ctl))
    For i = 0 To UBound(Ctl)
        If Controls(Ctl(i)).Value <> "" Then Lgn(i) = Controls(Ctl(i)).Value
    Next i
    With [Tableau1]
        With .Worksheet
            If .FilterMode Then .ShowAllData
        End With
        n = IIf(.Cells(1, 1) <> "", .Rows.Count + 1, 1)
        .Cells(n, 1).Resize(, UBound(Lgn) + 1).Value = Lgn
    End With
    CB1.Value = "" 'RAZ
    For i = 1 To 5
        Controls("cbxOp" & i).Value = ""
    Next i
    For i = 3 To 6
        Controls("TB" & i).Value = ""
    Next i
End Sub

Selon ton code antérieur, seules les saisies dans les Combo CB1, CB2 et CB3 sont considérées comme obligatoires. J'ai conservé les 3 lignes qui opèrent ce contrôle mais il faudra compléter si tu considères d'autres saisies comme devant être obligatoirement faites pour donner cours à la validation.

Pour la validation, on va opérer, non pas cellule par cellule à partir des contrôles, mais en constituant un tableau auquel on affecte les valeurs des contrôles dans l'ordre de leur emplacement sur la ligne.

Ensuite on affectera directement ce tableau (horizontal car à une dimension) à la ligne directement placée sous le tableau existant, laquelle y sera automatiquement incorporée.

Pour affecter les valeurs au tableau, on ne va pas non plus le faire élément par élément, mais en boucle, ce qui sera moins long à écrire, et aussi moins long à exécuter...

Méthode simple parmi d'autres pour faire une boucle quand les éléments invoqués ne s'y prêtent pas spontanément... On liste dans l'ordre les contrôles à invoquer en formant un texte avec les noms séparés par des espaces. En appliquant la fonction Split à cette chaîne on obtient un tableau de base 0 dont chaque élément sera le nom d'un contrôle (l'espace étant le séparateur par défaut, on peut l'omettre en invoquant la fonction).

Ctl = Split("CB1 CB2 TB1 TB2 CB3 cbxOp1 cbxOp2 cbxOp3 cbxOp4 cbxOp5 TB3 TB4 TB5 TB6")

Ctl étant alors un tableau de 14 éléments, on peut dimensionner pareillement un tableau et, faisant une boucle sur le tableau Ctl, affecter à chaque élément de notre tableau final la valeur du contrôle indiqué par l'élément correspondant du tableau Ctl.

    ReDim Lgn(UBound(Ctl))

Plutôt que dimensionner le tableau Lgn directement à 13, on le dimensionne sur la taille de Ctl, car si des évolutions font que le nombre de contrôles à affecter diminue ou augmente, on aura à modifier nécessairement le tableau Ctl, mais le reste suivra sans qu'on ait à y retoucher...

Et voilà notre boucle d'affectation au tableau :

    For i = 0 To UBound(Ctl)
        If Controls(Ctl(i)).Value <> "" Then Lgn(i) = Controls(Ctl(i)).Value
    Next i

On n'affecte que si valeur à affecter, car selon le cas une valeur "vide" pourrait au final se traduire par un 0 dans une cellule, ce qui est toujours déplaisant.

Et pour l'affectation, on va opérer avec Tableau1 :

    With [Tableau1]
        With .Worksheet
            If .FilterMode Then .ShowAllData
        End With
        n = IIf(.Cells(1, 1) <> "", .Rows.Count + 1, 1)
        .Cells(n, 1).Resize(, UBound(Lgn) + 1).Value = Lgn
    End With

On maintient évidemment le défiltrage du tableau au cas où, et tu peux noter à cet égard que .FilterMode et .ShowAllData devant référer à la feuille, on peut atteindre facilement la feuille à partir de la plage par la propriété .Worksheet de l'objet Range (ou également la propriété .Parent) [et sans avoir besoin de son nom!]

Avec n = .Rows.Count + 1 référant au Tableau, on détermine la ligne d'insertion relative au tableau.

La condition vérifiant si la permière cellule du tableau contient une valeur permet de démarrer avec un tableau vide. Un tableau Excel contient obligatoirement une ligne de données pour être défini. Si on insérait à la première ligne suivante au démarrage, on sauterait cette première ligne vide...

Pour l'affectation, on dimensionne la ligne d'affectation à la taille des donénes à affecter et on lui affecte le tableau.

Avec cette méthode, on a moins de choses à écrire (et à lire ensuite) et c'est plus rapide.

Cordialement.

tout un tas d'informations ! GRAND MERCI c'est très généreux de ta part et je comprends bien ce que tu veux faire. merci surtout d'avoir analyser le besoin suite à une simple demande tu as fais une maintenance générale.

Pour les chats je suis super fan je croix que je peux t'aider à les comprendre au mieux (les chats pour moi comme VBA pour toi) et c'est totalement vrai l'homme prétende propriétaire des chats mais c'est totalement le contraire

j'analyse tout ça et je reviens vers toi je te montre l’application entière.

Bonne soirée

Je croix que je dois rentrer sur cette discussion tous les matin et faire des compléments

Très propre comme travail.

Merci MFerrand je te contacterai au privé pour pouvoir te donner une version du fichier complet

Bonjour M MFerrand,

Je me permets de revenir sur ce sujet car je rencontre une difficulté que j'ai pas pu résoudre. En effet sur l'ancienne version j'avais la possibilité de copier une partie d'un planning qui existe déjà et puis je continu à importer de nouvelles tâche.

Avec cette version ça crée un dysfonctionnement.

La solution est de faire l'instruction d'importation sur la 1ere ligne qui est vide mais je ne sais pas dans quelle endroit je doit rajouter cette instruction ni la formuler d'une manière exacte

si ça marche c'est juste une erreur de ma part

Bonjour M.MFerrand,

Je confirme qu'il y'a un petit souci car j'ai oublié de motionner que j'ai des formules sur ma tableau dans la feuil de planification et pour cela le tableau est pré-dimensionné sur cette feuil à 250 lignes.

Le problème réside dans la partie Redim du tableau

Private Sub CommandButton1_Click() 'Valider
    Dim Lgn(), Ctl, i%, n%
    If CB1.Value = "" Then CB1.DropDown: Exit Sub
    If CB2.Value = "" Then CB2.DropDown: Exit Sub
    If CB3.Value = "" Then CB3.DropDown: Exit Sub
    Ctl = Split("CB1 CB2 TB1 TB2 CB3 cbxOp1 cbxOp2 cbxOp3 cbxOp4 cbxOp5 TB3 TB4 TB5 TB6")
    ReDim Lgn(UBound(Ctl))
    For i = 0 To UBound(Ctl)
        If Controls(Ctl(i)).Value <> "" Then Lgn(i) = Controls(Ctl(i)).Value
    Next i
    With [Tableau1]
        With .Worksheet
            If .FilterMode Then .ShowAllData
        End With
        n = IIf(.Cells(1, 1) <> "", .Rows.Count + 1, 1)
        .Cells(n, 1).Resize(, UBound(Lgn) + 1).Value = Lgn
    End With
    CB1.Value = "" 'RAZ
    For i = 1 To 5
        Controls("cbxOp" & i).Value = ""
    Next i
    For i = 3 To 6
        Controls("TB" & i).Value = ""
    Next i
End Sub

Je croix que la meilleur solution est de partir sur un remplissage sur dernière ligne qui est vide avec la procédure xlup que j'ai essayé de mettre en place mais je créer des dysfonctionnements plus que des solutions.

Sauf ça tout le reste fonctionne et parfaitement adapté à mon besoin.

Bonjour,

Justement, pour être bien utilisé, un tableau Excel ne doit pas être prédimensionné !

La logique étant d'inscrire sous le tableau, de façon que la ligne inscrite y soit automatiquement incorporée. Sinon, c'est toute une gymnastique, car End(xlUp) utilisé de façon classique renverra la dernière ligne du tableau sans se préoccuper qu'elle soit vide ou non !

Je ne dis pas que c'est impossible à gérer, mais cela revient à abandonner les simplifications qu'apportait le tableau Excel...

J'ai le sentiment que ton souci viendrait du tableau juxtaposé après la colonne U qui fait coupure, soit de V à CW, dont je n'ai pas cherché à creuser l'utilisation dans la mesure où cela était extérieur à la question.

Mais si le souci vient de ce tableau Planning, il me semble que c'est à ce niveau qu'il faut le régler. Il faudrait alors que tu en dises un peu plus sur son utilisation...

Cordialement.

pour chaque ligne j'ai une tâche qui à un durée (temps de phase) et pour faciliter le calcule j'ai des formules qui calcule l'heure de début (suivant le Gantt avec ENDEXEQUIV) et une formule qui calcule l'heure de fin ( H début + temps de phase + pause) et une formule qui numérote les tâches dans la colonne N de tâche).

je souhaite que ces formule reste sur un certain nombre de ligne là je suis aller jusqu'a 250 pour que si l'utilisateur supprime des lignes importés ou voir supprime tout le planning il aura les formule qui restent incrémentés dans les bonne cellules.

Je te joint un exemple au privé du tableau complet sans les données mais qui reste lui meme confidentiel.

Si on peut pas le faire ou si ça révolutionne le travail on laise les choses comme ça je vais mettre toutes les formules à part et une fois elle sont supprimés ils font copier la formule