Initialisation userform et gestion checkbox

**patricktoulon** · 07/08/2018, 14h15

je suis pas sur pierre si c'est ce que ton code fait mais

la condition intersect n'est que pour le mode modif pour le mode ajout on s'en fout un peu car c'est d'office la ligne 1 du tableau
sinon il faut que l'activecell soit toujour dans le tableau c'est pas top pour le mode "add"
et puis on selectionne une ligne uniquement si on veut la modifier
j'ai l'impression que dans ton code l'intersect decide du mode c'est bien ca ?
pour peu que l'activecell soit dans le tableau et que tu veuille ajouter walouh!!!
c'est pour cela que mes bouton injecte l'argument mode (0/1)
le intersect est juste la pour ne rien faire en mode modif si l'activecell n'est pas dans le tableau
bien entendu je parle dans le cadre de cet exercice

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
Sub CreateUpdateContact()
  Dim ID As Long
  Dim lr As ListRow
  Dim Data
 
  If Not Intersect(ActiveCell, Range("t_Contacts")) Is Nothing Then  ' ajouter ici la condition sur avec le mode add/modif 
    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

je parts en intervention je reviens toute a l'heure j'ai mon apprenti qui s'en sort pas
A+

**Pierre Fauconnier** · 07/08/2018, 14h17

On digresse un peu, mais bon

Sur base d'un tableau nommé t_Contacts:

Première cellule de la plage de données du tableau: range("t_Contacts")(1).address
Première cellule de la plage de données du tableau (autre méthode): range("t_Contacts").ListObject.DataBodyRange(1)
Première cellule du tableau (autrement dit, cellule d'entête de la première colonne dudit tableau): range("t_Contacts[#All]")(1).address
Première cellule du tableau (autrement dit, cellule d'entête de la première colonne du tableau) (autre méthode): range("t_Contacts").ListObject.Range(1)

Mais attention. Programmer avec les tableaux structurés, ce n'est pas juste transposer les anciennes manières de procéder. Par exemple, plus besoin de END(XLUP) pour déterminer la dernière ligne d'un tableau, tu peux récupérer la dernière ligne directement, soit en objet ListRow, soit en objet Range, et on crée (et récupère) une nouvelle ligne plus facilement:

Dernière ListRow: range("t_Contacts").ListObject.ListRows(range("t_Contacts").ListObject.ListRows.Count)
Plage de la dernière ListRow: range("t_Contacts").ListObject.ListRows(range("t_Contacts").ListObject.ListRows.Count).Range
Nouvelle ligne du tableau, créée physiquement sur la feuille: Set lr = range("t_Contacts").ListObject.ListRows.add() (avec Dim lr As ListRow)

Evidemment, c'est plus simple de travailler avec des objets intermédiaires, au moins le listobject

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub Demo()
  Dim lo As ListObject
  Dim LastRow As ListRow
  Dim LastRowRange As Range
  Dim NewRow As ListRow
 
  If Not Range("t_Contacts").ListObject Is Nothing Then
    Set lo = Range("t_Contacts").ListObject
    Set LastRow = lo.ListRows(lo.ListRows.Count)
    Set LastRowRange = LastRow.Range
    Set NewRow = lo.ListRows.Add()
  End If
End Sub

PS: Je propose que l'on ne pollue pas trop la discussion en cours…

**Pierre Fauconnier** · 07/08/2018, 14h21

Envoyé par patricktoulon

[…]la condition intersect n'est que pour le mode modif pour le mode ajout on s'en fout un peu[…]

Je donne juste un exemple d'une procédure mixte en fonction du fait que activecell est dans le tableau ou pas. Si Activecell est dans le tableau (testé grâce à Intersect), on récupère les données pour les mettre à jour, sinon on crée une nouvelle ligne puis on "la met à jour" avec les données du userform. Après, à chacun d'adapter, je n'ai pas dit que je collais pile poil à la demande

.

Le principe général est le suivant, et si Johan arrive à l'adapter à son cas, il aura gagné du galon plus que si on lui donne du tout cuit qui colle exactement à ce qu'il attend et qu'il copie-colle "bêtement"

déterminer la listrow sur laquelle on travaille (existante ou nouvelle);
transférer les données de la range de la listrow dans un array;
transférer les données de l'array dans les contrôles du userform;
après Validate, transférer les données du userform dans l'array passé en ByRef;
transférer les données de l'array dans la range de la listrow.

Et bien sûr, on isole chaque fonction qui doit pouvoir travailler indépendamment des autres. Le userform n'a pas à savoir d'où viennent les données, et la fonction qui le pilote non plus. Ce pourrait être d'un fichier txt, d'un adodb.Recordset ou autre, on s'en fout. Et si dans l'autre sens, les données n'étaient pas modifiées par userform mais par récupération de données dans un Access par exemple, on laisserait tomber le userform et on adapterait simplement la fonction UpdateContact, la mécanique pour déterminer quelle ligne du tableau impacter ne changeant pas d'un iota. C'est tout l'intérêt de cloisonner son code et de rendre chaque fonction indépendante des autres. On aurait pu passer la listrow à la fonction UpdateContact ou encore manipuler une variable tableau globale (beurk), mais on aurait alors manquer l'objectif de couplage faible et d'indépendance des fonctions les unes par rapport aux autres.

Envoyé par patricktoulon

[…]car c'est d'office la ligne 1 du tableau […]

Non, ce n'est pas la ligne 1, c'est une nouvelle ligne. Regarde bien la fonction GetNewRow qui ajoute une ligne au tableau

**Pierre Fauconnier** · 07/08/2018, 16h10

Envoyé par JohannCOU

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

Pour moi, c'est un mauvais choix, mais je propose que si tu as encore des soucis avec ces checkboxes, on en discute dans une autre discussion pour ne pas mélanger les sujets…

PS: C'est juste un constat technique, pas une attaque ad hominem

**JohannCOU** · 07/08/2018, 16h32

Bonjour à tous,

Merci Pierre et Patrick pour le temps passé et toutes ces explications. Je pense que vous avez fait un bon tour sur la question des Userforms et vous avez complétement répondu à mes attentes.
Maintenant, à moi de comprendre vos explications

pour simplifier mon code et l'adapter à mon fichier. J'ai un peu de travail vu le nombre de colonnes dans le tableau de mon fichier réel(Plus de 40)

. Mais bon je vais essayer de maitriser la fonction Array pour me simplifier la vie.

Pour la gestion des checkboxes, je vais passer la discussion actuelle en résolu et ouvrir une autre sur le sujet des checkboxes demain, je pense!

J'ai encore pleins d'idées pour mon fichier et je pense que je vais encore avoir besoin d'aide

. J'ouvrirai d'autres discussions en temps voulu si je ne trouve pas de solution.

Encore merci pour tout.

Johann.

**Qwazerty** · 07/08/2018, 20h06

Salut Pierre

Pour rester dans la digression

Envoyé par Pierre Fauconnier

On digresse un peu, mais bon

Sur base d'un tableau nommé t_Contacts:

Première cellule de la plage de données du tableau: range("t_Contacts")(1).address
Première cellule de la plage de données du tableau (autre méthode): range("t_Contacts").ListObject.DataBodyRange(1)
Première cellule du tableau (autrement dit, cellule d'entête de la première colonne dudit tableau): range("t_Contacts[#All]")(1).address
Première cellule du tableau (autrement dit, cellule d'entête de la première colonne du tableau) (autre méthode): range("t_Contacts").ListObject.Range(1)

Déjà, je viens de découvrir la balise "codeinline"... le nombre de fois où j'ai pesté en mettant du code et en ayant des gros placards au milieu du texte... Merci

Ceci dit, ça n'est pas l'objet de mon message.

Tu sembles préférer la forme range("t_Contacts").ListObject à Feuil1.ListObject("t_Contacts").
Étant un farouche partisan dans le fait de toujours préciser le classeur et la feuille sur laquelle on travaille, j'avoue que cette écriture me laisse perplexe. Y-a-t-il une raison particulière à l'utilisation de cette façon de faire?

Par contre, je n'avais pas remarqué (avant un de tes autres poste utilisant les ListObject) la possibilité à partir d'un range de pointer le listObject.
Je trouve que c'est une possibilité intéressante, d'autant qu'il est possible de remonter au tableau à partir de n'importe quelle cellule incluse dans celui-ci Feuil1.range("A8").ListObject (Juste pour le plaisir d'utiliser codeinline

[ça manque un bouton dans l'éditeur par contre!]).
Je n'ai pas d'exemple d'utilisation concrété en tête mais je suis sûr que ça peut servir! J'essaierais de le placer dans un code pour le fun ^^.

Ton dernier message m'a aussi interpelé, tu ne sembles pas partisan de l'utilisation des noms de champs lors de l'utilisation en VBA du Tableau Structuré, chose que je fais plus que régulièrement lors de réponse sur le forum, notamment en utilisant le tag des contrôles pour y mémoriser le nom du champs correspondant.
Sur ce dernier point, à la lecture de ton antépénultième réponse... je comprends que je n'isole pas assez mes userforms en pratiquant ainsi :p Ça aussi j'essaierais d'y penser lors du prochain post traitant de userform

(Je ne sais pas s'il y aura assez de place dans am tête

)

++
Qwaz

**Pierre Fauconnier** · 07/08/2018, 20h49

Salut Quaz

Envoyé par Qwazerty

[...]
Tu sembles préférer la forme range("t_Contacts").ListObject à Feuil1.ListObject("t_Contacts"). Étant un farouche partisan dans le fait de toujours préciser le classeur et la feuille sur laquelle on travaille, j'avoue que cette écriture me laisse perplexe. Y-a-t-il une raison particulière à l'utilisation de cette façon de faire?

Dans la mesure où le nom du tableau est unique dans le classeur, je préfère ne pas passer par la feuille. Si le tableau est déplacé sur une autre feuille, mon code continue à fonctionner

Envoyé par Qwazerty

[...]
Par contre, je n'avais pas remarqué (avant un de tes autres poste utilisant les ListObject) la possibilité à partir d'un range de pointer le listObject.
Je trouve que c'est une possibilité intéressante, d'autant qu'il est possible de remonter au tableau à partir de n'importe quelle cellule incluse dans celui-ci Feuil1.range("A8").ListObject (Juste pour le plaisir d'utiliser codeinline

[ça manque un bouton dans l'éditeur par contre!]).
Je n'ai pas d'exemple d'utilisation concrété en tête mais je suis sûr que ça peut servir! J'essaierais de le placer dans un code pour le fun ^^.

J'illustre le cas où c'est intéressant de faire ainsi, dans la fonction getRowFromCell(rng as Range), utilisé avec Intersect pour trouver le ListRow de la cellule active lorsque cell-ci se trouve dans le tableau, avec une simple arithmétique pour trouver l'index du ListRow souhaité

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

Envoyé par Qwazerty

[...]
Ton dernier message m'a aussi interpelé, tu ne sembles pas partisan de l'utilisation des noms de champs lors de l'utilisation en VBA du Tableau Structuré, chose que je fais plus que régulièrement lors de réponse sur le forum, notamment en utilisant le tag des contrôles pour y mémoriser le nom du champs correspondant.

Sur ce dernier point, à la lecture de ton antépénultième réponse... je comprends que je n'isole pas assez mes userforms en pratiquant ainsi :p Ça aussi j'essaierais d'y penser lors du prochain post traitant de userform

(Je ne sais pas s'il y aura assez de place dans am tête

)

++
Qwaz

En effet, j'essaie de pas trop coupler le userform avec la structure du tableau, histoire d'avoir le moins possible à modifier lorsque l'un ou l'autre change. La fonction qui alimente et récupère les données du userform ainsi que le userform lui-même pourraient être utilisés dans une autre configuration (Word, un autre classeur,...) et lier le userform à un listrow verrouille son utilisation à Excel. Comme je l'ai précisé à Patrick, si la source change, j'ai plus de souplesse avec un array qu'aver le listrow. Mais je pousse peut-être le bouchon un peu loin.

Pour autant, dans le cadre d'un framework où il faudrait travailler avec plusieurs userforms liés à des tableaux structurés, on pourrait passer le listrow à la fonction qui gère le userform en se servant des tags comme tu le préconises et en récupérant le nom de la colonne de chaque ligne du listrow, avec un jeu d'échange de données entre listrow et userform qui pourrait être générique et s'appliquer à n'importe quelle paire listrow/usrform

.

Mais pour ma part, je préfèrerais probablement une autre solution qui consisterait à passer deux tableaux d'array, l'un pour les données et un autre de même structure pour les noms des contrôles du userform. Ca rendrait plus générique tout en ne liant pas aux listrow et donc à la structure du classeur.

Quelque chose du genre:

Fonction d'un module standard pour échanger les données avec le userform qui reçoit les données et le tableau des contrôles

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
Function UpdateContact(ByRef Data, ByVal Controls) As Long
  Dim Counter As Long
 
  Unload usfContact
  With usfContact
    For Counter = 1 To UBound(Data, 2)
      .Controls(Controls(Counter)).Value = Data(1, Counter)
    Next Counter
    .Show
    UpdateContact = .Result
    If .Result = -1 Then
      For Counter = 1 To UBound(Data, 2)
        Data(1, Counter) = .Controls(Controls(Counter)).Value
      Next Counter
    End If
  End With
  Unload usfContact
End Function

La procédure de création/mise à jour devient alors celle-ci, avec l'ajout de la création du tableau. J'ai choisi d'initialiser Controls avec un VBA.array créé en dur et qui, à cause de VBA.Array, est de base 0 quel que soit le Option Base du module. Dès lors, pour coller avec le compteur du tableau Data qui lui est de base 1 quel que soit le Option Base, je lui met 0 comme premier élement.

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, Controls
 
  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
  Controls = VBA.Array(0, "tboID", "tboFirstName", "tboLastName")
  If UpdateContact(Data, Controls) = -1 Then
    lr.Range.Value = Data
    MsgBox "Ok"
  Else
    MsgBox "NOK"
  End If
End Sub

Pour moi, je me répète

, sauf classeur avec beaucoup de userform et donc où le couplage faible a moins d'importance, je préfère cela à l'utilisation des TAG des contrôles, surtout que ceux-ci pourraient devoir être utilisés pour autre chose. On serait alors obliger d'utiliser des tag multivalués avec un split sur un séparateur, mais je pense que ça devient aussi lourd que de créer l'array avec les noms des contrôles...

en fait, c'est une question de choix d'architecture

**Pierre Fauconnier** · 07/08/2018, 21h13

Re Quaz...

Sur base des tags, et toujours sans toucher au code du userform, on pourrait imaginer une fonction dans le module standard qui reçoit le userform et le nom du tag et qui renvoie le contrôle correspondant. Evidemment, vu mon aversion pour les Exit de toute sorte, on sort proprement de la boucle dès que le contrôle est trouvé

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
Function getControlByTag(usf As MSForms.UserForm, lr As ListRow, index As Long) As MSForms.Control
  Dim Counter As Long
  Dim Found As Boolean
  Dim Tag As String
 
  Tag = lr.Parent.ListColumns(index).Name
  Counter = 0
  Do While Counter < usf.Controls.Count And Not Found
    If StrComp(usf.Controls(Counter).Tag, Tag, vbTextCompare) = 0 Then
      Set getControlByTag = usf.Controls(Counter)
      Found = True
    End If
    Counter = Counter + 1
  Loop
End Function

La fonction d'échange recevrait alors le listrow à la place de l'array et récupèrerait le contrôle à valoriser où dont il faut récupérer la valeur via la fonction précédente (on utilisera ici le nom decolonen dont tu parles dans ton message).

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
Function UpdateContact(ByRef lr As ListRow) As Long
  Dim Counter As Long
  Dim Control As MSForms.Control
 
  Unload usfContact
  With usfContact
    For Counter = 1 To lr.Range.Count
      Set Control = getControlByTag(usfContact, lr, Counter)
      If Not Control Is Nothing Then Control.Value = lr.Range(Counter).Value
    Next Counter
    .Show
    UpdateContact = .Result
    If .Result = -1 Then
      For Counter = 1 To lr.Range.Count
        Set Control = getControlByTag(usfContact, lr, Counter)
        If Not Control Is Nothing Then lr.Range(Counter).Value = Control.Value
      Next Counter
    End If
  End With
  Unload usfContact
End Function

La procédure qui pilote le tout passerait alors le listrow et plus un array.

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
Sub CreateUpdateContact()
  Dim ID As Long
  Dim Lr As ListRow
 
  If Not Intersect(ActiveCell, Range("t_Contacts")) Is Nothing Then
    Set Lr = getRowFromCell(ActiveCell)
  Else
    Set Lr = getNewRow("t_Contacts")
  End If
  If UpdateContact(Lr) = -1 Then
    MsgBox "Ok"
  Else
    MsgBox "NOK"
  End If
End Sub

On notera cependant que cela déporte l'inscription des données dans le tableau dans la fonction d'échange, puisque l'on manipule directement le listrow. Perso, je ne suis pas chaud pour cette méthode:
telle qu'écrite ici:

elle oblige à ce que chaque contrôle à valoriser soit tagué précisément comme la colonne associée (en cas de changement de nom de colonne, on doit modifier le userform);
elle oblige à ce que le contrôle possède les propriétés Value et Tag;
elle oblige peu ou prou à créer une fonction qui récupère le contrôle sur base de son tag, invalidant l'utilisation du tag pour autre chose (sauf à multivalué le tag et à récupérer ses valeurs avec un split);
elle couple fortement le userfom et Excel.

Voilà. Je préfère la solution que j'ai proposée avec l'array Data, dans la mesure où il est relativement rare d'avoir des centaines de valeurs à échanger entre le tableau et le userform...

Mais les solutions proposées ici sur base des tes remarques offrent un autre choix et ouvrent peut-être, par l'exemple, à des utilisations spécifiques et des généralisations de code qui peuvent s'avérer utiles

**Qwazerty** · 07/08/2018, 21h56

Envoyé par Pierre Fauconnier

Dans la mesure où le nom du tableau est unique dans le classeur, je préfère ne pas passer par la feuille. Si le tableau est déplacé sur une autre feuille, mon code continue à fonctionner

Ha oui jolie, ça fait en fait appelle à un nom de cellule et les tableau sont déclarés au niveau classeur... je retiens

J'illustre le cas où c'est intéressant de faire ainsi, dans la fonction getRowFromCell(rng as Range), utilisé avec Intersect pour trouver le ListRow de la cellule active lorsque cell-ci se trouve dans le tableau, avec une simple arithmétique pour trouver l'index du ListRow souhaité

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

Pratique en effet.

En effet, j'essaie de pas trop coupler le userform avec la structure du tableau, histoire d'avoir le moins possible à modifier lorsque l'un ou l'autre change. La fonction qui alimente et récupère les données du userform ainsi que le userform lui-même pourraient être utilisés dans une autre configuration (Word, un autre classeur,...) et lier le userform à un listrow verrouille son utilisation à Excel. Comme je l'ai précisé à Patrick, si la source change, j'ai plus de souplesse avec un array qu'aver le listrow. Mais je pousse peut-être le bouchon un peu loin.

Pour autant, dans le cadre d'un framework où il faudrait travailler avec plusieurs userforms liés à des tableaux structurés, on pourrait passer le listrow à la fonction qui gère le userform en se servant des tags comme tu le préconises et en récupérant le nom de la colonne de chaque ligne du listrow, avec un jeu d'échange de données entre listrow et userform qui pourrait être générique et s'appliquer à n'importe quelle paire listrow/usrform

.

Mais pour ma part, je préfèrerais probablement une autre solution qui consisterait à passer deux tableaux d'array, l'un pour les données et un autre de même structure pour les noms des contrôles du userform. Ca rendrait plus générique tout en ne liant pas aux listrow et donc à la structure du classeur.

Quelque chose du genre:

Pour moi, je me répète

, sauf classeur avec beaucoup de userform et donc où le couplage faible a moins d'importance, je préfère cela à l'utilisation des TAG des contrôles, surtout que ceux-ci pourraient devoir être utilisés pour autre chose. On serait alors obliger d'utiliser des tag multivalués avec un split sur un séparateur, mais je pense que ça devient aussi lourd que de créer l'array avec les noms des contrôles...

en fait, c'est une question de choix d'architecture

Bon prochain UserForm, je regarde plus précisement, j'ai lu les codes, je les comprends dans l'enssemble mais il faudra des mises en pratiques pour bien en comprendre les avantages/inconvéniants.

Envoyé par Pierre Fauconnier

Evidemment, vu mon aversion pour les Exit de toute sorte, on sort proprement de la boucle dès que le contrôle est trouvé

Rien que ça

Mes codes doivent te hérisser les poiles car j'utilise très souvent les Exit For, que je trouve au demeurant fort pratique (sans vouloir faire de jeu de mot). J'utilise même parfois des Goto pour en sortir !

En tout cas, merci pour ces précieux conseils

A bientôt
++
Qwaz

**patricktoulon** · 07/08/2018, 22h32

re
merci Pierre
je vais deja m'entrainer avec ca pour comprendre le fonctionnement , il est temps que je m'y mette a ces satanés tableaux
j'attend ton tuto impatiement