Améllioration d'une macro

Nouvel essai

https://www.excel-pratique.com/~files/doc/VrQ1ATresorerie56_2008_essai.zip

En espérant que ça marche cette fois.

Henri

re,

Le tableau des codes n'est pas le même que sur le 1er fichier !

- Lequel est le bon ? (j'en ai besoin pour la nouvelle structure)

çà avance bien

Claude.

édit: combien de lignes par an sur la feuille CCP (environ) ?

Exact, j'ai été obligé d'alléger mon fichier pour descendre sous les 100Ko.

Le tableau des codes a prendre en compte est bien le 1er.

J'ai prévu 150 lignes de saisie car certaines sections font plus d'opérations que d'autres.

Si ça avance bien, c'est super.

Bon W.E. avec le soleil et la plage

Henri

Salut Henri et au forum,

Je m'en doutais un peu.

• La feuille "saisie" est prête.
• J'ai supprimé les 3 onglets : charge, bilan et produits devenus inutiles.
• J'ai supprimé toutes les formules : DECALER.....
• J'ai supprimé les filtrages AUTO et remplacé par "filtre élaboré)

Pour parfaire l'ensemble, envoie-moi tes macros, que je fasse le ménage.

Je t'enverrais comme çà le fichier complet (prêt à l'emploi)

• Faut-il protéger toutes les feuilles ?
• -------------------------

Fichier terminé lundi je pense.

amicalement

Claude.

Eh ben, tu as fait du ménage apparemment.

Il ne faut surtout pas toucher aux feuillets "Balance", "Cpt R", "Bilan1", "Bon_CCP" et "Etat_Rappr".

Voici les macros utilisées:

Accueil

Sub auto_open()

Sheets("CCP").Activate

Range("A1").Select

Range("A" & Application.WorksheetFunction.CountA(Columns(2)) + 5 - 1).Select

ActiveWindow.WindowState = xlMaximized

End Sub

Change_année

Sub Changer_A()

Dim rep, an As Integer

'CHANGER L'ANNEE

Range("A1").Select

Application.ScreenUpdating = True

rep = MsgBox("A T T E N T I O N , vous allez créer la nouvelle année et sauvegarder l'année précédente .", 4)

If rep = 6 Then

Sheets("CCP").Activate

ActiveWorkbook.Save

Range("J3") = Range("J3") + 1

'RECOPIE DE L'ACTIF DU BILAN DU BILAN EN ANNEE PRECEDENTE

Sheets("Bilan1").Select

Range("C5:C14").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Bilan1").Select

Range("E5:E14").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

'PASSER LES RESULTATS DU COMPTE DE RESULTAT EN ANNEE PRECEDENTE

Sheets("Cpt R").Activate

Range("B4:B25").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Cpt R").Activate

Range("C4:C25").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Sheets("Cpt R").Activate

Range("E4:E25").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Cpt R").Activate

Range("F4:F25").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Sheets("Cpt R").Activate

Range("A1").Select

'RECOPIE DES CHEQUES NON DEBITES

Sheets("Bilan1").Select

Range("B7").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Bilan1").Select

Range("D7").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

'RECOPIE DU PASSIF

Sheets("Bilan1").Select

Range("H5:H14").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Bilan1").Select

Range("I5:I14").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

'RECOPIE DU RESTE DU DES COTISATIONS VERSEES AU SIEGE

Sheets("Bilan1").Select

Range("G14").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Bilan1").Select

Range("G10").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Sheets("Bilan1").Select

Range("A1").Select

'RECOPIER LES SOLDES FIN D'ANNEE CCP PUIS CNE AU 31/12 DE L'ANNEE PRECEDENTE

Sheets("CCP").Activate

Range("C7").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("CCP").Activate

Range("C4").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Sheets("CCP").Activate

Range("D7").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("CCP").Activate

Range("D4").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

'EFFACER LA SAISIE DE L'ANNEE PRECEDENTE

Sheets("CCP").Activate

Range("B9:I150").Select

Application.CutCopyMode = False

Selection.ClearContents

'MESSAGE D'AVERTISSEMENT

Dim nom, nl As Integer, rep1, monrep

Sheets("CCP").Activate

Range("A1").Select

an = Range("J3")

nom = ActiveWorkbook.FullName

nl = Len(ActiveWorkbook.Name)

monrep = Mid$(nom, 1, Len(nom) - nl)

ActiveWorkbook.SaveAs FileName:=monrep & "Trésorerie56_" & an, FileFormat:=xlNormal

rep1 = MsgBox("Vous venez de créer votre fichier Trésorerie_" & an & " Vous pouvez maintenant saisir vos opérations dans la nouvelle année.", 0, "MESSAGE")

Sheets("CCP").Activate

Range("A1").Select

Else

Sheets("CCP").Activate

Range("A1").Select

End If

End Sub

Imp_Bons

Sub BONCCP_IMP()

'

' BONCCP_IMP Macro

Dim nbbon, nblgn, rep2 As Integer

Sheets("BON_CCP").Activate

Range("A1").Select

Application.ScreenUpdating = True

rep2 = MsgBox("Attention, vous allez éditer tous les bons.", 4)

nblgn = Application.WorksheetFunction.CountA(Sheets("CCP").Columns(2)) - 5

If rep2 = 6 Then

For nbbon = 1 To nblgn

Sheets("Bon_CCP").Select

Range("K2").Value = nbbon

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Next nbbon

Else

Sheets("CCP").Activate

End If

End Sub

Module1

Sub Etat_Rappr()

Sheets("CCP").Activate

ActiveSheet.Unprotect

If [b159].End(xlUp).Offset(0, 1) <> "" Then Exit Sub 'contrôle si N°CCP existant

'[Etat_Rappr!e10:g15].ClearContents 'efface l'etat précédent

[c159].End(xlUp).Offset(1, 0).Name = "top"

[d159].End(xlUp).Name = "top2"

Range([Top], [top2].Offset(0, -1)) = "voir " & [j3] + 1 'écrit col C

Range([Top].Offset(0, 1), [top2]).Copy Destination:=Range("Etat_Rappr!e10")

Range([Top].Offset(0, 5), [top2].Offset(0, 4)).Copy Destination:=Range("Etat_Rappr!g10")

Application.CutCopyMode = False

rep = MsgBox("Voulez-vous imprimer l'Etat de Rapprochement,", 4)

If rep = 6 Then

With Sheets("Etat_Rappr")

.Activate

.PageSetup.PrintArea = "$A$1:$G$19"

.PageSetup.PrintArea = "$A$1:$G$19"

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End With

End If

Sheets("Etat_Rappr").Select

ActiveSheet.Protect

Sheets("CCP").Select

ActiveSheet.Protect

End Sub

et le Userform d'impression que je n'arrive pas "copier/coller

Il faut protéger toutes les feuilles, mais que je puisse oter la protection pour des modifications éventuelles.

Pour la protection des codes VBA, j'aurai la réponse également la semaine prochaine.

Amicalement et Bon Week-end

Henri

la macro Change_Année est modifiée puisque les chèques nondébités ne sont plus dans le feuillet Bilan1.

Voici la nouvelle:

Sub Changer_A()

Dim rep, an As Integer

'CHANGER L'ANNEE

Range("A1").Select

Application.ScreenUpdating = True

rep = MsgBox("A T T E N T I O N , vous allez créer la nouvelle année et sauvegarder l'année précédente .", 4)

If rep = 6 Then

Sheets("CCP").Activate

ActiveWorkbook.Save

Range("J3") = Range("J3") + 1

'RECOPIE DE L'ACTIF DU BILAN DU BILAN EN ANNEE PRECEDENTE

Sheets("Bilan1").Select

Range("C5:C14").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Bilan1").Select

Range("E5:E14").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

'PASSER LES RESULTATS DU COMPTE DE RESULTAT EN ANNEE PRECEDENTE

Sheets("Cpt R").Activate

Range("B4:B25").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Cpt R").Activate

Range("C4:C25").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Sheets("Cpt R").Activate

Range("E4:E25").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Cpt R").Activate

Range("F4:F25").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Sheets("Cpt R").Activate

Range("A1").Select

'RECOPIE DES CHEQUES NON DEBITES

' Sheets("Bilan1").Select

' Range("B7").Select

' Application.CutCopyMode = False

' Selection.Copy

' Sheets("Bilan1").Select

' Range("D7").Select

' Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

' False, Transpose:=False

'RECOPIE DU PASSIF

Sheets("Bilan1").Select

Range("H5:H14").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Bilan1").Select

Range("I5:I14").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

'RECOPIE DU RESTE DU DES COTISATIONS VERSEES AU SIEGE

Sheets("Bilan1").Select

Range("G14").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Bilan1").Select

Range("G10").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Sheets("Bilan1").Select

Range("A1").Select

'RECOPIER LES SOLDES FIN D'ANNEE CCP PUIS CNE AU 31/12 DE L'ANNEE PRECEDENTE

Sheets("CCP").Activate

Range("C7").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("CCP").Activate

Range("C4").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Sheets("CCP").Activate

Range("D7").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("CCP").Activate

Range("D4").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

'EFFACER LES DONNEES DE L'ETAT DE RAPPROCHEMENT'

Sheets("Etat_Rappr").Select

Range("E10:G14").Select

Selection.ClearContents

'EFFACER LA SAISIE DE L'ANNEE PRECEDENTE

Sheets("CCP").Activate

Range("B9:I150").Select

Application.CutCopyMode = False

Selection.ClearContents

'MESSAGE D'AVERTISSEMENT

Dim nom, nl As Integer, rep1, monrep

Sheets("CCP").Activate

Range("A1").Select

an = Range("J3")

nom = ActiveWorkbook.FullName

nl = Len(ActiveWorkbook.Name)

monrep = Mid$(nom, 1, Len(nom) - nl)

ActiveWorkbook.SaveAs FileName:=monrep & "Trésorerie35_" & an, FileFormat:=xlNormal

rep1 = MsgBox("Vous venez de créer votre fichier Trésorerie_" & an & " Vous pouvez maintenant saisir vos opérations dans la nouvelle année.", 0, "MESSAGE")

Sheets("CCP").Activate

Range("A1").Select

Else

Sheets("CCP").Activate

Range("A1").Select

End If

End Sub

Excuses

Henri

Bonjour Henri et à tous,

Ton fichier remanié :

https://www.excel-pratique.com/~files/doc/Tresorerie_AFEH_3.zip

• Avant de commencer, clique sur le bouton "Aide" pour comprendre la procédure.
• j'ai pas mis de mot de passe sur toutes les feuilles (trop lourd pour tester), on verra çà

- Pour modifier la feuille "CCP", tu fait : Ctrl+b (déverouille la feuille et annihile les macros

événementielles).

• --------------------------------
• Les 2 boutons principaux sont : "Afficher tout" et "Saisie".
• En sélectionnant dans le tableau, tu filtre direct !

Reste à fignoler "Etat_Rapprochement" que je n'ai pas encore bien compris !

- J'ai testé le changement d'année, çà à l'air Ok

Je te laisse tester et dis-moi !

Remarques d'ordre général :

• 1 Auto_open est remplacé par Private Sub Workbook_Open() dans le ThisWorkbook.
• 2 les "feuilles-code" sont réservées aux Private Sub et aux macros évenementielles,

Tu auras surement des bogues (çà fait partie de la mise au point !)

J'attends tes remarques, n'hésite pas, on retoucheras !

amicalement

Claude.

édit : le mot de passe des feuilles est le même que celui des codes.

Bonjour Claude,

Un peu surpris par le nouvel environnement!

J'ai vérifié et modifié certaines données.

Dans "Balance" modification et mise à jour de la liste déroulante avec masquage des lignes non nécessaires pour l'impression.

Dans "Cpt_R" J'ai profité pour ajouter de nouveaux comptes.

La seule chose qui me chagrinait c'était que je ne pouvais plus modifier les saisies effectuées, mais je n'avais pas compris la fonction "CTRL B".

Dans la grille "Nouvelle saisie" modifie CCP en EXTRAIT CCP N°

J'ai testé également le changement d'année- pas de problèmes

voici le fichier après mes modifs:

https://www.excel-pratique.com/~files/doc/Tresorerie_AFEH_essai1.zip

Par contre, la grille de saisie devrait toujours comporter toutes les colonnes 'case +de saisie cochée)

Il reste aussi la fonction IMPRESSION mais ça n'est pas important.

Concernant la fonction "Etat de rappr" elle ne servira qu'une fois dans l'année, lors de la cloture des comptes le 31/12 (en fait on pourrait l'intègrer dans le module "Changement d'année") pour ne pas perturber les utilisateurs. En mettant un 2ème contrôle (le 1er étant l'absence du n° extrait de compte) qui serit la présence du n° de chèque.

Question: Combien de lignes de saisie as-tu prévu?

[color=red]Pour ton info sur le mot de passe de VBA

Sous VBA-Outis- Propriétés de VBA- Protection- et saisie du mot de passe et cocher verrouillage.

Au fait, j'ai dù l'enlever dans le fichier joint.

Amicalement

Henri

Bonjour à tous,

Henri, voici une nouvelle version

- Balance : j'ai supprimé le bouton "à lire", maintenant tu clique sur la balance pour afficher

les lignes.

- Le nombre de lignes à saisir est illimité (à chaque saisie validée, la base est réinitialisée)

- L'idée d'intégrer "l'état de Rappr" dans le changement d'année est une bonne idée !

Voir essai sur cette version, qui pourrait supprimer carrément l'etat de Rappr, en fait,

comme un arrêt des comptes !

J'ai du mal à comprendre les cas qui peuvent se présenter au moment de cette action.

(ce qui est évident pour toi, m'est encore obscure)

Le Ctrl+b n'est pas pour les utilisateurs, sinon inutile de verrouiller !!

J'ai modifié quelques bricoles (mise au point)

à te lire

amicalement

Claude.

edit : je me suis trompé de fichier.

https://www.excel-pratique.com/~files/doc/AFEH_essai2.zip

Bonsoir Claude,

J'ai regardé la nouvelle version que tu proposes, je l'ai modifiée quelque peu

- Voir info pour l' état de rapprochement, c'est vrai que ce n'est pas simple, en plus peu de sections se servent de cet état, mais ça a été une demande nationale.

ex: courant décembre, tu fais un chèque pour couvrir tel ou tel activité, si le débit n'a pa été constaté sur le dernier extrait CCP de l'année(car tous les comptes sont arrêtés au 31/12) il faut tout de même l'enregistrer dans l'année en cours car la dépense concerne l'année en cours. En plus pour le commissaire aux comptes, il faut impérativement que cela soit fait ainsi. Dond enregistrement de l'opération (ou les) sans N° d'extrait de compte et avec N° de chèque. C'est compliqué mais c'est comme ça

- Intégration de la macro Imprimer (feuiillet CCP à revoir)

Remarques:

• Suppression de l'option +de saisie(en conséquence suppression de la macro liée)
• Boutons Entrée/Sortie

• Pour moi le code compte ne sert plus à rien(on pourrait le supprimer!!!!!
• Si l'opérateur doit mofifier une saisie mal enregistrée, comment fait-il?

Ci-joint le fichier que j'ai modifié:

https://www.excel-pratique.com/~files/doc/Tresorerie_AFEH_essai2b.zip

C'est super ce que tu fais pour moi

Merci mais ne complique pas trop

Henri

Bonjour Henri et à tous,

Confirmation pour l'état de rappr. !

- il vaut mieux se répété et être bien en phase !

https://www.excel-pratique.com/~files/doc/imfSAquestion1.xls

- pour l'intégration dans le changement d'année, je pense que c'est une erreur ! ,

1.- il vaut mieux avoir un contrôle visuel

2 - il faut imprimer l'état de rappr. avant de clôturer le fichier.

Par-contre, il faut ajouter un contrôle qui interdise le changement d'année tant que l'"état-Rappr" n'a pas été vu.

Pour les erreur de saisie, pas facile ! ? Il faut y réfléchir,

- peut-être une option "annuler" qui effacerait l'entrée ou la sortie et écrirait "Annulé"

à la place du N° Extrait CCP ?

et refaire une nouvelle saisie.

- Il ne faut pas laisser l'opérateur bricoler dans le tableau, sinon à quoi bon verrouiller

les feuilles.

• On ne peut pas non plus se contenter d'effacer la ligne, se serait trop facile et pas de trace !
• ----------------------------------------
• Je ne m'occupe pas de l'impression (vois de ton coté)
• La colonne code compte est supprimée.

à+ ....Claude.

re,

Version4

https://www.excel-pratique.com/~files/doc/Tresorerie_AFEH_4.zip

- Nouveau bouton : "Annuler un bon" (en cas de mauvaise saisie)

Les valeurs sont effacées et le bon reste présent avec mention "Annulé"

• pour l'état-Rappr. j'ai fait comme j'ai compris
• Changement d'année bloqué si l'état-Rappr. n'est pas vu.
• colonne code compte supprimée
• -----Attention, il y a des formules en ligne 1-------------

amicalement

Claude.

Bonsoir Claude,

J'ai essayé l'option ANNULER BON

Il y a un bug quand je clique sur Annuler au lieu de OK

Pour ta question concernant une opération en milieu d'année qui n'aurait pas de N° extait de compte , c'est impossible car les saisies se font uniquement à réception de cet extrait.

Comme je le disais hier, les cas de chèques non débités au 31/12 sont rares mais s'il ou s'ils existent ils seront enregistrés en dernier lieu avant le changement d'année.

C'est pourquoi, ta version bloquant cette manip tant que l'état de rapprochement n'a pas été vérifié est judicieuse( mais si aucun enregistrement n'est concerné , est-ce que ça fonctionne aussi?)

Les enregistrements tels qu'ls figurent sur le feuillet CCP ne sont pas corrects.

lLe bon 15 devrait avoir une date et un N° extait

Le bon 16 étant une entrée ne nous intéresse pas

Le bon 17 peut correspondre à un chèque non débitésau 31/12

OK pour les codes compte

OK pour les boutons Entrée/Sortie

Merci

Henri

re,

Si il n'y a pas lieu de faire un "Rappr." , le changement d'année est direct.

lLe bon 15 devrait avoir une date et un N° extrait

Le bon 17 peut correspondre à un chèque non débités au 31/12

que vois-tu comme distingo entre ces deux bons ??

comprends pas cette remarque !

claude.

Bonjour au forum,

Henri, pour l'esthétique, je verrais bien la cellule AN (2008) en B3

Cela libérerai la zone de droite pour arranger les boutons.

Qu'en pense-tu ?

Claude.

OK pour AN en B3

Ma remarque d'hier concerne un mauvais enregistrement. On ne peut pas avoir un bon 15 sans date ni extrait de compte et ensuite un bon 16 relatif à un crédit qui ne peut être enregistré qu'au vu d'un extrait de compte. La chronologie n'est pas respectée. C'est une situation qu'on ne trouvera jamais

Enfin, l'essentiel est que le changement d'année ne peut se faire qu'après vérification de "Rappr".

NOTA: Je pars en vacances samedi prochain pendant 1 semaine

Merci encore et Bonnes Vacances à tous

re,

Bon, je vais faire un peu de ménage et arranger les modules.

Petite question : sur l'état de rapprochement, il n'y a aucun détail, on ne sait pas à quoi étaient dédiés les chèques ! c'est normal çà ?

Tu auras une nouvelle version à ton retour.

Bonnes vacances

Claude.

Oui, l'état de rapprochement ne contient que ces détails.

Il y a un bug dans le module "Changer année"

Paragraphe :

'EFFACER LA SAISIE DE L'ANNEE PRECEDENTE

Range([k12], [A65536].End(xlUp)).ClearContents

Range("report").Copy Destination:=Range("a12")

Range("report").Clear

A plus tard

Henri

Bonjour Henri et à tous,

- Je n'ai pas trop toucher au fichier cette semaine ,

çà fait du bien de prendre un peu de recul !

Voilà donc ou j'en suis :

https://www.excel-pratique.com/~files/doc/Tresorerie_AFEH_6.zip

Il me reste toujours une zone de flou dans mon esprit, concernant les 1ères colonnes.

- Je t'ai donc préparer un petit questionnaire :

https://www.excel-pratique.com/~files/doc/2cKV8question2.xls

afin de bien contrôler la saisie et le remplissage du N° Extrait.

- Si ce fichier va dans 80 départements, il faut être "carré".

Je crois qu'on est pas loin du but.

à te lire

amicalement

Claude.

Après 1 bonne semaine de vacances, Bonjour,

En retour, les réponses à tes questions:https://www.excel-pratique.com/~files/doc/2cKV8question2_rep.xls

J'ai testé ta nouvelle version. Comme on dit vulgairement "ça a de la gueule"

Le changement d'année se fait corectement, il faudrait ajouter la mise à jour de l'état de rapprochement(le blanchir)

Dans tes questions, j'ai oublié de signaler que le N° de chèque n'est pas obligatoire non plus lors du réversement des cotisations au siège .

Merci encore

Henri