Modifier supprimer userform

Le 21/04/2016 à 08:21

Bonjour au forum

Dans le fichier joint j'ai un userform qui alimente une feuille(Ressource CODD), je souhaiterai pouvoir modifier et supprimer des données après sélection dans un combobox.

Je ne sais pas comment m'y prendre

la combobox Recherche correspond à la colonne B "Nom"

Je cherche à charger les texbox et combobox en fonction des lignes de la feuille "Ressources CODD" et en cas de modification les combobox doivent aller chercher les données dans la feuille "Onglet Listes"

Bonne journée

237modifier-supprimer.xlsm (172.57 Ko)
Le 21/04/2016 à 16:12

Bonjour Citaro, bonjour le forum,

Il y avait une histoire de "signature" à l'ouverture de ton fichier et je l'ai renommé et ça passe...

J'ai pas mal remanié. Les contrôles (Comboboxes, TextBoxes, Checkboxes) ont été affublés d'une valeur pour leur propriété [Tag] correspondant au numéro de la colonne dans laquelle la valeur du contrôle doit être renvoyée.

Comme je ne savais pas si tu pouvais avoir plusieurs fois la même personne (avec des données différentes) dans la base, j'ai rajouter une ListBox (ListBox1).

Quand tu recherches dans la ComboBox1 :

• si la personne n'apparaît qu'une seule fois, ces données sont renvoyées dans les différents contrôle.

• Si la personne apparaît plusieurs fois, la ListBox1 se remplie, affichant toutes les lignes où elle apparait (les 10 premières colonnes)

• les boutons Modifier et Supprimer sont visibles

• Le bouton Ajouter est masqué

Quand tu n'as pas utilisé la recherche:

• Tu remplies les différents contrôles

• les boutons Modifier et Supprimer sont masqués

• Le bouton Ajouter est visible

C'est la variable LI qui en fonction de l'action effectuée sera définie comme étant la première ligne vide ou la ligne d'un élément sélectionné dans la ComboBox1 (si unique) dans la ListBox1 (si multiple).

Le code :

Private R As Worksheet 'déclare la variable R (onglet Ressources CODD)
Private L As Worksheet 'déclare la variable L (onglet Listes)
Private TV As Variant 'déclare la variable TV (Tableau des valeurs)
Private LI As Integer 'déclare la variable LI (LIgne)

Private Sub UserForm_Initialize() 'à l'initialisation de l'USerform
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)

Me.ListBox1.ColumnCount = 11 'définit la nombre de colonne de la ListBox1
Me.ListBox1.ColumnWidths = "0" 'masque la première colonne de la Listbox1
Set R = Sheets("Ressources CODD") 'définit l'onglet R
Set L = Sheets("Onglet Listes") 'définit l'onglet R
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
TV = R.Range("A5:J" & R.Cells(Application.Rows.Count, 2).End(xlUp).Row) 'définit la tableau des valeurs TV (les 10 premières colonnes en partant de la ligne 5...)
For I = 1 To UBound(TV, 1) 'boucle sur toutes les lignes I du tableau des valeurs TV
    'slimente le dictionnaire D avec nom/espace/prénom de chaque ligne I
    D(TV(I, 2) & " " & TV(I, 3)) = TV(I, 2) & " " & TV(I, 3)
Next I 'prochaine ligne de la boucle
Me.ComboBox1.List = D.Keys 'alimente la ComboBox1 avec la liste du dictionnaire D sans doublon
Me.CbFicheComplete.List = R.Range("AZ2:AZ4").Value 'alimente la combobox
Me.CbDeptCODD.List = L.Range("F7:F" & L.Cells(Application.Rows.Count, 6).End(xlUp).Row).Value 'alimente la combobox
Me.CbChampPro.List = L.Range("B7:B" & L.Cells(Application.Rows.Count, 2).End(xlUp).Row).Value 'alimente la combobox
Me.CbStructure.List = L.Range("G7:G" & L.Cells(Application.Rows.Count, 7).End(xlUp).Row).Value 'alimente la combobox
Me.CbPerimetre.List = L.Range("AG7:AG" & L.Cells(Application.Rows.Count, 33).End(xlUp).Row).Value 'alimente la combobox
LI = R.Cells(Application.Rows.Count, 2).End(xlUp).Row + 1 'définit la ligne LI (première ligne vide de la colonne B)
Me.CommandButton1.Visible = False 'masque le bouton "Modifier"
Me.CommandButton2.Visible = False 'masque le bouton "Supprimer"
End Sub

Private Sub ComboBox1_Change() 'au changement dans la ComboBox1
Dim I As Integer 'déclare la variable I (Incrément)
Dim J As Byte 'déclare la variable J (incrément)
Dim K As Integer 'déclare la variable K (incrément)
Dim L As Byte 'déclare la variable L (incrément)
Dim TL() As Variant 'déclare la variable TL (Tableau des Lignes)
Dim CTRL As Control 'déclare la variable CTRL (ConTRôLe)

Me.ListBox1.Clear 'vide la Listbox1
'si la ComboBox1 est effacée
If Me.ComboBox1.Value = "" Then
    LI = R.Cells(Application.Rows.Count, 2).End(xlUp).Row + 1 'définit la ligne LI (première ligne vide de la colonne B)
    Me.TbxNom.SetFocus 'place la curseur dans la texbox [TbxNom]
    Me.CommandButton1.Visible = False 'masque le bouton "Modifier"
    Me.CommandButton2.Visible = False 'masque le bouton "Supprimer"
    Me.CommandButtonAjouter.Visible = True 'affiche le bouton "Ajouter"
    Exit Sub 'sort de la procédure
End If 'fin de la condition
Me.CommandButtonAjouter.Visible = False 'masque le bouton "Ajouter"
Me.CommandButton1.Visible = True 'affiche le bouton "Modifier"
Me.CommandButton2.Visible = True 'affiche le bouton "Supprimer"

K = 1 'initialise K
For I = 1 To UBound(TV, 1) 'boucle 1 : sur toutes les lignes I du tableau des valeurs TV
    If TV(I, 2) & " " & TV(I, 3) = Me.ComboBox1.Value Then 'si le nom/espace/prénom de la ligne est égal à la valeur de la ComboBox1
        ReDim Preserve TL(1 To 11, 1 To K) 'redimensionne le tableau des lignes TL
        TL(1, K) = I + 4 'récupère dans la ligne 1 le numéro de la ligne
        For J = 2 To 11 'boucle 2 : sur les 10 autres colonnes de la ListBox1
            TL(J, K) = TV(I, J - 1) 'récupère dans la ligne J colonne K de TL, la donnée ligne I colonne J-1 de TV (Transposition)
        Next J 'prochaine colonne de la boucle 2
        K = K + 1 'incrément K (ajoute une colonne au tableau des lignes TL
    End If 'fin de la condition
Next I 'prochaine ligne de la boucle 1
If K > 1 Then 'condition 1 : si K est supérieur à 1 (=> au moins une occurrence a été trouvée)
    If K = 2 Then 'condition 2 : une seule occurrence a été trouvée
        LI = CInt(TL(1, 1)) 'définit la ligne LI (récupérée dans ligne 1 colonne 1 du tableau des lignes TL)
        For Each CTRL In Me.Controls 'boucle sur tous les contrôle de l'UserForm
            'condition 3 : si le contrôle est une TextBox ou une ComboBox
            If TypeOf CTRL Is MSForms.TextBox Or TypeOf CTRL Is MSForms.ComboBox Then
                'si le contrôle n'est la la ComboBox1, récupère pour le contrôle la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte)
                If Not CTRL.Name = "ComboBox1" Then CTRL.Value = R.Cells(LI, CByte(CTRL.Tag)).Value
            'condition 3 : si le contrôle est une CheckBox
            ElseIf TypeOf CTRL Is MSForms.CheckBox Then
                'si la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte) est égale à "X" coche la CheckBox, sinon, elle est décochée
                If R.Cells(LI, CByte(CTRL.Tag)).Value = "X" Then CTRL.Value = True Else CTRL.Value = False
            End If 'fin de la condition 3
        Next CTRL 'prochaine contrôle de la boucle
        Exit Sub 'sort de la procédure
    End If 'fin de la condition 2
    Me.ListBox1.List = Application.Transpose(TL) 'alimente la ListBox1 avec le tableau TL transposé
End If
End Sub

Private Sub ListBox1_Click() 'au clic dans la ListBox1
Dim CTRL As Control 'déclare la variable CTRL (ConTRôLe)

With Me.ListBox1 'prend en compte la ListBox1
    LI = CInt(.Column(0, .ListIndex)) 'définit le numéro de ligne LI (dans la colonne 0 (masquée) de la ListBox1)
End With 'fin de la prsie en compte de la Listbox1
For Each CTRL In Me.Controls 'boucle sur tous les contrôle de l'UserForm
    'condition 1 : si les contrôle est une TextBox ou une ComboBox
    If TypeOf CTRL Is MSForms.TextBox Or TypeOf CTRL Is MSForms.ComboBox Then
        'si le contrôle n'est la la ComboBox1, récupère pour le contrôle la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte)
        If Not CTRL.Name = "ComboBox1" Then CTRL.Value = R.Cells(LI, CByte(CTRL.Tag)).Value
    'condition 3 : si le contrôle est une CheckBox
    ElseIf TypeOf CTRL Is MSForms.CheckBox Then
        'si la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte) est égale à "X" coche la CheckBox, sinon, elle est décochée
        If R.Cells(LI, CByte(CTRL.Tag)).Value = "X" Then CTRL.Value = True Else CTRL.Value = False
    End If 'fin de la condition 1
Next CTRL 'prochaine contrôle de la boucle
End Sub

Private Sub CommandButtonAjouter_Click()
For Each CTRL In Me.Controls 'boucle sur tous les contrôle de l'UserForm
    'condition 1 : si les contrôle est une TextBox ou une ComboBox
    If TypeOf CTRL Is MSForms.TextBox Or TypeOf CTRL Is MSForms.ComboBox Then
        'si le contrôle n'est la la ComboBox1, récupère pour le contrôle la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte)
        If Not CTRL.Name = "ComboBox1" Then R.Cells(LI, CByte(CTRL.Tag)).Value = CTRL.Value
        If CTRL.Tag = "2" Then R.Cells(LI, CByte(CTRL.Tag)).Value = UCase(CTRL.Value)
        If CTRL.Tag = "3" Then R.Cells(LI, CByte(CTRL.Tag)).Value = Application.WorksheetFunction.Proper(CTRL.Value)
    'condition 3 : si le contrôle est une CheckBox
    ElseIf TypeOf CTRL Is MSForms.CheckBox Then
        'si la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte) est égale à "X" coche la CheckBox, sinon, elle est décochée
        If CTRL.Value = True Then R.Cells(LI, CByte(CTRL.Tag)).Value = "X" Else R.Cells(LI, CByte(CTRL.Tag)).Value = ""
    End If 'fin de la condition 1
Next CTRL 'prochaine contrôle de la boucle
R.Cells(LI, 1).Value = Left(Cells(LI, 2).Value, 4) & Left(Cells(LI, 3).Value, 1)
tri_ressource
Unload Me 'vide et ferme l'UserForm
End Sub

Private Sub CommandButton1_Click() 'bouton "Modifier"
For Each CTRL In Me.Controls 'boucle sur tous les contrôle de l'UserForm
    'condition 1 : si les contrôle est une TextBox ou une ComboBox
    If TypeOf CTRL Is MSForms.TextBox Or TypeOf CTRL Is MSForms.ComboBox Then
        'si le contrôle n'est la la ComboBox1, récupère pour le contrôle la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte)
        If Not CTRL.Name = "ComboBox1" Then R.Cells(LI, CByte(CTRL.Tag)).Value = CTRL.Value
        If CTRL.Tag = "2" Then R.Cells(LI, CByte(CTRL.Tag)).Value = UCase(CTRL.Value)
        If CTRL.Tag = "3" Then R.Cells(LI, CByte(CTRL.Tag)).Value = Application.WorksheetFunction.Proper(CTRL.Value)
    'condition 3 : si le contrôle est une CheckBox
    ElseIf TypeOf CTRL Is MSForms.CheckBox Then
        'si la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte) est égale à "X" coche la CheckBox, sinon, elle est décochée
        If CTRL.Value = True Then R.Cells(LI, CByte(CTRL.Tag)).Value = "X" Else R.Cells(LI, CByte(CTRL.Tag)).Value = ""
    End If 'fin de la condition 1
Next CTRL 'prochaine contrôle de la boucle
R.Cells(LI, 1).Value = Left(Cells(LI, 2).Value, 4) & Left(Cells(LI, 3).Value, 1)
Unload Me 'vide et ferme l'UserForm
End Sub

Private Sub CommandButton2_Click() 'bouton "Supprimer"
'si "Oui" au message, supprime la ligne
If MsgBox("Voulez-vous supprimer la ligne de " & R.Cells(LI, 2) & " " & R.Cells(LI, 3) & " ?", vbYesNo, "ATTENTION") = vbYes Then R.Rows(LI).Delete: Unload Me 'vide et ferme l'UserForm
End Sub

Private Sub CommandButtonQuitter_Click()
Unload Me 'vide et ferme l'UserForm
End Sub

Le fichier :

356modifier-supprimer.zip (134.41 Ko)
Le 21/04/2016 à 17:07

Bonjour ThauThème,

Excellent ton fichier, mais quel travail tu as eu, tu as tout repris le code

Je ne pensais pas qu'il y avait autant de modifications à apporter

Grand merci à toi

De plus tu as tout commenter, ça va m'aider à comprendre le code vba

Bonne soirée

Citaro

Le 21/04/2016 à 17:33

Peux-tu m'expliquer à quoi correspond le 6 sur la ligne :

Me.CbDeptCODD.List = L.Range("K7:K" & L.Cells(Application.Rows.Count, 6).End(xlUp).Row).Value 'alimente la combobox

Merci d'avance

Le 21/04/2016 à 21:38

Bonsoir ThauThème,

Je me suis servi du fichier précédent pour un autre useform avec comme colonne de départ F de la feuille "Suivi des Actions"

Je bloque sur ComboBox1_Change() avec un arrêt sur la ligne :

TL(J, K) = TV(I, J - 1) 'récupère dans la ligne J colonne K de TL, la donnée ligne I colonne J-1 de TV (Transposition)

Merci d'avance

Citaro

Le 21/04/2016 à 22:06

Re,

La ligne exacte est celle-ci :

Me.CbDeptCODD.List = L.Range("F7:F" & L.Cells(Application.Rows.Count, 6).End(xlUp).Row).Value 'alimente la combobox

La méthode que tu utilisais est très pratique pour alimenter des ComboBoxes/ListBoxes sans doublon. On utilise un objet Disctionary etc. Mais en regardant ton fichier de près, je me suis rendu compte que les listes de l'onglet Onglet Listes ne contenaient que des données uniques, sans doublons. Pas besoin dans ce cas d'utiliser un objet Dictionnary. On peut, soit parcourir la plage des données cellule par cellule et alimenter le contrôle avec AddItem, mais il est encore plus rapide de l'alimenter avec sa propriété [List]

  • et la plage suivie de .Value.

    ]Me.CbDeptCODD.List = L.Range("F7:F22").Value
    L.Range("F" & Application.Rows.Count).End(xlUp).Row
    L.Cells(Application.Rows.Count, 6).End(xlUp).row

    S'il te plaît, renvoie un fichier sans signature. Je ne comprends rien à ce truc et ça me fait flipper...

    • Le 22/04/2016 à 06:45

    Bonjour ThauThème,

    Merci pour tes explications; elles m'ont permis d'avancer sur le 1er fichier

    Désolé pour le second fichier plutôt brouillon, je l'ai revu, il est sans signature maintenant

    Je me suis servi du 1er fichier pour le faire second avec useform, il a comme colonne de départ F de la feuille "Suivi des Actions"

    Je bloque sur ComboBox1_Change() erreur l'indice n'appartient pas à la sélection, avec un arrêt sur la ligne :

    TL(J, K) = TV(I, J - 1) 'récupère dans la ligne J colonne K de TL, la donnée ligne I colonne J-1 de TV (Transposition)

    Merci d'avance, bonne journée

    Citaro

    168userform-bis.xlsm (165.74 Ko)
    Le 22/04/2016 à 10:39

    Re,

    J'ai encore galéré avec la sécurité. Il ma fallu créer un classeur vierge et faire des copier/coller des valeurs et des codes. C'est pénible !...

    À part ça, tu as assez bien adapté le code à ce projet mais il y demeure quelques erreurs :

    • l'alimentation de CbLieu avec une référence à la colonne 31 au lieu de 17 (=Q)
    • l'alimentation de toutes les dernières comboboxes CbR01 à CbR12 avec une référence à la colonne 4 au lieu de 25 (=Y)
    • Mais c'est de ma faute car je m'obstine à utiliser la méthode L1C1 pour définir la dernière ligne d'une colonne alors qu'il suffit de coder :
    Me.CbR01.List = L.Range("Y7:Y" & L.Range("Y" & Application.Rows.Count).End(xlUp).Row).Value

    pour éviter toute erreur...

    - Le problème que tu cites vient de ligne juste au-dessus la boucle n'est pas de 1 à 11 mais de 2 à 11.

    La ListBox1 contient 11 colonnes et la première est masquée. Le tableau de lignes TL contient 11 lignes.

    • dans la 1ère ligne du tableau des lignes TL(1, K) on récupère le numéro de ligne de l'élément. D'ailleurs, dans ton nouvel exemple celui-ci change par rapport à l'autre fichier. Il devient : I + 10 à la place de I + 4. Après la transposition, cette donnée sera masquée.

    • ensuite, on boucle sur 10 colonnes du tableau des valeurs TV (j'ai pris les 10 premières mais on pourrait changer).

    Ce qui fait :

    TL(2, K) = TV(I, 1)
TL(3, K) = TV(I, 2)
...
TL(11, K) = TV(I, 10)

    Que j'ai remplacé par une boucle :

    For J = 2 To 11
     TL(J, K) = TV(I, J - 1)
Next J

    Important ! À la fin, pour renvoyer les valeurs des contrôles de l'UserForm dans l'onglet, j'utilise la propriété [Tag] des contrôles. Cette propriété correspond au numéro de la colonne ou doit être renvoyée la valeur de ce contrôle.

    Par exemple, si la valeur de CbDateDebut doit être placée dans la colonne A alors la propriété [Tag] de CbDateDebut est égale à 1 (première colonne). Tu dois faire de même pour tous les contrôles que tu renseignes, sinon le code plantera.

    Pour Finir. L'ancien UserForm Contenait des Checkboxes et ce n'est plus le cas. Donc la partie du code qui les concerne n'est plus nécessaire.

    Conseil : Cette boucle s'avère pratique mais elle peut aussi être difficile à gérer quand les données renvoyées ont des formats différents (Nº de téléphone, Nombre à virgules, Majuscules, etc.).

    Dans ce cas soit tu supprimes la boucle et tu renvoies les valeurs, contrôle après contrôle (comme tu le faisait dans ton premier projet).

    Soit tu utilises Select Case CTRL.Tag avec des cas séparés ou des If... End If. À toi de voir ce qui convient le mieux...

    Le code modifié (je n'ai pas tout testé) :

    Private R As Worksheet 'déclare la variable R (onglet Ressources CODD)
Private L As Worksheet 'déclare la variable L (onglet Listes)
Private TV As Variant 'déclare la variable TV (Tableau des valeurs)
Private LI As Integer 'déclare la variable LI (LIgne)

Private Sub UserForm_Initialize() 'à l'initialisation de l'USerform
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)

Me.ListBox1.ColumnCount = 11 'définit la nombre de colonne de la ListBox1
Me.ListBox1.ColumnWidths = "0" 'masque la première colonne de la Listbox1
Set R = Sheets("Suivi des Actions") 'définit l'onglet R
Set L = Sheets("Onglet Listes") 'définit l'onglet L
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
TV = R.Range("A11:J" & R.Cells(Application.Rows.Count, 2).End(xlUp).Row) 'définit la tableau des valeurs TV (les 10 premières colonnes en partant de la ligne 5...)
For I = 1 To UBound(TV, 1) 'boucle sur toutes les lignes I du tableau des valeurs TV
    'slimente le dictionnaire D avec nom/espace/prénom de chaque ligne I
    D(TV(I, 6) & " " & TV(I, 7)) = TV(I, 6) & " " & TV(I, 7)
Next I 'prochaine ligne de la boucle
Me.ComboBox1.List = D.Keys 'alimente la ComboBox1 avec la liste du dictionnaire D sans doublon
'Me.CbFicheComplete.List = R.Range("AZ2:AZ4").Value 'alimente la combobox
Me.CbDateDebut.List = L.Range("X7:X" & L.Cells(Application.Rows.Count, 24).End(xlUp).Row).Value 'alimente la combobox
Me.CbDateFin.List = L.Range("X7:X" & L.Cells(Application.Rows.Count, 24).End(xlUp).Row).Value 'alimente la combobox
Me.CbPilote.List = L.Range("Y7:Y" & L.Cells(Application.Rows.Count, 25).End(xlUp).Row).Value 'alimente la combobox
Me.CbDuree.List = L.Range("Z7:Z" & L.Cells(Application.Rows.Count, 26).End(xlUp).Row).Value 'alimente la combobox
Me.CbTypeIntervention.List = L.Range("AB7:AB" & L.Cells(Application.Rows.Count, 28).End(xlUp).Row).Value 'alimente la combobox
Me.CbStructure.List = R.Range("F11:F" & R.Cells(Application.Rows.Count, 6).End(xlUp).Row).Value 'alimente la combobox
Me.CbLieu.List = L.Range("Q7:Q" & L.Cells(Application.Rows.Count, 17).End(xlUp).Row).Value 'alimente la combobox 17 au lieu de 31
Me.CbPers1.List = L.Range("AD7:AD" & L.Cells(Application.Rows.Count, 30).End(xlUp).Row).Value 'alimente la combobox
Me.CbPers2.List = L.Range("AD7:AD" & L.Cells(Application.Rows.Count, 30).End(xlUp).Row).Value 'alimente la combobox
Me.CbProfil.List = L.Range("AA7:AA" & L.Cells(Application.Rows.Count, 27).End(xlUp).Row).Value 'alimente la combobox
Me.CbDetail.List = L.Range("AC7:AC" & L.Cells(Application.Rows.Count, 29).End(xlUp).Row).Value 'alimente la combobox
'Me.TxbMemoire.List = R.Range("Y7:Y" & R.Cells(Application.Rows.Count, 25).End(xlUp).Row).Value 'alimente la combobox
Me.CbR01.List = L.Range("Y7:Y" & L.Cells(Application.Rows.Count, 4).End(xlUp).Row).Value 'alimente la combobox
Me.CbR02.List = L.Range("Y7:Y" & L.Cells(Application.Rows.Count, 4).End(xlUp).Row).Value 'alimente la combobox
Me.CbR3.List = L.Range("Y7:Y" & L.Cells(Application.Rows.Count, 4).End(xlUp).Row).Value 'alimente la combobox
Me.CbR4.List = L.Range("Y7:Y" & L.Cells(Application.Rows.Count, 4).End(xlUp).Row).Value 'alimente la combobox
Me.CbR5.List = L.Range("Y7:Y" & L.Cells(Application.Rows.Count, 4).End(xlUp).Row).Value 'alimente la combobox
Me.CbR6.List = L.Range("Y7:Y" & L.Cells(Application.Rows.Count, 4).End(xlUp).Row).Value 'alimente la combobox
Me.CbR7.List = L.Range("Y7:Y" & L.Cells(Application.Rows.Count, 4).End(xlUp).Row).Value 'alimente la combobox
Me.CbR8.List = L.Range("Y7:Y" & L.Cells(Application.Rows.Count, 4).End(xlUp).Row).Value 'alimente la combobox
Me.CbR9.List = L.Range("Y7:Y" & L.Cells(Application.Rows.Count, 4).End(xlUp).Row).Value 'alimente la combobox
Me.CbR10.List = L.Range("Y7:Y" & L.Cells(Application.Rows.Count, 4).End(xlUp).Row).Value 'alimente la combobox
Me.CbR11.List = L.Range("Y7:Y" & L.Cells(Application.Rows.Count, 4).End(xlUp).Row).Value 'alimente la combobox
Me.CbR12.List = L.Range("Y7:Y" & L.Cells(Application.Rows.Count, 4).End(xlUp).Row).Value 'alimente la combobox
LI = R.Cells(Application.Rows.Count, 2).End(xlUp).Row + 1 'définit la ligne LI (première ligne vide de la colonne B)
Me.CommandButton1.Visible = False 'masque le bouton "Modifier"
Me.CommandButton2.Visible = False 'masque le bouton "Supprimer"
End Sub

Private Sub ComboBox1_Change() 'au changement dans la ComboBox1
Dim I As Integer 'déclare la variable I (Incrément)
Dim J As Byte 'déclare la variable J (incrément)
Dim K As Integer 'déclare la variable K (incrément)
Dim L As Byte 'déclare la variable L (incrément)
Dim TL() As Variant 'déclare la variable TL (Tableau des Lignes)
Dim CTRL As control 'déclare la variable CTRL (ConTRôLe)

Me.ListBox1.Clear 'vide la Listbox1
'si la ComboBox1 est effacée
If Me.ComboBox1.Value = "" Then
    LI = R.Cells(Application.Rows.Count, 6).End(xlUp).Row + 1 'définit la ligne LI (première ligne vide de la colonne B)
    Me.CbDateDebut.SetFocus 'place la curseur dans la texbox [TbxNom]
    Me.CommandButton1.Visible = False 'masque le bouton "Modifier"
    Me.CommandButton2.Visible = False 'masque le bouton "Supprimer"
    Me.CommandButtonAjouter.Visible = True 'affiche le bouton "Ajouter"
    Exit Sub 'sort de la procédure
End If 'fin de la condition
Me.CommandButtonAjouter.Visible = False 'masque le bouton "Ajouter"
Me.CommandButton1.Visible = True 'affiche le bouton "Modifier"
Me.CommandButton2.Visible = True 'affiche le bouton "Supprimer"

K = 1 'initialise K
For I = 1 To UBound(TV, 1) 'boucle 1 : sur toutes les lignes I du tableau des valeurs TV
    If TV(I, 6) & " " & TV(I, 7) = Me.ComboBox1.Value Then 'si le nom/espace/prénom de la ligne est égal à la valeur de la ComboBox1
        ReDim Preserve TL(1 To 11, 1 To K) 'redimensionne le tableau des lignes TL
        TL(1, K) = I + 10 'récupère dans la ligne 1 le numéro de la ligne
        For J = 2 To 11 'boucle 2 : sur les 10 autres colonnes de la ListBox1
            TL(J, K) = TV(I, J - 1) 'récupère dans la ligne J colonne K de TL, la donnée ligne I colonne J-1 de TV (Transposition)
        Next J 'prochaine colonne de la boucle 2
        K = K + 1 'incrément K (ajoute une colonne au tableau des lignes TL
    End If 'fin de la condition
Next I 'prochaine ligne de la boucle 1
If K > 1 Then 'condition 1 : si K est supérieur à 1 (=> au moins une occurrence a été trouvée)
    If K = 2 Then 'condition 2 : une seule occurrence a été trouvée
        LI = CInt(TL(1, 1)) 'définit la ligne LI (récupérée dans ligne 1 colonne 1 du tableau des lignes TL)
        For Each CTRL In Me.Controls 'boucle sur tous les contrôle de l'UserForm
            'condition 3 : si le contrôle est une TextBox ou une ComboBox
            If TypeOf CTRL Is MSForms.TextBox Or TypeOf CTRL Is MSForms.ComboBox Then
                'si le contrôle n'est la la ComboBox1, récupère pour le contrôle la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte)
                If Not CTRL.Name = "ComboBox1" Then CTRL.Value = R.Cells(LI, CByte(CTRL.Tag)).Value
            End If 'fin de la condition 3
        Next CTRL 'prochaine contrôle de la boucle
        Exit Sub 'sort de la procédure
    End If 'fin de la condition 2
    Me.ListBox1.List = Application.Transpose(TL) 'alimente la ListBox1 avec le tableau TL transposé
End If
End Sub

Private Sub ListBox1_Click() 'au clic dans la ListBox1
Dim CTRL As control 'déclare la variable CTRL (ConTRôLe)

With Me.ListBox1 'prend en compte la ListBox1
    LI = CInt(.Column(0, .ListIndex)) 'définit le numéro de ligne LI (dans la colonne 0 (masquée) de la ListBox1)
End With 'fin de la prsie en compte de la Listbox1
For Each CTRL In Me.Controls 'boucle sur tous les contrôle de l'UserForm
    'condition 1 : si les contrôle est une TextBox ou une ComboBox
    If TypeOf CTRL Is MSForms.TextBox Or TypeOf CTRL Is MSForms.ComboBox Then
        'si le contrôle n'est la la ComboBox1, récupère pour le contrôle la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte)
        If Not CTRL.Name = "ComboBox1" Then CTRL.Value = R.Cells(LI, CByte(CTRL.Tag)).Value
    End If 'fin de la condition 1
Next CTRL 'prochaine contrôle de la boucle
End Sub

Private Sub CommandButtonAjouter_Click()
For Each CTRL In Me.Controls 'boucle sur tous les contrôle de l'UserForm
    'condition 1 : si les contrôle est une TextBox ou une ComboBox
    If TypeOf CTRL Is MSForms.TextBox Or TypeOf CTRL Is MSForms.ComboBox Then
        'si le contrôle n'est la la ComboBox1, récupère pour le contrôle la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte)
        If Not CTRL.Name = "ComboBox1" Then R.Cells(LI, CByte(CTRL.Tag)).Value = CTRL.Value
        If CTRL.Tag = "2" Then R.Cells(LI, CByte(CTRL.Tag)).Value = UCase(CTRL.Value)
        If CTRL.Tag = "3" Then R.Cells(LI, CByte(CTRL.Tag)).Value = Application.WorksheetFunction.Proper(CTRL.Value)
    End If 'fin de la condition 1
Next CTRL 'prochaine contrôle de la boucle
'R.Cells(LI, 1).Value = Left(Cells(LI, 2).Value, 4) & Left(Cells(LI, 3).Value, 1)
tri_ressource
Unload Me 'vide et ferme l'UserForm
End Sub

Private Sub CommandButton1_Click() 'bouton "Modifier"
For Each CTRL In Me.Controls 'boucle sur tous les contrôle de l'UserForm
    'condition 1 : si les contrôle est une TextBox ou une ComboBox
    If TypeOf CTRL Is MSForms.TextBox Or TypeOf CTRL Is MSForms.ComboBox Then
        'si le contrôle n'est la la ComboBox1, récupère pour le contrôle la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte)
        If Not CTRL.Name = "ComboBox1" Then R.Cells(LI, CByte(CTRL.Tag)).Value = CTRL.Value
        If CTRL.Tag = "2" Then R.Cells(LI, CByte(CTRL.Tag)).Value = UCase(CTRL.Value)
        If CTRL.Tag = "3" Then R.Cells(LI, CByte(CTRL.Tag)).Value = Application.WorksheetFunction.Proper(CTRL.Value)
    End If 'fin de la condition 1
Next CTRL 'prochaine contrôle de la boucle
R.Cells(LI, 1).Value = Left(Cells(LI, 2).Value, 4) & Left(Cells(LI, 3).Value, 1)
Unload Me 'vide et ferme l'UserForm
End Sub

Private Sub CommandButton2_Click() 'bouton "Supprimer"
'si "Oui" au message, supprime la ligne
If MsgBox("Voulez-vous supprimer la ligne de " & R.Cells(LI, 2) & " " & R.Cells(LI, 3) & " ?", vbYesNo, "ATTENTION") = vbYes Then R.Rows(LI).Delete: Unload Me 'vide et ferme l'UserForm
End Sub

Private Sub CommandButtonQuitter_Click()
Unload Me 'vide et ferme l'UserForm
End Sub
    Le 22/04/2016 à 11:01

    ThauThème,

    Quels problèmes de sécurité rencontres- tu avec mon fichier ?

    Je viens de tester le code, je rencontre un problème d’incompatibilité de type en avec le combobox recherche dans le ComboBox1_Change() à la ligne :

    If Not CTRL.Name = "ComboBox1" Then CTRL.Value = R.Cells(LI, CByte(CTRL.Tag)).Value

    Merci de ton aide, je sais que cela te prends du temps, c'est au dessus de mes compétences en vba

    Citaro

    Le 22/04/2016 à 11:32

    Re,

    As-tu attribué lesvaleurs aux propriétés [Tag] de chaque contrôles ?...

    Le 22/04/2016 à 11:36

    non je suis largué sur le tag, j'ai du mal à comprendre

    Le 22/04/2016 à 11:50

    Re,

    Dans VBE (l'Éditeur Visual Basic), tu sélectionnes un contrôle, dans la Fenêtre des Propriétés, tu attribues à la propriété [Tag] la valeur équivalente au numéro de la colonne où le contenu que tu éditeras dans ce contrôle doit être renvoyé. Tu fais ça pour toutes les TexBoxes et les ComboBoxes.

    Quand tu changes le nom du contrôle, tu fais la même chose mais avec la Propriété [Name]. Là, tu vas taper un numéro au lieu d'un texte à la propriété [Tag]...

    Le 22/04/2016 à 11:55

    Je n'avais pas remarqué cela sur ton 1er fichier, je m'y mets merci encore pour ton aide

    Le 22/04/2016 à 13:16

    Je viens d'ajouter les valeurs à la propriété des tags

    je rencontre toujours un problème d’incompatibilité de type en avec le combobox recherche dans le ComboBox1_Change() à la ligne :

    If Not CTRL.Name = "ComboBox1" Then CTRL.Value = R.Cells(LI, CByte(CTRL.Tag)).Value

    En validant l'ajout de données par le bouton Ajouter j'ai le même type d'erreur dans CommandButtonAjouter_Click() à la ligne :

    If Not CTRL.Name = "ComboBox1" Then R.Cells(LI, CByte(CTRL.Tag)).Value = CTRL.Value

    Je ne sais pas résoudre le problème

    Citaro

    Le 22/04/2016 à 15:08

    Re,

    Mets en pièce jointe le fichier avec les [Tag]...

    Le 22/04/2016 à 16:35

    Re,

    Je viens de trouver un autre dysfonctionnement : Lorsque l'on saisit des données en cliquant sur Ajouter, j'ai le message d'erreur du post de 13:16 mais le report des données se fait sauf dans les colonnes de V à AA

    Merci

    78userform-bis.xlsm (196.00 Ko)
    Le 22/04/2016 à 22:16

    Re,

    Toujours la même galère avec la signature électronique de ton fichier qui désactive systématiquement les macros !... Tu peux pas te passer de ça ? Au moins pour les fichiers que tu mets ici...

    Bon, en fait tu avais deux ComboBoxes masquées au niveau de la Ress. 1 et de la Ress. 3. Comme elle n'avaient pas de propriété [Tag] ça plantait. Il m'a fallu les sortir de la frame Les ressources utilisées, les agrandir, puis supprimer la Combobox inutile. Alors si les frames peuvent paraître avoir un intérêt esthétique (personnellement je leur préfère un label), qu'est-ce qu'elles sont chiantes lorsqu'on recherche un contrôle !...

    j'ai renommé les variables des onglets SdA et OL.

    J'ai renommé les frames pour pouvoir les ordonner plus rapidement (ordre des tabulations) , j'ai homogénéisé le nom des contrôles des Ress. (CbR...).

    J'ai supprimé dans le code des lignes inutiles et recoder la variable de la dernière ligne éditée avec la méthode A1.

    J'ai modifié le code de la macro tri_ressource du module 1 pour l'adapter à ce tableau.

    L'alimentation de la combobox CbStructure est la seule à se faire par l'onglet Suivi des Actions. Comme la méthode [List]

  • nécessite au moins deux lignes, sinon ça plante, j'ai modifié le code pour cette combobox.

    Private SdA As Worksheet 'déclare la variable SdA (onglet Suivie des Anctions)
Private OL As Worksheet 'déclare la variable OL (onglet Onglet Listes)
Private TV As Variant 'déclare la variable TV (Tableau des valeurs)
Private LI As Integer 'déclare la variable LI (LIgne)

Private Sub UserForm_Initialize() 'à l'initialisation de l'USerform
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)

Me.ListBox1.ColumnCount = 11 'définit la nombre de colonne de la ListBox1
Me.ListBox1.ColumnWidths = "0" 'masque la première colonne de la Listbox1
Set SdA = Sheets("Suivi des Actions") 'définit l'onglet SdA
Set OL = Sheets("Onglet Listes") 'définit l'onglet OL
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
TV = SdA.Range("A11:J" & SdA.Cells(Application.Rows.Count, 2).End(xlUp).Row) 'définit la tableau des valeurs TV (les 10 premières colonnes en partant de la ligne 5...)
For I = 1 To UBound(TV, 1) 'boucle sur toutes les lignes I du tableau des valeurs TV
    'alimente le dictionnaire D avec Struture concernée/[espace]/Lieu de chaque ligne I
    D(TV(I, 6) & " " & TV(I, 7)) = TV(I, 6) & " " & TV(I, 7)
Next I 'prochaine ligne de la boucle
Me.ComboBox1.List = D.Keys 'alimente la ComboBox1 avec la liste du dictionnaire D sans doublon
Me.CbDateDebut.List = OL.Range("X7:X" & OL.Range("X" & Application.Rows.Count).End(xlUp).Row).Value 'alimente la combobox
Me.CbDateFin.List = OL.Range("X7:X" & OL.Range("X" & Application.Rows.Count).End(xlUp).Row).Value 'alimente la combobox
Me.CbPilote.List = OL.Range("Y7:Y" & OL.Range("Y" & Application.Rows.Count).End(xlUp).Row).Value 'alimente la combobox
Me.CbDuree.List = OL.Range("Z7:Z" & OL.Range("Z" & Application.Rows.Count).End(xlUp).Row).Value 'alimente la combobox
Me.CbTypeIntervention.List = OL.Range("AB7:AB" & OL.Range("AB" & Application.Rows.Count).End(xlUp).Row).Value 'alimente la combobox
'la méthode [List] génère une erreur tant que le tableau ne contient pas au moins deux éléments
If SdA.Range("F11").Value = "" Then 'condition : si le tableau est vide
    Me.CbStructure.AddItem "" 'alimente la combobox
ElseIf SdA.Range("F12").Value = "" Then 'condition : si F12 est vide
    Me.CbStructure.AddItem SdA.Range("F11").Value 'alimente la combobox
Else 'sinon
    Me.CbStructure.List = SdA.Range("F11:F" & SdA.Range("F" & Application.Rows.Count).End(xlUp).Row).Value 'alimente la combobox
End If 'fin de la condition
Me.CbLieu.List = OL.Range("AE7:AE" & OL.Range("AE" & Application.Rows.Count).End(xlUp).Row).Value 'alimente la combobox
Me.CbPers1.List = OL.Range("AD7:AD" & OL.Range("AD" & Application.Rows.Count).End(xlUp).Row).Value 'alimente la combobox
Me.CbPers2.List = OL.Range("AD7:AD" & OL.Range("AD" & Application.Rows.Count).End(xlUp).Row).Value 'alimente la combobox
Me.CbProfil.List = OL.Range("AA7:AA" & OL.Range("AA" & Application.Rows.Count).End(xlUp).Row).Value 'alimente la combobox
Me.CbDetail.List = OL.Range("AC7:AC" & OL.Range("AC" & Application.Rows.Count).End(xlUp).Row).Value 'alimente la combobox
For I = 1 To 12
    Me.Controls("CbR" & I).List = OL.Range("Y7:Y" & OL.Range("Y" & Application.Rows.Count).End(xlUp).Row).Value 'alimente la combobox
Next I
LI = SdA.Range("A" & Application.Rows.Count).End(xlUp).Row + 1 'définit la ligne LI (première ligne vide de la colonne A)
Me.CommandButton1.Visible = False 'masque le bouton "Modifier"
Me.CommandButton2.Visible = False 'masque le bouton "Supprimer"
End Sub

Private Sub ComboBox1_Change() 'au changement dans la ComboBox1
Dim I As Integer 'déclare la variable I (Incrément)
Dim J As Byte 'déclare la variable J (incrément)
Dim K As Integer 'déclare la variable K (incrément)
Dim OL As Byte 'déclare la variable OL (incrément)
Dim TL() As Variant 'déclare la variable TL (Tableau des Lignes)
Dim CTRL As control 'déclare la variable CTRL (ConTRôLe)

Me.ListBox1.Clear 'vide la Listbox1
'si la ComboBox1 est effacée
If Me.ComboBox1.Value = "" Then
    LI = SdA.Range("F" & Application.Rows.Count).End(xlUp).Row + 1 'définit la ligne LI (première ligne vide de la colonne F)
    Me.CbDateDebut.SetFocus 'place la curseur dans la texbox [TbxNom]
    Me.CommandButton1.Visible = False 'masque le bouton "Modifier"
    Me.CommandButton2.Visible = False 'masque le bouton "Supprimer"
    Me.CommandButtonAjouter.Visible = True 'affiche le bouton "Ajouter"
    Exit Sub 'sort de la procédure
End If 'fin de la condition
Me.CommandButtonAjouter.Visible = False 'masque le bouton "Ajouter"
Me.CommandButton1.Visible = True 'affiche le bouton "Modifier"
Me.CommandButton2.Visible = True 'affiche le bouton "Supprimer"
K = 1 'initialise K
For I = 1 To UBound(TV, 1) 'boucle 1 : sur toutes les lignes I du tableau des valeurs TV
    If TV(I, 6) & " " & TV(I, 7) = Me.ComboBox1.Value Then 'si le nom/espace/prénom de la ligne est égal à la valeur de la ComboBox1
        ReDim Preserve TL(1 To 11, 1 To K) 'redimensionne le tableau des lignes TL
        TL(1, K) = I + 10 'récupère dans la ligne 1 le numéro de la ligne
        For J = 2 To 11 'boucle 2 : sur les 10 autres colonnes de la ListBox1
            TL(J, K) = TV(I, J - 1) 'récupère dans la ligne J colonne K de TL, la donnée ligne I colonne J-1 de TV (Transposition)
        Next J 'prochaine colonne de la boucle 2
        K = K + 1 'incrément K (ajoute une colonne au tableau des lignes TL
    End If 'fin de la condition
Next I 'prochaine ligne de la boucle 1
If K > 1 Then 'condition 1 : si K est supérieur à 1 (=> au moins une occurrence a été trouvée)
    If K = 2 Then 'condition 2 : une seule occurrence a été trouvée
        LI = CInt(TL(1, 1)) 'définit la ligne LI (récupérée dans ligne 1 colonne 1 du tableau des lignes TL)
        For Each CTRL In Me.Controls 'boucle sur tous les contrôles de l'UserForm
            'condition 3 : si le contrôle est une TextBox ou une ComboBox
            If TypeOf CTRL Is MSForms.TextBox Or TypeOf CTRL Is MSForms.ComboBox Then
                'si le contrôle n'est la la ComboBox1, récupère pour le contrôle la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte)
                If Not CTRL.Name = "ComboBox1" Then CTRL.Value = SdA.Cells(LI, CByte(CTRL.Tag)).Value
            End If 'fin de la condition 3
        Next CTRL 'prochaine contrôle de la boucle
        Exit Sub 'sort de la procédure
    End If 'fin de la condition 2
    Me.ListBox1.List = Application.Transpose(TL) 'alimente la ListBox1 avec le tableau TL transposé
End If
End Sub

Private Sub ListBox1_Click() 'au clic dans la ListBox1
Dim CTRL As control 'déclare la variable CTRL (ConTRôLe)

With Me.ListBox1 'prend en compte la ListBox1
    LI = CInt(.Column(0, .ListIndex)) 'définit le numéro de ligne LI (dans la colonne 0 (masquée) de la ListBox1)
End With 'fin de la prsie en compte de la Listbox1
For Each CTRL In Me.Controls 'boucle sur tous les contrôle de l'UserForm
    'condition 1 : si les contrôle est une TextBox ou une ComboBox
    If TypeOf CTRL Is MSForms.TextBox Or TypeOf CTRL Is MSForms.ComboBox Then
        'si le contrôle n'est la la ComboBox1, récupère pour le contrôle la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte)
        If Not CTRL.Name = "ComboBox1" Then CTRL.Value = SdA.Cells(LI, CByte(CTRL.Tag)).Value
    End If 'fin de la condition 1
Next CTRL 'prochaine contrôle de la boucle
End Sub

Private Sub CommandButtonAjouter_Click()
For Each CTRL In Me.Controls 'boucle sur tous les contrôle de l'UserForm
    'condition 1 : si les contrôle est une TextBox ou une ComboBox
    If TypeOf CTRL Is MSForms.TextBox Or TypeOf CTRL Is MSForms.ComboBox Then
        'si le contrôle n'est la la ComboBox1, récupère pour le contrôle la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte)
        If Not CTRL.Name = "ComboBox1" Then SdA.Cells(LI, CByte(CTRL.Tag)).Value = CTRL.Value
    End If 'fin de la condition 1
Next CTRL 'prochaine contrôle de la boucle
tri_ressource
Unload Me 'vide et ferme l'UserForm
End Sub

Private Sub CommandButton1_Click() 'bouton "Modifier"
For Each CTRL In Me.Controls 'boucle sur tous les contrôle de l'UserForm
    'condition 1 : si les contrôle est une TextBox ou une ComboBox
    If TypeOf CTRL Is MSForms.TextBox Or TypeOf CTRL Is MSForms.ComboBox Then
        'si le contrôle n'est la la ComboBox1, récupère pour le contrôle la valeur de la cellule ligne: LI, colonne : propriété [Tag] du contrôle (convertie en byte)
        If Not CTRL.Name = "ComboBox1" Then SdA.Cells(LI, CByte(CTRL.Tag)).Value = CTRL.Value
    End If 'fin de la condition 1
Next CTRL 'prochaine contrôle de la boucle
SdA.Cells(LI, 1).Value = Left(Cells(LI, 2).Value, 4) & Left(Cells(LI, 3).Value, 1)
Unload Me 'vide et ferme l'UserForm
End Sub

Private Sub CommandButton2_Click() 'bouton "Supprimer"
'si "Oui" au message, supprime la ligne
If MsgBox("Voulez-vous supprimer la ligne de " & SdA.Cells(LI, 2) & " " & SdA.Cells(LI, 3) & " ?", vbYesNo, "ATTENTION") = vbYes Then SdA.Rows(LI).Delete: Unload Me 'vide et ferme l'UserForm
End Sub

Private Sub CommandButtonQuitter_Click()
Unload Me 'vide et ferme l'UserForm
End Sub

    Le fichier :

    • 242citaro-v02.zip (79.89 Ko)
    Le 23/04/2016 à 07:19

    Bonjour,

    Désolé pour les problèmes de sécurité, et du temps que tu as consacré à ce fichier

    Tu as réussi à venir à bout des derniers problèmes, le fichier correspond à mes attentes

    Je te remercie de ton aide

    Très bon weekend

    Citaro

    Rechercher des sujets similaires à "modifier supprimer userform"