Bonjour le forum,
Il s'agit ici d'un projet que je mène dans le cadre de mon alternance dans l'entreprise où je travaille. Je dois avouer que j'aurai dû m'y prendre plus tôt pour demander de l'aide, ou ne serait-ce qu'avoir vos avis concernant la structure de ce classeur. Néanmoins, vu que j'ai reçu de nouvelles directives dernièrement, qui ont pas mal bouleversé l'organisation que j'avais jusque lors, je me suis dit qu'il n'était pas trop tard pour m'enquérir de vos connaissances
Sans plus tarder, je vais vous expliquer de quoi il s'agit, et vous partager les bouts de code que j'ai : Je ne peux malheureusement pas vous partager le fichier car s'y situent des informations confidentielles. Je tâcherai de vous détailler au mieux.
Je précise que le problème auquel je suis confronté est plutôt structurel. Je voudrai être sûr de démarrer dans le bon contexte avant de partir dans le gros du projet. Ainsi, si vous avez des propositions pour structurer différemment mon projet / userform, je suis tout à fait preneur avant que je n'aie codé des milliers de lignes qui ne serviront à rien.
L'interaction avec Excel est prohibée, tout doit se passer par formulaires. C'est sûrement sur la création d'un formulaire efficace que j'ai besoin d'aide. Vous comprendrez dans la suite de ma demande, que... je risque d'avoir un nombre d'objet conséquent. Si cela est inévitable, je suis preneur de conseils pour renommer de manière efficace ces derniers objets. J'ai déjà réfléchi à des dénominations précises sans être trop longues afin de m'y retrouver.
Ex : T_Cons_TBAC
T...1ère lettre du type du fichier... T - TOTAL // E - Extournes etc...
Cons... abréviation de l'action choisie... Cons - Consultation
TB...abréviation du type d'objet... TB - TextBox // CBTN - CommandButton // CB - CheckBox
AC... abréviation du nom de l'objet... AC - Année Comptable
Je vous souhaite une bonne lecture, et vous remercie à l'avance de l'aide que vous m'apporterez.
Passez un agréable moment dans le méandre des mes mots
Quel est l'objectif de ce projet ?
L'objectif initial était d'automatiser les calculs permettant d'afficher le Suivi Budgétaire de la gestion des contrats en temps réel.
Il y a de cela quelques jours, mon responsable m'a demandé d'automatiser la saisie des données dans les "Classeurs Source" - qui alimentent le classeur de Suivi Budgétaire.
Quels sont ces fichiers sources ?
Il me paraît adéquat de commencer par vous parler de ces fichiers Source qui sont donc les informations de base que le programme utilisera. Il y en a 6 en particulier :
- TOTAL - Représente les contrats clos
- Extournes - Représente les montants liés à des contrats qui se situent sur l'Année Comptable N+1 mais en année calendaire N.
- Contrats Actifs - Représente les contrats actifs, comme son nom l'indique
- Commandes - Représente les commandes saisies, mais non payées
- Budget - Représente le budget attribué
- Résumé - Qui ne fera qu'afficher les résultats de l'exercice
NB : Chacun de ses fichiers existe en quintuple, 1 pour 1 région. (5 régions - découpage de la France selon les critères de mon entreprise)
Affichage du UserForm
Objets : (Oui j'ai oublié l'OptionButton pour les Commandes )
Frame1 - Représente le cadre autour des OptionButton et du CommandButton[*] Frame1.Caption = Nom_Region (Tiré du code AuthMOD -> attribution des régions pour gérer les autorisations)
OptionButton1 (Renommé : OB_Budget)
OptionButton2 (Renommé : OB_Ext)
OptionButton3 (Renommé : OB_CA)
OptionButton4 (Renommé : OB_TOTAL)
OptionButton5 (Renommé : OB_Cmd)
OptionButton6 (Renommé : OB_Resume)
CommandButton1 (Renommé : CB_Confirm)
Voici le code AuthMOD :
Classeur source - TOTAL
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 Sub AuthMOD(ID As String) 'Dans le UserForm_Initialize on accède à cette procédure de la manière suivante - AuthMOD(Application.UserName) Select Case ID Case "NOM Prenom", "NOM Prenom" Frame1.Caption = "Nord-Est" Case "NOM Prenom", "NOM Prenom" Frame1.Caption = "Sud-Ouest" Case "NOM Prenom", "NOM Prenom", "NOM Prenom" Frame1.Caption = "Ouest" Case "NOM Prenom", "NOM Prenom" Frame1.Caption = "Sud-Est" Case Else MsgBox ("Vous n'êtes pas renseigné dans la base de données") End Select End Sub
Dans ce classeur, je souhaiterai que l'utilisateur puisse effectuer différentes opérations :
- Saisie - Nouvelle ligne de contrat à rajouter
- Consultation - Ligne déjà existante
- Modification - Certaines données sont à modifier (Date de saisie dans le logiciel <> Date de paiement - cette dernière doit donc être éditée ultérieurement)
J'ai déjà commencé à travailler mon formulaire de Consultation TOTAL. Toutes ces actions s'effectueront à travers un autre classeur Excel, qui permettra de manipuler librement l'un des 5 classeurs TOTAL. La région de l'utilisateur étant déterminée par Application.Username dont les noms sont enregistrés sous forme de listes.
Ainsi, l'ouverture d'un classeur se fait avec la syntaxe : Workbooks.Open("Chemin...\" & Type_Fichier & Nom_Region & ".xlsx")
Il faut encore que je crée les lignes pour l'OptionButton sur les Commandes, mais ça ne risque pas d'être trop compliqué
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 Private Sub CBConfirm_Click() If OB_Budget.Value = True Then If MsgBox("Confirmez-vous vouloir accéder au fichier BUDGET de la région " & Frame1.Caption & " ?", vbYesNo, "Demande de confirmation") = vbYes Then Else End If End If If OB_Ext.Value = True Then If MsgBox("Confirmez-vous vouloir accéder au fichier EXTOURNES de la région " & Frame1.Caption & " ?", vbYesNo, "Demande de confirmation") = vbYes Then Else End If End If If OB_CA.Value = True Then If MsgBox("Confirmez-vous vouloir accéder au fichier CONTRATS ACTIFS de la région " & Frame1.Caption & " ?", vbYesNo, "Demande de confirmation") = vbYes Then Else End If End If If OB_TOTAL.Value = True Then If MsgBox("Confirmez-vous vouloir accéder au fichier TOTAL de la région " & Frame1.Caption & " ?", vbYesNo, "Demande de confirmation") = vbYes Then Workbooks.Open ("Z:\PBR_LOG\ARIBA_2019\TestVBA\TOTAL\TOTAL" & Frame1.Caption & ".xlsx") Call TOTAL_Ent Else End If End If If OB_Resume.Value = True Then If MsgBox("Confirmez-vous vouloir accéder au classeur de Suivi Budgétaire ?", vbYesNo, "Demande de confirmation") = vbYes Then Else End If End If End Sub
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 Private Sub TOTAL_Ent() Set Wb = Workbooks(OB_TOTAL.Caption & Frame1.Caption) Dim answer As Integer answer = Autretest Select Case answer Case 1 With MP1 .Pages("Page1").Visible = True .Pages("Page2").Visible = False End With Case 2 Case 3 End Select Wb.Sheets(InputBox("Veuillez saisir une année comptable - 4 chiffres")).Select End SubLe MultiPage représente la collection complète de toutes les pages que je créerai - pour les 6 types de classeurs - ce qui représenterai une quinzaine de pages selon moi.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5 Public Function Autretest() As Integer Do Autretest = InputBox("Souhaitez-vous Consulter (1), Ajouter (2), Modifier (3) ?") Loop While Autretest < 1 Or Autretest > 3 End Function
Consultation_TOTAL
Voici la page que j'ai créée pour la Consultation de la feuille TOTAL.
L'utilisateur devra dans un premier temps saisir l'année qu'il souhaite enregistrer grâce à la procédure TOTAL_Ent()
La valeur ainsi retournée sera automatiquement stockée dans la TextBox1 (Renommée : T_Cons_TBAC [TOTAL_Consultation_TextBox_AnnéeComptable])
Au vu du nombre de pages, j'ai décidé de créer une procédure d'initialisation pour chacun des objets qui se situent dans chacune des pages. Peut-être devrai-je même faire :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 Private Sub T_Cons_Init() T_Cons_TBAC.Enabled = False 'La TextBox Année Comptable dans la feuille Consulter(TOTAL) n'est pas modifiable T_Cons_CBBIC.Visible = False T_Cons_LBBIC.Visible = False 'Le Label et la CheckBox liés aux sites BIC sont cachés T_Cons_TBCC.Enabled = False T_Cons_TBSAP.Enabled = False 'La TextBox Centre de Coût et Plan de Compte dans la feuille Consulter(TOTAL) ne sont pas modifiables Call T_Cons_CBSite_Init 'Appel de la procédure d'initialisation de la CheckBox_Site (Liste Déroulante) Call T_Cons_CBP5_Init 'Appel de la procédure d'initialisation de la CheckBox_Pavé5 (Liste Déroulante) Call T_Cons_CBP4_Init 'Appel de la procédure d'initialisation de la CheckBox_Pave4 (Liste Déroulante) End Sub
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6 Private Sub Userform_Initialize() Call T_Init Call E_Init '... 'Call B_Init End SubQuestion 1
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6 Private Sub T_Init() Call T_Cons 'Procédure d'initialisation des objets se situant dans la page T_Cons Call T_Saisie Call T_Modif End Sub
...ainsi de suite. Cela me permettra d'avoir une meilleure lisibilité, non ? Ou l'imbrication d'appel de procédures n'est pas recommandé ?
La procédure T_Cons_CBSite_Init prend ses données au sein du classeur principal.
La 1ère ligne de la page 2 représente le nom des régions - le programme va chercher quelle valeur correspond à celle située dans Frame1.Caption
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 Private Sub T_Cons_CBSite_Init() Dim CBR As Range, CellCBR As Range, CBS As Range, CellCBS As Range Dim derLig As Integer, derCol As Integer Dim WbO As Worksheet Set Wb = ThisWorkbook Set WbO = Wb.Sheets(2) derCol = WbO.Cells(1, Columns.Count).End(xlToLeft).Column 'Récupère la dernière colonne dans la base de données des régions -> sites Set CBR = WbO.Range(WbO.Cells(1, 1), WbO.Cells(1, derCol)) 'Définit la plage de cellule qui correspond à la première ligne de la Feuille 2, qui contient toutes les régions (en-têtes) For Each CellCBR In CBR 'Pour chaque cellule dans cette plage... If CellCBR.Value = Frame1.Caption Then '...si la valeur de la cellule correspond à la région de l'utilisateur... derLig = WbO.Cells(Rows.Count, CellCBR.Column).End(xlUp).Row '...déterminer la dernière ligne non-vide de la colonne associée à la Région - Correspond à tous les sites de la région Set CBS = WbO.Range(WbO.Cells(2, CellCBR.Column), WbO.Cells(derLig, CellCBR.Column)) '...puis attribue la totalité de la plage (- l'en-tête) à la variable CBS For Each CellCBS In CBS 'Pour chaque cellule dans cette plage (CBS)... T_Cons_CBSite.AddItem (CellCBS.Value) '...ajouter la valeur de cette cellule à la Liste Déroulante Next End If Next End Sub
Les colonnes de chacune des 5 lignes représentent les sites principaux associés à chacune de ces régions.
L'appellation BIC signifie les sites secondaires. La saisie de BIC dans la CheckBox_Site permettra l'affichage de la CheckBox_BIC qui est initialisée exactement de la même manière que celle pour les sites principaux.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11 Private Sub T_Cons_CBSite_Change() If T_Cons_CBSite.Value = "BIC" Then 'Si la valeur renseignée via la CheckBox_Site est BIC alors... Call T_Cons_CBBIC_Init '...on initialise la CheckBox_BIC Else T_Cons_CBBIC.Clear T_Cons_LBBIC.Visible = False T_Cons_CBBIC.Visible = False End If End Sub
Procédure d'initialisation de la liste déroulante Pavé 5 (fonctionnement identique pour Pavé 4, sur une autre colonne)
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13 Private Sub T_Cons_CBP5_Init() 'Ce code permet d'initialiser la CheckBox SANS prendre en compte les valeurs nulles. Cela évite d'avoir un choix "vide" dans la liste Set Wb = ThisWorkbook Set mondico = CreateObject("Scripting.Dictionary") a = Wb.Sheets(1).Range("M2:M" & Sheets(1).[A65000].End(xlUp).Row) For i = LBound(a) To UBound(a) If a(i, 1) <> "" Then mondico(a(i, 1)) = "" Next i T_Cons_CBP5.List = mondico.Keys End Sub
J'ai besoin d'ignorer les cellules vides, car je n'ai pas envie d'avoir d'espace dans ma liste déroulante, c'est pas joli
Cette procédure a pour objectif de mettre à jour la liste déroulante Pavé 5 en fonction du choix réalisé dans la liste déroulante Pavé 4. En effet, le niveau de détail est tel que 1 valeur du Pavé 4 englobe plusieurs valeurs du Pavé 5. Néanmoins, après avoir discuté avec une collègue, elle souhaiterait pouvoir filtrer dans le sens inverse.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 Private Sub T_Cons_CBP4_Change() Dim CBR As Range, CellCBR As Range, CBS As Range, CellCBS As Range Dim derLig As Integer Dim WbO As Worksheet Set Wb = ThisWorkbook Set WbO = Wb.Sheets(1) derLig = WbO.Range("L2:L" & Rows.Count).End(xlUp).Row WbO.Range("A2").AutoFilter WbO.Range("A2").AutoFilter Field:=12, Criteria1:=T_Cons_CBP4.Value If T_Cons_CBP5.Value <> "" Then T_Cons_CBP5.Clear Set CBR = WbO.Range(WbO.Cells(2, 12), WbO.Cells(derLig, 12)).SpecialCells(xlCellTypeVisible) For Each CellCBR In CBR T_Cons_CBP5.AddItem (CellCBR.Value) Next End If End Sub
C'est là qu'intervient ma deuxième question :
Question 2
Après avoir effectué de multiples tests...je n'ai pas réussi à atteindre le résultat souhaité, à savoir :
Si j'ai déjà des valeurs saisies dans Pavé 4 et Pavé 5 - donc que mon filtre automatique a trié mon tableau en fonction de ces deux critères, comment faire pour que lorsque à travers l'événement T_Cons_CBP4_Change() - déjà utilisé pour modifier la liste déroulante Pavé 5, cette dernière se vide automatiquement et se réinitialiser en fonction de la valeur nouvellement saisie en Pavé 4 ?
J'ai essayé en partant sur un algorithme du style :
Si Valeur_P4 <> "" Alors
Supprimer les filtres actifs
Vider P5
Créer un filtre sur P4
Réinitialiser P5 en fonction de P4
Sinon
Créer un filtre sur P4
Fin Si
Y a-t-il un ordre que je n'aurai pas respecté dans cette réflexion ? Parce que j'ai essayé de le réaliser et je me retrouvais avec des erreurs, si l'algorithme a un sens logique, c'est que je me suis trompé dans la réalisation du code.
Voilà l'état actuel de mon projet. Je préfère m'en tenir à là pour l'instant, afin d'être sûr de partir dans la bonne direction avant de réfléchir à la création de toutes les autres pages de la multipage.
Je vous remercie pour l'attention portée à mon projet, je reste à votre disposition pour tout éclaircissement à apporter. J'espère aussi ne pas m'être trompé de sous-forum, j'ai longtemps hésité mais je pense que j'ai surtout besoin d'aide sur la structure de mon classeur. J'ai partagé les codes VBA pour être précis et partager l'avancement.
Partager