VBA Amélioration macro fichier association

david84

XLDnaute Barbatruc
Bonjour à tous,
J'ai fait il y a quelques temps un fichier permettant l'enregistrement et le suivi d'associations sportives : enregistrer les nouvelles associations, mise à jour de leur coordonnées (adresse, tel , mail,...) possibilité de les contacter par mail,...
Ce fichier a été fait à l'aide de formules et d'enregistrement de macros, auxquelles s'étaient ajoutées une ou deux macros faites par des personnes de ce forum (notamment JNP que je remercie au passage).
Il fonctionne correctement mais j'aimerai maintenant optimiser les codes afin de le rendre plus rapide et de nettoyer les codes des "sélect" et autres informations parasites dues à l'enregistreur de macro, et au besoin reformuler certaines parties de la macro afin de gagner en efficacité.
Pour ce faire et pour simplifier le travail, je voudrais traiter feuille par feuille et avancer au fur et à mesure.
Le but est que je comprenne la manière de faire pour pouvoir ensuite le faire moi-même (je vais le prendre comme des travaux pratiques afin de comprendre un peu plus le VBA).
Dans le fichier ci-joint, ne sont présentes que 3 feuilles :
- une feuille "création" permettant de créer une nouvelle fiche
- une feuille "BD" dans lesquelles les différentes associations sont regroupées
- une feuille "listes" contenant l'ensemble des listes mises à jour.

La macro que je vous demande de m'aider à traiter se trouve dans le module et permet d'enregistrer une fois tous les champs remplis la nouvelle fiche dans la feuille "BD" et de mettre à jour la feuille "listes" afin que les menus déroulants soient à jour.
J'ai bien sûr essayé de le faire moi-même mais il y a toujours quelque chose qui cloche. Je veux donc voir comment vous procéderiez et ne manquerai pas de vous questionner pour comprendre les procédures utilisées.

Je me tiens à votre disposition pour d'éventuelles précisions et vous remercie de votre collaboration.
Cordialement
 

Pièces jointes

  • fichier_NvelleFicheV2.zip
    65.3 KB · Affichages: 231
  • fichier_NvelleFicheV2.zip
    65.3 KB · Affichages: 234
  • fichier_NvelleFicheV2.zip
    65.3 KB · Affichages: 236
Dernière édition:

JNP

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re :),
Concernant la macro de fermeture, chez moi, elle ferme le classeur mais pas l'application:eek:.
Celle du #21 devrait le faire.
J'ai vu également que tu m'avais placé une petite sub dans this workbook "application.enable.Events=True"
Quel est son rôle ?
Le but etait d'empêcher les événementielles dans Before_Close. Le problème est qu'après la fermeture, le code que je t'avais fourni ne les relançais pas :confused:.
D'où la petite sub pour les rétablir ;).
Concernant l'USF, je trouve que c'est une très bonne idée mais que je ne maîtrise pas pour l'instant (mais cela va ma donner l'occasion de m'y mettre;)).
C'est bien pour celà que je te l'ai proposé, c'est bien plus souple que des formulaires en feuille, et tu verras que tu maitriseras assez vite :D.
Une chose cependant : où sont placées les listes des 2 premiers menus déroulants présents dans l'USF ?
Je ne les vois pas:confused:.
Ahhhhhhhhhhhhhhhhhh :p.
Voilà comment je veux économiser 9 modules et 3 feuilles :D...
Tout simplement, il scanne ta feuille BD et (grace au Dico de JB) te le restitue dans le combo.
De plus, un petit Find.Next permet de restituer les associations en fonction de la discipline.
Et tu as vu combien de lignes de code ça représente...
Pour charger les communes et le code postal adéquat, change les 2 premières sub par ces 3 là avec la déclaration en tête
Code:
Dim AChercher As Range
Private Sub Commune_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Set AChercher = Range("Tableau4[COMMUNES]").Find(Commune, lookat:=xlWhole)
If AChercher Is Nothing Then Exit Sub
Call Charge(CodePostal, Filtre(Range("Tableau4[COMMUNES]"), Range("Tableau4[CODES POSTAUX]"), Commune))
CodePostal.ListIndex = 0
End Sub
Private Sub Discipline_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Set AChercher = Range("Tableau4[DISCIPLINES]").Find(Discipline, lookat:=xlWhole)
If AChercher Is Nothing Then Exit Sub
Call Charge(Association, Filtre(Range("Tableau4[DISCIPLINES]"), Range("Tableau4[ASSOCIATIONS]"), Discipline))
Association.ListIndex = 0
End Sub
Private Sub UserForm_Initialize()
Call Charge(Discipline, Range("Tableau4[DISCIPLINES]"))
Call Charge(Association, Range("Tableau4[ASSOCIATIONS]"))
Call Charge(Commune, Range("Tableau4[COMMUNES]"))
Call Charge(CodePostal, Range("Tableau4[CODES POSTAUX]"))
End Sub
Après, il n'y aura pas besoin d'enregistrer les nouvelles assocs ou disciplines, le simple fait de les décharger dans le tableau les ajoutera ;).
Bonne nuit :cool:
 

david84

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re

Citation:
Envoyé par david84 Voir le message
Concernant la macro de fermeture, chez moi, elle ferme le classeur mais pas l'application.
Celle du #21 devrait le faire.
Pourquoi le #21 ? Tu veux sûrement dire le #42, et je te confirme que j'ai testé sur mon ordi perso et le professionnel et que le classeur se ferme mais pas l'application;).
Code:
Ahhhhhhhhhhhhhhhhhh .
Voilà comment je veux économiser 9 modules et 3 feuilles ...
Tout simplement, il scanne ta feuille BD et (grace au Dico de JB) te le restitue dans le combo.
De plus, un petit Find.Next permet de restituer les associations en fonction de la discipline.
Et tu as vu combien de lignes de code ça représente...
Pour charger les communes et le code postal adéquat, change les 2 premières sub par ces 3 là avec la déclaration en tête

Alors là, tu m'as scotché :eek:!
J'ai mis un quart d'heure à trouver où tu avais mis le code !
C'est vraiment bien cet outil:D!
Bon j'ai fait comme tu m'as dit et j'avais l'ensemble des listes mais je n'ai pas trouvé comment valider une nouvelle fiche. Je n'ai donc pas pu me rendre compte des modif opérées dans la BD.
Mais bon, j'ai compris que ce n'est que le début et que tu voulais simplement me faire comprendre l'intérêt de la chose:).
Bon j'ai compris : fini l'amateurisme, on passe au choses sérieuses :D!
Je vais me pencher plus attentivement sur les codes que tu as utilisés et tenter de comprendre comment tu procèdes.
Merci et A+
 

JNP

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re :),
Pourquoi le #21 ? Tu veux sûrement dire le #42, Bon j'ai fait comme tu m'as dit et j'avais l'ensemble des listes mais je n'ai pas trouvé comment valider une nouvelle fiche. Je n'ai donc pas pu me rendre compte des modif opérées dans la BD.
Mais bon, j'ai compris que ce n'est que le début et que tu voulais simplement me faire comprendre l'intérêt de la chose:).
C'est les soldes à 50%, #21 pour #42 :p... Non, c'est que lorsque tu es en réponse, tu ne vois plus les #, alors j'en ai mis un au hasard, comptant le corriger juste après validation, et j'ai zappé :D.
Bon, c'est pas dramatique, mais comme dit en MP, c'est certainement le PERSONNAL.XLSB qui merdouille ;)...
Exactement, le déchargement dans la feuille n'est pas encore prévu, ni le N° de fiche d'ailleurs. C'était, comme tu le dit, pour te mettre l'eau à la bouche :p... Et vu le mode de raisonnement, ça t'explique aussi pourquoi je barbottais un peu dans tes codes précédents :D.
Par contre, pour l'instant, pas de retour de Skoobi et Pierrot si l'utilisation des entêtes de tableau fonctionnent sous 2003 (le reste du code doit être compatible).
Bonne journée :cool:
 

JNP

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re :),
Une version un petit peu plus complète ;).
Bon WE :cool:
 

Pièces jointes

  • New_BD_asso.xlsm
    125.6 KB · Affichages: 143
  • New_BD_asso.xlsm
    125.6 KB · Affichages: 162
  • New_BD_asso.xlsm
    125.6 KB · Affichages: 164

skoobi

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Salut les gars :),

Par contre, pour l'instant, pas de retour de Skoobi et Pierrot si l'utilisation des entêtes de tableau fonctionnent sous 2003 (le reste du code doit être compatible).

Non, les entêtes de colonne, du-moins la syntaxe entre crochet n'est pas reconnu sur excel 2003 :(.
En tout cas David, avec tout ce que l'on vient de te montrer, j'espère que tu ne va pas en rêver du VBA :D:p.

Bon week-end les amis :)
 
Dernière édition:

JNP

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

salut Skoobi
Non, les entêtes de colonne, du-moins la syntaxe entre crochet n'est pas reconnu sur excel 2003 :(.
C'est vraiment dommage...
Les avantages :
Chaque nouvelle ligne à la fin du tableau s'incorpore au tableau, et si il y a une formule dans une colonne, celle-ci se copie dans la ligne supplémentaire.
Plus besoin de End(XLUp), la tête de colonne sélectionne directement la colonne du tableau.
La mise en page (ligne 1 sur 2) s'agrandi avec le tableau.
Etc.
Évidemment, mon code peut être traduit en 2003 en oubliant ces avantages, et en alourdissant la syntaxe...
Il sera peut-être intéressant de le faire en fin de projet pour le rendre compatible ;).
En tout cas, merci pour ton retour :).
Bon WE :cool:
 

Modeste

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Bonjour David, Pierrot, Skoobi, JNP,

Je suis passé par ici, avant tout pour assister aux premiers pas de David en VBA (il "se tient" déjà bien sur ses gambettes et va courir d'ici peu :D)

Ceci dit, je n'ai pu m'empêcher de relever les difficultés liées aux particularités de syntaxe de 2003 et 2007. Ce dont JNP parlait ici:
JNP à dit:
Chaque nouvelle ligne à la fin du tableau s'incorpore au tableau, et si il y a une formule dans une colonne, celle-ci se copie dans la ligne supplémentaire.
... et qu'on trouve dans Insertion > Tableau (si j'ai bien compris) existait en 2003 sous Données > Liste > Créer une Liste ...

S'il s'agit bien de la même chose, les ListObjects évoqués par Pierrot (et qui existent toujours en 2007) permettront sans doute de rendre un code "portable" sur les deux versions (mais en faisant le deuil de Xl 2000 au passage !)

Pour le reste David étant déjà dans les starting blocks, bien entouré de tonton Pierrot, oncle Skoobi et parrain JNP, moi je retourne trottiner et essayer de mettre un pied devant l'autre sans m'emmêler les pinceaux :rolleyes:
 

JNP

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re :),
S'il s'agit bien de la même chose, les ListObjects évoqués par Pierrot (et qui existent toujours en 2007) permettront sans doute de rendre un code "portable" sur les deux versions (mais en faisant le deuil de Xl 2000 au passage !)
Je suis un peu septique, sur ce fil, on avait pu voir un rare exemple où les macros 2003 ne passait pas sous 2007, et il s'agit justement des ListObjects :eek:...
Bonne journée :cool:
 

david84

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Bonjour JNP:), Skoobi:), Pierrot:), Modeste:),
JNP, je te fais comme prévu le retour sur le dernier document.
La structure de l'USF proposé me paraît adaptée (à voir si elle le sera toujours au fur et à mesure de l'avancement du projet mais je n'ai pas le vécu nécessaire pour avoir réponse à cette question).
J'ai commencé à noter différents aménagements à prendre en compte dans la partie "créer une nouvelle fiche" (je pense qu'il vaut mieux procéder par étape en travaillant sur "créer une fiche", puis voir ensuite les parties "modifier" et "supprimer" une fiche, mais à toi de me dire si cela te convient;) :
- inclure la possibilité de créer une nouvelle discipline lorsque celle-ci n'apparaît pas encore dans la liste. De mon côté, j'ai vérifié si la propriété "style" sélectionnée était bien "fmstyleDropDownCombo" (ok) et tenté de me servir de AddItem dans la macro (mais sans succès, je patauge:eek: !).
- enlever la liste déroulante dans la zone de texte "association". Tu avais relevé cette bizarrerie par MP et en avait deviné la raison (moyen de vérifier que le nom rentré n'appartient pas à la liste) mais dans les faits, le collègue me dit que c'est inutile.
- liste des communes : même remarque que pour "discipline"
- enlever la liste déroulante des codes postaux et soit affichage direct du code postal lorsque la commune est sélectionnée (donc commune déjà présente dans la liste déroulante), soit possibilité de rentrer un nouveau code postal.
- rendre la zone de texte "mail" activable afin de permettre d'envoyer un mail par ce biais.
Il restera ensuite à régler certains points mais qui ne sont pas propres à la partie "créer" une fiche;).

Pour l'instant, je n'ai donc pas abordé les parties "modifier" et "supprimer" une fiche mais je voulais tout de même déjà attirer ton attention sur un point sur les listes déroulantes proposées dans la partie "modifier" : une fois la discipline sélectionnée, le collègue a la possibilité soit de sélectionner une association, puis la commune, soit de sélectionner une commune puis l'association. Cette procédure lui est très utile et je voulais attirer ton attention dessus au cas où tu dois inclure cette donnée dès maintenant dans la constitution des macros.
De mon côté, j'ai récupéré un code de JB permettant de faire cela. Je vais te placer le fichier sur le site dans la journée au cas où cela peut te servir.

Bien entendu, je continue à chercher de mon côté et à tenter de comprendre les macros proposées. J'ai déjà commencé ce WE lors de la découverte et la manière de créer un USF mais je vais les reprendre tranquillement et me permettrait de te questionner lorsque j'aurai des interrogations:rolleyes:.
Un grand merci:):).
A+
 

Modeste

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Bonjour,

Merci JNP, pour les infos ... Ca ne semble effectivement pas gagné :(
Ceci dit, en consultant le lien que tu renseignais sur msdn, j'ai lu:
Developers who have written code by using the ListObject object in Office Excel 2003 might need to make adjustments if that code uses ListObject.InsertRowRange.
... ça semble limiter les difficultés à cette propriété et en terme "d'ajustements".
A part ça, si j'ai bien vu, il leur a fallu trois mois pour trouver une réponse!? Voilà qui m'inquiète beaucoup plus et pour éviter de me mélanger, tout compte fait, le '.end(xlup)' a tout de même des avantages certains, de même que les plages nommées.
Sur ce, je vous laisse travailler en paix ;) Encore merci et bon courage à David!
 

JNP

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re :)
J'ai commencé à noter différents aménagements à prendre en compte dans la partie "créer une nouvelle fiche" (je pense qu'il vaut mieux procéder par étape en travaillant sur "créer une fiche", puis voir ensuite les parties "modifier" et "supprimer" une fiche, mais à toi de me dire si cela te convient;) :
Pas très logique, vu que tout découle de la même interface, qui d'ailleur est déjà opérationnelle. Je ne sais pas si tu as remarqué, mais dès que la discipline existe déjà associée avec une association, Créer se grise, et c'est Modifier et Supprimer qui sont actifs :).
- inclure la possibilité de créer une nouvelle discipline lorsque celle-ci n'apparaît pas encore dans la liste. De mon côté, j'ai vérifié si la propriété "style" sélectionnée était bien "fmstyleDropDownCombo" (ok) et tenté de me servir de AddItem dans la macro (mais sans succès, je patauge:eek: !).
Le simple fait de saisir une discipline la rajoute directement. A voir si il faut en plus signaler à l'utilisateur que c'est une nouvelle discipline (pour éviter les problème singulier/pluriel par exemple) et lui demander validation :rolleyes:.
- enlever la liste déroulante dans la zone de texte "association". Tu avais relevé cette bizarrerie par MP et en avait deviné la raison (moyen de vérifier que le nom rentré n'appartient pas à la liste) mais dans les faits, le collègue me dit que c'est inutile.
On peux garder un contrôle et vérifier qu'il n'y a pas doublon ;)
- liste des communes : même remarque que pour "discipline"
Même motif, même punition, il faut le mettre en Exit et non en Change :p.
- enlever la liste déroulante des codes postaux et soit affichage direct du code postal lorsque la commune est sélectionnée (donc commune déjà présente dans la liste déroulante), soit possibilité de rentrer un nouveau code postal.
C'est pour cela que j'ai ajouté une feuille avec les communes du 84 (seulement 151, j'en ai 513 chez moi :eek:). A voir si après, on ajoute les communes hors département par saisie, ou si on ajoute les départements limitrophes, voir France entière, mais à ce moment là, un petit combo Département près réglé sur 84 me paraitrait utile :D.
- rendre la zone de texte "mail" activable afin de permettre d'envoyer un mail par ce biais.
La partie Mail, je pensait plutôt faire un USF avec recherche par discipline, un bouton Ajout pour pouvoir sélectionner plusieurs disciplines, puis avoir des champs Objet, Corp de message, PJ, etc. pour un envoi par Outlook en scindant par groupe de 20, par exemple, afin d'éviter d'être blacklisté :p.
Pour l'instant, je n'ai donc pas abordé les parties "modifier" et "supprimer" une fiche mais je voulais tout de même déjà attirer ton attention sur un point sur les listes déroulantes proposées dans la partie "modifier" : une fois la discipline sélectionnée, le collègue a la possibilité soit de sélectionner une association, puis la commune, soit de sélectionner une commune puis l'association. Cette procédure lui est très utile et je voulais attirer ton attention dessus au cas où tu dois inclure cette donnée dès maintenant dans la constitution des macros.
On peux très bien utiliser ma fonction filtre en l'imbriquant.
De mon côté, j'ai récupéré un code de JB permettant de faire cela. Je vais te placer le fichier sur le site dans la journée au cas où cela peut te servir.
Bon courage :cool:
 

david84

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re:)

Pas très logique, vu que tout découle de la même interface, qui d'ailleur est déjà opérationnelle. Je ne sais pas si tu as remarqué, mais dès que la discipline existe déjà associée avec une association, Créer se grise, et c'est Modifier et Supprimer qui sont actifs
OK, on va suivre ta démarche;).

Le simple fait de saisir une discipline la rajoute directement.
Oui, une fois la modif effectuée (Exit au lieu de Change), cela le fait (j'ai remarqué au passage que la tabulation ou la validation par "entrée" faisait passer la sélection à "agrément" en sautant "association"...).
Edit : je pense avoir compris comment rectifier : vérifier la position du contrôle dans l'ordre de tabulation de la feuille (propriété TabIndex) et vérifier que la propriété Enabled est sélectionnée sur True (vérifier également TabStop aussi je pense).

A voir si il faut en plus signaler à l'utilisateur que c'est une nouvelle discipline (pour éviter les problème singulier/pluriel par exemple) et lui demander validation.
Bonne idée à tester:)

On peux garder un contrôle et vérifier qu'il n'y a pas doublon
Parfait:cool:

C'est pour cela que j'ai ajouté une feuille avec les communes du 84 (seulement 151, j'en ai 513 chez moi ). A voir si après, on ajoute les communes hors département par saisie, ou si on ajoute les départements limitrophes, voir France entière, mais à ce moment là, un petit combo Département près réglé sur 84 me paraitrait utile.
Je te rejoins là-dessus:p

La partie Mail, je pensait plutôt faire un USF avec recherche par discipline, un bouton Ajout pour pouvoir sélectionner plusieurs disciplines, puis avoir des champs Objet, Corp de message, PJ, etc. pour un envoi par Outlook en scindant par groupe de 20, par exemple, afin d'éviter d'être blacklisté.

Pourquoi pas... l'idée était de pouvoir contacter par mail l'association sans être obligé de passer par une manip plus longue. Fais comme tu penses, cela me permettra de visualiser et tester cette idée;)

On peux très bien utiliser ma fonction filtre en l'imbriquant.
Pas de problème. Je joins le fichier et à toi de voir si cela peut t'aider ou non:).
Merci pour tes explications et conseils avisés.
A+:)

Edit : le fichier testé correspond au dernier fichier que tu as déposé sur cette discussion et non sur celui que tu m'as fais parvenir par mail (je viens de voir ton message : je le teste) ;).
 

Pièces jointes

  • DVCascadeMenu3NivInf200InterChange-2_JB.xlsm
    20.6 KB · Affichages: 77
Dernière édition:

david84

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Bonjour à tous,
dans la dernière mise à jour fournie par JNP:) (ci-jointe), j'ai essayé de placer dans la textbox "Tel1" une macro permettant de placer un tiret entre chaque paire de nombres afin d'automatiser la saisie lorsque l'on veut renter un N° de tel lors de la modification d'une fiche :
Private Sub Tél1_Change()
Dim Valeur As Byte
'nb caractères maxi autorisé dans le textbox=14
Tél1.MaxLength = 14
Valeur = Len(Tél1)
'séparer les paires de nombres par un tiret
If Valeur = 2 Or Valeur = 5 Or Valeur = 8 Or Valeur = 11 Then Tél1 = Tél1 & "-"
End Sub
Cela fonctionne en l'état.
Par contre, j'ai ensuite essayé de placer d'autres conditions permettant de vérifier :
- que la valeur rentrée est bien numérique, j'ai tenté :
If isnumric(Tél1.Value) = False Then
MsgBox Tél1.Value & " n'est pas une valeur valide.", vbExclamation, "Valeur non valide"
Tél1.Value = " "
mais sans succès. Je me suis donc arrêté là:(.
En fait, soit cela plante, soit les tirets ne sont plus automatiquement inclus.
Où ai-je fait l'erreur :confused:?
Merci de m'éclairer et de m'expliquer comment faire.
A+
 

Pièces jointes

  • TestNew_BD_assoV3.zip
    94.6 KB · Affichages: 52
Dernière édition:

JNP

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Bonsoir David,
03-83 n'est pas numérique, d'où ton problème...
Personnellement, je passerais déjà par une séparation d'évènement :
Code:
Private Sub Tél1_Change()
Dim Valeur As Byte
    'nb caractères maxi autorisé dans le textbox=14
    Tél1.MaxLength = 14
    Valeur = Len(Tél1)
    'séparer les paires de nombres par un tiret
    If Valeur = 2 Or Valeur = 5 Or Valeur = 8 Or Valeur = 11 Then Tél1 = Tél1 & "-"
End Sub
et
Code:
Private Sub Tél1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Teste(Tél1.Value, "^(\d\d-){4}\d\d$", "Unique") = False And Tél1 <> "" Then
    MsgBox Tél1.Value & " n'est pas une valeur valide.", vbExclamation, "Valeur non valide"
    Tél1.Value = ""
    Cancel = True
    End If
End Sub
en utilisant dans un module
Code:
Function Teste(ATester As String, MonPattern As String, MonType As String) As Boolean
Dim Match, Matches
Application.Volatile
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = MonPattern
    Set Matches = .Execute(ATester)
    If MonType = "Unique" Then
        If Matches.Count = 1 Then Teste = True Else Teste = False
    Else
        If Matches.Count >= 1 Then Teste = True Else Teste = False
    End If
End With
End Function
Le fait de passer par Exit te permet de ne vérifier qu'à la sortie du TextBox ;).
La méthode peut paraître assez complexe mais la notion de vérification de masque sous Excel n'est pas top :eek:.
Si tu veux éclaircir ma méthode, tu veux (re)jeter un œil à ce fil.
Bon courage :cool:
 

Discussions similaires

Réponses
7
Affichages
292
Réponses
13
Affichages
261
Réponses
1
Affichages
78
Compte Supprimé 979
C

Statistiques des forums

Discussions
311 725
Messages
2 081 940
Membres
101 845
dernier inscrit
annesof