Discussion :

[JohannCOU]

Bonjour à tous,

Je suis nouveau sur le forum et je vous sollicite car je me met au VBA. Et j'avoue je rencontre quelques difficultés.
Je cherche à réaliser un fichier Excel contenant une multitude de données sur l'ensemble des produits de mon entreprise (environ 1500). Et j'aimerais, a l'aide de VBA, rendre ce fichier ergonomique pour les utilisateurs. Je voudrais remplir ou modifier un tableau avec toutes les données à l'aide d'une userform. Jusque là, pas de problème, mais j'ai un problème quand je rouvre la userform: elle n'est pas à jour. Je suis obligé de la fermer et de la rouvrir. C'était mon premier problème!!

J'ai un second problème: A l'aide d'une autre userform, je gère l'affichage des colonnes pour limiter la largeur du tableau et que mes collaborateurs n'est que les colonnes qui les intéressent. Le problème est dans la gestion des Checkboxes, je voudrais pouvoir en cochant ou décochant une checkbox, qu'elle en gère d'autres. Et que sa valeur soit mise à jour en fonction de ces mêmes checkboxes.

Je ne suis pas sûr d'être très clair, j'ai donc réalisé un fichier ultra simplifié qui illustre, je pense, bien mieux mes problèmes!

Gestion données simplifiée.xlsm

Pouvez-vous m'aider, s'il vous plait?

Je vous remercie par avance à tous ceux qui se pencheront sur mes problèmes!

Johann.

[gaby277]

Bonjour,

Pour ton problème 1

par exemple:

Code :

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

1
2
3
4
Sub Transfertvaleurs() 'Sert à tranposer les valeurs du tableau1 dans la USFDonnées
...
USFDonnées.Show 0
...

0 pour non modale

Ou

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Sub Transfertvaleurs() 'Sert à tranposer les valeurs du tableau1 dans la USFDonnées
 
 
l = ActiveCell.Row - Sheets("Feuil1").ListObjects("Tableau1").Range.Row
Li = ActiveCell.Row
 
'Application.EnableEvents = False
If l > 0 And l < Sheets("Feuil1").ListObjects("Tableau1").ListRows.Count + 1 Then
USFDonnées.TextBox1.Text = Cells(Li, 1)
USFDonnées.TextBox2.Text = Cells(Li, 2)
USFDonnées.TextBox3.Text = Cells(Li, 3)
USFDonnées.TextBox4.Text = Cells(Li, 4)
USFDonnées.Show 'vbModeless

le .show est après l'initialisation des textbox

[JohannCOU]

Bonjour Gaby et merci pour ta réponse,

Tes solutions fonctionnent parfaitement. Par contre, je ne comprends pas une chose. Dans ta première solution tu rends la userform non modal et je pensais que de ce fait la feuille de calcul restait accessible, et bizarrement ce n'est pas le cas ?? (Tant mieux je ne le souhaite pas). J'ai fais le test avec la userform controlecolonne pour confirmer et là, la feuille est accessible?

Pourrais-je avoir une explication?

Merci d'avance.
Johann

[gaby277]

Bonjour,
Je t'ai donné du code pour te permettre d'avancer. Pour explications détaillées, je laisse le soin aux vrais experts, pour ne pas te donner d'explications plus ou moins approximatives et fumeuses. Mais en gros le problème sur lequel tu as buté c'est l'accès aux feuilles de calcul avec une userform modale.

Par curiosité j'ai aussi essayer de déporter l'initialisation des textbox dans le OnActivate de l'userForm mais ça marche pas non plus.

Je trouve qu'effectivement c'est un peu déconcertant … mais il doit y avoir des raisons, que j'ignore, pour qu' Excel se comporte ainsi.

Pierre Fauconnier va (a ?) publier(é) sous peu un tutoriel sur les userForms tu pourras le consulter avantageusement.

Bonne programmation

[Qwazerty]

Salut

[Edit] Oupss la solution a été donné par Gaby... la lecture en diagonale c'est pas mon fort :p [/Edit]


Il suffit simplement de d'abord renseigner tes textbox, puis d'afficher ta UserForm.
Visiblement à l'appel du Show, le code source stop pour donner la main au userform.

Code :

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

1
2
3
4
5
6
7
If l > 0 And l < Sheets("Feuil1").ListObjects("Tableau1").ListRows.Count + 1 Then
    USFDonnées.TextBox1.Text = Cells(li, 1)
    USFDonnées.TextBox2.Text = Cells(li, 2)
    USFDonnées.TextBox3.Text = Cells(li, 3)
    USFDonnées.TextBox4.Text = Cells(li, 4)
    USFDonnées.Show 'vbModeless
Else

Petit coup de

Lorsqu'un objet UserForm n'est pas modal, le code suivant est exécuté dès qu'il apparait. Les feuilles non modales n'apparaissent pas dans la barre des tâches et ne figurent pas dans l'ordre de l'onglet de la fenêtre.

Note Vous risquez de perdre les données associées à un objet UserForm non modal si vous apportez une modification au projet UserForm à l'origine de la recompilation, par exemple, lorsque vous supprimez un module de code.
Un objet UserForm est toujours modal, il en résulte que l'utilisateur doit toujours répondre avant d'utiliser une autre partie de l'application. Aucun autre code ne s'exécutera tant que l'objet UserForm ne sera pas masqué ou déchargé. Bien que les autres feuilles de l'application soient désactivées pendant l'affichage d'un objet UserForm, les autres applications ne le sont pas.

Lorsqu'un objet UserForm n'est pas modal, le code suivant est exécuté

Ce qui explique pourquoi la modification de gaby277 à résolu ton problème, le code qui suit Userofrm.Sow False est exécuté jusqu'à la fin de la procédure qui le contient. A l'inverse, on comprend que ça n'est pas le cas, Userofrm.Sow (true) stoppe l'exécution du code appelant et le reste de la procédure n'est pas exécutée tant que l'utilisateur n'a pas fermé la Userform.

C'est assez logique, si le userform est fait pour demander un renseignemenbt à l'utilisateur, on attent bien que l'utilisateur est donné le renseignement (et fermé la fen^tre) pour continuer à exécuter le code.

---

Tu peux aussi regarder du coté de la propriété RowSource des composants

---

Je suppose que ton code varie en fonction des connaissances que tu acquières (tu ne pratiques pas partout de la même façon). Ici il est inutile de passer par Controls(...), tu peux "appeler" directement après le nom du UserForm.

Code :

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

1
2
3
    If Worksheets("Feuil1").Columns(2).Hidden = True Then
    USFColonnes.CheckBox1.Value = False
    USFColonnes.Controls("CheckBox1").Value = False

Controls peut être intéressant si tu veux faire des boucles en recomposant le nom des composants

Code :

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

1
2
3
4
5
Dim i as integer
 
for i = 1 to 3
   USFColonnes.Controls("CheckBox" & i).Value = true
Next

Tu peux également simplifier tes écritures, lorsque tu travailles avec des données booléennes, il n'est pas nécessaire de préciser "= True" à chaque fois (pareille pour le code juste au dessus pour Hidden)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
'Données 2
    If USFColonnes.Controls("CheckBox1").Value = True _
    Or USFColonnes.Controls("CheckBox5").Value = True _
    Or USFColonnes.Controls("CheckBox9").Value = True Then
        Worksheets("Feuil1").Columns(2).Hidden = False
    Else
        Worksheets("Feuil1").Columns(2).Hidden = True
    End If
 
    'Se résume en
    Worksheets("Feuil1").Columns(2).Hidden = Not (USFColonnes.CheckBox1.Value Or USFColonnes.CheckBox5.Value Or USFColonnes.CheckBox9.Value)
 
    'Tu peux aussi te servir de ton Tableau Structuré si besoin
    Feuil1.ListObjects("Tableau1").ListColumns("Données 2").DataBodyRange.EntireColumn.Hidden = Not (USFColonnes.CheckBox1.Value Or USFColonnes.CheckBox5.Value Or USFColonnes.CheckBox9.Value)

Pareil ici

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
    If USFColonnes.Controls("CheckBox4").Value = True Then
       USFColonnes.Controls("CheckBox1").Value = True
       USFColonnes.Controls("CheckBox2").Value = True
       USFColonnes.Controls("CheckBox3").Value = True
    Else
       USFColonnes.Controls("CheckBox1").Value = False
       USFColonnes.Controls("CheckBox2").Value = False
       USFColonnes.Controls("CheckBox3").Value = False
 
    End If
 
    'se résume en
    USFColonnes.CheckBox1.Value = USFColonnes.CheckBox4.Value
    USFColonnes.CheckBox2.Value = USFColonnes.CheckBox4.Value
    USFColonnes.CheckBox3.Value = USFColonnes.CheckBox4.Value

---

Un conseil plus global, renomme tes composants, leur nom doit être parlant pour que tu puisse y faire appel facilement lors de l'écriture du code sans retourner voir sur le userfom. C'est également bien plus simple à la relecture du code pour comprendre ce qui a été fait.
N'hésite pas non plus à commenter ton code pour que plus tard, tu ne perdes pas de temps à rechercher ce que tu souhaitais faire (c'est aussi très pratique pour nous si tu viens poser une question sur le forum).

Bon dev'
++
Qwaz

[Pierre Fauconnier]

Salut.

@Gaby: Pas encore publié... J'ai pris un peu de retard, mais Il y a celui-ci de Arkham46

Pour le problème du userform qui n'est pas "mis à jour"...

Il faut comprendre les différents stades de la vie d'une instance de userform.

1. Dès son invocation, un usf est chargé en mémoire s'il n'y est pas déjà.
2. On peut alors le manipuler, et notamment initialiser une série de contrôles (comme Gaby l'a illustré dans sa proposition)
3. Puis on l'affiche;
4. Après les manipulations par l'utilisateur, on le masque;
5. A ce stade, on peut encore le manipuler, notamment pour traiter les données saisies ou modifiées par l'utilisateur;
6. Puis on le décharge de la mémoire.

Tu as oublié la sixième étape dans ton code. Dès lors, ton usf est toujours en mémoire et donc, lorsque tu l'affiches, tu récupères les données qui s'y trouvent.
Pour décharger le usf, utilise simplement unload usf. Idéalement, cette ligne de code se situe hors du usf.

Perso, je coderais l'acquisition, la modification et l'enregistrement des données autrement, mais je trouve que c'est déjà très chouette de ne pas avoir tout mis dans le usf

[patricktoulon]

Bonjour

j'ai entierement repris ton fichier en ce qui concerne les donéees (add/modif) avec l'userform en mode boite de dialog modal (2 petites fonction de rien du tout ,ca c'est bon

par contre je viens de remarquer quelque chose d'etonnant
tout tes checkbox sont gérés par l'evenement click

la mise a true des 3 de chaque probleme est géré par le maitre (le 4 eme) de chaque probleme) ca c'est ok

parcontre les click des 3 autres de chaque probleme gere la mise a jour pour le maitre(le 4 eme) de chaque probleme et c'est la que ca va plus
j'ai mis un msgbox dans la sub mise ajour pour comprendre
en effet USFColonnes.activecontrol.name donne le nom de la frame parent du groupe du checkbox cliqué alors qu'il devrait donner le nom du checkbox cliqué
j'en ai conclu que le fait d'etre dans une frame les evenement click se subclassaient en quelque sorte
j'ai donc pour verifier mes soupcons ,supprimer les frames et la tout va bien ca fait bien le job


si quelqu'un a une explication sur ce point je suis curieux
EDIT : aujourd'hui lundi
je reprends le projet
je viens de constater aussi que changer la valeur du checkbox 4 d'un probleme declanchait son evenement click c'est un peu le serpent qui se mort la queue
je n'ai pas d'explication la dessus il semblerait que ca soit normal il et donc visiblement impossible a ma connaissance de gérer les 4 checkboxs de cette maniere
le 4 eme doit etre un autre type de control je vois que ca
je n'avais jamais fait attention a ce phenomene

[JohannCOU]

Bonjour à tous et merci pour tous vos conseils,

Avec vos explications, j’ai bien compris le fonctionnement des Userforms et je vous en remercie.

@Qwazerty : Effectivement dans le fichier simplifié que j’ai créé comme exemple, le Controls devant les checkboxes est inutile mais dans mon fichier réel j’ai attribué des constantes aux colonnes de ma feuille de calcul pour me simplifier la vie. Et surtout, si je veux modifier l’emplacement des colonnes, je n’ai qu’à modifier la valeur des constantes et non modifier la valeur des colonnes partout où elles apparaissent dans mon code. Sinon l’idée d’utiliser les en-têtes de mon tableau est très intéressante et me plait beaucoup mais si je décide de les renommer à un moment donné pour X raisons, il faut que j’aille modifier mon code partout où je fais appel à la colonne. C’est pour cela que je préfère rester avec mes constantes de numéro de colonnes.

Par contre, les résumés de code que tu as fait me plaisent énormément et raccourcissent considérablement mon code. Je suis en train de modifier mon fichier en conséquence. Merci

@Pierre Fauconnier : Merci pour les explications et aussi d’avoir déplacer la conversation à un endroit plus opportun. (Je ne maitrise pas encore bien le site.) Tu dis que tu coderais différemment, Si je peux avoir un conseil ou deux, je suis preneur !!

@Patricktoulon : Merci d’avoir pris le temps de travailler sur mon fichier. Serait-il possible de récupérer le fichier avec tes modifications afin que je puisse les apprécier ? J’ai supprimé la Frame « Problème 3 » pour essayer de constater la même chose que toi mais j’ai du mal comprendre car je ne vois pas d’amélioration. Et je ne vois pas pourquoi elle jouerait un rôle dans la gestion des checkboxes car j’en fais appel nulle part dans mon code. Pour moi, les frames influencent seulement la valeur des Optionbuttons mais mes connaissances sont limitées.

En tout cas je vous remercie pour tout et il reste plus qu’à gérer le problème de Checkboxes.

A+

[patricktoulon]

voici mo model basé sur le tien
tu verra pour les données (ajout/modif) j'ai tranformé le usfdonnées en boite de dialog modal par deux tout petites fonctions

par contre le userform avec tes checkbox c'est un truc que je pige pas
le fait de changer la valeur du checkbox4 en fonction du click sur checkbox 1,2,3 declenche l'evenemnt click du checkbox4
j'ai bien essayé l'evenement change mais ca change rien

essaie le comme ca en cliquant sur le checkbox4 du probleme 1 (j'ai fait que celui la ) il fonctionne
maintenant debloque la ligne "'If x < 3 Then maitre.Value = False Else maitre.Value = True" dans la sub de mise a jour par les 3 premiers checkboxs du probleme 1
et ressaie le checkbox4 et been ca marche plus

si tu debloque le msgbox dans l'evenement click du checheckbox 4 et que tu clique sur une des 3 premier checkboxs le message s'affiche

il est clair donc que le changement de valeur du checkbox 4 par macro declanche sont evenemlent click ( c'est ca que je pige pas )
mon fichier en piece jointe

sinon le userform usfdonnées fonctionne tres bien dans les deux modes
et pour le coup les codes sont pas tres compliqués

[JohannCOU]

@Patricktoulon : Merci beaucoup d’avoir pris le temps de modifier mon fichier. Ça va énormément m’aider à structurer et simplifier mon fichier réel. Il y a juste une chose : Ta variable flag sert principalement à interdire le remplissage du tableau si une donnée n’est pas renseignée. C’est ça ?

Pour les checkboxes, c’est incompréhensible.

A+

[patricktoulon]

oui c'est ca
c'est une variable public dans le userform
c'est donc comme je l'ai dis l'ouverture/fermeture du userform qui mene la danse
autrement dit il se comporte comme une boite de dialog excel ou autre
tant que tu n'a pas validé tu peux rien faire sur le sheets meme pas selectionner une cellule
la fonction n'envoie la reponse que si le userform est en hide puis elle le ferme
si la condition des 4 textboxs remplies ne convient pas on peut metre flag a true si au moins 1 ou plusieurs textbox on été remplie c'est pas tout a fait la meme chose

pour comprendre bien le principe il faut que tu annlyse le code fonction/userform les deux vont de paire

Pour les checkboxes, c’est incompréhensible.

on est d'accords sur ce point

[Pierre Fauconnier]

[…]@Pierre Fauconnier : Merci pour les explications et aussi d’avoir déplacer la conversation à un endroit plus opportun. (Je ne maitrise pas encore bien le site.) Tu dis que tu coderais différemment, Si je peux avoir un conseil ou deux, je suis preneur !![…]

Voici l'exemple que je développe sur mon blog... avec un fichier d'exemple


Ce billet fait également écho à cette discussion dans laquelle une problématique similaire est développée… . J'espère ainsi apporter une réponse générique à cette question récurrente sur nos forums

Je ne parle pas des checkbox ici (qui devraient normalement faire l'objet d'une autre discussion… ). Si tu as toujours un problème avec les checkbox, ouvre une nouvelle discussion pour exposer ce problème-là et uniquement celui-là stp…

[patricktoulon]

re
pas mal pierre ta fonction qui reuni les deux miennent en une (add/modif) ligne
le seul probleme dans le cas du demandeur c'est que pour add la ligne n'existe pas
du coup la premiere partie du code ne convient pas
a moins d'instruire data en tant que tableau vide pour le bouton ajout de ligne
redim data(1,4)'meme nombre que ses textbox
quand c'est le bouton ajout les textboxs doivent etre vides quand c'est modif les textboxs contiennent les valeurs de la cellule active c'est pour ca que je lui ai fait en deux fonction

Code :

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

1
2
3
4
5
6
Function UpdateContact(ByRef Data) As Long
  Unload usfContact
  With usfContact
    .tboID.Value = Data(1, 1)
    .tboFirstName.Value = Data(1, 2)
    .tboLastName.Value = Data(1, 3)

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
'ajout de donnée dans le tableaux
Public Function newline() As Boolean
    With USFDonnées
        .Show
        If .flag = True Then
            Sheets("Feuil1").ListObjects("Tableau1").ListRows.Add (1)
            Sheets("Feuil1").Cells(5, 1).Resize(1, 4) = Array(.TextBox1.Text, .TextBox2.Text, .TextBox3.Text, .TextBox4.Text)
        End If
        newline = .flag
    End With
    Unload USFDonnées
End Function
'
'
'modifiction des données d'une ligne
Public Function modif_ligne() As Boolean
    Dim pl&, i&
      If Not Intersect(Range(Sheets("Feuil1").ListObjects("Tableau1")), ActiveCell) Is Nothing Then
        pl = ActiveCell.Row
        With USFDonnées
            For i = 1 To 4: .Controls("TextBox" & i).Value = Sheets("Feuil1").Cells(pl, i): Next
            .Show
            If .flag = True Then
                Sheets("Feuil1").Cells(pl, 1).Resize(1, 4) = Array(.TextBox1.Text, .TextBox2.Text, .TextBox3.Text, .TextBox4.Text)
            End If
            modif_ligne = .flag
        End With
        Unload USFDonnées
    End If
End Function

le 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
Option Explicit
Public flag As Boolean
Dim i&, x&
Private Sub CommandButton1_Click()
flag = False: Me.Hide
End Sub
Private Sub CommandButton2_Click()
For i = 1 To 4
If Me.Controls("TextBox" & i) <> "" Then x = x + 1
Next
If x = 4 Then flag = True
Me.Hide
End Sub
 
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then Cancel = True: flag = False: Me.Hide
End Sub

ton idée de 2 en une m'interesse je vais voir si je peux adapter pour son cas

[Pierre Fauconnier]

Il faut aller au plus simple et génériser son code au maximum (ca évite le resize, notamment).

Si on veut modifier, on crée l'array sur base de la listrow récupérée. Si on veut ajouter une donnée, c'est exactement pareil. On crée l'array sur base d'une nouvelle listrow renvoyée par une formule générique et on se fout du resize et du nombre de cellules, puis on utilise la fonction d'update.

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
Function getNewRow(TableName As String) As ListRow
  Set getNewRow = Range(TableName).ListObject.ListRows.Add()
End Function
 
Sub CreateContact()
  Dim lr As ListRow
  Dim Data
 
  Set lr = getNewRow("t_Contacts")
  Data = lr.Range.Value
  If UpdateContact(Data) = -1 Then
    lr.Range.Value = Data
    MsgBox "Ajout effectué"
  Else
    MsgBox "Erreur durant l'ajout"
  End If
End Sub

Après, on peut essayer de fondre l'update et le create en une seule fonction, notamment pour éviter la redondance des messages finaux (EDIT)

[patricktoulon]

et la voila en une seule fonction pour ajout ou modif de ligne

code dans le module de la feuille(ses boutons)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Option Explicit
 
Private Sub Ajouter_Click()
Dim x
x = updaterow(1)
End Sub
Private Sub Modifier_Click()
Dim x
x = updaterow(0)
End Sub
Private Sub CommandButton3_Click()
USFColonnes.Show
End Sub

code la fonction dans un module standard

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
'ajout/modif  de donnée dans le tableaux
Public Function updaterow(Optional mode As Long = 0) As Boolean
    With USFDonnées
        If mode = 0 Then
            'mode modifier
            If Intersect(Range(Sheets("Feuil1").ListObjects("Tableau1")), ActiveCell) Is Nothing Then: Exit Function  'pour le bouton modif si l'Activecell n'est pas dans le tableau on sort
            pl = ActiveCell.Row
            .TextBox1.Text = ActiveSheet.Cells(pl, 1).Value  'remplissage des textboxs avec les données de la ligne 
            .TextBox2.Text = ActiveSheet.Cells(pl, 2).Value  'idem
            .TextBox3.Text = ActiveSheet.Cells(pl, 3).Value  'idem
            .TextBox4.Text = ActiveSheet.Cells(pl, 4).Value  'idem
        End If
        .Show
        If .flag = True Then
            If mode = 1 Then Sheets("Feuil1").ListObjects("Tableau1").ListRows.Add (1): pl = 5
            Sheets("Feuil1").Cells(pl, 1).Resize(1, 4) = Array(.TextBox1.Text, .TextBox2.Text, .TextBox3.Text, .TextBox4.Text)
        End If
        updaterow = .flag
    End With
    Unload USFDonnées
End Function

et le userform on change rien

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
 
Option Explicit
Public flag As Boolean
Dim i&, x&
Private Sub CommandButton1_Click()
flag = False: Me.Hide
End Sub
Private Sub CommandButton2_Click()
For i = 1 To 4
If Me.Controls("TextBox" & i) <> "" Then x = x + 1
Next
If x = 4 Then flag = True
Me.Hide
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then Cancel = True: flag = False: Me.Hide
End Sub

a oui!! c'est plus leger

[Pierre Fauconnier]

Patrick,

Tu dois travailler avec les tableaux structurés (listobject, listrow) pour simplifier ton code d'une part et le coupler le moins possible à la structure de ton classeur.

Si on déplace le tableau dans la feuille et/ou qu'on ajoute des colonnes, tu vas devoir revoir ton code. En travaillant avec un listrow comme je le fais, je peux déplacer mon tableau où je veux dans mon classeur sans avoir rien à modifier dans mon code et je limite les modifs dues à l'ajout de colonnes dans le tableau

De plus, travailler avec le listrow et sa propriété Range pour créer l'array me dispense du resize. Ca veut dire que si demain j'ajoute des colonnes dans le tableau t_Contacts, je n'ai que la fonction UpdateContact et le userform à modifier. Je n'ai pas à me préoccuper ni de l'endroit où le tableau est, ni du nombre de colonnes pour le Resize.

Les tableaux structurés Excel et leur pendant en VBA (ListObject) facilitent et sécurisent la programmation, tout en facilitant la maintenance

A mon avis, leur utilisation n'est pas une option, mais une obligation!

[Pierre Fauconnier]

Et si vraiment on veut travailler avec une seule procédure pour l'update et le create, par exemple en fonction du fait que activecell est dans le tableau ou pas, on peut aussi. Ca peut être plus souple.

Toujours dans l'idée de décomposer le code en procédures/Fonctions qui n'ont qu'un seul objectif, je propose alors une fonction pour récupérer la listrow qui contient activecell, et la procédure mixte Create/Update choisit la listrow en fonction de activecell. Et on ne touche pas du tout ni au userform ni même à la fonction qui le manipule. C'est l'énorme avantage du découpage en petits blocs de code.

Voici la fonction qui récupère la ligne du tableau structuré de la cellule passée en argument (idéalement, on la générise en la sécurisant par un test if not rng.listobject is Nothing then… puis on teste dans le code appelant que la fonction a bien renvoyé un objet ListRow et pas Nothing).

Code :

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

1
2
3
4
5
6
Function getRowFromCell(rng As Range) As ListRow
  Dim lo As ListObject
 
  Set lo = rng.ListObject
  Set getRowFromCell = lo.ListRows(rng.Row - lo.Range.Row)
End Function

Voici la procédure mixte

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
 
Sub CreateUpdateContact()
  Dim ID As Long
  Dim lr As ListRow
  Dim Data
 
  If Not Intersect(ActiveCell, Range("t_Contacts")) Is Nothing Then
    Set lr = getRowFromCell(ActiveCell) ' Modification de la ligne courante
  Else
    Set lr = getNewRow("t_Contacts") ' Nouvelle ligne du tableau (attention, elle est physiquement créée)
  End If
  Data = lr.Range.Value
  If UpdateContact(Data) = -1 Then
    lr.Range.Value = Data
    MsgBox "Ok"
  Else
    MsgBox "NOK"
  End If
End Sub

On a alors un code très propre, notamment parce qu'il s'appuie sur les listobject et listrow et qui permet de limiter les modifs au minimum lors d'une modification structurelle du classeur. Je remets tout le code ici, ainsi que celui du userform contenant tboID, tboFirstName et tboLastName comme textboxes, ainsi que btnValidate et btnCancel comme commandbuttons, tel que celui-ci.



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
Option Explicit
 
Public Result As Long
 
Private Sub btnCancel_Click()
  Result = 1
  Me.Hide
End Sub
 
Private Sub btnValidate_Click()
  Result = -1
  Me.Hide
End Sub

Code d'un module standard

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
Function UpdateContact(ByRef Data) As Long
  Unload usfContact
  With usfContact
    .tboID.Value = Data(1, 1)
    .tboFirstName.Value = Data(1, 2)
    .tboLastName.Value = Data(1, 3)
    .Show
    UpdateContact = .Result
    If .Result = -1 Then
      Data(1, 1) = .tboID.Value
      Data(1, 2) = .tboFirstName.Value
      Data(1, 3) = .tboLastName.Value
    End If
  End With
  Unload usfContact
End Function
 
Function getNewRow(TableName As String) As ListRow
  Set getNewRow = Range(TableName).ListObject.ListRows.Add()
End Function
 
Function getRowFromCell(rng As Range) As ListRow
  Dim lo As ListObject
 
  Set lo = rng.ListObject
  Set getRowFromCell = lo.ListRows(rng.Row - lo.Range.Row)
End Function
 
Sub CreateUpdateContact()
  Dim ID As Long
  Dim lr As ListRow
  Dim Data
 
  If Not Intersect(ActiveCell, Range("t_Contacts")) Is Nothing Then
    Set lr = getRowFromCell(ActiveCell)
  Else
    Set lr = getNewRow("t_Contacts")
  End If
  Data = lr.Range.Value
  If UpdateContact(Data) = -1 Then
    lr.Range.Value = Data
    MsgBox "Ok"
  Else
    MsgBox "NOK"
  End If
End Sub

[patricktoulon]

j'avoue m'y intérésser que depuis peu a listobject(tableaux)et surtout parce que depuis peu les fichiers que je recois en contiennent
je ne metrise pas tres bien ,la syntaxe n'est pas tout a fait pareil

il y a un tuto la dessus ?

[Pierre Fauconnier]

[…]il y a un tuto la dessus ?

Je suis en train de les finaliser


Merci de ton intervention, en tout cas. Je vais compléter mon billet de blog avec le "tout en un"

[patricktoulon]

re
et voila maintenant je me langui
si ca peut simplifier mes fichiers pro crois moi je prends
merci pour ton travail en tout cas

edit :
jusqu'a present je me debroullais avec les names ce qui mepermettais d'avoir x tableaux distincts dans un meme sheets ce qui a ce que je constate dans les names fait la creation de tableaux

deja si je savais determiner la cellule (x) d'un tableaux ,son address bref ces proriétés quoi ,ca serait un grand pas
pour le moment par exemple je determine la cells(1) du tableaux comme ceci

Code :

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

MsgBox Range(Sheets("Feuil1").ListObjects("Tableau1")).Cells(1).Address

comme je le ferais avec les names

est ce la bonne methode ou pas pfff!!!! j'en sais fichtrement rien