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..
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.

Télécharger le classeur

Éventuellement si vous voulez avoir un classeur déjà initialisé (Modifié le 6/07/2009)
Le classeur de la démo sur ((lien mort)). Un lien Cjoint sera disponible dans quelques jours.
Il ne correspond plus exactement à la démo mais le principe est le même, sauf qu'il est un peu optimisé et qu'il contient également un module de classe pour gérer les événements des textBox.

Dans le module de la feuille "Base"

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
Dim N As Long, Fin As Long 
    If Target.Count > 1 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 

'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 Ce document intitulé « VBA - Ecrire/lire une série de TextBox en une seule Sub » issu de CommentCaMarche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.