La méthode 'Add' de l'objet 'ListRows' a échoué

Version imprimable

Voir 40 message(s) de cette discussion en une page

04/09/2017, 15h30
1357VBA

2 pièce(s) jointe(s)

je repose ma question car je suis breton...

Pourquoi le fichier origine de JP fonctionne-t-il

Pièce jointe 309024

et pas la copie ?

Pièce jointe 309029

modification portant uniquement sur le lancement du userform
04/09/2017, 15h37
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...

2 pièce(s) jointe(s)

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:

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:

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
Pièce jointe 309044

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

Pièce jointe 309054

@+JP

04/09/2017, 15h55
1357VBA

Citation:

Envoyé par mjpmjp

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
04/09/2017, 16h00
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)?
04/09/2017, 16h02
mjpmjp

3 pièce(s) jointe(s)

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

Pièce jointe 309065

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

Pièce jointe 309066

Pièce jointe 309069

le code est dans le module du form ,
je vais le déplacer et re tester
04/09/2017, 16h10
Pierre Fauconnier
Citation:

Envoyé par 1357VBA

[...]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:

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.

3 pièce(s) jointe(s)

re ,

fichier modifieé : Pièce jointe 309075

après la modif (code UserForm1) :

Code:

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:

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:

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

le même message :
Pièce jointe 309080

qui pointe sur Set Tb :
Pièce jointe 309085

les amis , je vais :java: ...

@+JP

04/09/2017, 17h53
1357VBA

Citation:

Envoyé par mjpmjp

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

@+JP

Je te comprends... tu te souviens quand je te disais je me noie ;) et moi je débute à peine
04/09/2017, 18h10
1357VBA

Citation:

Envoyé par Pierre Fauconnier

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 ?
04/09/2017, 21h11
Pierre Fauconnier

Citation:

Envoyé par 1357VBA

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.
04/09/2017, 21h24
1357VBA

JP, de mon côté ton fichier V4 ne plante pas, il ajoute les valeurs en dehors du tableau
04/09/2017, 21h32
Pierre Fauconnier

Citation:

Envoyé par mjpmjp

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

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... ;)
04/09/2017, 21h37
1357VBA

Citation:

Envoyé par Pierre Fauconnier

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:calim2:
04/09/2017, 21h50
Pierre Fauconnier

Citation:

Envoyé par 1357VBA

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...
04/09/2017, 21h52
Pierre Fauconnier

Citation:

Envoyé par 1357VBA

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:calim2:

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...
04/09/2017, 22h05
1357VBA

2 pièce(s) jointe(s)

Résumé

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 :
Pièce jointe 309210

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 :mouarf:

Pièce jointe 309219

2 pièce(s) jointe(s)

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:

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:

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é).

Pièce jointe 309260

05/09/2017, 13h41
1357VBA

Merci beaucoup Pierre
Vraiment !

Eric
07/09/2017, 12h05
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

Voir 40 message(s) de cette discussion en une page