Forum Clubic

Demande d'aide sur fichier excel

Bonjour à tous,

J’ai un petit projet que je galère à mettre au jour.

Sur un fichier excel avec différent onglet, j’aimerais y gérer un listing de client sous contrat.

Sur le premier onglet, j’aimerais que l’on puisse y voir ou y ajouter des nouveau client qui seront enregistré dans l’onglet 2.

Quelqu’un peux m’aider car je suis complètement perdu. J’ai essayer de le faire mais sans réussite, donc je joint mon document excel ( vierge niveau script ).

Merci à tous qui veulent m’aider.

Lien du fichier excel: [http://cjoint.com/?DAFt1Q1UTSk]

Cordialement,
David.

Salut.

Avant d’entamer le sujet, je remarque que les propriétés du fichier que tu as mis en ligne contiennent des informations nominatives. Libre à toi de faire ainsi si c’est délibéré, mais je préfère t’en informer au cas où c’est par inadvertance.


Ce que tu décris va nécessiter d’utiliser les macros et ça va te prendre un peu de temps. Je vois que tu dispenses des formations en informatique mais je ne sais pas à quel point tu es familier avec la programmation.

La 1ère chose à faire, c’est de “sauvegarder sous” et de choisir un format “Excel macro-enabled workbook”. Excel te proposera de sauvegarder ton fichier avec une extension xlsm.

Ton besoin contient 2 choses qui se découpent en plusieurs tâches :

  • aller chercher les données pour afficher un profil client existant
  • enregistrer un nouveau client (ou mettre à jour un client existant?)

On va commencer en s’intéressant exclusivement à l’appel d’un profil client existant (parce que la 2nde partie sera plus longue).

Comptes-tu taper le nom/prénom entier et exact dans ton champs texte ? Si oui, ça simplifie la programmation. Mais dans la pratique, au jour le jour, ça va être chiant à gérer. Ceci dit, ça peut être un point de départ et que tu amélioreras plus tard. Et puis je vois que ça correspond à la macro présente dans ton fichier.

Il faudrait que tu ajoutes un bouton à coté de ton champs texte et comme ça il te suffira de cliquer sur ce bouton pour lancer la macro qui va chercher le client. Je pense que tu es familier avec les boutons donc je ne détaille pas. Tu diras si tu as besoin de précisions.

Pour ce qui est de coder des macros, je vois que ce que tu as créé est un peu maladroit parce que tu as une boucle “For” qui va passer en revue chaque ligne. C’est le genre de solution qui marche mais qui :

  • galère quand il y a beaucoup de lignes à traiter
  • n’est pas conforme à l’esprit des macros en VBA

L’idée est d’utiliser au mieux les fonctionnalités d’Excel. Donc je te suggère de faire :

  • Enregistrer macro (le bouton rouge dans la barre de statut en bas à gauche)
  • aller dans la feuille “listing” faire une recherche (sélectionner la colonne, CTRL+F, etc.)
  • Arreter l’enregistrement de macro

Le résultat va ressembler à ceci :

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("listing").Select
    Columns("B:B").Select
    Selection.Find(What:="John Smith", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Sheets("Accueil").Select
End Sub

L’élément important est bien entendu le “Find”. Et voilà comment on a pu utiliser une fonctionnalité intégrée (donc optimisée) d’Excel plutôt que de faire une boucle For.

Ensuite, il y a le truc important qui change la vie des programmeurs en VBA :
il n’est presque jamais nécessaire de faire “select” ou “activate”.

En l’occurrence, on s’en fout d’activer une cellule. Ce qu’on veut, c’est trouver sa ligne. Du coup, au lieu de faire :
Selection.Find(blabla).Activate
on va faire :
ma_ligne = Selection.Find(blabla).Row

On va encore se débarrasser des sélections de feuilles Excel et on va obtenir un truc épuré du style :

ma_ligne = Sheets("listing").Columns("B:B").Find(blabla).Row

Et voilà comment on s’est épargné le changement de feuille et la boucle For. Il faut néanmoins se prémunir contre un nom pas trouvé. Donc on met une capture d’erreur comme ci-après :

Sub Macro1()
On Error GoTo ErrorHandler
    
  nom_recherche = Sheets("Accueil").Range("F11").Value
  
  ma_ligne = Sheets("listing").Columns("B:B").Find(What:=nom_recherche, After:=[B1], LookIn:= _
      xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
      xlNext, MatchCase:=False, SearchFormat:=False).Row
  
ErrorHandler:
If Err Then 
  MsgBox "Client non trouvé"
End If
    
End Sub

Note : Tu remarqueras également que j’ai mis
After:=[B1]

C’est une notation valide qui remplace Range(“B1”). C’est juste pour ne pas laisser le “ActiveCell” qui aurait posé problème. Donc puisqu’on cherche une info dans la colonne B, on va chercher “after B1”.

Je te laisse là pour le moment. Ca devrait t’inspirer pour la suite.

Tu peux soit continuer à développer le reste (prendre les infos de la feuille listing et les afficher dans l’accueil) dans la même fonction… ce que je ne recommande pas… soit transformer cette “sub” en “function” qui renvoie la ligne voulue et faire le traitement dans une fonction séparée.

Je reste à ta dispo pour la suite. Mais je préfère t’aider que faire tout le truc à ta place… conformément au dicton sur donner du poisson ou apprendre à pêcher.

Merci pour ton aide. Je n’ai pas de connaissance en programmation. Je me forme grâce à mon meilleur ami “google” mais je ne comprend pas tout.

Oui je souhaite rechercher les clients en ayant écrit comme élément de recherche son nom de famille complet.

Désolé d’être aussi nul

Débuter n’est pas une tare.

Je me permets de réinsister sur un point que je disais plus haut : quand tu commences à faire des macros en Excel, l’idéal est vraiment d’enregistrer la macro, de faire les manipulations, puis de plonger dans le code généré pour le comprendre et l’adapter.

N’hésite pas à poser des questions supplémentaires ! J’essaie d’aider sur les sujets Excel.

Avec la macro donnée plus haut, tu peux obtenir la ligne sur laquelle sont enregistrées les données du client recherché. Connaissant la ligne sur laquelle prendre les données du client, tu peux continuer le développement de la macro pour copier/coller les données depuis le listing vers les champs qui vont bien sur la feuille “Accueil”.

…et mets un bouton à cliquer pour appeler la macro ! Tu peux en insérer à partir du menu “Developer” qui est caché par défaut mais qu’on peut faire apparaitre depuis les options d’Excel :

  • gros bouton rond d’Excel
  • Excel options
  • Catégorie “popular”
  • Show developer tab in the ribbon

Merci de ton aide.

Voici un lien du fichier terminé: cjoint.com…

Sur la partie consultation, le VLOOKUP marche bien mais c’est galère de taper le nom complet et en respectant la casse. Tu pourrais envisager de transformer ton champ de recherche en une liste de validation. Comme ça tu choisis directement le client depuis un menu dropdown. Pas d’erreur possible.

  • tab “Data”
  • Data validation
  • Allow “List”
    =listing!B2:B1942

Si tu as vraiment beaucoup de clients et que c’est embêtant de chercher le bon dans une liste trop grande, tu peux combiner un champ texte et une liste de validation. A partir d’un événement worksheet_change, tu génères (sur une feuille cachée, par exemple) une liste filtrée donc raccourcie qui contient le contenu tapé dans le champ texte.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target = Range("F11") Then
    Call filtrerListeDeValidation
  End If
End Sub

Concernant la partie enregistrement, ça marche mais ça pourrait être mieux. Voici quelques conseils pour améliorer ta programmation.

  1. Par souci de lisibilité, il faut t’habituer à indenter ton programme. Tout ce qui est à l’intérieur d’un “if…endif” il faut le décaler (sélectionne les lignes voulues et appuie sur Tab). Pareil pour ce qui est à l’intérieur d’une boucle.

  2. Tu fais souvent appel à des feuilles en les référençant comme Sheet(“nomDeMaFeuille”). Si tu définissais des variables dès le début de ta macro, tu pourrais rendre ton code plus lisible et manipuler tes feuilles directement. Ca te simplifiera aussi la vie le jour où tu voudras changer le nom d’une feuille. Ainsi tu auras juste à modifier un seul endroit au début de ta macro.

ex:
dim sAccueil as Worksheet
Set sAccueil = Sheets(“Accueil”)

  1. gare aux paramètres d’application ! Tu joues avec ScreenUpdating et Calculation. Ca peut être justifié (quoique dans le cas présent, pas trop) mais si tu quittes la macro prématurément, ces paramètres d’application ne seront pas restaurés à leur valeur initiale. Une sortie prématurée peut être causée par une erreur d’exécution ou bien… et tu es coupable sur ce coup là… un appel à “Exit Sub” quand tu as laissé des champs obligatoires vides. Du coup, le calcul reste en manuel et le screenupdating reste désactivé.

Toucher aux paramètres d’application, c’est un recours à une méthode “sérieuse” pour ne pas dire radicale… Du coup, ça nécessite des précautions d’usage. Tu peux par exemple mettre un contrôle d’erreur qui restitue les valeurs. La même précaution est de rigueur pour le “Exit Sub”. En fait tu peux te faire une macro supplémentaire comme ceci :

'restore application parameters and end macro
Sub CleanupAndExit()
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  End 'arrête la macro
End Sub
  1. Quand tu rétablis ScreenUpdating et Calculation, il faut d’abord rétablir Calculation puis ScreenUpdating. Sinon tu rétablis l’affichage, puis le calcul automatique va changer des données qui vont nécessiter un nouvel affichage. Donc le ScreenUpdating se rétablit après le calcul.

  2. Quand tu copies tes données vers la feuille “listing” on se perd dans les boucles For. Il serait plus lisible de mettre une ligne de code par cellule copiée et de mettre un commentaire derrière chacune, précisant à quelle donnée ça correspond.

  3. Je ne sais pas si tu es familier avec la fonctionnalité “Offset” qui existe à la fois en fonction Excel et en fonction VBA. Cette fonction peut changer ta vie d’utilisateur Excel autant que la fonction VLOOKUP, notamment pour définir des zones de données de taille variable, y compris dans le name manager et dans les graphiques, pour s’adapter automatiquement à la quantité de données que tu as.