Discussion :

[1357VBA]

Pourquoi le fichier origine de JP fonctionne-t-il

(XL-2013) La méthode 'Add' de l'objet 'ListRows' a échoué.xlsm

et pas la copie ?

(XL-2013) La méthode 'Add' de l'objet 'ListRows' a échoué - Copie.xlsm

modification portant uniquement sur le lancement du userform

[Pierre Fauconnier]

Pas le temps pour l'instant d'ouvrir tes fichiers et de tester...

Mais si, sur chacun des fichiers, tu démarres le process en pas à pas, tu vas forcément tomber sur le code qui initie la variable sur l'un et pas sur l'autre...

[mjpmjp]

bonjour,
par rapport au code de Pierre,
j'ai donc revu le code , pour éviter les contraintes de la "localisation de l'onglet"

mais je me suis heurté à ce problème de localisation (qui reste un mystère) avec la RowSource de la ComboBox
code qui fonctionne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
    'initialise ComboBox1
    ComboBox1.RowSource = Tb.DataBodyRange.Parent.Name & "!" & Tb.DataBodyRange.Address

j'ai donc indiqué le "Parent" de Tb --> tableau

pour ce qui est de .Add et .Delete , là aussi , c'est pas clair...

.Add , je comprend que : on modifie "physiquement" un peu comme select qui vise "physiquement" une cellule..

alors j'utilise la solution proposée par Pierre , qui est de ajouter mais pas "directement" et donc "physiquement" ,
mais "automatiquement" en modifiant la valeur de la cellule , qui va , par le mécanisme du tableau , être "ajouter"

je reste ainsi "géolocalisé" dans mon tableau et je n'est pas besoin d'indiquer l'onglet

.Delete , je comprend que : le raisonnement doit être le même que "Add" ,
mais pourtant , çà fonctionne ...et puis , sans aucune raison (?) çà ne fonctionne plus

voila le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
Dim Tb As ListObject
Dim TbRows As ListRows
Dim TbCols As ListColumns
Dim EffaceEnCours As Boolean
 
Private Sub UserForm_Initialize()
    Call Initialise
End Sub
 
Private Sub ComboBox1_Change()
    If EffaceEnCours Then
        EffaceEnCours = False
    Else
        With ComboBox1
          UserForm1.PrenomLabel.Caption = .Column(1, .ListIndex)
        End With
    End If
End Sub
 
Private Sub AddIntervenantBtn_Click()
    Call Initialise
    If UserForm1.NomTextBox.Value <> "" Then
        'derniere ligne
        TbLigne = TbRows.Count + 1
        'ecrire nom
        TbCols("Nom").DataBodyRange(TbLigne).Value = UserForm1.NomTextBox.Value
        'ecrire prenom
        TbCols("Prénom").DataBodyRange(TbLigne).Value = UserForm1.PrenomTextBox.Value
        'initialise ComboBox1
        ComboBox1.RowSource = Tb.DataBodyRange.Parent.Name & "!" & Tb.DataBodyRange.Address
        Call EffaceControle
    End If
End Sub
 
Private Sub SupprIntervenantBtn_Click()
    Call Initialise
    'ligne à supprimer
    TbLigne = UserForm1.ComboBox1.ListIndex + 1
    'supprime ligne
    Tb.ListRows(TbLigne).Delete
    'initialise ComboBox1
    ComboBox1.RowSource = Tb.DataBodyRange.Parent.Name & "!" & Tb.DataBodyRange.Address
    Call EffaceControle
End Sub
 
Private Sub Initialise()
    'tableau
    Set Tb = Range("Intervenants").ListObject
    'contient les lignes
    Set TbRows = Tb.ListRows
    'contient les colonnes
    Set TbCols = Tb.ListColumns
    'ComboBox
    If Not Tb.DataBodyRange Is Nothing Then
        ComboBox1.RowSource = Tb.DataBodyRange.Parent.Name & "!" & Tb.DataBodyRange.Address
    End If
    EffaceEnCours = False
End Sub
Private Sub EffaceControle()
    EffaceEnCours = True
    With UserForm1
        .PrenomLabel.Caption = ""
        .NomTextBox.Value = ""
        .PrenomTextBox.Value = ""
        .ComboBox1.Value = ""
    End With
End Sub

et voila le fichier ,
qui contient 2 onglet et qui démarre sur le 2 eme onglet (vide)
et qui lance le form
[XL-2013] La méthode 'Add' de l'objet 'ListRows' a échoué v2.xlsm

quand je charge ce fichier et que j'active la modification ,
il y a une erreur , qui n'est plus à l' utilisation suivante ?



@+JP

[1357VBA]

bonjour,
par rapport au code de Pierre,
j'ai donc revu le code , pour éviter les contraintes de la "localisation de l'onglet"[...]

Merci JP

Ce qui me rassure c'est que je ne suis pas le seul à avoir quelque chose qui fonctionne (j'avais la banane) et puis plus du tout (j'avais la banane à l'envers!)...
J'espère que quelqu'un trouvera la réponse sur ce forum car c'est très déconcertant.

Merci encore
@+ Eric

[Pierre Fauconnier]

C'est sur quelle ligne, l'erreur?

Et en fonctionnant en pas à pas, ça donne quoi?

L'erreur se produit-elle sur des lignes particulières du combobox (première, dernière, ligne située directement avant ou après celle qui vient d'être sélectionnée)?

[mjpmjp]

re,
il faut que je poste à nouveau mon fichier avec un point d'arret...



fichier pour test , avec le point d’arrêt

[XL-2013] La méthode 'Add' de l'objet 'ListRows' a échoué v3.xlsm



le code est dans le module du form ,
je vais le déplacer et re tester

[Pierre Fauconnier]

[...]J'espère que quelqu'un trouvera la réponse sur ce forum car c'est très déconcertant.
@+ Eric

Ce n'est pas déconcertant... Le problème de la suppression de données, c'est qu'elle décale les lignes. On se trouve donc à un moment donné à plonger hors du tableau, par exemple. Si on supprime la cellule vers laquelle pointe rng, rng ne peut plus pointer vers une cellule valide (c'est le problème du #REF! en Excel) et donc, on se prend une erreur.

C'est pour cela qu'il faut passer en pas à pas en ayant un œil sur la fenêtre des variables locales, voire avec des variables espion pour voir ce qui se passe et comment bougent les variables.

Si on observe le code suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub TestDelete()
  Dim rng As Range
 
  Set rng = Range("a3")
  Range("a2").EntireRow.Delete
  Debug.Print rng.Address
End Sub

il affichera $A$2, car lors de la suppression de A2, A3 a glissé vers le haut, et donc l'objet qui pointait vers A3 a suivi et pointe maintenant vers A2. C'est pourquoi il est utile de découpler et d'utiliser des fonctions isolées car on peut beaucoup plus facilement tester les arguments qu'on leur passe.

[mjpmjp]

re ,

fichier modifieé : [XL-2013] La méthode 'Add' de l'objet 'ListRows' a échoué v4.xlsm

après la modif (code UserForm1) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
Private Sub UserForm_Initialize()
    'ComboBox
    If Not Tb.DataBodyRange Is Nothing Then
        ComboBox1.RowSource = Tb.DataBodyRange.Parent.Name & "!" & Tb.DataBodyRange.Address
    End If
End Sub
 
Private Sub ComboBox1_Change()
    If EffaceEnCours Then
        EffaceEnCours = False
    Else
        With ComboBox1
          UserForm1.PrenomLabel.Caption = .Column(1, .ListIndex)
        End With
    End If
End Sub
 
Private Sub AddIntervenantBtn_Click()
    Call Initialise
    If UserForm1.NomTextBox.Value <> "" Then
        'derniere ligne
        TbLigne = TbRows.Count + 1
        'ecrire nom
        TbCols("Nom").DataBodyRange(TbLigne).Value = UserForm1.NomTextBox.Value
        'ecrire prenom
        TbCols("Prénom").DataBodyRange(TbLigne).Value = UserForm1.PrenomTextBox.Value
        'initialise ComboBox1
        ComboBox1.RowSource = Tb.DataBodyRange.Parent.Name & "!" & Tb.DataBodyRange.Address
        Call EffaceControle
    End If
End Sub
 
Private Sub SupprIntervenantBtn_Click()
    Call Initialise
    'ligne à supprimer
    TbLigne = UserForm1.ComboBox1.ListIndex + 1
    'supprime ligne
    Tb.ListRows(TbLigne).Delete
    'initialise ComboBox1
    ComboBox1.RowSource = Tb.DataBodyRange.Parent.Name & "!" & Tb.DataBodyRange.Address
    Call EffaceControle
End Sub
 
Private Sub EffaceControle()
    EffaceEnCours = True
    With UserForm1
        .PrenomLabel.Caption = ""
        .NomTextBox.Value = ""
        .PrenomTextBox.Value = ""
        .ComboBox1.Value = ""
    End With
End Sub

code module :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
Public Tb As ListObject
Public TbRows As ListRows
Public TbCols As ListColumns
Public EffaceEnCours As Boolean
 
Public Sub Initialise()
    'tableau
    Set Tb = Range("Intervenants").ListObject
    'contient les lignes
    Set TbRows = Tb.ListRows
    'contient les colonnes
    Set TbCols = Tb.ListColumns
    EffaceEnCours = False
End Sub

code WorkBook

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
Private Sub Workbook_Open()
    Call Initialise
    UserForm1.Show
End Sub

le même message :


qui pointe sur Set Tb :


les amis , je vais ...

@+JP

[1357VBA]

[...]
les amis , je vais ...

@+JP

Je te comprends... tu te souviens quand je te disais je me noie et moi je débute à peine

[1357VBA]

Ce n'est pas déconcertant... Le problème de la suppression de données, c'est qu'elle décale les lignes. On se trouve donc à un moment donné à plonger hors du tableau, par exemple.[...]

Je vais sûrement dire une bêtise mais la plage nommée, normalement ne permet-elle justement pas d'éviter de plonger hors du tableau ?

[Pierre Fauconnier]

Je vais sûrement dire une bêtise mais la plage nommée, normalement ne permet-elle justement pas d'éviter de plonger hors du tableau ?

Si. C'est justement ce que j'utilise pour ajouter une ligne, en débordant sur la ligne qui suit directement celle du tableau. Je profite ainsi du fait qu'Excel étend automatiquement le tableau à cette nouvelle ligne.

J'expliquais donc que ce pourrait être une des raisons qui fait que le listobject plante puisqu'il ne permet pas, lui, de déborder du tableau.

[1357VBA]

JP, de mon côté ton fichier V4 ne plante pas, il ajoute les valeurs en dehors du tableau

[Pierre Fauconnier]

[...]
les amis , je vais ...[...]

Ca fait toujours du bien...

1357VBA, il n'est pas trop tard pour utiliser la plage nommée au sein d'une fonction d'ajout...

[1357VBA]

Ca fait toujours du bien...

1357VBA, il n'est pas trop tard pour utiliser la plage nommée au sein d'une fonction d'ajout...

Oui je comprends bien la logique de la chose c'est pourquoi j'ai tenté de faire Tableau ET Plage nommée (mon code du 01/09 14h40) mais je suis loin d'avoir votre pratique

[Pierre Fauconnier]

Pourquoi le fichier origine de JP fonctionne-t-il


et pas la copie ?



modification portant uniquement sur le lancement du userform

Perso, je n'ai pas eu de soucis particuliers dans les fichiers donnés, hormis le plantage si absence de sélection dans la combo ou si tableau vide. Peut-être serait-il intéressant de bien examiner les conditions dans lesquelles les classeurs se trouvent avant chaque clic sur le bouton...

[Pierre Fauconnier]

Oui je comprends bien la logique de la chose c'est pourquoi j'ai tenté de faire Tableau ET Plage nommée (mon code du 01/09 14h40) mais je suis loin d'avoir votre pratique

Hum... J'ai un peu la flemme de relire toute la discussion, mais si tu fais un rapide résumé de ce que souhaites réaliser par le userform, je veux bien tenter de refaire un petit topo facilement adaptable...

[1357VBA]

Je souhaitais avoir un userform à "3 étages"

1 - possibilité d'ajouter 1 à n valeurs sur une seule ligne (ex: nom, prénom, ville, etc.) dans la Plage Nommée d'un Tableau
2 - possibilité de les supprimer après sélection via combobox1
3 - possibilité de les modifier dans les textbox (en utilisant le combobox1)

ça donne ceci comme userform :


et ça après avoir ajouté une colonne Nom + Prenom dans le tableau avec une formule texte qui se réplique à tout le tableau
et un tri A->Z dans le code du formulaire

[Pierre Fauconnier]

Salut.

Voici un code tout simple qui gère des données d'un contact au sein d'un userform. Il se découpe en deux parties, le code du userform lui-même et celui de l'application.

Tu remarqueras que le code du userform est totalement indépendant d'Excel. Il pourrait fonctionner avec Word ou PowerPoint si on voulait, et pourrait être utilisé avec des donnée issues d'une vraie DB pour lire et enregistrer les données. C'est une brique qui sert simplement à la saisie et à l'affichage des données et qui n'a pas à savoir d'où viennent ni ou vont les données. L'intérêt de coder ainsi réside dans le fait que si tu dois un jour manipuler les données autrement que par le userform, tu as tout à ta disposition hors userform; Il n'est en effet pas de la responsabilité du userform de gérer la sauvegarde et la récupération des données.

Le formulaire est géré en trois temps: chargement, préparation, affichage. Avant le déchargement, on aurait pu gérer un quatrième temps, par exemple pour savoir comment on l'aurait quitté.

Ainsi, si un jour tes données sont stockées ailleurs (sql, access, xml), tu ne dois modifier que les fonctions de lecture, d'écriture et de suppression d'un contact et la fonction de chargement de la combobox dans le module standard. Ton userform, lui, ne doit subir aucune modification.

Le chargement de la combobox se fait via un tableau et non en direct sur la plage, pour garder ce découplage total entre Excel et le userform. En prime, ça évite le plantage d'Excel qui est assez capricieux lorsque l'on modifie la plage du rowsource d'un combobox.

Tu pourras aussi constater qu'à aucun moment je ne me préoccupe de manipuler des feuilles ni d'opérer des sélections sur des feuilles Excel. Tout se passe au travers des plages nommées utilisant les références structurées. Enfin, tu remarqueras que grâce à ce découpage, on obtient des procédures/fonctions très courtes, qui ne font qu'une seule chose au sein de l'application et qui sont très peu voire pas couplées entre elles car elles reçoivent chacune les arguments nécessaires à leur fonctionnement. De ce fait, on peut tester chaque partie du code de façon indépendante, et lorsqu'une partie du code est modifiée, on ne touche qu'à très peu de code.

Voici le code du userform

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Option Explicit
 
Private RowIndex As Long
 
Private Sub cboChoice_Change()
  If cboChoice.ListIndex <> -1 Then
    RowIndex = cboChoice.ListIndex + 1
    pl.getContact RowIndex
  Else
    RowIndex = 0
  End If
End Sub
 
Private Sub cmdADd_Click()
  Dim Result As VbMsgBoxResult
 
  Result = MsgBox("Voulez-vous ajouter ce contact?", vbQuestion + vbYesNo)
  If Result = vbYes Then
    pl.SaveContact tboFirstname.Value, tboName.Value
    PrepareForm
  End If
End Sub
 
Private Sub cmdDelete_Click()
  Dim Result As VbMsgBoxResult
 
  Result = vbNo
  If RowIndex <> 0 Then
    Result = MsgBox("Voulez-vous supprimer le contact?", vbQuestion + vbYesNo)
  Else
    MsgBox "Vous devez sélectionner un contact pour le supprimer", vbExclamation
  End If
  If Result = vbYes Then
    pl.DeleteContact RowIndex
    PrepareForm
  End If
End Sub
 
Private Sub cmdUpdate_Click()
  Dim Result As VbMsgBoxResult
 
  Result = vbYes
  If RowIndex = 0 Then
    Result = MsgBox("C'est un nouveau contact. Voulez-vous l'ajouter?", vbQuestion + vbYesNo)
  End If
  If Result = vbYes Then
    pl.SaveContact tboFirstname.Value, tboName.Value, RowIndex
    PrepareForm
  End If
End Sub
 
Function SetCboChoiceSource()
  cboChoice.List = pl.gettblContacts()
End Function
 
Function PrepareForm()
  SetCboChoiceSource
  cboChoice.Value = vbNullString
  tboFirstname.Value = vbNullString
  tboName.Value = vbNullString
End Function

Et voici le code du module standard (nommé pl).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Option Explicit
 
Sub ShowFormContact()
  Load uContact
  uContact.SetCboChoiceSource
  uContact.Show
  Unload uContact
End Sub
 
Function getContact(RowIndex As Long) As Long
  With uContact
    .tboName = Range("t_contacts[Nom]")(RowIndex).Value
    .tboFirstname = Range("t_contacts[prénom]")(RowIndex).Value
  End With
End Function
 
Function SaveContact(FirstName As String, LastName As String, Optional RowIndex As Long)
  If RowIndex = 0 Then
    If Range("t_contacts").ListObject.DataBodyRange Is Nothing Then
      RowIndex = 1
    Else
      RowIndex = Range("t_Contacts").Rows.Count + 1
    End If
  End If
  Range("t_contacts[prénom]")(RowIndex).Value = FirstName
  Range("t_contacts[nom]")(RowIndex).Value = LastName
End Function
 
Sub DeleteContact(RowIndex As Long)
  Range("t_contacts").ListObject.ListRows(RowIndex).Delete
End Sub
 
Function gettblContacts()
  gettblContacts = Range("t_Contacts")
End Function

Idéalement, il faudrait ajouter une gestion d'erreur sur chaque procédure déclenchée par l'utilisateur (pl.ShowFormContact et les évènements du userform), mais je montre ici une architecture de développement qui permet de scinder les responsabilités du code.

Tu constateras également que le userform n'utilise qu'un jeu de textbox pour gérer les données du contact au lieu de deux (l'un pour la modif et l'autre pour l'ajout). C'est plus ergonomique, et au niveau du code, cela permet de ne coder qu'une seule fois les éventuelles lignes de vérification de saisie (non présentes dans le code proposé).

[1357VBA]

Merci beaucoup Pierre
Vraiment !

Eric

[1357VBA]

Bonjour Pierre,

Je suis toujours sur ton code qui est vraiment EXTRA parce que tout n'est pas encore limpide pour moi qui ai encore beaucoup de notions à assimiler. Je dois le digérer si je puis dire.

J'ai vu qu'il y a une plage nommée mais que ton code n'y fait pas appel sauf erreur de ma part. Y-a-t-il un intérêt à la garder ?

Je voudrai faire un tri Alphabétique sur les Noms. Après avoir tenté d'adjoindre le code enregistré via l'enregistreur de la macro à "gettblContacts" ce qui me semblait le plus logique et récolté quelques messages d'erreur d'Excel ; j'ai essayé à peu près partout mais avec le même succès.

Aurais-tu une autre piste à me souffler ?

Par avance merci
Eric