Planning de livraisons interactif avec demandes

Bonjour,

Je travaille dans la construction en tant que gestionnaire de projet sur des chantiers. Je souhaiterais réaliser un planning de livraisons pour un chantier. L'objectif est simple, mes sous-traitants réalisent des demandes de livraisons avec date souhaitée, heure de début, heure de fin, description rapide de la livraison, mode de déchargement (s'ils sont autonomes ou s'ils ont besoin de la grue).

L'objectif est qu'ils réalisent leurs demande sur une feuille de mon fichier Excel et que je puisse ensuite traiter au cas par cas chaque demande en les validant ou non. Une fois validées, ces demandes seraient intégrées à un planning hebdomadaire qui me permettrait d'organiser plus facilement les livraisons et d'avoir un aperçu de l'utilisation de la grue.

J'ai vu sur Internet qu'il était possible d'utiliser Power Query mais je ne sais pas ce qui serait le mieux entre cet outil et les macros/VBA pour mes besoins. J'ai donc pour l'instant seulement configuré quelques macros pour la soumission des demandes et je commence à me familiariser avec PowerQuery..

J'avoue en plus ne pas être un grand spécialiste des macros et encore mins un pro de Power Query mais j'aimerais savoir si je suis sur la bonne voie ou si vous avez des pistes/idées pour atteindre mon objectif !

Merci d'avance pour votre aide

Bonjour

Un premier point à corriger. Votre feuille Demande de livraisons contient un tableau dit au format structuré
Pour bien commencer il faut déjà supprimer toutes les lignes.
Mais avant dites-moi le pourquoi de deux feuilles demande de livraison ?
Si c'est pour y mettre les demandes validées, pourquoi ne pas faire cela sur la même feuille ?

Bonjour,

La feuille "Demandes de livraisons (2)" s'est créée lorsque j'ai lancé PQ (une fausse manip de ma part).

Pour le format structuré j'ai ensuite juste sélectionné une plage de données allant de A1 à G1000 en anticipant le fait que ma macro "soumettre demande" créait à chaque fois une nouvelle ligne sur cette feuille.

J'avoue être pour l'instant un peu perdu dans le fait de combiner macros et PQ

La feuille "Demandes de livraisons (2)" s'est créée lorsque j'ai lancé PQ (une fausse manip de ma part).

Ok. Je pense que c'est plus facile d'utiliser une seule feuille et si une demande est validée vous pouvez confirmer dans la colonne G. Vous auriez une seule feuille qui vous permet de voir ce qui est validé et ce qui est en attente
Votre avis ?

Autre point : Vous utilisez des Inputbox pour entrer vos infos. Si vous cliquez sur Annuler, il faudrait sortir de la macro. Exemple si vous cliquez sur Annuler pour le nom d'entreprise, le code va continuer à afficher toutes les inputbox....
Si vous annulez il faut que le code s'arrête afin de ne pas aller créer une ligne qui serait incomplète, non ?

Oui c'est plus simple d'utiliser une seule feuille. Pour l'instant la colonne G permet d'afficher trois choix de réponse en fonction du traitement de la demande et une macro "ValiderDemandes" permet d'accepter ou non. Lorsque j'aurais plus avancé mon planning je mettrais en place une sécurité pour que je sois le seul à avoir accès à cette macro.

Vous avez raison pour les InputBox, il faudrait que je fasse une boucle pour chaque question posée c'est cela?

Voici ce que vous devez faire

1. Allez dans le gestionnaire de nom
2. Sélectionnez "Tableau2" puis cliquez sur bouton Modifier et remplacez Tableau2 par ceci --> Tab_demande_livraison
3. Allez dans votre feuille "Demande de livraison"
4. Sélectionnez la plage A2 à Gx (x étant la dernière ligne du tableau"
5. Faite un clic droite dans la plage sélectionnée et choisir l'option "Supprimer" --> "Lignes de tableau" (Attention vous devez absolument voir l'option Lignes de tableau)
6. Allez dans l'éditeur VBA --> Module 1 et remplacez le code par celui ci-dessous

Sub SoumettreDemande()
Dim lig As Integer
Dim tablo(0 To 6)
Dim i As Byte

tablo(0) = InputBox("Entrez le nom de l'entreprise demandeuse :", "Nom entreprise")
If tablo(0) = vbNullString Then Exit Sub
tablo(1) = InputBox("Entrez la date de livraison (jj/mm/aaaa) :", "Date Livraison")
If tablo(1) = vbNullString Then Exit Sub
tablo(2) = InputBox("Entrez l'heure de début (hh:mm) :", "Heure debut")
If tablo(2) = vbNullString Then Exit Sub
tablo(3) = InputBox("Entrez l'heure de fin (hh:mm) :", "Heure fin")
If tablo(3) = vbNullString Then Exit Sub
tablo(4) = InputBox("Entrez la description de la demande :", "Description")
If tablo(4) = vbNullString Then Exit Sub
tablo(5) = InputBox("Mode de déchargement (Autonome/Grue) :", "Déchargement")
If tablo(5) = vbNullString Then Exit Sub
tablo(6) = "En attente"

With Range("Tab_demande_livraison").ListObject
    If .ListRows.Count = 0 Then
        .ListRows.Add: lig = 1
    Else: .ListRows.Add: lig = .ListRows.Count
    End If
    For i = 0 To UBound(tablo)
       .DataBodyRange.Item(lig, i + 1) = tablo(i)
    Next i
End With
MsgBox "Demande soumise avec succès!", vbInformation, "Soumission"
End Sub

Faites un test d'ajout et dites-moi

Rem : Pour supprimer la ligne inutilement créée, refaites les pt 4 et 5 ci-dessus.

Merci pour les instructions cela fonctionne correctement.

Pensez-vous que l'utilisation des macros et de PQ est compatible pour les besoins que j'ai évoqués?

Pensez-vous que l'utilisation des macros et de PQ est compatible pour les besoins que j'ai évoqués?

Ce n'est pas une question de compatibilité. Power query permet d'éviter les macros
Selon votre cas, on trouve une solution adaptée tout en étant que si on peut utiliser Power query, il n'y a pas de raison à l'éviter

La question est de savoir ce que vous voulez faire ou quel résultat à obtenir avant de penser aux macros ou à PQ.

Actuellement, le problème Nouvelle demande est solutionné par code
Le code met en G la mention "En attente" par défaut.
le point suivant est de savoir comment mettez-vous la mention Validée ?

Edit : je relis votre post précédent

Pour l'instant la colonne G permet d'afficher trois choix de réponse en fonction du traitement de la demande et une macro "ValiderDemandes"

Dans la colonne G vous avez une liste déroulante ?. Là je ne vois rien mais vous pourriez la créer avec les trois options En Attente, Validée, Refusée

Pour votre feuille Planning, je ne suis pas certain que PQ fasse l'affaire mais à analyser plus tard une fois que tout est bien figé dans la demande de livraison
De base, dans votre feuille Planning je mettrais toutes les dates en ligne 1 et pas sur plusieurs tableaux. Donc en B1, 26/08/2024 jusque K1 06/09/2024

D'accord, comme je ne l'ai encore jamais utilisé je me posais la question vu que pour les macro cela semble simple de séquencer les tâches via différents boutons là où PQ me semble (pour le moment en tous cas) plus basé sur un processus complet qu'il suffit d'actualiser à chaque modif.

J'essaye de le rendre automatisé au maximum afin de le rendre simple et rapide à utiliser par n'importe qui (j'aimerais à terme le diffuser à mes autres collègues)

J'ai essayé dans les macros du module2 de faire une macro permettant d'uniformiser le format des dates et des heures. Cependant, cela ne semble pas fonctionner correctement puisque dans ma macro "Valider demandes" module3 ligne 19 j'ai du ajouter un *24 pour passer de mon format d'heures en fraction de jour (affiche 0.33 soit 8/24 lorsque l'on écrit 08:00 dans la cellule). Pour l'instant cela ne me dérange pas dans le process de validation puisque je suis au courant de ce soucis et que l'heure s'affiche dans le Msgbox à la fin. Le soucis serait je pense que lorsque je vais transposer les demandes validées dans ma trame de planning hebdomadaire je vais avoir des soucis au niveau des formats d'affichage.

Je pensais faire une macro (manuellement en enregistrant les étapes 1 à 1) qui :

1. Filtre les données validées
2. Trie par date croissante puis par heure de début croissante
3. Retourne sur la feuille Planning hebdomadaire

Rentre dans chaque cellule une formule RECHERCHEV qui recherche dans mes demandes la correspondance des heures de début et affiche la description de la demande dans les bonnes cases

Mes soucis sont que :

1. Les formats d'heures ne vont pas fonctionner à mon sens
2. Mon fichier ne fait pas de vérification des doublons sur les créneaux de livraisons
3. L'heure de début s'afficherait dans la bonne cellule mais je ne sais pas encore comment gérer des livraisons étalées sur plusieurs créneaux

C'est dans ce sens que je me suis intéressé à PQ qui pouvait peut être me permettre de simplifier ces multiples demandes sans passer par une macro

Les formats d'heures ne vont pas fonctionner à mon sens

C'est un cas qu'il faut analyser dans le code que je vous ai proposé.
Il faut un contrôle :
1. sur le format date
2. sur le format d'heure entrée dans l'inputbox
3. Sur l"heure de fin qui doit être plus grande que l'heure de début. A ce sujet, je suppose que votre livraison ne concerne qu'un jour à chaque fois ?

- Mon fichier ne fait pas de vérification des doublons sur les créneaux de livraisons

Vous voulez dire de ne pas pouvoir entrer la même date avec les même heures de début et fin ?

- L'heure de début s'afficherait dans la bonne cellule mais je ne sais pas encore comment gérer des livraisons étalées sur plusieurs créneaux

Que voulez-vous dire par plusieurs créneaux ?

Avez-vous vu mon Edit dans le post précédent... concernant la colonne G notamment

Dans la colonne G vous avez une liste déroulante ?. Là je ne vois rien mais vous pourriez la créer avec les trois options En Attente, Validée, Refusée

J'ai pour l'instant la macro "Validerdemandes" qui automatise ce qui est écrit dans la colonne G (Validée/Refusée). Le demandeur n'aura pas accès à la modification de cette colonne (il pourra juste vérifier que la demande est prise en compte avec le "En attente").

C'est un cas qu'il faut analyser dans le code que je vous ai proposé.

Il faut un contrôle :
1. sur le format date
2. sur le format d'heure entrée dans l'inputbox -->

A ces 2 premiers points j'ai une macro "Formatage dates et heures" que j'ai rentrée en enregistrement macro mais elle ne change pas le format. Il apparaît comme correct dans la cellule mais c'est lors de l'export de données que celan ne semble pas marcher correctement. Il faudrait que j'applique cette macro également aux cellules de destination dans ce cas?

3. Sur l"heure de fin qui doit être plus grande que l'heure de début. A ce sujet, je suppose que votre livraison ne concerne qu'un jour à chaque fois ?

--> oui les livraisons ne se font que sur un jour

Vous voulez dire de ne pas pouvoir entrer la même date avec les même heures de début et fin ?

--> C'est plutôt ne pas pouvoir proposer un créneau de livraison qui empiète sur un créneau existant. Pour le moment je pense que cela peut se limiter aux demandes qui sont faites et par l'avenir j'essayerais de faire en sorte que cela se limite aussi aux créneaux qui sont déjà affichés dans le planning hebdo.

Que voulez-vous dire par plusieurs créneaux ?

--> j'ai figé des créneaux toutes les 30 minutes et donc j'aimerais qu'une livraison qui dure 1h soit affichée dans les 2 cellules (par ex une livraison qui démarre à 8h et finisse à 9h soit affichée comme cela). Pour l'instant je n'entre pas dans les détails de mise en forme avec des fusions de cellules.

--> PQ serait plus adapté pour la transposition dans le planning hebdo si j'ai bien compris le sens?

De base, dans votre feuille Planning je mettrais toutes les dates en ligne 1 et pas sur plusieurs tableaux. Donc en B1, 26/08/2024 jusque K1 06/09/2024

--> Très bien je note la modif

Désolé pour la lecture de ma réponse qui est peut être difficile j'avais initialement mis toutes vos réponses en bleu et en italique mais ça n'a pas l'air d'être passé à la publication

Désolé pour la lecture de ma réponse qui est peut être difficile j'avais initialement mis toutes vos réponses en bleu et en italique mais ça n'a pas l'air d'être passé à la publication

Oui il y a quelques fois un petit souci avec les couleurs.
Au delà de cela, vous avez l'icône citation (à droite de l'icone </>) qui vous permet de coller le texte que vous reprenez d'un post précédent dans une fenêtre séparée (comme je le fais ici)

J'ai corrigé votre post

--> j'ai figé des créneaux toutes les 30 minutes et donc j'aimerais qu'une livraison qui dure 1h soit affichée dans les 2 cellules (par ex une livraison qui démarre à 8h et finisse à 9h soit affichée comme cela). Pour l'instant je n'entre pas dans les détails de mise en forme avec des fusions de cellules.

Waouh là c'est plus complexe à faire. A analyser...

Pour le contrôle des dates et Heures, vous pouvez remplacer ce que je vous ai proposé par ce code ci-dessous

Sub SoumettreDemande()
Dim lig As Integer
Dim tablo(0 To 6)
Dim i As Byte

tablo(0) = InputBox("Entrez le nom de l'entreprise demandeuse :", "Nom entreprise")
If tablo(0) = vbNullString Then Exit Sub
tablo(1) = InputBox("Entrez la date de livraison (jj/mm/aaaa) :", "Date Livraison")

If Not IsDate(tablo(1)) Then MsgBox "Les dates doivent être dans un format JJ/MM/AAAA", vbCritical, "Erreur format": Exit Sub
If tablo(1) = vbNullString Then Exit Sub

tablo(2) = InputBox("Entrez l'heure de début (hh:mm) :", "Heure début")
If tablo(2) = vbNullString Then Exit Sub
If Not IsDate(tablo(2)) Then MsgBox "Les heures doivent être dans un format HH:MM", vbCritical, "Erreur format": Exit Sub

tablo(3) = InputBox("Entrez l'heure de fin (hh:mm) :", "Heure fin")
If tablo(3) = vbNullString Then Exit Sub
If Not IsDate(tablo(3)) Then MsgBox "Les heures doivent être dans un format HH:MM", vbCritical, "Erreur format": Exit Sub
If tablo(3) < tablo(2) Then MsgBox "l'heure de fin ne peut être inférieur à l'heure de début", vbCritical, "Heure": Exit Sub

tablo(4) = InputBox("Entrez la description de la demande :", "Description")
If tablo(4) = vbNullString Then Exit Sub

tablo(5) = InputBox("Mode de déchargement (Autonome/Grue) :", "Déchargement")
If tablo(5) = vbNullString Then Exit Sub
tablo(6) = "En attente"

With Range("Tab_demande_livraison").ListObject
    If .ListRows.Count = 0 Then
        .ListRows.Add: lig = 1
    Else: .ListRows.Add: lig = .ListRows.Count
    End If
    For i = 0 To UBound(tablo)
       .DataBodyRange.Item(lig, i + 1) = tablo(i)
    Next i
End With
MsgBox "Demande soumise avec succès!", vbInformation, "Soumission"
End Sub

Dans la colonne G vous avez une liste déroulante ?. Là je ne vois rien mais vous pourriez la créer avec les trois options En Attente, Validée, Refusée

J'ai pour l'instant la macro "Validerdemandes" qui automatise ce qui est écrit dans la colonne G (Validée/Refusée). Le demandeur n'aura pas accès à la modification de cette colonne (il pourra juste vérifier que la demande est prise en compte avec le "En attente").

Je vais regarder votre macro Validerdemande

Re,

Votre code de validation des demandes comme ceci

Sub ValiderDemandes()
Dim i As Integer
Dim TS As ListObject

Set TS = Range("Tab_demande_livraison").ListObject

With TS
    If .ListRows.Count = 0 Then Exit Sub 'cas où le tableau ne comporterait pas de ligne
    For i = 1 To .ListRows.Count
        Select Case MsgBox("Voulez-vous valider la demande de " & _
                .DataBodyRange(i, 1).Value & vbCrLf & vbCrLf & _
                "pour le " & .DataBodyRange(i, 2).Value & " à " & _
                Format(.DataBodyRange(i, 3).Value, "hh:mm") & " Heure(s) avec déchargement " & _
                .DataBodyRange(i, 6).Value & " ?", vbYesNoCancel + vbQuestion, "Validation demande")

            Case vbYes: .DataBodyRange(i, 7).Value = "Validée"
            Case vbNo: .DataBodyRange(i, 7).Value = "Refusée"
            Case vbCancel
        End Select
    Next i
End With
MsgBox "Validation terminée !", vbInformation, "Processus validation"
End Sub

dans l'attente de votre retour sur les deux derniers posts et codes proposés

Merci pour les astuces !

Waouh là c'est plus complexe à faire. A analyser...

C'est pour cela que dans un premier temps je ne m'attarde pas là-dessus, je verrais à l'usage si c'est vraiment nécessaire

tablo(0) = InputBox("Entrez le nom de l'entreprise demandeuse :", "Nom entreprise")

If tablo(0) = vbNullString Then Exit Sub

tablo(1) = InputBox("Entrez la date de livraison (jj/mm/aaaa) :", "Date Livraison")

If Not IsDate(tablo(1)) Then MsgBox "Les dates doivent être dans un format JJ/MM/AAAA", vbCritical, "Erreur format": Exit Sub

If tablo(1) = vbNullString Then Exit Sub

J'ai essayé la macro "Soumettre demande" en mettant un faux format de date et je n'ai pas eu le message d'erreur. Je me demande s'il ne faudrait pas que je créé une variable pour les jours et les mois et qui vérifie ensuite que jour est <31 et que le mois <12. Qu'en pensez-vous?

Concernant les jours j'ai modifié comme ceci :

tablo(3) = InputBox("Entrez l'heure de fin (hh:mm) :", "Heure fin")
If tablo(3) = vbNullString Then Exit Sub
If Not IsDate(tablo(3)) Then MsgBox "Les heures doivent être dans un format HH:MM", vbCritical, "Erreur format": Exit Sub
If tablo(3) <= tablo(2) Then MsgBox "l'heure de fin ne peut être inférieur ou égal à l'heure de début", vbCritical, "Heure": Exit Sub

j'ai ajouté le "=" pour signifier que l'heure de fin ne puisse pas être égale non plus.

Pour ce qui est de la macro de validation tout fonctionne parfaitement merci

Bonjour,

J'ai essayé la macro "Soumettre demande" en mettant un faux format de date et je n'ai pas eu le message d'erreur. J

Je ne sais pas quel format de date vous avez utilisé mais essayez le code comme ceci.

Sub SoumettreDemande()
Dim lig As Integer
Dim tablo(0 To 6)
Dim i As Byte
Dim ddate As String

tablo(0) = InputBox("Entrez le nom de l'entreprise demandeuse :", "Nom entreprise")
If tablo(0) = vbNullString Then Exit Sub

On Error Resume Next
ddate = InputBox("Entrez la date de livraison (jj/mm/aaaa) :", "Date Livraison", Format(Now(), "dd/mm/yyyy"))
If Not IsDate(ddate) Or Len(ddate) < 10 Then MsgBox "Les dates doivent être dans un format JJ/MM/AAAA", vbCritical, "Erreur format": Exit Sub
If ddate = vbNullString Then Exit Sub
tablo(1) = ddate
On Error GoTo 0

tablo(2) = InputBox("Entrez l'heure de début (hh:mm) :", "Heure début", Format(Now(), "hh:mm"))
If tablo(2) = vbNullString Then Exit Sub
If Not IsDate(tablo(2)) Then MsgBox "Les heures doivent être dans un format HH:MM", vbCritical, "Erreur format": Exit Sub

tablo(3) = InputBox("Entrez l'heure de fin (hh:mm) :", "Heure fin", Format(Now(), "hh:mm"))
If tablo(3) = vbNullString Then Exit Sub
If Not IsDate(tablo(3)) Then MsgBox "Les heures doivent être dans un format HH:MM", vbCritical, "Erreur format": Exit Sub
If tablo(3) <= tablo(2) Then MsgBox "l'heure de fin ne peut être inférieur à l'heure de début", vbCritical, "Heure": Exit Sub

tablo(4) = InputBox("Entrez la description de la demande :", "Description")
If tablo(4) = vbNullString Then Exit Sub

tablo(5) = InputBox("Mode de déchargement (Autonome/Grue) :", "Déchargement")
If tablo(5) = vbNullString Then Exit Sub

tablo(6) = "En attente"

With Range("Tab_demande_livraison").ListObject
    If .ListRows.Count = 0 Then
        .ListRows.Add: lig = 1
    Else: .ListRows.Add: lig = .ListRows.Count
    End If
    For i = 0 To UBound(tablo)
        If i = 1 Then
            With .DataBodyRange
                .Item(lig, i + 1) = CDate(tablo(i))
                .Item(lig, i + 1).NumberFormat = "dd/mm/yyyy"
            End With
        Else:
            .DataBodyRange.Item(lig, i + 1) = tablo(i)
       End If
    Next i
End With
MsgBox "Demande soumise avec succès!", vbInformation, "Soumission"
End Sub

J'ai introduit une valeur par défaut dans les inputbox
Pour ce qui concerne les heures, l'inputbox renseigne l'heure actuelle par défaut mais vous pouvez remplacer aussi Format(Now(), "hh:mm") par "00:00" ou par "HH:MM". Dites ce que vous faites de manière à ce que je corrige dans le fichier de travail

Bonjour,

Je vous remercie, avec le code comme ceci tout fonctionne. Cependant, je pense faire en sorte d'ajouter les heures de début et de fin de livraison avec une liste déroulante permettant de choisir des créneaux d'heures entre 7:00 et 18:30 pour les heures de début et entre 7:30 et 19:00 pour les heures de fin (avec un pas de 00:30) afin de faciliter la lecture du planning à la fin.

J'ai pour se faire ajouter un "UserForm1" relié à une feuille "Données" que j'ai ajoutée et renseignant les créneaux possibles. Je souhaiterais remplacer la demande de saisie des heures manuelle par les données sélectionnées lors de l'affichage du UserForm1.

Je vous ajoute mon fichier Excel actuel avec les modifications évoquées

re

J'ai pour se faire ajouter un "UserForm1" relié à une feuille "Données" que j'ai ajoutée et renseignant les créneaux possibles. Je souhaiterais remplacer la demande de saisie des heures manuelle par les données sélectionnées lors de l'affichage du UserForm1.

Hum ...oui mais là il faut tout refaire.
Cela n'a d'ailleurs pas de sens d'utiliser une userform pour les heures et pas pour le reste.

Si vous utilisez uniquement votre fichier sous excel Windows et pas sur excel MAC, cela peut aussi s'envisager. Dites moi

Rem : ce qui est aussi possible c'est une inputbox qui par exemple utilise un chiffre avec ou sans décimal et qui renvoie l'heure. (exemple : 7 pour 7 heures, 7,5 pour 7:30) etc...
C'est surtout à vous de voir le plus pratique à l'utilisation.

NB : attention vos combobox font référence à la propriété Rowssource. A éviter car cela provoque quelques fois des soucis. Il faut préférer la méthode ADDITEM ou LIST

Par tout refaire vous entendez reprendre cette macro depuis le début ?

Rem : ce qui est aussi possible c'est une inputbox qui par exemple utilise un chiffre avec ou sans décimal et qui renvoie l'heure. (exemple : 7 pour 7 heures, 7,5 pour 7:30) etc...

C'est surtout à vous de voir le plus pratique à l'utilisation.

Cette solution est peut être la plus simple à mettre en place dans ce cas. A l'usage cela ne poserait pas de problème

NB : attention vos combobox font référence à la propriété Rowssource. A éviter car cela provoque quelques fois des soucis. Il faut préférer la méthode ADDITEM ou LIST

En quoi consiste cette méthode?

Si vous utilisez uniquement votre fichier sous excel Windows et pas sur excel MAC, cela peut aussi s'envisager. Dites moi

Si jamais je partage le fichier à quelqu'un utilisant un MAC les userform ne fonctionneraient pas c'est cela?

Par tout refaire vous entendez reprendre cette macro depuis le début ?

Tout se ferait via l'userform donc le code pourrait changer effectivement. On peut le faire si vous préférez.

Si jamais je partage le fichier à quelqu'un utilisant un MAC les userform ne fonctionneraient pas c'est cela?

Si l'utilisateur à la version 2011 (version dont je dispose d'ailleurs), cela fonctionnera. Si supérieur à cette version, il se pourrait que cela fonctionne mais pas en modification de code. Mais par précaution je préfère dire non.
Le plus simple avec les versions MAC 2016 et suivantes, est de consacrer une feuille comme formulaire en lieu et place d'USF. Ce qui peut aussi être moins compliqué.