[XL-2010] Appliquer à une Textbox une formule d'une cellule [Résolu]

[Jerome_Hej]

Bonjour,

J'ai une petite problème que je n'arrive à résoudre mais je vais d'abord présenter le projet :

J'ai deux fichiers Excel :
- "Bdd.xls" contenant les données dans une feuille. La première ligne contient les champs des données. Chaque ligne suivante est une fiche avec les données.
- "Formulaire.xls" contenant un Userform, le programme VBA et une feuille "parametres" qui contient aussi des champs (les mêmes que la Bdd normalement) mais avec des informations de paramétrage comme par exemple
* le contenu des listes déroulantes, (de la ligne 6 à 24)
* le texte à afficher dans une bulle d'aide qui explique comment remplir le champs, (ligne 26)
* la formule de calcul (ligne 25)

L'Userform contient des Textbox et des Listbox lié à la Bdd via le ControlSource. Les Listbox sont alimentées via un code VBA qui reprend le contenu des listes dans la feuille Parametres.
En fait, les Textbox et Listbox ont le même nom que les champs.

Parmi les Textbox, certaines sont des chiffres à saisir. Par exemple : Chiffre1 et Chiffre2

Parmi les Textbox, j'ai aussi certaines dans lesquelles je souhaite afficher le total d'autres Textbox via la formule indiquée dans la feuille Parametres du champs correspondant. Par exemple la Textbox "Total" serait égale à Chiffre1 + Chiffre2.

Pour cela :
- via un code VBA, j'ai donné un nom à chaque colonne de la BDD (le nom de la colonne = le nom du champs)
- j'ai créé dans la Bdd les champs de totaux et via un code VBA les colonnes de ces champs se remplissent avec les formules indiquées dans la feuille Parametre. En effet, à la base le Bdd ne contient pas les formules. Les formules reprennent les nom de colonne pour pouvoir additionner. Jusque là çà va, les totaux se font correctement.

Le hic est lorsque j'ai lié les Textbox de totaux à ces champs totaux de la bdd via le ControlSource des Textbox. En effet, ce lien efface les formules des cellules liées par le résultat en dur. Donc forcément lorsque je change un chiffre, le total ne se met pas à jour puisqu'il n'y a plus de formule dans la cellule liée au Textbox. Une chose que j'ai compris puisqu'il y a interactivité dans les deux sens entre une Textbox et sa cellule.

Mais donc comment faire pour que mes Textbox de totaux fassent bien les totaux selon les formules indiquées dans la feuille Paramètres ? Je pourrais mettre en dure dans le code VBA chaque formule (du genre TextboxTotaux.value = TextboxChiffre1 + TextboxChiffre2) mais je veux éviter cela pour pouvoir ajouter des champs facilement via la feuille Parametres et donc sans retourner dans le VBA à chaque fois. De plus, mettre les formules en dures dans VBA c'est faire autant de ligne de code qu'il y a de textbox servant à un résultat d'un total.

J'avais essayé aussi de mettre TextboxChiffre1 + TextboxChiffre2 dans les options de la TextboxTotaux mais çà ne fonctionne pas.

Je ne sais plus comment faire, je bloque. Auriez vous une piste s'il vous plait ?

Bien à vous.

[jfontaine]

Bonjour,

Tu devrait pourvoir faire cela avec l'instruction Evaluate

Code :

TextBox3.Text = Evaluate(TextBox1.Text & "+" & TextBox2.Text)

Le "+" pouvant être une variable correspondant à l'opération choisi par l'utilisateur

[Jerome_Hej]

Bonjour Jfontaine,

Peux tu me dire STP où je dois écrire cette formule ?

En effet, j'ai parlé à la fois du ControlSource des Textbox, de formule dans les cellules d'une feuille et de code à mettre dans chaque Textbox objet d'un calcul

A bientôt

[gFZT82]

Bonjour,

Voici une solution qui utilise l’événement Worksheet.Change.

Feuille "Paramètres"
Les 2 champs de données de la feuille "Paramètres" sont liées à la feuille "Bdd".
Le Total est calculé à partir de la formule contenue dans le champ "Formule".

Code :

1
2
3
4
5
6
7
8
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MaPlage As Range
    Set MaPlage = Application.Intersect(Target, Application.Union(Range("A2:B2"), Range("D2")))
    If Not MaPlage Is Nothing Then
        'La valeur du total est copiée dans l'userform
        UserForm1.TextBox3.Value = Range("C2")
    End If
End Sub

Feuille " Bdd "
Le Total est lié à la feuille "Paramètres".

Userform1
Les 2 TextBox sont liées aux cellules de la feuille "Paramètres" par le ControlSource.
A l’ouverture du formulaire, la formule de la feuille "Paramètres" est copiée dans la cellule Total.
Le total est évalué.
Lors de la modification d’une des 2 données dans le formulaire (TextBox1 ou TextBox2), la cellule correspondante de la feuille "Paramètres" est automatiquement modifiée. Ce changement provoque l’événement Worksheet.Change. La procédure associée à l’événement permet de copier le Total dans le formulaire.

Code :

1
2
3
4
5
6
7
8
9
Private Sub UserForm_Initialize()
    TextBox1.ControlSource = "A2"
    TextBox2.ControlSource = "B2"
    With Worksheets("parametres")
        .Range("C2").Formula = .Range("D2")
        .Range("C2").Value = .Range("C2").Value
    End With
    TextBox3.Value = Range("C2")
End Sub

Tu dois pouvoir adapter cela à ton projet .
Cordialement.

[jfontaine]

Ce code est a mettre dans la CommandButton qui exécutera l'opération

[Jerome_Hej]

Merci gFZT82 et JFontaine

La solution de gFZT82 correspond plus à la finalisation que je souhaite.

C'est excellent comment gFZT82, tu as réussi à contourner la contrainte (ou le manque) de VBA. Je vais voir comment d'adapter à mon projet et voir aussi le fait que dans ton programme les champs 1 et 2 de la bdd ne se mettent pas à jour.

Je reviendrais pour vous tenir au courant, et, si çà fonctionne je mettrais "Résolu".

[gFZT82]

Oups !

Effectivement, pour assurer la mise à jour des données de la feuille "Bdd", il a fallu que je modifie les procédures :

Feuille "parametres"

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MaPlage As Range
    Set MaPlage = Application.Intersect(Target, Range("C2:D2"))
    If Not MaPlage Is Nothing Then
        'La valeur du total est copiée dans l'userform
        UserForm1.TextBox3.Value = Range("C2")
        'Les données de la feuille "Bdd" sont mises à jour
        With Worksheets("Bdd")
            .Range("A2") = Range("A2")
            .Range("B2") = Range("B2")
        End With
    End If
End Sub

Userform1

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Private Sub TextBox1_Change()
With Worksheets("Bdd")
    .Range("A2") = TextBox1.Value
    TextBox3.Value = .Range("C2")
End With
End Sub
Private Sub TextBox2_Change()
With Worksheets("Bdd")
    .Range("B2") = TextBox2.Value
    TextBox3.Value = .Range("C2")
End With
End Sub
Private Sub UserForm_Initialize()
    With Worksheets("parametres")
        TextBox1.Value = .Range("A2")
        TextBox2.Value = .Range("B2")
        .Range("C2").Formula = .Range("D2")
        .Range("C2").Value = .Range("C2").Value
        TextBox3.Value = .Range("C2")
    End With
End Sub

A toi le plaisir de tester .

Cordialement.

[Jerome_Hej]

Merci gFZT82 pour ta réponse rapide.
Je fais ce projet en plus d'autres choses dans mon boulot mais çà va me permettre de progresser dès que possible.
Je vous tiens au courant du résultat.

Bonjour,

J'essaye de reprendre ce que tu m'as donné et d'adapter tout çà à mon projet. Je me confronte à un problème. Voici le code :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
For ParamChampsVar = 2 To ParamChampsMaxi ' Boucle sur les champs de paramètres
    For BddChampsVar = 1 To BddChampsMaxi ' Boucle sur les champs de la Bdd
 
        ' Si le champs des Paramètres existe dans la Bdd
        If Workbooks(WBbdd).Sheets(WSbdd).Cells(1, BddChampsVar).Value = FeuilleParam.Cells(1, ParamChampsVar).Value Then
 
             ' Alors la valeur de la Bdd de la ligne sélectionné est égale à la ligne "Valeur en cours"
                  Workbooks(WBbdd).Sheets(WSbdd).Cells(IdentifNomSite_Choix.ListIndex + 2, BddChampsVar).Value = FeuilleParam.Cells(ParamValeurEnCoursNoLigne, ParamChampsVar).Value
 
 
         End If
 
    Next BddChampsVar
Next ParamChampsVar

En fait, je ne souhaite pas qu'il y ai de formule dans la Feuille Bdd. Donc j'ai décidé qu'à chaque fois que l'utilisateur choisie la fiche (une fiche par ligne de la Bdd) dans la liste déroulante "IdentifNomSite_Choix" de l'Userform alors les données de la bdd sont copiées dans la feuille "parametres" dans les champs correspondant. çà çà fonctionne alors que c'est presque le même code que ci-dessus.

Les ListBox et TextBox ont un controlsource pointé sur la ligne "donnée en cours" de la feuille Paramètres. Donc quand je mets en jour un TextBox de l'Userform, on retrouve bien la donnée dans la feuille Paramètre. Quand j'ai fini toutes les modif de données, le code ci-dessus est censé cette fois-ci copier les données de la feuille Paramètres vers la feuille Bdd à la ligne de la fiche correspondante.

Cependant, il ne copie pas, c'est à dire qu'il ne remplace pas les anciennes valeur de la Bdd par les nouvelles de la feuille Paramètres. Au contraire, les nouvelles de la feuille Parametres sont remplacées par les anciennes.

Pourtant je fais bien "Cellule de la Bdd = Cellule de la Parametres". Je ne comprends pas.

[gFZT82]

Bonjour,

D’emblée, ce qui me semble curieux ce sont tes 2 boucles.
Après modification des données de ta feuille « Parametres», tu ne devrais boucler que sur les données de cette feuille.
De plus, pour éviter les problèmes liés aux interactions entre TextBox et cellules, j’avais supprimé l’utilisation du controlsource.

Cordialement.

[Jerome_Hej]

Ben, je veux éviter de mettre en dur Textbox1 = ....
Sinon si j'ai 120 Textbox, faudra 120 fois Textbox =

Du coup, les boucles servent à prendre les champs un par an dans la feuille parametre et de comparait le nom de ces champs par ceux, un par an, de la feuille bdd. S'il trouve un champs ayant le même nom alors il fait la copie.

Si les champs de la bdd était exactement dans le même ordre que ceux de Parametre, la 2ième boucle ne servirait pas mais pour éviter toutes erreurs ultérieur (un champs ajoutés à la bdd mais pas à la Parametre) j'ai préféré faire la vérification à chaque fois. Pour info, le classeur contenant la feuille Bdd est une extraction d'une base Access.

Ici le ControleSource rempli bien les champs de la Parametre. Mais c'est la copie vers la Bdd qui ne fonctionne pas.

[gFZT82]

Si j’ai bien compris, quand tu écris :

Code :

If Workbooks(WBbdd).Sheets(WSbdd).Cells(1, BddChampsVar).Value = FeuilleParam.Cells(1, ParamChampsVar).Value Then

tu compares des valeurs qui sont des noms de champs.

La structure de ta boucle est donc correcte. Il te reste à voir du coté du ControleSource ou l’affectation de tes variables WSbdd et FeuilleParam.

[Jerome_Hej]

C'est çà qui me chiffonne

Le ControlSource met bien à jour la ligne "Valeur en cours" de la Parametre. Vérif faite en désactivant le code qui ne fonctionne pas. Dans ce cas, j'ai fait des modif de saisie dans l'userform puis je suis allé sur le Feuille Parametres et les modif y étaient bien.

Concernant les variables, je pense que çà fonctionne puisqu'à l'ouverture de l'Userform, le code fait la même opération mais à l'envers : copie des données de la bdd vers la Parametre.

Voici le code du transfert des données de la Bdd vers la Parametre :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
Private Sub IdentifNomSite_Choix_Change()
 
    ' *** Copier les données de la ligne sélectionnée de la Bdd dans la ligne "Valeur en cours" des paramètres ***
 
    For ParamChampsVar = 2 To ParamChampsMaxi ' Boucle sur les champs de paramètres
        For BddChampsVar = 1 To BddChampsMaxi ' Boucle sur les champs de la Bdd
            ' Si le champs des Paramètres existe dans la Bdd
            If Workbooks(WBbdd).Sheets(WSbdd).Cells(1, BddChampsVar).Value = FeuilleParam.Cells(1, ParamChampsVar).Value Then
                ' Alors la ligne "Valeur en cours" est égale à la valeur de la Bdd de la ligne sélectionné
                FeuilleParam.Cells(ParamValeurEnCoursNoLigne, ParamChampsVar).Value _
                = Workbooks(WBbdd).Sheets(WSbdd).Cells(IdentifNomSite_Choix.ListIndex + 2, BddChampsVar).Value
            End If
        Next BddChampsVar
    Next ParamChampsVar
 
End Sub

Et celui du transfert des données de la Parametre vers la Bdd :

Code :

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 MultiPage1_Change()
 
    ' Si l'onglet sélectionné est la premier onglet
    If Me.MultiPage1.SelectedItem.Index = 0 Then
 
        ' Alors
        ' *** Copier les données de la ligne sélectionnée de la Bdd dans la ligne "Valeur en cours" des paramètres ***
 
        For ParamChampsVar = 2 To ParamChampsMaxi ' Boucle sur les champs de paramètres
            For BddChampsVar = 1 To BddChampsMaxi ' Boucle sur les champs de la Bdd
                ' Si le champs des Paramètres existe dans la Bdd
                If Workbooks(WBbdd).Sheets(WSbdd).Cells(1, BddChampsVar).Value = FeuilleParam.Cells(1, ParamChampsVar).Value Then
                    ' Alors la valeur de la Bdd de la ligne sélectionné est égale à la ligne "Valeur en cours"
                    Workbooks(WBbdd).Sheets(WSbdd).Cells(IdentifNomSite_Choix.ListIndex + 2, BddChampsVar).Value _
                    = FeuilleParam.Cells(ParamValeurEnCoursNoLigne, ParamChampsVar).Value
                End If
            Next BddChampsVar
        Next ParamChampsVar
    End If
 
End Sub

Vous remarquerez qu'à partir de "For ParamChampsVar = 2 To ..." c'est exactement le même code sur les deux sauf pour le sens de la copie. Et le premier code donne l'action voulu mais pas le deuxième code

[Jerome_Hej]

J'ai ENFIN trouvé

Voici ce que j'ai fait :

- Nous avons la feuille "Bdd" :
> qui contient toutes les colonnes de données à modifier
> aussi les colonnes des totaux à recalculer selon les données modifiées. Les colonnes Totaux contiennent les formules de calculs.
> chaque colonne a pour nom le nom du champs correspondant (le nom du champs est mentionné sur la première ligne de la feuille)

- Tous les TextBox et ListBox pointent sur la feuille "Bdd" via un code qui défini le Controlsource selon ce qu'on a choisi dans une liste déroulante des fiches (une fiche par ligne de la Bdd). Sauf pour les TextBox qui servent de totaux. Cela évite que le ControlSource écrase la formule de calcul par le résultat de la formule.

- Afin que les TextBox totaux se mettent à jour en direct, le code ci-dessous fait le tour de l'ensemble des contrôles et pour ceux qui servent de totaux il est dit que leur valeur est égale à la valeur contenu dans la cellule correspondant de la Bdd. Ce code s'active à chaque changement de tous les contrôles de l'Userform.

Code :

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
 
Sub FaireLesTotaux()
'*******************************************************************************************
'Faire les totaux de tous les champs totaux
'*******************************************************************************************
 
For ParamChampsVar = 2 To ParamChampsMaxi 'Boucle sur les champs des Parametres
    ' Si le champs contient une formule
    If FeuilleParam.Cells(ParamFormuleNoLigne, ParamChampsVar).Value <> "" Then
        ' Alors Chercher le controle correspondant
        For Each Ctrl In Me.Controls 'Boucle sur les contrôles du formulaire
            'Si le controle a le même nom que le champs
            If Ctrl.Name = FeuilleParam.Cells(1, ParamChampsVar).Value Then
                'Alors Chercher le champs correspondant dans la Bdd
                For BddChampsVar = 1 To BddChampsMaxi
                    'Si La Bdd a un champs du même nom
                    If FeuilleParam.Cells(1, ParamChampsVar).Value = Workbooks(WBbdd).Sheets(WSbdd).Cells(1, BddChampsVar).Value Then
                    'Alors le Contrôle = le champs de la Bdd correspondant
                    Me.Controls(FeuilleParam.Cells(1, ParamChampsVar).Value).Value _
                    = Workbooks(WBbdd).Sheets(WSbdd).Cells(IdentifNomSite_Choix.ListIndex + 2, BddChampsVar).Value
                    'Sort de la boucle
                    Exit For
                    End If
                Next BddChampsVar
                'Sort de la boucle
                Exit For
            End If
        Next Ctrl
    End If
Next ParamChampsVar
 
End Sub

Je remercie les intervenants et tout particulièrement gFZT82 pour le temps passé à essayer de me trouver une solution correspondant à mes besoins assez particuliers et complexes.

Bien à vous.