Gestionnaire de location sur Excel

Bonjour dans cette nouvelle série sur le gestionnaire de location sur Excel nous allons voir comment créer un simple gestionnaire de location de film. Donc dans cette série nous allons voir comment créer des tableaux et comment les faire fonctionner entre eux. Nous allons voir comment utiliser la recherche avancé, comment ajouter des données dans un tableau et aussi comment le modifier ou le supprimer.

Création de notre base de données pour le gestionnaire de location sur excel

Alors dans cette première partie nous allons créer nos bases de données en parenthésé rendra notre gestionnaire de location déjà fonctionnel à elle seul. Puis dans les autres parties nous allons créer les formulaires qui nous permettront d’interagir avec nos bases de données.

Les pages pour le gestionnaire de location Excel

Nous allons créer 3 pages que nous allons nommer Film, Client et Location

Base de données Film de notre gestionnaire de location Excel

Nous allons créer notre base de données film en placent les titres suivent.

Dans notre base de données nous définir si le film est disponible ou pas pour le moment nous allons faire en sorte que quand nous allons ajouter un nouveau film il soit automatiquement disponible. C’est pourquoi nous allons placer la formule suivante

= « disponible »

Plus tard nous allons devoir comment modifier ou supprimer un film dans notre base de données par le biais d’un formulaire. pour cela nous allons devons savoir quelle ligne nous allons devoir modifier. Et pour faire simple nous allons automatiquement indiquer le numéro de ligne avec le code suivent.

=ligne()

Désormais à chaque fois que nous allons créer une nouvelle ligne dans notre tableau nous allons avoir le numéro de ligne.

Base de données Client de notre gestionnaire de location Excel.

Pour le client nous allons faire la même chose que la base de données des films donc je vais le garder vraiment simple, c’est-à-dire que je vais créer un numéro ID le nom du client, addresse, addresse mail et le nom. Ho oui il ne faut pas oublier le numéro de ligne.

Voila c’est tout pour la page client

Base de données Location de notre gestionnaire de location Excel.

Ici nous allons le faire une peut plus différent.

Nous allons ajouter le numéro d’ID de nos film et client qui louent le film. Nous utilisons le numéro d’ID car nous pouvons avoir plusieurs fois le même film mais le numéro ID reste unique.

Puis nous avons la date de location et dans la colonne AU nous allons place jusque quand nous allons louer le film. Dans notre exemple c’est 2 jour et cela nous allons le faire avec la formule suivante

=SI(E4<> » »;E4+2; » »)

Avec cette formule nous allons vérifier s’il y a une date de location et puis ajouter 2 jours.

Désormais que nous avons une date de retour prévu nous allons pouvoir vérifier s’il y a des retard et ça nous allons le faire avec la formule suivante.

=SI(ET(g4= » »;F4<AUJOURDHUI());AUJOURDHUI()-F4; » »)

Ici nous vérifions qu’il n’est pas encore rendu et que la date actuelle est plus grande que la date de retour. Si le film nous est rendu alors nous allons afficher disponible dans la colonne statuts avec la formule suivante.

=SI(ET(E4<> » »;G4= » »); »Location »; »disponible »)

Et nous terminer cette page en indiquant le numéro de ligne.

Travailler avec 2 tableaux

Désormais nous allons afficher dans notre base de données film si un film est disponible ou louer. Pour cela nous allons créer le code suivant dans l’emplacement status.

=SI(NB.SI.ENS(Tableau3[ID Film];[@ID];Tableau3[Status]; »location »)>0; »location »; »Disponible »)

Avec cette formule nous allons compter le nombre de fois qu’un film apparaît dans le tableau location et ou la colonne statuts = location. Si nous avons un résultat plus grand que 0 c’est-à-dire qu’il est loué donc nous allons afficher le texte Location sinon il est Disponible.

Gestionnaire de location ajouter un film 

Dans la partie précèdent nous avons créer nos base de données dans cette partie nous allons créer un formulaire pour ajouter un nouveau film et client à notre base de données. Et dans cette partie nous allons voir comment créer une nouvelle ligne dans un tableau et puis ajouter nos données dans le tableau et aussi créer automatiquement un numéro iD

Création du formulaire ajouter un film

Notre formulaire devrais avoir un zone de texte pour afficher le titre de deux bouton 1 pour ajouter et un autre pour retour que nous allons utiliser plus tard.

Déclaration des variables

Premiere chose importante nous allons placer le code pour controler les varaible avec option explicite. Puis nous allons déclarer notre variable DL en tant que nombre avec le code suivant

Le DL est pour « Dernière Ligne »

Contrôler s’il y a un titre.

Avant de commencer nous allons devoir contrôler qu’il y a bien un titre dans la zone de texte afin d’éviter d’ajouter des ligne vide.  Et cela nous allons le faire avec la condition if.

Ajouter une nouvelle ligne dans le tableau et définir le numéro de ligne

Ici c’est la partie la plus importante car ceci nous allons l’utiliser souvent. Alors nous allons pour commencer ajouter une nouvelle ligne à notre tableau. cela nous allons le faire avec le code suivant

Puis nous allons cherche sur quelle ligne elle se trouve cette nouvelle ligne avec le code suivant.

Voila notre petit combo est en place.

Ajouter le film à la base de données.

Avant de commencer nous allons placer le chiffre 2 dans la cellule A1 de chaque page. Se chiffre va servir pour créer un numéro ID unique.

Voila nous allons pouvoir ajouter le film dans notre base de données apres avoir confirmer l’ajout du film dans notre collection. Avec le code suivant.

Alé hop on test cela pour voir si tout fonctionne. Et on fait la même chose pour ajouter un nouveau client

Voila cette partie ce termine et nous avons donc vu comment ajouter une nouvelle ligne dans un tableau et comment y placer des information. Dans la prochaine partie nous allons créer un formulaire de recherche. Alé on se retrouve dans la prochaine partie.  

Rechercher un film dans notre gestionnaire de location sur Excel

Dans cette partie de notre gestionnaire de location nous allons voir comment rechercher des films dans un formulaire. cella veut dire que nous allons voir comment utiliser les zones de liste dans un tableau, exécuter un code automatiquement quand on indique des données et nous allons voire la méthode la plus efficace pour exécuter une recherche très facilement avec le filtre avancé.

Rechercher avec le filtre avancé.

Alors pour commencer nous allons voir la méthode la plus simple pour effectuer une recherche qui servira à alimenter une zone de liste. Car la particularité de notre recherche est quelle pourra afficher plusieurs résultats.

Pour cela nous allons devoir préparer la zone ou nous voulons placer les résultats rechercher et un emplacement de critère.

Alors ici nous allons copier tous les titres de notre tableau il est important de copier tous les titres de notre tableau même ceux qui sont masquer. Si nous se ne pas le cas alors cela ne fonctionnera pas. Une fois que c’est copier nous allons le coller à la même hauteur que le titre de notre tableau dans la cellule J3

Puis nous allons placer dans la cellule H3 le titre Film qui définira notre critère de recherche.

Désormais nous allons voir comment fonctionne le filtre avancé. Pour cela nous allons sur Données puis filtrer avancé.

Une fenêtre s’ouvrira et nous allons devoir placer les informations suivantes apres avoir cocher la case copier vers un autre emplacement. Dans la plage : nous allons placer notre tableau de film avec le titre, dans la zone de critères nous allons placer l’emplacement du critère avec le titre et dans copier dans : nous allons sélectionner uniquement les titres de l’emplacement où nous voulons copier le résultat. et on click sur OK

Alors la nous avons sélectionner tous les donnes qui coréspondent plus au moins à la zone de critéres à la zone de copier et comme nous n’avons pas de critées définit notre résultat serra tout.

Par contre si on place comme critères de film la lettre T alors nous allons filtrer tous les film qui commencent par la lettre T.

Par contre si nous voulons voulons rechercher tout les films qui contient la lettre T nous allons placer * devant la lettre et du coup nous allons recherche tous ce qui comporte la lettre T

 

Création du formulaire de recherche.

Alé il est temps de créer notre formulaire de recherche.

Alors notre formulaire de recherche serra composé d’une zone de Texte que nous allons nommée Txt_recherche avec un label. D’une zone de liste et de 3 boutons. Que nous pouvons décorer comme nous le voulons.

 

Rechercher avec une zone de texte.

Alors ici l’objectif et de placer dans sur la feuille Film dans la zone de critères les données qui se trouve dans notre zone de texte, puis de lancer le filtre au moment que nous placer des informations dans la zone de texte. Ainsi nous allons avoir au fur à mesure que nous plaçons nos informations des résultats qui s’affiche.

Première étape c’est de placer les informations dans notre cellule de critères pour cella nous allons faire une double clique sur notre zone de texte et placer le code suivant.

Alors ici nous si nous lançon notre code et que nous indiquons des informations dans notre zone de texte on peut voir qu’au moment ou nous tapons des mots elle s’affiche aussi dans la cellule de crêter. Désormais nous allons travailler à la seconde étape qui consiste à lancer le filtre avancé automatiquement au moment que nous indiquons des informations dans la zone de texte donc nous allons ajouter le code suivant.

Il est temps de lancer le code et voir se qu’il ce passe. Nous pouvons voir que les informations qui se trouvent dans notre zone de texte s’afficher dans la zone de critère et que le filtre avancé se lance a chaque fois que nous placer une nouvelle lettre dans la zone de texte.

Afficher le résultat rechercher dans la zone de liste.

Il est temps de placer les informations rechercher dans notre zone de liste. Mais avant de faire cela nous allons devoir définir notre zone de recherche à afficher dans notre liste. Pour la nous allons créer un nom recherche_film qui comporte la formule suivante

=DECALER(Film!$J$4;0;0;NBVAL(Film!$J$4:$J$1048576);4)

Alors nous avons dit avec cette formule que nous nous plaçons dans la cellule J4 et que nous sélectionnons ne nombre de valeurs non vides vers le bas donc nous allons compter le nombre de cellule qui ne sont pas vide à partir de la cellule J4 à J1048576 puis nous allons nous déplacer de 4 colonnes.

Voilà nous avons notre sélection à afficher

Désormais que nous avons définit notre zone de résultat nous allons pouvoir l’afficher dans notre liste. Pour cela nous allons faire un double click dans notre zone de texte et place le code suivant.

Désormais on lance le tous pour tester, Oo on constate que la recherche fonctionne mais il y a uniquement le numéro d’ID qui s’affiche. C’est parce que nous n’avons pas configuré notre zone de liste.

Pour cela nous allons dans les propriété de notre zone de liste et configurer comme suite.

ColumnCount = 4

Columnwidths = 0 pt;150 pt;75 pt;0 pt

Columnheads = true

Voila on test à nouveau et on peut voire que tous fonctionne.

 

Et donc voila cette partie est terminé, dans la prochaine partie nous allons voir comment modifier ou supprimer un film. D’ici la je vous invite à créer la même chose pour vos données client et on se retrouve dans la prochaine partie. 

Modifier et supprimer dans notre gestionnaire de location sur Excel

Dans la partie précédente nous avons vu comment créer une recherche qui affiche plusieurs résultat grâce au filtre avancé qui sont très mais alors très puisante, dans cette partie nous allons voir comment modifier ou supprimer les résultat rechercher.

Création du formulaire de modification.

Ici rien de compliquer nous allons créer un formulaire avec une zone de texte que nous allons nommée Txt_modifier un label pour le titre et un bouton. Nous allons aussi ajouter un label 2 que nous allons nommée la_ligne pour afficher notre numéro de ligne à modifier.

Afficher les informations dans notre formulaire modifier.

Alors ici le but c’est de rechercher un film dans notre formulaire de recherche, puis de sélectionner un film dans notre zone de liste ensuite cliquer sur le bouton modifier pour faire apparaître le formulaire de modification que nous venons de créer. Les informations du film à modifier devrons s’afficher dans le formulaire de modification ainsi que sont emplacement.

Pour commencer nous allons faire un petit contrôle pour vérifier qu’il y a bien un film de sélectionner. Puis nous allons prendre les informations de la sélection dans les différant colonne pour les placer dans notre formulaire de modification. Et pour finir afficher notre formulaire de modification.

Tout ça avec le code suivant.

Si on test nous devons avoir le film sélectionner dans notre recherche qui s’affiche dans le formulaire modifier.

Modifier notre base de données avec le formulaire.

Ici la difficulté pour modifier notre base de données c’est de savoir ou se trouver l’information que nous voulons modifier. C’est pourquoi nous avons placer dans la première partie de se cours l’emplacement avec la formule =ligne().

Grâce à cette information nous savons sur quelle ligne se trouve le film que nous voulons modifier et la colonne hé bien elle est déjà connue c’est la colonne C donc désormais nous avons toutes les information dans notre exemple le film ce trouve sur la ligne 10 donc la cellule C10.

Il nous reste plus qua confirmer la modification du titre et la modifier et fermer le formulaire de modification avec le code suivant.

Supprimer un film dans notre gestionnaire de location Excel

Pour supprimer nous allons faire comme la modification c’est-à-dire rechercher la ligne à supprimer demander la confirmation puis supprimer. Sauf que nous allons ajouter une protection afin de ne pas supprimer tous s’il n’y a qu’un seul film.

Avec le code suivant.

Et si on test le code ça nous donne

Et voila nous avons vue comment modifier et supprimer un film. Mais le plus important c’est que nous avons vue qu’il faut bien planifier ça base de données afin de pouvoir réaliser cela. Dans la prochaine partie nous allons voir comment louer un film et comment réceptionner un film louer. D’ici là je vous invite à créer une modification et suppression avec votre base de données client. Salut tout le monde

Alléger le code de notre gestionnaire de location sur Excel

Dans cette partie nous allons voir comment réduire le nombre de code VBA répétitif que nous utilisons ceci afin d’alléger et de rendre notre code vba plus lisible mais se n’est pas tout car ceci vas aussi nous permettre de modifier 1 seul fois le code et non pas recherche ou elle pourrait bien se situé.

Création du module.

Alors pour optimiser notre code nous allons créer un module que nous pouvons nommer comme nous le voulons ce n’est pas vraiment important. Puis dans ce module nous allons créer notre procédure que nous allons nommer lookup_film qui veut dire rechercher film. Dans cette procédure nous allons placer le code que nous avons utiliser pour rechercher un film dans la zone de texte ce qui devrais nous donner le code suivant.

Faire appel à la procédure lookup_film

Pour faire appel à notre procédure créer nous allons remplacer le code existant sur notre zone de texte par le code.

Tester votre code vous allez voir que rien à changer tout fonctionne encore comme avant. Nous allons désormais remplacer supprimer nos recherche et la faire remplacer par l’appelle a la procédure lookup_film

L’avantage de cela.

Désormais nous allons voir l’avantage. Dison que nous voulons modifier notre page film et lui ajouter deux colonne et ajouter dans la colonne I3 le titre Status et dans la colonne J3 ID. nous allons devoir changer notre code de recherche partout ou nous l’avons placer avec les nouveau critère et ID de plus nous allons devoir remplacer l’emplacement ou il faut placer notre résultat de recherche. Ceci demandera beaucoup de travaille si et il y a de grande risque de faire des erreurs.

Avec cette méthode de call il faut modifier 1 seul fois le code et c’est tout

Voila vous venez de voir une méthode facile d’optimisation de code est surtout éviter de répéter un code et donc alléger votre fichier Excel

Louer un film de notre gestionnaire de location sur Excel

Dans notre gestionnaire de location sur excel nous avons vue comment rechercher, modifier et supprimer des données dans une base de données avec du VBA. Désormais nous allons louer nos films avec du code VBA bien sur. Alors ici le but est asser simple nous allons chercher 1 film et puis nous allons controler s’il est déjà louer oui ou non. Si il est libre alors nous allons pouvoir le louer sinon pas. en bonus nous allons voir comment placer des filtre de recherche

Formulaire de location.

Alors pour commencer nous allons créer notre formulaire de location qui comportera de 3 label 1 combobox et un bouton.

Récupérer les informations.

Désormais nous allons devoir récuperer les donner de notre zone de liste et les placer dans notre nouveau formulaire sauf que nous allons placer une condition si le film est disponible c’est-à-dire pas encore louer. Pour cela rien de plus facile car nous allons faire cela avec le code suivant.

Ici nous demandons a notre code de vérifier dans la zone de liste la colonne nr 2 s’il contient le mot Location, si c’est le cas il ne fait rien sinon on copie le numéro d’id dans le label Film_id et le nom dans le label Film_Name et on affiche le formulaire.

Sélectionner le client

Il nous reste plus qua placer le nom du client dans notre zone de texte. Pour cela nous allons nommer notre tableau client en client bien sur et le placer comme rowsource de notre combobox. Puis nous allons masquer se que nous ne voulons pas voir. Donc uniquement afficher le nom.

Valider la location.

Désormais il est grand temps de valider la location et pour cela nous allons contrôler s’il y a tous les information besoin puis nous allons demander la confirmation et pour finir nous allons registrer la location au client. Pour finir nous allons relancer une recherche pour afficher le résultat louer pour se film. Cela nous allons le faire avec le code suivant.

Afficher uniquement les films disponibles.

Nous allons voir comment rendre notre formulaire de recherche un peut plus pratique en affichant uniquement les films disponibles. Poru cela nous allons sur notre page film  et nous allons placer dans la cellule I3 le titre status. Puis nous allons placer 3 bouton d’option sur notre formulaire. comme suite.

Nous allons faire une double clic sur la première option qui est disponible et place le code suivant.

Nous allons modifier le code de façon qu’il affiche  le résultat au moment que nous sélectionnons une option. C’est-à-dire si j’indique un film alors je vois tout ceux qui sont loué ou disponible mais au moment ou je clique sur un filtre alors je veux qu’il ce met à jour. Cela nous allons le faire avec le code suivant.

Ceci nous allons le faire pour la location aussi.

Et si nous voulons tout afficher nous allons utiliser le code suivant

Voila cette partie est terminé nous avons donc vue comment attribuer un film à un client et ainsi changer le statuts disponible en location et nous avons aussi vue comment ajouter des filtre à notre formulaire de recherche

7# Gestionnaire de location sur Excel réceptionner un film.

Dans notre gestionnaire de location sur Excel nous avons ajouter un film, nous avons modifier un film et nous avons louer un film il est désormais tems de réceptionner les films louer.

Préparation de la recherche de location.

Pour faire la recherche nous allons faire à nouveau une recherche avec les filtre avancé sur notre page Location.

Pour cela il faut définir les critères qui seront ID Film, Status et Retard. 1 seul critère restera fixe est c’est celui du status que nous allons mettre en Location car nous voulons uniquement rechercher les films qui sont en location.

Voila désormais il nous reste plus qua effectuer la recherche et tout comme la recherche de film nous allons placer cette recherche dans un module que nous allons nommer lookup_location.

Désormais nous avons notre recherche qui affiche les information d’un ID de film qui est en location.

Le formulaire de retour

Nous allons créer un formulaire pour les films de retour qui devrais ressemble à ceci.

Quelque information du formulaire :

• Nom du formulaire = Retour
• Label Client ID = Nom_client
• Label Retard = Location_retard

Rechercher un film louer

Alors ici le but est quand nous recevons un film de retour nous allons placer le numéro id du film puis nous allons lancer la recherche et afficher les informations si le film est en location comme le Numéro id du client et les nombre de jour en retard.

Par contre si nous lançon le code les informations ne s’affiche pas dans le formulaire pour cela nous allons modifier notre code lookup_location en ajoutant se qui manque.

Voila désormais cela devrais fonctionner.

Modifier le statut de location.

Désormais que nous avons nos informations nous pouvons réceptionner le film louer et le rendre disponible à nouveau pour la location en cliquant sur le bouton Retour.

 Ajouter le bouton Retour à la recherche

Nous allons ajouter un nouveau bouton sur notre formulaire recherche pour afficher le formulaire retour.

Et nous allons afficher notre formulaire avec le code suivant

Voila désormais nous avons réceptionner les films louer dans la prochaine et dernière partie nous allons voir comment sécuriser notre code vba afin que les utilisateurs n’aient pas accès au code.

8# Gestionnaire de location sur Excel Protéger le code VBA.

Dans cette dernière partie de notre gestionnaire de location sur Excel nous allons voir comment protéger notre code vba afin que les utilisateurs n’aient pas accès et ainsi ne pourront pas modifier le code sans le bon mot de passe.

Création de la page home

Alors avant de commencer nous allons créer une nouvelle page que nous allons nommer home. Et sur cette page nous allons placer un bouton afin d’ouvrir notre formulaire de recherche.

Et pour faire appel à notre formulaire nous allons placer le code suivant dans notre bouton

Masquer les pages

Désormais nous allons commence par super masquer nos pages FILM, Client et Location au moment d’ouvrir notre ficher. Pour cela nous allons devoir indiquer dans le code Workbook avec l’event open le code suivant.

Cliquer sur exécuter le code et si tout va bien les 3 pages devrais être masquer.

Protéger son code vba

Il est grand temps de protéger notre code et cela nous allons le faire dans outils à propriétés de VBAproject…

Dans l’onglet Protection cocher la case verrouiller le projet pour l’affichage c’est cette option qui fera en sorte de masquer les Code VBA et n’oublier pas de placer un mot de passe et de le confirmer par la suite.

! attention n’oublier ou ne perdez pas le mot de passe car il est impossible de le récupérer si vous l’oublier vous pouvez recommencer votre fichier, même Microsoft est incapable de vous aider.

Sauvegarder votre ficher fermer et ouvrer le à nouveau. Désormais quand vous allez sur votre page de développeur vous ne voyez plus rien. Si on clique sur le signe + pour afficher le projet un mot de passe est demander et c’est la seule façon de faire afficher le code utiliser.

Voila notre gestionnaire de location sur excel est terminé dans cette longue série nous avons vu pas mal de chose qui sont utiliser souvent dans un formulaire qui est lier à un tableau comme ajouter modifier ou rechercher des données dans un tableau grâce à un formulaire. je pense même que cette série est un très bon fil conducteur pour ceux qui débute avec du VBA.  J’espère que cela vous à plus et je vous retrouve dans un autre cours.