Note de frais avec barème et distances entre villes

Bonjour à tous,

étant néophyte en excel et vba, je vous soumet une note de frais avec plusieurs données qui doivent être calculées automatiquement.

L'utilisateur entre la puissance de son véhicule et la commune ou il passe la nuit le cas échéant.

En fonction du véhicule et de la ville de séjour il sera procédé au calcul.

Le prix du repas est quant à lui fixé à 17,50 €

Je vous remercie par avance pour votre contribution

Bonjour normane et le forum,

Voici ma proposition.

J'ai créé un nouvel onglet "Communes" où j'ai copié toutes les communes avec leurs codes. il faudrait également renseigner la colonne "montant nuitée" qui pourrait servir.

Les colonnes B, D et G se remplissent par listes déroulantes (voir onglet "Communes")
La colonne J (masquée) contient une formule pour calculer le barème kilométrique en fonction de la distance parcourue (colonne I)
La colonne O est alimentée par une formule pour retrouver le code de la commune de déplacement colonne G
La colonne P contient également une formule pour trouver le tarif de la nuitée en fonction du code de la commune (colonne O)

Il serait prudent de protéger la feuille en laissant les colonnes de A à I, K à N et R accessibles, afin de ne pas effacer les formules.

Il y a surement mieux à faire, mais pour l'instant !!
Cdt

Papy Henri

Re

Voici une autre proposition avec saisie des données dans un formulaire.
Cliquer sur "APPEL FORMULAIRE" et Valider la saisie. Les données sont transférées sans la feuille "Calcul frais".
Cdt

Papy Henri

Bonsoir normane et le forum

Pas de réponse, mais il est vrai que c'est le week-end!!!!
J'ai amélioré la version précédente en proposant dans le formulaire la possibilité de modifier les données d'un enregistrement.
Les enregistrements s'affichent dans la listBox. Au clic sur l'un d'eux, les données sont modifiables. On valide la modification avec le bouton "Valider Modif".
Bien entendu, on suppose que le véhicule est toujours le même.
Cdt
Papy Henri

Bonjour normane et le forum

étant néophyte en excel et vba, je vous soumet une note de frais avec plusieurs données qui doivent être calculées automatiquement.

Après plusieurs propositions , toujours pas de réponse. Normane ne respecte pas les règles de ce forum.
Cependant, cette nouvelle version que je propose devrait lui convenir. Et peut-être qu'elle servira à d'autres.
Particularités:
- possibilité de changer la puissance fiscale du véhicule selon les déplacements.
- les dates départ et retour sont à saisir à l'aide du calendrier qui s'affiche au clic sur le bouton de commande situé à droite du TextBox.
- les heures de départ et de retour sont à saisir sous la forme "hh:mn".
- le nombre de nuitées est calculé en fonction des dates de départ et de retour saisies à l'aide du calendrier. Elles sont à valider.
- le nombre de repas est calculé en fonction des dates et des heures de départ et de retour. Elles sont à valider.
- bouton pour imprimer le déplacement du mois.
- bouton pour effacer les données du mois écoulé.
Papy Henri

Bonjour AFEH,

J'ai regardé ton formulaire, c'est pas mal, et j'aurais quelques remarques.

Concernant les distances, l'idée du demandeur est bonne.

Avec le site http://www.distance2villes.com/ j'ai pu voir qu'il s'agissait des distances depuis Marseille.

J'ai cherché à savoir qu'est-ce qui faisait que les noms étaient préenregistrés:

Private Sub UserForm_Initialize()
Dim j As Integer, c As Range, n As Long
    Set ws = Sheets("Calcul frais")
    TriCommunes
        Me.ComboBox1.List = Array("5", "7", "8")
        Me.cbxVillDep.List = [Tableau1[Communes]].Value
        Me.cbxVillArr.List = [Tableau1[Communes]].Value

Mais les colonnes du tableau1 ne correspondent pas vraiment:

En attendant j'ai corrigé quelques bugs et rajouté le kilométrage, qui se met correctement en ajout mais en modif seulement si on change de ville

Bien sûr il faudrait rajouté les villes local de chaque itinérant.

J'ai fait une application sur les notes de frais il y a quelques années maintenant,

mais il n'y avait pas de contrôle sur les kilométrages.

Cdt

Désolé mais je n'ai pas vu de bug sur mon fichier.
Par contre sur celui que tu as fourni, tu as changé certains noms de textbox et combobox, mais les modifications n'ont pas été faites partout (voir dans le sub ajout: zone contrôles). Tu as également supprimé le calcul des repas!!
La demande initiale de normane était:

L'utilisateur entre la puissance de son véhicule et la commune ou il passe la nuit le cas échéant.
En fonction du véhicule et de la ville de séjour il sera procédé au calcul.

Mon formulaire tient compte:
- de la puissance du véhicule utilisé (modifiable éventuellement pour chaque déplacement).
- de la commune du lieu de déplacement qui est saisie dans le ComboBox5 après filtrage sur le Département (ComboBox4). Le choix de la commune détermine le code la commune (colonne O de la feuille "calcul frais", ainsi que la base de la nuitée, colonne P).
- du calcul de nombre de nuitées (différence entre date de retour et date de départ) et du nombre de repas (à revoir car je n'ai peut-être pas exploité toutes les possibilités).

Je n'ai pas étudié les distances entre la ville de départ et la ville du déplacement. Le nombre de kms est à saisir manuellement.
D'autant plus que notre demandeur normane reste muet depuis le début de son post!!!
Concernant le Tableau1, je l'ai créé dans la feuille "communes" en reprenant toutes villes répertoriées par le demandeur dans son fichier initial dans les feuilles "communes grand paris" et "villes de +hab". Ensuite j'ai affecté en colonne C le code pour chaque commune afin de définir le montant de la nuitée, en colonne D.
voici ma version "test4bis" avec une petite correction dans la formule en cellule P29.

Papy Henri

Bonjour à tous

Merci pour vos réponses, et désolé pour cette absence. Là ou j'étais, je ne pouvais malheureusement pas voir vos différentes contributions.

Je regarde en détail et vous fais un retour

merci encore

re bonjour,

Après avoir essayé les différents fichiers, j'obtiens les erreurs suivantes.

Visiblement la 'combox2' indique que la ville est introuvable, or je l'ai choisie dans la liste déroulante.

Le fichier normane31000-test semble fonctionner, je vais l'essayer en testant des notes de frais réelles et je reviens vers vous

Merci d'avance pour votre retour et surtout désolé pour cette absence indépendante de ma volonté

Bonjour normane et le forum

Effectivement le fichier fourni par fronck "autre-ndf-uf" ne fonctionne pas correctement. Je l'ai précisé dans ma réponse dde lundi à 15h15.

Essayer le fichier" normane31000-test4bis.xlsm" qui doit fonctionner correctement.

Une question: la distance parcourue est bien celle qui part de la ville de départ vers la ville de déplacement pour l'aller + la ville de déplacement vers la ville de retour?
Cdt
Papy Henri

Merci pour la précision, je vais tester le fichier que tu m'as préconisé.

Concernant ta question, les distances sont calculées entre la ville de départ et la destination multipliée par 2

exemple

départ de paris

arrivée marseille

kilométrage = 765*2 = 1530 kms

merci encore pour votre contribution

Bonjour à tous,

J'avais repris le fichier de AFEH, et je me souviens avoir apporté que les distances en kms entre Marseille et une autre ville se mettent automatiquement, ce que je confirme. Y'a pas d'erreur.

Ensuite j'ai corrigé 2 bugs. Quand je prends la dernière version de ton fichier avant le mien, çà bug au calcul du repas.

Et au calcul de la nuitée que j'ai corrigé avec ce code:

Sub CalculNuitees()
If Me.TextBox3.Value <> Me.TextBox1.Value Then
Me.TxtNuits = CDate(Me.TextBox3.Value) - CDate(Me.TextBox1.Value)
Else
Me.TxtNuits.Value = 0
Exit Sub
End If
End Sub

Ce qui fonctionne

J'ai aussi posé une question sur le préenregistrement des villes, mais n'ai pas eut de réponse.

Voilà mes bonnes intentions, mais AFEH ne semble pas avoir les mêmes à me charger sans arrêt.

Salut

Bonsoir

Pour Fronck: Loin de moi de vouloir offenser qui que ce soit sur ce forum. C'est tellement super de pouvoir s'entraider.
Toutes les bonnes volontés sont les bienvenues.
Je suis désolé que tu aies pris mes remarques pour "des charges" à ton égard.
Cdt

Papy Henri

Bonjour normane et le forum

Concernant ta question, les distances sont calculées entre la ville de départ et la destination multipliée par 2

exemple: départ de paris, arrivée marseille, kilométrage = 765*2 = 1530 kms

Eclaircissement: La ville de retour n'a donc pas d'importance, puisque le calcul des kms se fait de la ville de départ à la ville de destination * 2
On peut envisager de supprimer cette saisie?
Cdt
Papy Henri

Bonjour à tous

j'ai testé le fichier normane31000-test4bis

Il a l'air de fonctionner correctement, je suis d'accord qu'il faut supprimer la ville de retour et le calcul entre la ville de départ et la destination serait la bienvenue.

En tous cas, je vous remercie beaucoup pour votre contribuion

Bonjour normane et le forum.

Je suis absent jusqu'à jeudi soir, mais j'avais pris les devants en préparant une nouvelle version avec la suppression de la ville de retour. A mon retour, je la mets à disposition.

Cdt

Papy Henri

bonsoir normane et le forum

Comme convenu, voici la version "test5" sans prise en compte de la ville de retour.
J'ai cependant gardé dans le Tableau de la feuille "calcul frais" la colonne "Ville retour" qui se met par défaut à la valeur de la "Ville départ".
Le formulaire diffère un peu de l'ancien. On doit choisir le département de la ville de départ, puis la ville de départ dans la liste triée du département choisi.
Le nombre de kms parcourus sera à saisir manuellement. Par contre, si la ville de départ est toujours la même (ex: Marseille) on pourrait ajouter une colonne supplémentaire dans le Tableau1 de la feuille "Communes" et initialiser les distances entre toutes les villes de la liste et Marseille. L'automatisation de la distance serait alors possible.
A tester
Cdt
Papy Henri