Pour tester les codes ci-dessous, j'ai mis des données dans une feuille et 9 TextBox dans un UserForm
Un nouveau classeur..
Renommez "Feuil2" par "Base" et agencez les cellules comme l'image ci-dessous.
Entrez dans l'IDE de VBA et ajoutez un UF que vous renommez "Joueur", ajoutez les contrôles comme l'image ci-dessous en nommant les TextBox et le label exactement comme indiqué sur les textes.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Fin As Long
If Target.Count = 1 Then
Fin = Range("A" & Rows.Count).End(xlUp).Row
If Target.Column < 11 And Target.Row <= Fin And Target.Row > 1 Then
Joueur.Show 1
End If
End If
End Sub
Dans le module de l'UF
Option Explicit
Dim Lig As Long
Private Sub UserForm_Initialize()
Lig = ActiveCell.Row
RemplirFiche
End Sub
'Retransmettez les données sur la feuille Base, il est possible
'aussi de contrôler si c'est nécessaire.
Private Sub OK_Click()
Dim Cont As Control
Dim N As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Base").Select
Cells(Lig, 1) = Label1.Caption
'-------------------------------------------------------------
'C'est la routine "écrire" de cette astuce
For Each Cont In Me.Controls
If TypeOf Cont Is MSForms.TextBox Then
N = Val(Right(Cont.Name, 2))
Cells(Lig, N) = Trim(Cont.Object.Text)
End If
Next Cont
'-------------------------------------------------------------
Application.EnableEvents = True
Application.ScreenUpdating = True
Unload Me
End Sub
Sub RemplirFiche()
Dim Cont As Control
Dim N As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Base").Select
Label1.Caption = Cells(Lig, 1).Value
'-------------------------------------------------------------
'C'est la routine "Lire" de cette astuce
For Each Cont In Me.Controls
If TypeOf Cont Is MSForms.TextBox Then
N = Val(Right(Cont.Name, 2))
Cont.Object.Text = Cells(Lig, N)
End If
Next Cont
'-------------------------------------------------------------
Me.Caption = "Fiche de " & Text02.Text & " " & Text03.Text
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Variante pour contrôles dans une feuille de calcul
Private Sub CommandButton21_Click()
Dim Obj As OLEObject
'boucle sur les objets de la Feuil1
For Each Obj In Sheets("Feuil1").OLEObjects
'verifie s'il s'agit d'un CheckBox
If TypeOf Obj.Object Is MSForms.CheckBox Then
If Obj.Object.Value = True Then
'Exemple d'action si le checkbox est coché.
N = Val(Right(Obj.Name, 3)) + Lig1
Rows(N).Hidden = True
End If
End If
Next Obj
End Sub
Il y a plusieurs lignes de code mais en fait il n'y a que 6 lignes pour lire les données, et 6 lignes pour écrire les données.
Le reste c'est pour faire fonctionner la démo.
Conclusion
L'astuce consistait simplement à :
Trouver le moyen de déterminer le nom du Textbox
Pouvoir lire/écrire sur ces Textbox, il fallait trouver le code pour changer le texte d'un Objet.
En faisant coïncider les 2 derniers caractères du nom du TextBox avec l'adresse de son lien, le tour est joué.
Le résultat serait identique avec un tableau, il suffirait simplement de modifier les sub en conséquence
en nommant les derniers caractères des TextBox avec le même indice que les index du tableau.
Si les indices devaient être plus grands que 99, il faudrait modifier les lignes N = Val(Right(Cont.Name, 2)) par N = Val(Right(Cont.Name, 3)) pour aller jusqu'à 999
Complément
Cette démo montre comment lire et écrire sur une série de TextBox mais la multiplication des contrôles d'événement tel que TextBox_Click, TextBox_Change, TextBox_MouseUp etc.. nécessite encore de multiplier les lignes de code. Pour travailler sur le même principe, vous pouvez vous inspirer de l'astuce VBA et les collections d'objets
Cet article est régulièrement mis à jour par des experts sous la direction de Jean-François Pillou, fondateur de CommentCaMarche et directeur délégué au développement numérique du groupe Figaro.
