Langage VBA, vlookup et conditions

Fermé

Ofeu Messages postés 26 Date d'inscription mardi 21 août 2018 Statut Membre Dernière intervention 11 septembre 2018 - 23 août 2018 à 13:34
pilas31 Messages postés 1825 Date d'inscription vendredi 5 septembre 2008 Statut Contributeur Dernière intervention 24 avril 2020 - 11 sept. 2018 à 10:10

Bonjour à tous,

J’ai quelques problèmes avec mon formulaire et j’ai besoin de votre aide précieuse, je vous exlique :
Sur mon fichier Excel, sur ma feuil « Model », j’ai mon bouton pour mon formulaire Useform.
Sur mon formulaire, l’utilisateur doit remplir seulement les cases (combobox et textebox) en blanc.
Mes deux comBobox correspond à une liste déroulante en cascade. Après le choix de ces deux combobox, j’ai fait une concaténation de ces deux combobox (combobox2 & combobox1) dans la textbox18. La textbox18 est très importante car elle me permettra de faire une VLOOKUP sur le reste des textBox (sauf pour la textbox1).

Jusqu’à là, aucun souci !

Pour ma textBox1. En effet, j'ai appliqué une limite acceptable entre 10 et 25, et si j'applique d'autre valeurs < à 10 ou supérieur à 25, j'affiche la msg "Entry a value between 10 and 25 kg/day".

Mon problème : si l'utilisateur mets 9, il accepte. Cependant, Il ne va pas accepter si je mets 09 (c'est à dire si je saisis les 2 numéros, logique !). Idem pour 2 (il faut mettre 02, pour que je reçois une msg "Entry a value between 10 and 25 kg/day").

Vous savez comment je peux faire si j'ajoute la valeur 2 par exemple et il envoie la message "Entry a value between 10 and 25 kg/day" seulement quand je sors de la cellule ?

2ème problème, plus complexe :

Cela concerne les textbox 6, 7, 8 et 9.
Je veux appliquer une limite acceptable qui est fonction textbox18 (issu de la concatenation du combobox 1 et combobox2). Cette limite est différent pour chaque fois que textBox cjange et elles se trouvent dans la feuil Ranges_1 , entre les cellules A3:M34. Ma colonne A correspond au textbox18, et donc cela change lorsque je modifie mes 2 combobox.

Sur Excel, ça marche bien quand je mets une condition entre min et max dans "validation de donnée"
=RECHERCHEV($D$6;Ranges_1!$A$3:$M$34;3;FAUX), ici la valeur min est situé dans la colonne 3
=RECHERCHEV($D$6;Ranges_1!$A$3:$M$34;4;FAUX), ici la valeur max est situé dans la colonne 4

Je dois procéder pour 4 Textbox (6, 7, 8 et 9) :
-Pour la Textbox6, le minimum acceptable est situé dans la colonne 3 et le maximum dans la colonne 4 (en fonction du textBox18 préalablement choisis)
-Textbox7, le minimum acceptable est situé dans la colonne 6 et le maximum dans la colonne 7.
-Textbox8, le minimum acceptable est situé dans la colonne 9 et le maximum dans la colonne 10.
-Textbox9, le minimum acceptable est situé dans la colonne 12 et le maximum dans la colonne 13.

Le problème avec un exemple, si je choisis pour :
Combobox1 « _C160 »
Combobox2 « Seed/Oil/Fat »
Automatiquement j’ai dans ma TextBox18 : « Seed/Oil/Fat_C160 »
Ensuite je dois saisir des valeurs dans les TextBox6 et TextBox7 selon les limites minimum et maximum.

Pour la textBox6 dans ce cas, la limite est entre 0.07 et 2.80 (je démontre cette valeur dans le formulaire, dans variations). Ainsi, si je mets la valeur « 2 » dans la textBox6, aucun problème car elle est dans la limite acceptable.
Pour la textBox7, la limite est entre 3.70 et 24.8. Ainsi, si je mets la valeur « 4 » dans la textBox7, aucun problème car elle est dans la limite acceptable. MAIS si je veux mettre 15 ou 23 par exemple, une valeur que commence avec 1- ou 3-, directement il refuse.

J’ai un 3 question (mais je dois encore travailler dessus), est-il possible dans mon Bouton « Verify » mettre une commande afin de vérifier si toutes les cellules ont été saisie ?
Je vous mets si joint un fichier pour montrer mon codage.

J’ai déjà bcp appris en deux semaines le langage VBA avec vous, Vous êtes génial !!!!
Merci beaucoup de votre aide.

Ci- joint le fichier

https://www.cjoint.com/c/HHxlHYoNbAg

A voir également:

Langage VBA, vlookup et conditions
Mise en forme conditionnelle excel plusieurs conditions - Guide
Incompatibilité de type vba ✓ - Forum Programmation
Vba attendre 1 seconde ✓ - Forum VB / VBA
Google sheet mise en forme conditionnelle 2 conditions ✓ - Forum Bureautique
Vba dernière colonne non vide ✓ - Forum VB / VBA

4 réponses

Réponse 1 / 4

pilas31 Messages postés 1825 Date d'inscription vendredi 5 septembre 2008 Statut Contributeur Dernière intervention 24 avril 2020 643
Modifié le 23 août 2018 à 16:04

Bonjour,

En fait il faut tenir compte des deux remarques de ta discussion précédente.
Eric à raison (salut à lui) il faut utiliser la fonction cdbl() pour comparer des numériques. Sinon tu compares des chaines de caractères.
Et pour ma part j'insiste, il faut utiliser les AfterUpdate() car avec Change(), à chaque caractère que tu tapes, la fonction se déclenche. Il ne faut effectuer les tests qu'aprés validation du champ complet.

Donc celà va résoudre tes deux problèmes.
Je te donne ci après les fonctions à changer :

pour ton premier problème :

Private Sub TextBox1_AfterUpdate()
TextBox1.MaxLength = 2
'If Len(TextBox1.Text) < 2 Then Exit Sub
If Not IsNumeric(TextBox1.Value) And TextBox1 <> "" Then
    MsgBox "Entry a number"
    TextBox1 = ""
ElseIf TextBox1.Value < 10 Or TextBox1.Value > 25 And TextBox1 <> "" Then
    MsgBox "Entry a value between 10 and 25 kg/day"
    TextBox1 = ""
End If
    
    Sheets("Model_FA").Range("B13").Value = TextBox1.Value
    
End Sub

Pour ton deuxième problème :

Private Sub TextBox6_AfterUpdate()
Dim myRange3 As Range
Set myRange3 = Worksheets("Ranges_1").Range("A3:M34")
    If Not IsNumeric(TextBox6.Value) And TextBox6 <> "" Then
          MsgBox "Entry a number"
          TextBox6 = ""
    ElseIf CDbl(TextBox6.Text) < CDbl(WorksheetFunction.VLookup(TextBox18.Text, myRange3, 3, False)) Or CDbl(TextBox6.Text) > CDbl(WorksheetFunction.VLookup(TextBox18.Text, myRange3, 4, False)) And TextBox6 <> "" Then
          MsgBox "See ranges for this fatty acids"
          TextBox6 = ""
          
    TextBox6.MaxLength = 5
    If Len(TextBox6.Text) < 5 Then Exit Sub
   
   Else:

   End If
   
    Sheets("Model_FA").Range("D15").Value = TextBox6.Value
    Sheets("Model").Range("E17").Value = TextBox6.Value
    
   End Sub

Private Sub TextBox7_AfterUpdate()
Dim myRange4 As Range
Set myRange4 = Worksheets("Ranges_1").Range("A3:M34")
    MsgBox WorksheetFunction.VLookup(TextBox18.Text, myRange4, 6, False)
    MsgBox WorksheetFunction.VLookup(TextBox18.Text, myRange4, 7, False)
    MsgBox TextBox7.Text
    If Not IsNumeric(TextBox7.Value) And TextBox7 <> "" Then
            MsgBox "Entry a number"
            TextBox7 = ""
    
                        
    ElseIf CDbl(TextBox7.Text) < CDbl(WorksheetFunction.VLookup(TextBox18.Text, myRange4, 6, False)) Or CDbl(TextBox7.Text) > CDbl(WorksheetFunction.VLookup(TextBox18.Text, myRange4, 7, False)) And TextBox7 <> "" Then
            MsgBox "See ranges for this fatty acids"
            TextBox7 = ""
            
    TextBox7.MaxLength = 5
    If Len(TextBox7.Text) < 5 Then Exit Sub
            
   Else:
    End If
  
    Sheets("Model_FA").Range("D16").Value = TextBox7.Value
    Sheets("Model").Range("E18").Value = TextBox7.Value
End Sub

ça fonctionne chez moi.
A tester

Ofeu Messages postés 26 Date d'inscription mardi 21 août 2018 Statut Membre Dernière intervention 11 septembre 2018
23 août 2018 à 16:57

Bonjour Pilas31, Eric

D'abord merci pour la rapidité de la réponse. de plus, CEla marche très bien pour les deux cas !
C'est vraiment génial, super content, vraiment MERCIIII

Je regardais les forums jusqu'à 5h du matin afin de trouver une solution pour chaque commande (liste deroulante en cascade, Vlookup...) et j'osais pas me lancer à vous poser les questions !

Je pense que j'aurais bientôt d'autres questions ;-), mais je vais essayer de voir si cela a été déjà discuté dans d'autres forums.

MERCIII

Réponse 2 / 4

Ofeu Messages postés 26 Date d'inscription mardi 21 août 2018 Statut Membre Dernière intervention 11 septembre 2018
23 août 2018 à 17:22

VOILA un nouveau obstacle, mais c'est plutôt pour le visuel et la praticité :

Si je choisi pour la combobox1 « _C180 » et pour la Combobox2 « Seed/Oil/Fat »
J’ai dans des données indiquées dans mes textBox (2,3,4 et 5). Ainsi, je pourrais saisir dans les textBox (6,7,8 et 9), la quantité pour chaque variable.

Cependant, si je choisi pour la combobox1 « _C160 » et pour la Combobox2 « Seed/Oil/Fat »

Dans ma colonne « Entrée » dans le formulaire il y a :
« totalC161int » dans la textBox2 et
« C160int » dans la textBox3.
Ainsi la textBox6 et textBox7 (en blanc) sont disponible pour la saisir des données. Mais la TextBox8 et textBox9 sont aussi disponible.

Je voudrais savoir si c’est possible dans ce cas, mettre la textBox8 invisible et/ou bloquée à la saisir (lorsqu’il n’y a aucune information dans la textBox3). Idem pour la textBox9 en fonction de la textBox4.

Merci les Genius !

Lucas

Réponse 3 / 4

pilas31 Messages postés 1825 Date d'inscription vendredi 5 septembre 2008 Statut Contributeur Dernière intervention 24 avril 2020 643
24 août 2018 à 11:05

Bonjour,

Oui c'est possible. A la validation de la combobox2 il faut jouer sur la visiblilité des TextBox 6 à 9 en fonction du contenu des TextBox 2 à 5

Voila une proposition d'écriture synthétique :

Private Sub ComboBox2_Change()
TextBox18 = ComboBox2 & ComboBox1
Sheets("Model_FA").Range("B10").Value = ComboBox2.Value

TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
TextBox9.Value = ""

TextBox6.Visible = Not (TextBox2.Value = "")
TextBox7.Visible = Not (TextBox3.Value = "")
TextBox8.Visible = Not (TextBox4.Value = "")
TextBox9.Visible = Not (TextBox5.Value = "")

End Sub

Ofeu Messages postés 26 Date d'inscription mardi 21 août 2018 Statut Membre Dernière intervention 11 septembre 2018
24 août 2018 à 14:15

Bonjour Pilas31,

Vous m'avez encore sauvé !
J'avais regardé des forums et regardé des autres, mais cette option "visible" était plutôt demandé lorsqu'une checkbox était coché !

ça prends vraiment de la forme le formulaire !

Il va falloir vous ajouter dans mes remerciments ;-)

Lucas

Ofeu Messages postés 26 Date d'inscription mardi 21 août 2018 Statut Membre Dernière intervention 11 septembre 2018
24 août 2018 à 21:20

Bonjour,

Comme vous pouvez voir, ça prends vraiment de la forme.

J’ai encore quelques obstacles que j’ai essayé d’appliquer sur les commandes, mets j’ai tjrs de débogage.

Voici le fichier :
https://www.cjoint.com/c/HHyttDOtEsh

1. Je voudrais dans mon fichier excel (feuil « Model »), lorsque je click sur « GO » j’ouvre le formulaire et que automatiquement les cellules (D17 :G20) et (G11 :H13) soient vidés. J’ai bien ma commande pour ouvrir le formulaire avec le bouton « GO », mais avec différents essaies je n’ai pas réussi effacer ces cellules de la feuil « Model »

2. Dans mon formulaire, j’ai ajouté un bouton « REFRESH ». Avec ce bouton, j’ai reussi effacé les cellules (D17 :G20) et (G11 :H13) de la feuil « Model ». Mais je voudrais aussi vider les combobox (1 et 2), ainsi que le TextBox (6,7,8 et 9). Mais avec les commandes que j’ai essayé de faire, ça ne marche pas…

3. Je ne sais pas si c’est possible mais, pourrais-je faire apparaître les commandes suivantes qu’à partir du moment que j’ai rempli les combobox1 et Combobox2 (lié à la textBox18).
-Labels (4 à 12)
-TextBox (2 à 17)
-CommandButton (3,4 et 5)

Je vous remercie déjà de votre énorme aide !

Réponse 4 / 4

pilas31 Messages postés 1825 Date d'inscription vendredi 5 septembre 2008 Statut Contributeur Dernière intervention 24 avril 2020 643
28 août 2018 à 13:54

Bonjour,

pour les dernières questions, voici mes suggestions :

pb 1 :

Private Sub UserForm_Activate()
    ComboBox1.ControlTipText = "Choose the milk fatty acid to predict"
    ComboBox2.ControlTipText = "Choose the database ???"
    TextBox1.ControlTipText = "Insert the dry matter intake, between 10 and 25 kg/day "
    TextBox2.ControlTipText = "variable X1"
    TextBox3.ControlTipText = "variable X2"
    TextBox4.ControlTipText = "variable X3"
    TextBox5.ControlTipText = "variable X4"
    CommandButton3.ControlTipText = "Cancel the ???"
    CommandButton5.ControlTipText = "Check the plausibility of the data entered"
    Range("D17:G20").ClearContents
    Range("G11:H13").ClearContents
End Sub

pour les pb 2 et 3 voici la modification à faire sur l’événement ComboBox2

Private Sub ComboBox2_Change()
    Dim i As Integer
    If ComboBox2.Value = "" Then
        For i = 2 To 17
            Me.Controls("TextBox" & i).Visible = False
        Next i
        For i = 4 To 12
            Me.Controls("label" & i).Visible = False
        Next i
        For i = 3 To 5
            Me.Controls("CommandButton" & i).Visible = False
        Next i
        
        Exit Sub
    Else
        For i = 2 To 17
            Me.Controls("TextBox" & i).Visible = True
        Next i
        For i = 4 To 12
            Me.Controls("label" & i).Visible = True
        Next i
        For i = 3 To 5
            Me.Controls("CommandButton" & i).Visible = True
        Next i
    
        TextBox18 = ComboBox2 & ComboBox1
        Sheets("Model_FA").Range("B10").Value = ComboBox2.Value
        
        TextBox6.Value = ""
        TextBox7.Value = ""
        TextBox8.Value = ""
        TextBox9.Value = ""
            
        TextBox6.Visible = Not (TextBox2.Value = "")
        TextBox7.Visible = Not (TextBox3.Value = "")
        TextBox8.Visible = Not (TextBox4.Value = "")
        TextBox9.Visible = Not (TextBox5.Value = "")
        
        Label9.Visible = Not (TextBox2.Value = "")
        Label10.Visible = Not (TextBox3.Value = "")
        Label12.Visible = Not (TextBox4.Value = "")
        Label11.Visible = Not (TextBox5.Value = "")
    End If
End Sub

L'explication est que quand ComboBox2 ="" cela n'était pas filtré et donc TextBox18 était mis à jour avec une valeur incohérente qui faisait planter le Vlookup.

A tester.

Remarque : Je pense qu'il reste sans doute quelques cas qui peuvent faire planter encore. Je n'ai pas le temps de tout tester. N'hésites pas si tu détecte ces cas tu peux me les signaler

Ofeu Messages postés 26 Date d'inscription mardi 21 août 2018 Statut Membre Dernière intervention 11 septembre 2018
28 août 2018 à 21:21

Bonjour Pilas31,

Merci beaucoup, effectivement j'ai travaillé sur quelques "bug".
Pour le 1er problème, j'ai vu que je devrais mettre plutôt /

Sheets("Model").Range("D17:G20") = ""
Sheets("Model").Range("G11:H13") = ""

car avec le .ClearContents pour des cellules avec des formules, les formules étaient également supprimées, alors qu'avec ce codage, cela marche bien !

Pour le problème 2 et 3, ça marche SUPER !

Vraiment merci et je vais essayer de faire quelques essaye afin de voir si il y a de "bug".

Ahh oui, pour le choix de la combobox1 (si je choisis _EPA), ça plante !
j'ai vu que c'est la cause que la combobox2 a juste une seul variable cad, la liste déroulante contient un seule choix. L'unique solution que j'ai trouvé c'était de mettre une variable vide de plus,. C'est possible d'avoir une liste déroulante avec une seule variable ? Mais sinon, c'est pas vraiment un pb.

MERCI

Cordialement,
Lucas

pilas31 Messages postés 1825 Date d'inscription vendredi 5 septembre 2008 Statut Contributeur Dernière intervention 24 avril 2020 643
29 août 2018 à 10:08

Bonjour,

Oui pour la valeur unique de la liste du pouvait aussi faire le test suivant. Comme il n'y a qu'une valeur, tu l'affecte directement :

If NomDefini(NomRange) Then
    If Range(NomRange).Rows.Count > 1 Then
        ComboBox2.List = Application.Transpose(Range(NomRange))
    Else
        ComboBox2.Value = Range(NomRange).Value
    End If
End If

Cordialement,

Ofeu Messages postés 26 Date d'inscription mardi 21 août 2018 Statut Membre Dernière intervention 11 septembre 2018
11 sept. 2018 à 03:33

Bonsoir Pilas31,

Merci. Effectivement j'ai essayé mais je ne pense pas que j'ai bien placé le codage, j'ai mis en private Sub Combobox2_Change( )... j'ai essayé d'autres aussi, mais ça bug ! :


Private Sub ComboBox2_Change()
If NomDefini(NomRange) Then
    If Range(NomRange).Rows.Count > 1 Then
        ComboBox2.List = Application.Transpose(Range(NomRange))
    Else
        ComboBox2.Value = Range(NomRange).Value
    End If
End If

Pour l'instant j'ai gardé la liste déroulante avec une ligne vide, mais je pense que pourra causer de confusion à l'utilisateur...

MErci bcp de votre aide !
Bien cdlt
Lucas

pilas31 Messages postés 1825 Date d'inscription vendredi 5 septembre 2008 Statut Contributeur Dernière intervention 24 avril 2020 643
11 sept. 2018 à 10:10

Bonjour,

Je viens de regarder à nouveau. En fait c'est quand il y a changement de Combobox1 qu'il faut faire ce text.
Voila ou je l'ai placé :

Private Sub ComboBox1_Change()
    Sheets("Model_FA").Range("B7").Value = ComboBox1.Value
    Sheets("Model").Range("G11").Value = ComboBox1.Value
    ComboBox2.Clear
    If ComboBox1.Value = "" Then Exit Sub
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
    'TextBox10.Text = ""
    'TextBox11.Text = ""
    'TextBox12.Text = ""
    'TextBox13.Text = ""
    'TextBox14.Text = ""
    'TextBox15.Text = ""
    'TextBox16.Text = ""
    'TextBox17.Text = ""
    'TextBox18.Text = ""
    
    Dim NomRange As String
    NomRange = CaracSpec(ComboBox1.Value)
    If NomDefini(NomRange) Then
        If Range(NomRange).Rows.Count > 1 Then
            ComboBox2.List = Application.Transpose(Range(NomRange))
        Else
            ComboBox2.Value = Range(NomRange).Value
        End If
    End If
        
    TextBox18 = ComboBox2 & ComboBox1
End Sub

Cordialement,

Discussions similaires

"L'indice n'appartient à la sélection" + VBA

VBA : Range avec références variables..

Méthode find dans VBA - Recherche de données sous Excel

VBA erreur 1004

ouverture d'un fichier excel à partir d'un code VBA

Discussions similaires

Newsletters