XL 2013 incrémentation ComboBox

[filierfilier]

bonjour à tous, j'aurai bien besoin de votre aide concernant mon fichier (achats fournisseurs 1) qui a pour simplifié, deux onglets :
1) " base plan" où se trouve les genres de plats, la source pour le nom des genres et le nom des produits
2) "Recettes" où je peux, créer, supprimer et modifier les recettes

Après plusieurs essais pas très concluant, je n'y suis plus.
Plusieurs soucis concernant le code de ce fichier en cliquant dans l'onglet Recettes sur Créer ou modifier une recette :
1) incrémentation dans les onglets recettes et base plan, du genre de la recette qui ne se fait plus.
2) problème également dans l'affichage des ingrédients après le troisième textbox
3) à voir aussi pour la modification et suppression des recettes fonctionne aussi
4) arrondir avec un chiffre entier le calcul des quantités automatiques.

Voilà si quelqu'un peut me trouver une solution, je suis preneur. Grand merci à tous !

Code :
Private Sub UserForm_Initialize()
Me("Genre").RowSource = "genre"
Me.Nombre_convive = ""
Me.Note = ""
For I = 1 To 3
Me("Ingredient_" & I).RowSource = "nomproduits"
Me("Quantite_" & I) = ""
Me("qp_" & I) = ""
Next I
End Sub
Private Sub B_validation_quantite_Click()
Dim T As Double, A As Double, B As Double, c As Double, D As Double, E As Double, F As Double, G As Double, H As Double
If IsNumeric(Me.Nombre_convive) Then
Nc = CDbl(Me.Nombre_convive)
End If
If IsNumeric(Me.Quantite_1) Then
Q1 = CDbl(Me.Quantite_1)
End If
If IsNumeric(Me.Quantite_2) Then
Q2 = CDbl(Me.Quantite_2)
End If
If IsNumeric(Me.Quantite_3) Then
Q3 = CDbl(Me.Quantite_3)
End If
If IsNumeric(Me.Quantite_4) Then
Q4 = CDbl(Me.Quantite_4)
End If
If IsNumeric(Me.Quantite_5) Then
Q5 = CDbl(Me.Quantite_5)
End If
If IsNumeric(Me.Quantite_6) Then
Q6 = CDbl(Me.Quantite_6)
End If
If IsNumeric(Me.Quantite_7) Then
Q7 = CDbl(Me.Quantite_7)
End If
If IsNumeric(Me.Quantite_8) Then
Q8 = CDbl(Me.Quantite_8)
End If
Me.qp_1 = Format(Q1 / Nc, "0.000")
Me.qp_2 = Format(Q2 / Nc, "0.000")
Me.qp_3 = Format(Q3 / Nc, "0.000")
Me.qp_4 = Format(Q4 / Nc, "0.000")
Me.qp_5 = Format(Q5 / Nc, "0.000")
Me.qp_6 = Format(Q6 / Nc, "0.000")
Me.qp_7 = Format(Q7 / Nc, "0.000")
Me.qp_8 = Format(Q8 / Nc, "0.000")
Application.ScreenUpdating = True
End Sub
Private Sub B_valider_Click()
Dim I As Integer, DLig As Long
'--- Positionnement dans la base
With Sheets("Recettes")
.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
'--- Transfert Formulaire dans BD
ActiveCell.Offset(0, 1).Value = Me.Nom_de_la_recette
For I = 2 To 18 Step 2
ActiveCell.Offset(0, I).Value = Me("Ingredient_" & I / 2)
ActiveCell.Offset(0, 1 + I).Value = Me("qp_" & I / 2)
ActiveCell.Offset(0, 18).Value = Me.Note
Next I
' Choix de la recette
For I = 1 To 18
If Me("Genre") = True Then
ActiveCell.Value = Me("Genre").Caption
' Inscrire le nom de la recette dans la listes des plats
' En partant du principe que les OptionButton sont dans le mêmes sens que sur la feuille : Liste des plats
With Sheets("Base plan")
' Trouver la dernière ligne de la colonne concernée
DLig = .Cells(Rows.Count, I).End(xlUp).Row + 1
.Cells(DLig, I).Value = Me.Nom_de_la_recette
End With
' Sortir de la boucle
Exit For
End If
Next I
End With

'--
nettoie
End Sub

 

[vgendron]

Hello

Pas sur d'avoir compris le problème ..
dans ton Initialize
tu ne set le rowsource que sur 3 listes. alors que tu en as 8 sur ton formulaire

Private Sub UserForm_Initialize()
Me("Genre").RowSource = "genre"
Me.Nombre_convive = ""
Me.Note = ""
For I = 1 To 8 'au lieu de 3
    Me("Ingredient_" & I).RowSource = "nomproduits"
    Me("Quantite_" & I) = ""
    Me("qp_" & I) = ""
Next I
End Sub

et dans ton code "Valider", tu fais une boucle de 1 à 18 (=2*9) alors que tu n'as QUE 8 ingrédients ==> 2*8=16

Private Sub B_valider_Click()
Dim I As Integer, DLig As Long
'--- Positionnement dans la base
With Sheets("Recettes")
    .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Select
    '--- Transfert Formulaire dans BD
    ActiveCell.Offset(0, 1).Value = Me.Nom_de_la_recette
    For I = 2 To 16 Step 2
        ActiveCell.Offset(0, I).Value = Me("Ingredient_" & I / 2)
        ActiveCell.Offset(0, 1 + I).Value = Me("qp_" & I / 2)
        ActiveCell.Offset(0, 18).Value = Me.Note
    Next I
    ' Choix de la recette
    For I = 1 To 16 'au lieu de 18
        If Me("Genre") = True Then
            ActiveCell.Value = Me("Genre").Caption
            ' Inscrire le nom de la recette dans la listes des plats
            ' En partant du principe que les OptionButton sont dans le mêmes sens que sur la feuille : Liste des plats
            With Sheets("Base plan")
                ' Trouver la dernière ligne de la colonne concernée
                DLig = .Cells(Rows.Count, I).End(xlUp).Row + 1
                .Cells(DLig, I).Value = Me.Nom_de_la_recette
            End With
            ' Sortir de la boucle
            Exit For
        End If
    Next I
End With

   '--
   nettoie
End Sub

 

[vgendron]

pour les zones nommées de ta feuille "Base Plan", je vois que tu as défini à chaque fois la colonne complète
je trouve la définition "dynamique" plus "légère" car la liste se trouve automatiquement ajustée en fonction du nombre de valeurs dans la colonne
ex pour la liste Crudités
=decaler(A2;;;nbval(A:A)-1)

Comme tu as pas mal d'ingrédients différents. et donc de liste à créer..
j'ai fait un petit code qui se charge de ca à ta place

Sub CreerZonesNommées()
'
' Macro1 Macro
'
With Sheets("Base plan")
    LastCol = .UsedRange.Columns.Count
    For I = 1 To LastCol
        Nom = WorksheetFunction.Substitute(.Cells(1, I), " ", "_")
        Nom = WorksheetFunction.Substitute(Nom, "%", "pc")
        Nom = WorksheetFunction.Substitute(Nom, "/", "_")
        Nom = WorksheetFunction.Substitute(Nom, "+", "p")
        Nom = WorksheetFunction.Substitute(Nom, "-", "")
        Nom = WorksheetFunction.Substitute(Nom, "<", "Inf")
        If Nom <> "" Then
            formule = "=OFFSET('Base plan'!R2C" & I & ",,,COUNTA('Base plan'!C" & I & ")-1)"
            On Error GoTo ErrHndl
RetourErreur:           
            ActiveWorkbook.Names.Add Name:=Nom, RefersToR1C1:=formule
        End If
    Next I
Exit Sub
End With
ErrHndl:
MsgBox ("Erreur de nom: " & Nom)
Nom = InputBox("tapez un nom pour cette liste:")
GoTo RetourErreur
End Sub

 

[vgendron]

autre problème observé dans ta macro "Valider"
tu ne mets jamais rien en colonne A de la feuille Recettes.. donc. à chaque fois.. tu vas écraser la dernière ligne
quelle colonne prend quoi? il faudrait ajouter une ligne d'entête

 

[vgendron]

et dernière chose pour l'instant.. comme on a pas tout le classeur avec toutes les feuilles.. les très nombreuses listes nommées que tu as déja définies sont en erreur.. donc; le code ne peut pas vraiment fonctionner, surtout quand il y a des rowsources=...

 

[filierfilier]

bonjour, merci pour toutes ces sympathiques réponses qui vont me donner pas mal à réfléchir (n'étant pas un programmeur confirmé)
je vous joins les deux fichiers en entier (économat1 et fournisseurs1) pour une meilleure compréhension de mon soucis qui se trouve sur la création de la recette.
Merci de votre aide

 

[vgendron]

Ouh laaaa !!
va y avoir du boulot ...et tu n'as pas de bug dans tes fichiers??

des macros en double (dans le code de la feuille, et dans un module)
la quasi totalité des zones nommées en triple, de nombreuses autres HS

Dans le fichier Economat
utilisation de la fonction "indirect" étonnante (et surement inutile..)
des zones nommées (Base.....) définies dans le fichier économat qui n'en fait rien...

SI((INDIRECT("STOCK_INITIAL!A"&(LIGNE(A5))))="";"";SI(INDIRECT("STOCK_INITIAL!H"&(LIGNE(A5)))=0;F5;(INDIRECT("STOCK_INITIAL!G"&(LIGNE(A5)))+G5)/I5))

peut etre remplacé simplement par

SI(STOCK_INITIAL!A5="";"";SI(STOCK_INITIAL!H5=0;F5;(STOCK_INITIAL!G5+G5)/I5))

Dans le fichier AchatFournisseur
les zones 'BaseXX" font référence à la feuille "Calcul Proportion"
tandis que la zone "BASE" fait référence à la feuille "Base Plan".. et encore.. pas toute la feuille...

==> je crois qu'il faudrait commencer par faire du ménage
puis bien définir OU sont les données d'entrées : La database
ce qu'elle alimente

Exemple: Feuille "Catalogue" du fichier "Fournisseur" semble etre une copie de la feuille "Stock Initial" du fichier "Economat"
==> plutot que mettre des formules qui imposent que les DEUX fichiers soient ouverts, ne vaudrait il mieux pas créer un petit code qui
ouvre le fichier "Economat", fait un copier coller des data dans le classeur Fournisseur - Ferme Economat, et ensuite tu travailles sur le fichier Fournisseur..?

 

[vgendron]

Re moi..
Bon.. j'ai comencé par ce qui semble etre le fichier de départ: Economat

Ce que j'ai compris:
1) Feuille "Produits et Fournisseurs"
==> Contient la liste des fournisseurs - chacun est associé à un code client (ou plusieurs clients ==> d'ou plusieurs fois "Pro à Pro"
==> Contient la liste des mesures
j'en ai fait deux TABLES Excel ==> tu peux ajouter ou supprimer des éléments, les tables se mettent à jour toutes seules

Les deux boutons pemettent de modifier un des éléments

2) Feuille "Stock_Initial"
C'est LA liste de tous les produits qui peuvent etre utilisés, avec définition des conditionnements, quté, prix Stock Initial et fournisseur
J'en ai fait une zone Nommée "TabStockInit"
Apparemment, la colonne "Fournisseur" = liste de validation avec la liste des fournisseurs de la feuille précédente..
SAUF que la majorité des lignes sont saisies avec Légumes, Epiceler Viandes... qui ne SONT pas dans la liste et qui ne sont pas des fournisseurs....??...??

3) feuilles "Stocks_Jxx" = Stock au jour le jour ==> J'imagine que ca pourrait aller jusqu'à 31 jours (mensuel) ou plus ..?
==>Colonnes A et B= Colonnes A et B du Stock Initial ==> J'ai changé la formule (et dans celle que tu avais. il y avait un décalage.. les premiers Produits n'étaient pas recopiés..
==>calcul des Stocks Colonne I et K
en I = le stock du Jour précédent + Entrées du jour (colonne C)
en K=le stock en fin de journée = Stock I-Sorties (colonneJ)
du coup.. la. on peut réutiliser la fonction Indirect.. sauf si tu as envie de modifier les formules à la main à chaque fois que tu ajoutes un jour...
pour cette fonction Indirect, il faut lui dire dans quelle feuille aller chercher==> d'ou la cellule A2 que j'ai utilisée pour y mettre J1 - J2....et Initial pour le Stock_J1

==> calcul du Prix Moyen : Semble évoluer de jour en jour en fonction des stocks restants..
Idem: utilisation de la fonction Indirect

J'ai supprimé le test récurrent que tu faisais dans toutes les formules : Si A5="";""....
il suffit je pense d'encadrer les formules qui pourraient poser problème avec un Sierreur(;"") ==> à voir...

4) Gestionnaire de noms
J'ai supprimé toutes les zones nommées "Bases...." qui ne servent à rien (enfin. je pense)

5) les macros: je les ais adaptées pour qu'elles utilisent le nom des tables
et j'ai modifié celles qui "masquent" les lignes à 0: très longue d'execution. deviennent très rapide dès qu'on utilise les fonctions d'Excel telle que le Filtrage.. :-D

Voila.. pour un premier jet :-D

 

[filierfilier]

Bonjour Vgendron, merci pour ce super travail.
Comme je l'ai dit, je ne suis pas un expert dans le VBA. J'apprends un peu par ci et par là en ajoutant un peu de ce que les autres participant peuvent m'indiquer.
Je vois qu'il se passe déjà quelques amélioration. Mon problème est toujours présent dans le classeur Achats fournisseurs à la feuille Recettes pour la création de recette (insertion du genre dans la Base plan et Recettes. Dans la feuille Catalogue, la macro TotalDesCommandes est elle aussi, je pense un peu longue à exécutée.
J'attends beaucoup de vos attentions sur mon problème.
Grand merci à tous. Philippe