Discussion :

[Floyd-44]

Bonjour,
Débutant en VBA, j'ai testé de créer un premier fichier pour faire des devis, des factures etc... A force de bidouiller, plus rien ne marchait, plantage à chaque fois. Donc, j'ai trouvé un cours que j'ai lu très attentivement. Je suis ensuite reparti d'une page blanche.

Alors mon problème qui fonctionne apparemment sur d'autres classeurs (vidéo à l'appui sur YouTube):

Je suis dans USF "Tarifs":
- Je sélectionne via ComboBox1 le tarif que je souhaite modifier et remplit tous les textbox de l'USF

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
 Private Sub ComboBox1_Change() 'Sélection du tarif à modifier
Dim T As Control
Dim a As Integer, Ta As Worksheet
Set Ta = ThisWorkbook.Sheets("Tarif")
a = Ta.Range("Tarifs").Cells.Find(Me.ComboBox1, lookat:=xlWhole).Row
'Affiche les textbox, labels etc...
For Each T In Me.Controls
If TypeOf T Is MSForms.textbox Or TypeOf T Is MSForms.Label Then
T.Visible = True
End If
Next T
CommandButton2.Visible = True
CommandButton4.Visible = False
'On remplit les textbox
TextBoxID = Ta.Range("A" & a)
TextBox1 = Ta.Range("B" & a)
TextBox2 = Ta.Range("C" & a)
TextBox3 = Ta.Range("D" & a)
ComboBox1.Visible = False
TextBox1.SetFocus
Set Ta = Nothing
 
End Sub

Cette première étape fonctionne très bien

Ensuite j'effectue mes modifications dans les différents textbox et je veux les reporter dans le tableau "Tarifs" :

Code :

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

1
2
3
4
5
6
7
8
 Dim a As Integer, Ta As Worksheet
Set Ta = ThisWorkbook.Sheets("Tarif")
a = Ta.Range("Tarifs").Cells.Find(Me.ComboBox1.Text, lookat:=xlWhole).Row 'recherche rang dans tableau "Tarif"
Ta.Cells(a, 2) = TextBox1.Value
Ta.Cells(a, 3) = TextBox2.Value
Ta.Cells(a, 4) = TextBox3.Value
Set Ta = Nothing
End Sub

Et là est mon problème
seul le textbox1 change. Le 2 et 3 restent inchangés
Si j'inverse les ligne textbox1 avec textbox3 alors c'est textbox3 qui change correctement

Deux questions:
- Pourquoi?
- Un petit coup de main peut être ?

Merci d'avance

[Theze]

Bonjour,

Quand tu postes du code, postes-le entre les balises CODE (bouton dièse #), il sera plus facile à récupérer pour nous !
Quand tu fais une recherche avec la méthode Find() contrôle d'abords si elle retourne bien un objet Range :

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
 
Sub Test()
 
    Dim a As Range
    Dim Ta As Worksheet
 
    Set Ta = ThisWorkbook.Sheets("Tarif")
 
    Set a = Ta.Range("Tarifs").Cells.Find(Me.ComboBox1.Text, , xlValues, xlWhole) 'recherche rang dans tableau "Tarif"
 
    If Not a Is Nothing Then
 
        Ta.Cells(a.Row, 2) = TextBox1.Value
        Ta.Cells(a.Row, 3) = TextBox2.Value
        Ta.Cells(a.Row, 4) = TextBox3.Value
 
    End If
 
    Set Ta = Nothing
 
End Sub

[Floyd-44]

Bonjour Theze et Merci,

Je vais mettre en appli le conseil et voilà à quoi cela ressemble:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
Dim a As Range, Ta As Worksheet
Set Ta = ThisWorkbook.Sheets("Tarif")
Set a = Ta.Range("Tarifs").Cells.Find(Me.ComboBox1.Text, lookat:=xlWhole) 'recherche rang dans tableau "Tarif"
MsgBox a.Address
If Not a Is Nothing Then
Ta.Cells(a.Row, 2) = TextBox1.Value
Ta.Cells(a.Row, 3) = TextBox2.Value
Ta.Cells(a.Row, 4) = TextBox3.Value
End If
Set Ta = Nothing
Set a = Nothing

Et c'est toujours le même problème:
J'ai la bonne "address" dans le msgbox
Les données de Textbox1 vont dans la feuille
mais pas les deux autres.

J'avais essayé avec:
a as integer
cell.find(xxx).row
et
Ta.Range("C" & a) = Textbox2.value

Mais en vain

Merci du coup de main

[Pierre Fauconnier]

Salut.

Il faut travailler avec les tableaux structurés. C'est mille fois plus simple...

J'explique le va-et-vient entre tableau et userform dans cette discussion et surtout dans celle-ci (+ fichier d'exemple)

Dans le fichier d'exemple, intéresse-toi particulièrement aux fonctions ControlsToValues et ValuesToControls

Au passage, le FIND, ce n'est pas la meilleure méthode, à mon avis...

[Floyd-44]

Bonjour,

J'ai commencé à regarder hier soir et vais continuer aujourd'hui pour essayer de comprendre la gymnastique.

merci

[Pierre Fauconnier]

Au passage, nomme tes contrôles. Travailler avec TextBox1 et consorts, c'est prendre le risque de se mélanger les pinceaux à brève échéance.

Il est plus simple de savoir que tboNom contient le nom que d'essayer de se souvenir que c'est Textbox43...

Si ça tombe, c'est là que le bât blesse dans ton cas.

[Floyd-44]

Oui j'ai bien vu le principe dans l'exemple

[Floyd-44]

Salut.

Il faut travailler avec les tableaux structurés. C'est mille fois plus simple...

J'explique le va-et-vient entre tableau et userform dans cette discussion et surtout dans celle-ci (+ fichier d'exemple)

Dans le fichier d'exemple, intéresse-toi particulièrement aux fonctions ControlsToValues et ValuesToControls

Au passage, le FIND, ce n'est pas la meilleure méthode, à mon avis...

Bonjour Pierre,

J'ai bien travaillé le sujet et je pense, même s'il faudra encore pratiquer pas mal, avoir compris l'idée de la gymnastique entre les classes mais il faudra du temps pour bien l'assimiler. Mais les explications du fonctionnment de ton cours de "Tennis" sont très bien, Merci.
Tous mes tableaux étaient des tableaux structurés et nommés donc pour ça ok.
J'ai cependant plusieurs question et pour commencer sur ton fichier exemple "Tennis".
- Pourquoi le unload en début et en fin? Pourquoi le formulaire reste à l'écran alors que tu finis par Unload

Code :

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

1
2
3
4
5
6
7
8
9
10
Sub ouvreformulaire()
  Unload frm_Saisie
  With frm_Saisie
    .cbxClub.List = Range("t_Clubs[Clubs]").Value
    .cbxMute.List = Range("t_Mutations[Mutation]").Value
    .cbxSelection.List = Range("t_Joueurs[Nom & Prenom]").Value
    frm_Saisie.Show
  End With
  Unload frm_Saisie
End Sub

C'était pour l'aparté
Sinon, dans mon principe de fonctionnement:
Mes variables dans "Déclarations"

Code :

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

1
2
3
4
Option Explicit
Private T As Control
Private MyCol As Range
Private MyRow As ListRow

j'ai à l'écran 2 bouton Visibles:
- Ajout Tarif
- Modif Tarif
quand je click "Ajout" il affiche tous les textbox, renseigne l'ID unique (je suis preneur d'un commentaire sur l'ID_Unique si besoin est) et l'utillisateur remplit les champs Désignation, Unité et Prix Unitaire

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
Private Sub CmdAjoutTarif_Click()
'Affiche les textbox et labels
Dim I As Integer
  For Each T In Me.Controls
    If TypeOf T Is MSForms.TextBox Or TypeOf T Is MSForms.Label Then
    T.Visible = True
    End If
  Next T
  CmdValidQuitte.Visible = True
  CboModif.Visible = False
  CmdModifTarif.Visible = False
'J'incrémente l'ID unique
    Set MyCol = Range("Tarifs[ID]")
    I = Application.WorksheetFunction.Max(MyCol) + 1
    TxtID = I
End Sub

Quand je click "Modif", Il affiche la combobox pour sélectionner le Tarif à modifier, copie le contenu dans "TxtDesign" et je masque le combobox. (J'ai besoin d'effectuer des saisies multilignes.) J'ai alimenté la Combobox dans Userform_Initialize

Code :

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

CboModif.List = Range("Tarifs[Désignation]").Value

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
Private Sub CboModif_Change()
'Combobox Sélection du tarif à modifier
  If CboModif.ListIndex <> -1 Then
    Set MyRow = Range("Tarifs").ListObject.ListRows(CboModif.ListIndex + 1)
  End If
'Affiche les textbox, labels etc...
  For Each T In Me.Controls
    If TypeOf T Is MSForms.TextBox Or TypeOf T Is MSForms.Label Then
      T.Visible = True
    End If
  Next T
  TxtDesign.Value = CboModif.Value
  CmdAjoutTarif.Visible = False
  CboModif.Visible = False
 
'Recupérer les données du Tableau "Tarifs"
  ValuesToControls
  TxtDesign.SetFocus
End Sub

Ensuite je récupère tout avec "ValuesToControls" (j'ai gardé le même nom qui avait un sens pour moi)

Code :

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

1
2
3
4
5
6
7
8
Sub ValuesToControls()
  With MyRow
    TxtID.Value = .Range(1).Value
    TxtDesign.Value = .Range(2).Value
    TxtUnit.Value = .Range(3).Value
    TxtPUnit.Value = .Range(4).Value
  End With
End Sub

2 Points à ce sujet:
- 1 J'ai du mal à comprendre la gymnastique de ".Range(1)". Comment peut-on renseigner que l'index de Colonne alors que range demande 2 arguments?
- 2 Tu expliquais que cette méthode était "moins pro mais plus simple". Ca ressemblerait à quoi en pro?

Sinon ca marche bien mieux comme ça. Je vais pouvoir renseigner toutes mes tables dans ce principe et j'envisageais de trouver les liaisons entre tables avec "Find" Mais à priori pas idéal selon toi. Un conseil peut être à ce sujet?

Merci encore

[Pierre Fauconnier]

Salut.

Unload
Unload en début pour être sûr qu'il a été déchargé de la mémoire
Unload en fin de procédure pour être sûr de le décharger de la mémoire
Le formulaire ne reste pas affiché après Unload, ce n'est pas possible puisqu'après Unload il n'est plus en mémoire. La procédure que tu exposes est suspendue durant toute la période durant laquelle le userform est affiché. Ce n'est qu'après le Me.Hide qui est appelé sur clic sur un bouton du userform que le code repasse dans la procédure appelante OuvreFormulaire et donc, Unload est appelé après que le formulaire ait été masqué.




.Range(1)
Lorsque tu utilises un seul argument, tu parles en fait de la position d'une cellule par rapport à la cellule supérieure gauche du range considéré... (wouah, moi-même, j'ai du mal ). Un indice positif positionne en zigzag de gauche à droite et de haut en bas, en débordant éventuellement de la plage mais en restant dans les colonnes délimitées par la plage. Un indice négatif positionne de droite à gauche et de bas en haut dans une plage miroir de la plage d'origine, la cellule supérieure gauche étant l'axe de rotation, là aussi, dans la limite des colonnes de la plage qui a pivoté (wouah, même moi, j'ai du mal ).

Voici un exemple sur base d'un .range qui serait G13:I14 (en jaune, la plage d'origine et en bleu la plage résultant de la rotation, qui a pivoté autour de la cellule G13, en vert). Tu remarqueras que les indices à un chiffre font que l'on reste dans les colonnes définies par la plage d'origine (indices positifs) ou la plage qui a pivoté (indices négatifs) et qu'il n'est pas possible, avec un seul argument, de sortir des colonnes délimitées par les pointillés. Tu remarqueras également qu'il n'y a pas de correspondance évidente entre les indices à une position et à deux positions pour une même cellule.



Attention également à ne pas confondre le positionnement Range(x,y) et le décalage Range.Offset(x,y) ! Le positionnement renvoie toujours une seule cellule, le décalage renvoie une plage aux dimensions identiques à la plage source du décalage. De plus, vu que dans le premier cas on parle de position et dans le second de décalage, les mêmes indices ne renvoient pas les mêmes plages.

Range("I4:K5)")(1,1) = par rapport au coin supérieur gauche de I4:K5, soit I4, je me positionne en ligne 1 et colonne 1 (soit en I4, une seule cellule), range("I4:K5").offset(1,1) = par rapport à I4:K5, je me décale d'une ligne et d'une colonne (soit J5:L6, mêmes dimensions que l'origine I4:K5).




Méthode plus "pro"
Dans le code que j'ai donné (.range(1), on dépend de la position de la colonne dans le tableau structuré. Si tu insères ou permutes des colonnes, tu dois modifier ton code... Gênant! Il est plus pro de travailler sur les noms des colonnes. Mais le nom d'une colonne n'est pas directement accessible par le listrow, il faut remonter au parent (le listobject, cad le tableau structuré), puis redescendre que la collection des listcolumns et trouver la position de la colonne pour le nom souhaité. Ce n'est pas bien compliqué. On peut adresse une listcolumn soit par son index (sa position dans le tableau), soit par son nom. Et avec l'un, on peut retrouver l'autre. Dans le code qui suit, j'utilise le nom pour trouver la position, et donc, dans le range de ma listrow, la cellule de la position x est forcément dans la colonne qui a la position x. Avec le code qui suit, tu peux ajouter ou permuter des colonnes dans ton tableau structuré, tout continue à fonctionner sans problème et sans besoin de modification.

Code :

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

1
2
3
4
5
6
7
Function getValue(lr As ListRow, Fieldname As String) As Variant
  getValue = lr.Range(lr.Parent.ListColumns(Fieldname).Index).Value
End Function
 
Function setValue(lr As ListRow, Fieldname As String, Value As Variant)
  lr.Range(lr.Parent.ListColumns(Fieldname).Index).Value = Value
End Function

Une petite procédure de test sur base du tableau structuré Tableau1 ci-dessous.

Code :

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

1
2
3
4
5
6
7
8
 
Sub Test()
  Dim lr As ListRow
 
  Set lr = Range("tableau1").ListObject.ListRows(2)
  Debug.Print getValue(lr, "Nom")
  setValue lr, "Nom", "Pleinlesbottes"
End Sub

Si on veut encore être plus pro, on testera l'existence de la colonne dont on renseigne le nom. Voici les codes des fonctions getValue et SetValue qui utilisent columnExists... Ici, getValue et setValue renvoient Null si problème, comme la procédure de test adaptée l'illustre.

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
Function getValue(lr As ListRow, Fieldname As String) As Variant
  If ColumnExists(lr.Parent, Fieldname) Then
    getValue = lr.Range(lr.Parent.ListColumns(Fieldname).Index).Value
  Else
    getValue = Null
  End If
End Function
 
Function setValue(lr As ListRow, Fieldname As String, Value As Variant) As Variant
  If ColumnExists(lr.Parent, Fieldname) Then
    lr.Range(lr.Parent.ListColumns(Fieldname).Index).Value = Value
    setValue = -1
  Else
    setValue = Null
  End If
End Function
 
Function ColumnExists(Table As ListObject, Name As String) As Boolean
  Dim Counter As Long
 
  Counter = 1
  Do While Counter <= Table.ListColumns.Count And Not ColumnExists
    If StrComp(Table.ListColumns(Counter), Name, vbTextCompare) = 0 Then ColumnExists = True
    Counter = Counter + 1
  Loop
End Function
 
Sub Test()
  Dim lr As ListRow
  Dim Value As Variant
 
  Set lr = Range("tableau1").ListObject.ListRows(2)
  Value = getValue(lr, "sqdf")
  If IsNull(Value) Then MsgBox "La colonne n'existe pas" Else Debug.Print Value
  Value = setValue(lr, "Nsqdfom", "Pleinlesbottes")
  If IsNull(Value) Then MsgBox "La colonne n'existe pas"
End Sub

[Floyd-44]

Avec ses explication sur le .Range(1) on peut être assuré que Pierre est un "grand malade" . J'ai besoin de plusieurs années pour assimiler le concept mais en tout cas, un grand merci pour ton aide

[Pierre Fauconnier]

C'est vrai que ça semble être un truc de malade, mais c'est Microsoft qui est "malade", alors, pas moi

Pour compléter sur Range(x), il faut être attentif au fait que Range("G13:I14")(7) ne pointera pas vers la même cellule que Range("G13:L14")(7) alors que la position se calcule dans les deux cas par rapport à G13, mais dans un cas avec un zigzag sur 3 colonnes et dans l'autre un zigzag sur 6 colonnes, comme je l'explique dans mon billet sur la question...