[VBA] Ecrire/lire une série de TextBox en une seule Sub

En VBA, les TextBox ne disposent pas d'un index et dans la plupart des cas, pour entrer du texte, on procède par..

• TextBox1.Text = Cells(2,1).value
• TextBox2.Text = Cells(2,2).value
• TextBox3.Text = Cells(2,3).value

Et l'inverse pour lire les contenus
Ça peut aller s'il n'y a que quelques TextBox, mais cela devient fastidieux s'il y en a une plus grande quantité.

Préliminaires

Pour tester les codes ci-dessous, j'ai mis des données dans une feuille et 9 TextBox dans un UserForm
Un nouveau classeur..
Renommer "Feuil2" par "Base" et agencer les cellules comme l'image ci-dessous.

Entrez dans l'IDE de VBA et ajouter un UF que vous renommez "Joueur", ajouter les contrôles comme l'image ci-dessous en nommant les TextBox et le label exactement comme indiqué sur les textes.

Télécharger le classeur

Éventuellement si vous voulez avoir un classeur déjà initialisé
Le classeur de la démo sur Cjoint avant le 20/09/2008
Un classeur plus élaboré et reprenant ce principe sur Cjoint avant le 20/09/2008
Sinon, vous pouvez me contacter par MP je le remettrai sur CJoint.

Dans le module de la feuille "Base"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim N As Long, Fin As Long
Dim TB
    TB = Split(Target.Address, ":")
    If UBound(TB) > 0 Then Exit Sub 'quand sélectionne un bloc, sinon bug
    Fin = Range("A65536").End(xlUp).Row

    If Target.Column < 11 And Target.Row  <= Fin And Target.Row > 2 Then
        Joueur.Show 1
    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

'Retransmet 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

Il y a assez bien de 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

Fin Page