Pré-remplir champ formulaire à partir d'une cellule d'heure Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
E
Eyneva
Jeune membre
Jeune membre
Messages : 26
Inscrit le : 13 avril 2018
Version d'Excel : 365 Proplus

Message par Eyneva » 25 mai 2018, 16:04

Bonjour à tous,

Malgré de multiples recherches, je ne trouve pas de solution à mon problème et je me permets donc de vous solliciter afin d'essayer de le résoudre. Dans le cadre de mon travail, je dois mettre en place un planning de production avec sauvegarde des horaires.

Je découvre le VBA et modifie au fur et à mesure mon fichier car le raisonnement et les problématiques évoluent sans cesse mais j'avance.

Je vais vous présenter rapidement le fichier et ce qu'il fait : il y a dans la première feuille ('planning') la liste des dossiers avec leurs infos et le calendrier des arrivées de documents et des départs des commandes (nous sommes une société de routage).
Dans la 2ème feuille ('Dossier_Prod'), je reprends certaines infos des dossiers (numéro, nom et quantité) plus les horaires des personnes ayant travaillé dessus. J'avais fait au départ un bouton "valider" qui envoyait les horaires rentrés pour chaque personne et qui clôturait le dossier mais plusieurs personnes pouvant intervenir sur un dossier, et ce, sur plusieurs jours, il serait plus pratique que chaque salarié puisse remplir ses horaires au fur et à mesure ou les modifier. Je n'ai pas besoin de calculer le total d'heure sur plusieurs jours pour un salarié, il calculera lui-même son total passé mais ce que je veux, c'est qu'un salarié qui a préparé une partie de la commande puisse mettre tout de suite ses horaires tant que c'est frais dans sa tête car nous avons certaines situations où le dossier peut être mis de côté et repris plusieurs jours plus tard.

J'aimerais donc que le formulaire se remplisse avec ce qui est déjà sauvegardé dans la feuille Dossier_Prod s'il y a des informations au niveau des horaires.
Actuellement, il s'initialise avec la liste des numéros de dossiers. Quand on choisit un numéro, j'ajoute le nom et la quantité mais c'est là que je bloque, je n'arrive pas à récupérer mes horaires dans le bon format.

Pour ce format, je passe par 2 textbox (une pour les heures et une pour les minutes) mais forcément, je regroupe le tout dans la cellule excel et donc, quand je fais l'opération inverse, je me retrouve avec un numéro décimal à la place et je ne sais pas comment le scinder en 2 textbox.

Merci d'avance à tous ceux qui prendront le temps de me lire et de me venir en aide.

N'hésitez pas si quelque chose n'était pas clair, ce n'est pas forcément évident d'expliquer par écrit et aussi, un peu d'indulgence quant à mon code, je débute....
Je tiens d'ailleurs à préciser que ce code est encore très incomplet, par exemple, je ne vérifie pas s'il y a déjà des données rentrées pour un dossier ce qui fait que si j'enregistre un même numéro, ça me crée un doublon.... je ne vérifie pas si les cellules sont vides ou pas, bref, encore beaucoup de boulot mais si vous voulez bien en même temps m'aider aussi la-dessus, je suis preneur.

Et enfin, s'il y a d'autres logiciels ou d'autres façons de faire, n'hésitez pas non plus car Excel n'est peut-être pas la meilleure des solutions pour ce genre de projet. Ma plus grande peur étant le temps de traitement le jour où il y aura X dossiers dans la feuille Dossier_Prod ::(

Encore merci d'avance

Cdt
Thomas
Classeur_PlanningMAS_Formulaires.xlsm
(105.39 Kio) Téléchargé 19 fois
A
Andre13
Membre impliqué
Membre impliqué
Messages : 1'412
Appréciations reçues : 88
Inscrit le : 13 juillet 2017
Version d'Excel : 2007-2013

Message par Andre13 » 25 mai 2018, 17:19

Bonjour toutes et tous,
@ Eyneva bonjour
exemple: pour le format heure dans un textbox
j'ai créé un un textbox que j'ai nommé : Txt_Cyprien3
Private Sub Txt_Cyprien3_Change()
Me.Txt_Cyprien3.Value = Format(Me.Txt_Cyprien3.Value, "hh:mm")
End Sub
puis, dans le : Private Sub Cbo_NumDossier_Change()
j'ai ajouté en dessous de tes lignes de code en dessous des textbox 'Cyprien'
Txt_Cyprien3 = Sheets("Dossier_Prod").Range("E" & i).Value
afin d'être vu au lancement de ton userform

peut-être par la suite utiliser tes deux texboxs Cyprien1 (heures) et Cyprien2 (minutes) qui s'additionne/soustrait par l'intermédiaire de tes Spinbutons
une idée,
crdlt,
André
Avatar du membre
thev
Membre impliqué
Membre impliqué
Messages : 2'128
Appréciations reçues : 156
Inscrit le : 13 juin 2016
Version d'Excel : 2013 FR 64 bits

Message par thev » 27 mai 2018, 20:38

Bonsoir,

ci-jointe une proposition avec

1- une seule TextBox pour les heures et minutes avec par défaut des tranches de 15 minutes
2- une seule TextBox pour l'ensemble des salariés
3- ajout d'une ListBox pour modifier les heures de chaque salarié
4- définition d'un nom "Dossiers" pour simplification du code
5- ajout de 2 colonnes dans la Combobox pour récupération du nom de dossier et de la quantité
6- utilisation des méthodes et propriétés de l'objet tableau de la feuille Dossier_Prod pour simplification du code et prévision de mises à jour ultérieures
Classeur_PlanningMAS_Formulaires2.xlsm
(94.21 Kio) Téléchargé 21 fois
E
Eyneva
Jeune membre
Jeune membre
Messages : 26
Inscrit le : 13 avril 2018
Version d'Excel : 365 Proplus

Message par Eyneva » 28 mai 2018, 11:27

Bonjour à tous,

Merci pour les retours.
Je n'ai pas eu le temps de regarder en profondeur mais la proposition de Thev avec des changements conséquents me parait idéale :clap: :bien:

Vraiment, un grand merci et je reviendrai donc dans la journée si j'ai le temps aujourd'hui sinon demain pour reparler de tout ça.

Cdt
Thomas
E
Eyneva
Jeune membre
Jeune membre
Messages : 26
Inscrit le : 13 avril 2018
Version d'Excel : 365 Proplus

Message par Eyneva » 29 mai 2018, 11:14

Bonjour à toutes et à tous,

Je reviens vers vous comme prévu pour faire le point.
Dans l'ensemble, c'est quasiment ça qu'il me faut donc un très grand MERCI à Thev. Je découvre grâce à vous les tableaux en vba et reconnais que ça permet de grandes choses :wink:
Par contre, malgré les commentaires, je n'ai malheureusement pas tout compris et ça m'embête, d'une parce que je n'aime pas ne pas comprendre quelque chose et de deux, parce que si je veux modifier le code, ce ne sera pas possible.
Serait-il possible de me détailler les explications un peu plus ? Ou au moins les points suivants ?

1 - D'où vient le nom « Dossiers » dans la ligne RowSource de la comboBox NumDossier qui permet d'initialiser la combobox (très pratique d'ailleurs, soit dit en passant) ? Et peut-on modifier la largeur des colonnes de l'entête car là, j'ai une colonne toute petite avec le nom du dossier et une très large avec la quantité ? Je n'arrive à modifier que la première avec la donnée "Columnwidths"
Je me réponds à moi-même, il suffit de séparer par un point virgule les différentes valeurs souhaitées pour chaque colonne, par exemple ici : 90pt ; 150pt ; 90pt


2 – J’avoue, cette partie….euh, pas tout compris, je comprends dans les grandes lignes mais ce n’est pas clair et j’aime pas.
Comment sont calculées les variables i_heures et Décalage ? En fait, je ne comprends pas trop ce champ « HeaderRowRange ». C’est bien la ligne d’en-tête ? mais si oui, quel est l’intérêt du coup ? Ne peut-on pas retirer 1 tout simplement ?
 
i_heures = cell.Row - .HeaderRowRange.Row
*****
'affiche dans la ListBox1 les salariés avec leurs heures respectives
        décalage = .ListColumns("Total Heures").Range.Column - .HeaderRowRange.Column + 1   'décalage pour atteindre le 1er salarié
        Set salariés = .HeaderRowRange.Offset(, décalage).Resize(2, .ListColumns.Count - décalage)  'définition ligne entête des salariés
        ListBox1.List = Application.Transpose(salariés.Value)   'création liste des salariés
        For i = 0 To ListBox1.ListCount - 1     'remplissage heures du salarié
            ListBox1.List(i, 1) = Format(.ListColumns(i + 1 + décalage).DataBodyRange.Rows(i_heures), "hh:mm")
        Next 
3 – Y a-t-il une raison particulière pour que la somme des heures dans la colonne « Total_heures » de la feuille « dossier_prod » soit calculée de cette manière ? Car il faudrait simplement ajouter les heures précises pour obtenir un total du style 05:35. Je précise que ce total devra être récupéré dans un autre fichier excel.

4 - Dernier point, est-il possible de rentrer des heures au-delà de 24h ? Je n’ai pas trouvé comment faire. J’ai modifié pour que les minutes s’incrémentent par tranche de 5, ce qui sera un peu plus précis que par 15mn mais les heures restent bloquées à 23h maxi, même si je mets la variable "Timeset1.Max = 23" à 99.

Voilà, d’avance merci à tous ceux qui m'aideront.

Ha, j'oubliais, pour parfaire le truc, pensez-vous qu'il soit possible de trier les dossiers par date de départ dans le planning ? COmme ça, les collègues auront une meilleure vue des dossiers "urgents"
Avatar du membre
thev
Membre impliqué
Membre impliqué
Messages : 2'128
Appréciations reçues : 156
Inscrit le : 13 juin 2016
Version d'Excel : 2013 FR 64 bits

Message par thev » 29 mai 2018, 12:53

Eyneva a écrit :
29 mai 2018, 11:14
D'où vient le nom « Dossiers » dans la ligne RowSource de la comboBox NumDossier qui permet d'initialiser la combobox
Menu Formules --> Gestionnaire de noms
Sélectionner le nom "Dossiers" et cliquer sur le carré coloré en bas de la fenêtre pour visualiser la plage à laquelle ce nom correspond.


Eyneva a écrit :
29 mai 2018, 11:14
Comment sont calculées les variables i_heures et Décalage ?
i_heures = cell.Row - .HeaderRowRange.Row
cell.row est la ligne de la feuille Dossier_Prod correspondant au numéro de dossier choisi. Si ce dernier est le numéro 5, il s'agit donc de la ligne n°3.

HeaderRowRange est la ligne d'entête de l'objet tableau, .HeaderRowRange.Row représente donc la position de la ligne d'entête dans la feuille. il s'agit donc ici de la ligne n° 1. Cette ligne d'entête n'est pas forcément positionnée en ligne 1 de la feuille, le fait de prendre en compte ce paramètre vous permet d'insérer sans souci d'autres lignes au-dessus de votre tableau.

i_heures représente donc le numéro de la ligne sélectionnée dans les données du tableau(variable = DatabodyRange). Pour le dossier n°5, il s'agit de la ligne n°2.

Le décalage actuel dans votre tableau pour atteindre le premier salarié est de 4 colonnes à partir du début du tableau. Pour prévoir l'ajout éventuel d'autres colonnes, j'ai calculé un décalage à partir de la colonne "Total Heures" mais si vous ne souhaitez prévoir cette éventualité, vous pouvez directement écrire : décalage = 4.



Eyneva a écrit :
29 mai 2018, 11:14
Y a-t-il une raison particulière pour que la somme des heures dans la colonne « Total_heures » de la feuille « dossier_prod » soit calculée de cette manière ?
Oui, car justement le total pourrait dépasser 24h auquel cas les heures ne peuvent plus être gérées au format Date et doivent l'être au format décimal.


Eyneva a écrit :
29 mai 2018, 11:14
Dernier point, est-il possible de rentrer des heures au-delà de 24h ?
Oui mais seulement au format décimal et non plus au format Date
Eyneva a écrit :
29 mai 2018, 11:14
mais les heures restent bloquées à 23h maxi
Avec votre modification de tranche de 5mns, vous devez pouvoir aller jusqu'à 23h55
E
Eyneva
Jeune membre
Jeune membre
Messages : 26
Inscrit le : 13 avril 2018
Version d'Excel : 365 Proplus

Message par Eyneva » 31 mai 2018, 10:16

Merci beaucoup pour les explications, je comprends mieux le fonctionnement et surtout l'intérêt d'utiliser HeaderRowRange. Il ne m'était pas venu à l'idée que l'entête du tableau puisse être ailleurs dans la feuille.

Pour les heures, je vais regarder comment les passer en décimal car j'ai besoin pour pouvoir aller au delà de 23h55 de production. Nous avons parfois de gros dossiers sur lesquels mes collègues passent plusieurs jours.

Et concernant ma dernière demande, pensez-vous que ce soit possible, à savoir pouvoir trier le planning en fonction des dates de départ des dossiers pour qu'on ait une meilleure vue des 'urgences' à traiter en priorité.

Et enfin, je rajoute une demande : il faudrait un contrôle pour qu'on ne puisse pas clôturer un dossier tant qu'il n'y a pas au moins une heure d'un salarié.

D'avance merci
Avatar du membre
thev
Membre impliqué
Membre impliqué
Messages : 2'128
Appréciations reçues : 156
Inscrit le : 13 juin 2016
Version d'Excel : 2013 FR 64 bits

Message par thev » 31 mai 2018, 17:24

Eyneva a écrit :
31 mai 2018, 10:16
Et concernant ma dernière demande, pensez-vous que ce soit possible, à savoir pouvoir trier le planning en fonction des dates de départ des dossiers pour qu'on ait une meilleure vue des 'urgences' à traiter en priorité.
Il fauit une colonne supplémentaire "Date de départ" avec une formule permettant de ramener dans cette colonne la première date existante du planning.




ci-jointe nouvelle version avec heures au format décimal (maxi heures paramétré = 48, à modifier …)
Classeur_PlanningMAS_Formulaires3.xlsm
(94.32 Kio) Téléchargé 10 fois
E
Eyneva
Jeune membre
Jeune membre
Messages : 26
Inscrit le : 13 avril 2018
Version d'Excel : 365 Proplus

Message par Eyneva » 5 juin 2018, 16:24

Bonjour à tous,

Désolé pour le retour tardif mais beaucoup de boulot en ce moment.
Alors, la dernière version est quasiment parfaite. J'ai changé quelques petites choses comme l'affichage des heures même si le calcul se fait toujours de manière décimal.
J'ai également modifié la formule qui récupère la date de départ et non d'arrivée et en ai profité pour rajouter un bouton "TRI".

En l'état, tout marche et toutes les "options" y sont mais j'ai encore quelques questions pour ma culture personnelle :
1 - Dans la formule utilisée pour récupérer la date, pourquoi devoir remettre un INDEX pour récupérer la plage dans la formule EQUIV
INDEX(calendrier;6;EQUIV("Dép";INDEX(calendrier;LIGNE(E7););0))
J'ai bien sûr essayé en mettant la plage directement mais forcément ça ne marche pas mais pourquoi ?

2 - J'avais fait au départ dans le formulaire un bouton pour ajouter les salariés afin d'éviter de surcharger l'affichage. Une telle fonction est-elle possible dans excel ? Je précise que c'est purement informatif car la solution de Thev me convient parfaitement.

Je remets le fichier avec mes dernières modifications

Encore un grand merci à tous et particulièrement à Thev
Classeur_PlanningMAS_Formulaires3.xlsm
(105.05 Kio) Téléchargé 18 fois
Avatar du membre
thev
Membre impliqué
Membre impliqué
Messages : 2'128
Appréciations reçues : 156
Inscrit le : 13 juin 2016
Version d'Excel : 2013 FR 64 bits

Message par thev » 5 juin 2018, 21:15

Bonsoir,
Eyneva a écrit :
5 juin 2018, 16:24
Dans la formule utilisée pour récupérer la date, pourquoi devoir remettre un INDEX pour récupérer la plage dans la formule EQUIV
Calendrier représente une plage de plusieurs lignes et colonnes. La fonction EQUIV ne peut porter que sur une plage d'une seule ligne ou d'une seule colonne. La fonction Index appliquée à la plage "calendrier" permet de sélectionner une seule ligne de recherche.
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message