Discussion :

[Benzoid69]

Bonjour à tous,

Je suis en train de réaliser une nomenclature pour le service achat. Après plusieurs essai, je sais comment elle est organisée maintenant.
Je souhaiterai dorénavant l'améliorer.
Voici ci-joint dans le dossier compressé la nomenclature que j'utilise actuellement et une nomenclature que j'ai modifié et donc, que je souhaiterai.

Avant de lire la suite, regardez bien la nomenclature général afin de voir son fonctionnement.

Mon onglet référent est l'onglet "BOM".

Problème 1:

Tout d'abord, dans la notice se trouve un tableau "type de pièce".
Je souhaiterai que les onglets en Jaune se créaient en fonction de se tableau. (En excluant la ligne vide) et que ces onglets se nomment pareils.
Ex: Si j'ajoutes un type de pièce "Broches", je souhaiterai qu'un onglet se créé automatiquement avec le nom "Broches".
De même si je supprime un type de pièce, je souhaiterai que l'onglet existant soit supprimé.

Problème 2:

Lors de la création d'un onglet, une mise en page est nécessaire.
Tout d'abord, quand un onglet est créé, je souhaiterai qu'il y ai un copier/coller de la partie information + l'en-tête du tableau de BOM, c'est-à-dire de la plage de cellule C1 à AB3.
Afin de garder les information en lien, je souhaiterai un "copié-coller avec lien" de la date, N° de commande, ...

Problème 3:

Je souhaites que la nomenclature fonctionne comme pour ma nomenclature actuelle.
En fonction de la LOCALISATION, les pièces se rangent automatiquement dans le bon onglet.
La seule différence actuelle entre les 2 nomenclatures sont les dispositions des colonnes.

Voila, j'ai pratiquement tout énoncé. Si certaines explications se sont pas explicite, n'hésitez pas à me répondre.
J'ai encore d'autres petit éléments à réaliser mais j'essayerai par moi même avant de vous demandez de l'aide.

Cordialement

[EricDgn]

Bonjour,

Un début de solution pour le point 1: ajout d'onglets. Code placé dans la feuille Notice

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
Option Explicit
 
Private Sub Worksheet_Change(ByVal target As Range)
   Dim vNew As Variant, vOld As Variant, sCell As String
   Dim rInts As Range, k As Long
   If target.Column <> 3 Then Exit Sub
   sCell = target.Address
   vNew = target.Value
   Application.EnableEvents = False    '=== no event
   Application.Undo
   vOld = Range(sCell).Value
   Range(sCell).Value = vNew
   Range(sCell).Offset(0, 1).Select
   Application.EnableEvents = True     '=== event
   Debug.Print sCell, vOld, vNew
   Set rInts = Application.Intersect(ListObjects(1).Range, Range(sCell))
   If rInts Is Nothing Then
      '--- la cellule n'est pas dans la ListObjects(1) = type de pièces
      Debug.Print "Hors liste"
   Else
      If vNew = "" Then
         If MsgBox("Supprimer ce type de pièce", vbYesNo) = vbNo Then
            Application.EnableEvents = False    '=== no event
            Application.Undo
            Range(sCell).Value = vOld
            Application.EnableEvents = True     '=== event
         Else
            Application.DisplayAlerts = False
            On Error Resume Next
            Sheets(vOld).Delete     '--- erreur si feuille déjà effacée
            On Error GoTo 0
            Application.DisplayAlerts = True
            k = Range(sCell).Row - ListObjects(1).HeaderRowRange.Row
            Range(sCell).Select
            Application.EnableEvents = False
            Range(sCell).ListObject.ListRows(k).Delete
            Application.EnableEvents = True
         End If
      Else
         On Error Resume Next                '--- erreur si Sheets(vOld) n'existe pas
         Sheets(vOld).Name = vNew            '--- renomme l'onglet
         If Err.Number = 9 Then
            '--- feuille(vOld) n'existe pas, créer feuille(vNew) en copiant la feuille modèle TypePiece (masquée)
            Sheets("TypePiece").Copy After:=Sheets("BOM")
            Sheets("TypePiece (2)").Visible = True
            Sheets("TypePiece (2)").Name = vNew
            Sheets("Notice").Select
         End If
         On Error GoTo 0
      End If
   End If
End Sub
 
Private Sub Worksheet_Deactivate()
   '--- supprime l'éventuelle dernière ligne vide du tableau Type de pièces
   '--- il est supposé qu'il n'y en a qu'une
   Dim sAdr As String, kR As Long, k As Long
   sAdr = ListObjects(1).Range.Address
   kR = InStrRev(ListObjects(1).Range.Address, "$")
   kR = Mid(sAdr, kR + 1)
   Debug.Print "Worksheet_Deactivate", sAdr, kR
   If Cells(kR, 3) = "" Then
      k = kR - ListObjects(1).HeaderRowRange.Row
      Cells(kR, 3).ListObject.ListRows(k).Delete
   End If
End Sub

Pour le point 2, le plus simple est de prévoir une feuille modèle: "TypePiece", de la masquer, et de la recopier pour créer un nouvel onglet. C'est le moyen utilisé dans le code ci-dessus.

Le petit code suivant placé dans la feuille modèle "TypePiece" permet de basculer facilement entre les modes "masqué" et "affiché".

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Private Sub AfficherMasquer()
   Sheets("TypePiece").Visible = Not Sheets("TypePiece").Visible
End Sub

A compléter pour gérer les ajouts dans la 2e liste "Sous-traitance" (qu'il serait sans doute plus indiqué de mettre à côté de l'autre, plutôt qu'en dessous).

Pour le point 3, à mon avis le plus simple serait de garder le même procédé, mais masquer les colonnes A et B.

Bonne continuation.

[78chris]

Bonjour

Quelques remarques d'ordre général :

• éviter de mettre en ligne des données non anonymisées
• pourquoi utiliser le format xls ancien, plus lourd et ne supportant pas toutes les fonctionnalités apportées depuis la version 2007 ?
• pourquoi ne pas nommer tes tableaux : c'est vite ingérable de ne pouvoir gérer correctement les tableaux, tant manuellement que par VBA en raison de noms automatiques
• excel offre la possibilité de créer des modèles d'onglets : ce peut être une meilleure pratique que de dupliquer un onglet
• concernant le code :
  
  • dans Workbook_SheetDeactivate, plutôt qu'une liste fermée et donc non évolutive, prévoir d'utiliser le tableau des types de pièces de l'onglet notice...
  • puisque tu utilises des tableaux, listobjects il faut coder sur la base des listobjects et non des plages classiques. C'est ce que fait le code de EricDgn.
• le tableau vide de la feuille servant de modèle ne doit avoir que la ligne de titre et une seule ligne de données : il s'agrandit automatiquement lorsqu'on y ajoute les données

[EricDgn]

Bonjour,

Tout à fait d'accord avec les remarques générales formulées par Chris.

Effectivement, il est vraiment indiqué de renommer les tableaux quand on utilise du code. Cela se fait très simplement.
(1) sélectionner une cellule du tableau, (2) ouvrir l'onglet Outils de tableau, (3) changer le Nom du tableau.



Bonne continuation.

[EricDgn]

dans Workbook_SheetDeactivate, plutôt qu'une liste fermée et donc non évolutive, prévoir d'utiliser le tableau des types de pièces de l'onglet notice...

Pour cette partie, quelque chose de ce genre devrait convenir.

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
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
   Dim TypeU As String, W As Range, rT As Range
   Application.ScreenUpdating = False
   TypeU = ActiveSheet.Name
   Set rT = Worksheets("Notice").ListObjects("Tableau10").Range '--- Tableau10 = types de pièces
   If rT.Find(what:=TypeU, LookAt:=xlWhole) Is Nothing Then
      '--- TypeU non trouvé dans le tableau --- continuer
   Else
      Set W = Sheets("BOM").Range("N5")
      W.AutoFilter
      W.AutoFilter Field:=14, Criteria1:=TypeU
      Sheets(TypeU).Range("A4:M1000").ClearContents
      Sheets("BOM").Range("A4:M1000").Copy Destination:=Sheets(TypeU).Range("A4")
      W.AutoFilter
   End If
   Application.ScreenUpdating = True
End Sub

à compléter/adapter pour ce qui concerne les onglets sous-traitance.

Bonne continuation.

[Benzoid69]

Bonjour,

Déjà,un grand Merci pour vos nombreuses réponses.

Etant donner que je suis un amateur en VBA excel, j'ai encore du mal sur certaines fonction comme par exemple L'argument Field.
J'ai voulu mettre en place ce que vous avez fait mais sur la "nomenclature à modifié" (Organisation des colonnes différentes).
Fichier ci-joint.

Par contre, les macros ne marchent pas. Pouvez-vous me signaler le problème?

[Benzoid69]

Je me rend compte que j'ai donné la feuille excel incorrect.
Voici celle modifié avec un copier coller des macro et en les modifiant légèrement mais je n'arrive pas à avoir le même résulat que vous.

Merci

[EricDgn]

En fait il y a 2 anomalies:
- le tableau a été nommé Typ_de_Piece (sans e à Type), la macro ne reconnait donc pas Type_de_Piece !
- dans la feuille BOM, il se fait que le colonne 1 est hors du tableau nommé 'Nomenclature'. Cela se voit quand on sélectionne la cellule A4: l'onglet Outils de tableau disparait. Il revient quand on sélectionne la cellule voisine B4. Donc le 1er champ (field) du tableau 'Nomenclature' se trouve être la colonne B.

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
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
   Dim TypeU As String, W As Range, rT As Range
   Application.ScreenUpdating = False
   TypeU = ActiveSheet.Name
   Set rT = Worksheets("Notice").ListObjects("Typ_de_Piece").Range '--- Type_de_Piece = types de pièces
   If rT.Find(what:=TypeU, LookAt:=xlWhole) Is Nothing Then
      '--- TypeU non trouvé dans le tableau --- continuer
   Else
      Set W = Sheets("BOM").Range("B4")      '--- B4 fait partie du tableau "Nomenclature" (A4 est hors tableau ?)
      W.AutoFilter
      W.AutoFilter Field:=1, Criteria1:=TypeU   '--- Field 1 = 1er champ du tableau "Nomenclature" = Localisation
      Sheets(TypeU).Range("A4:Z1000").ClearContents
      Sheets("BOM").Range("C4:AB1000").Copy Destination:=Sheets(TypeU).Range("A4")
      W.AutoFilter
   End If
   Application.ScreenUpdating = True
End Sub

Cordialement.

[Benzoid69]

Bonjour,

J'ai réglé les quelques soucis énoncés mais la feuille ne fonctionne tjrs pas comme celle que tu avais fournis précédemment.
La création d'onglet ne se réalise pas quand j'ajoute un nouveau type de pièce (ex: BROCHE).

J'ai d'autres éléments à mettre en place sur cette nomenclature pour quelle soit parfaite.


1) Je souhaiterai avoir la même chose pour la partie sous-traitance.
J'ai créé un modèle d'onglet SousTraitance. (je n'ai pas caché l'onglet pour que vous puissiez le voir.)


2) Ce document excel permet de gérer les commandes au service achat.
Mais pour un client, le document sera ma nomenclature.
L'inconvénient est que sur la nomenclature "BOM" se trouve des colonnes qui ne me servent à rien dans ma nomenclature.
Dans la notice, en fonction si la case est cocher ("Nomenclature EUROCAST"), l'onglet EUROCAST doit être afficher et les informations doivent correspondre aux colonnes de l'onglet "BOM".

3) Dans chaque onglets se trouve le nom du client, la désignation, la date, la référence et le N° de pièce.
Je souhaiterai que chaque onglet à un lien avec l'onglet "BOM".

4)J'ai mis en place une mise en forme conditionnelle sur le tableau Type d'Etat dans la Notice.
Je souhaiterai que la mise en forme conditionnelle de la colonne Etat de l'onglet BOM corresponde au même que précédemment.
De plus, il faudrait que le texte dans les cellules à fond noir soit en lien avec la mise en forme conditionnelle principale.

Oulalalala, j'en demande beaucoup.

Je souhaiterai juste que la personne se trouvant en service achat est à personnaliser ce qu'elle souhaite sans aide.

Encore merci pour toute cette aide que vous m'avez déjà fournis.


Bonne Journée

Benzoid

[EricDgn]

Bonjour,

Pour le (1), gérer à la fois des onglets pour la partie "types de pièces" et "sous-traitance", quelque chose de ce genre devrait pouvoir convenir (dans le code de la feuille Notice):

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
Option Explicit
 
Dim vNew As Variant, vOld As Variant, sCell As String
 
Private Sub Worksheet_Change(ByVal target As Range)
   If target.Column = ListObjects("Type_de_Piece").Range.Column _
      Or target.Column = ListObjects("Sous_Traitance").Range.Column Then
      '--- la cellule modifiée se trouve dans la 1ère colonne du tableau
      sCell = target.Address
      vNew = target.Value
      Application.EnableEvents = False    '=== no event
      Application.Undo
      vOld = Range(sCell).Value
      Range(sCell).Value = vNew
      Range(sCell).Offset(0, 1).Select
      Application.EnableEvents = True     '=== event
      Debug.Print sCell, vOld, vNew
      '---
      GererOnglet "Type_de_Piece"
      GererOnglet "Sous_Traitance"
   Else
      '--- la cellule modifiée ne se trouve pas dans la 1ère colonne du tableau
      '--- ne rien faire, continuer
   End If
End Sub
 
Private Sub GererOnglet(sTableau As String)
   Dim rInts As Range, k As Long, sModele As String
   Set rInts = Application.Intersect(ListObjects(sTableau).Range, Range(sCell))
   If rInts Is Nothing Then
      '--- la cellule n'est pas dans la ListObjects(sTableau)
      Debug.Print "Hors tableau" & sTableau
   Else
      Debug.Print "Dans tableau" & sTableau
      If vNew = "" Then
         If MsgBox("Supprimer ce type de pièce", vbYesNo) = vbNo Then
            Application.EnableEvents = False    '=== no event
            Application.Undo
            Range(sCell).Value = vOld
            Application.EnableEvents = True     '=== event
         Else
            Application.DisplayAlerts = False
            On Error Resume Next
            Sheets(vOld).Delete                 '--- erreur si feuille déjà effacée
            On Error GoTo 0
            Application.DisplayAlerts = True
            k = Range(sCell).Row - ListObjects(sTableau).HeaderRowRange.Row
            Range(sCell).Select
            Application.EnableEvents = False    '=== no event
            Range(sCell).ListObject.ListRows(k).Delete
            Application.EnableEvents = True     '=== event
         End If
      Else
         On Error Resume Next                   '--- erreur si Sheets(vOld) n'existe pas
         Sheets(vOld).Name = vNew               '--- renomme l'onglet
         If Err.Number = 9 Then
            '--- feuille(vOld) n'existe pas, créer feuille(vNew)
            On Error GoTo 0
            If sTableau = "Type_de_Piece" Then sModele = "TypePiece"
            If sTableau = "Sous_Traitance" Then sModele = "SousTraitance"
            Sheets(sModele).Copy After:=Sheets("BOM")
            Sheets(sModele & " (2)").Visible = True
            Sheets(sModele & " (2)").Name = vNew
            Sheets("Notice").Select
         End If
         On Error GoTo 0
      End If
   End If
End Sub
 
Private Sub Worksheet_Deactivate()
   SupprimerLigneVide "Type_de_Piece"
   SupprimerLigneVide "Sous_Traitance"
End Sub
 
Private Sub SupprimerLigneVide(sTableau As String)
   '--- supprime l'éventuelle dernière ligne vide du tableau sTableau
   '--- il est supposé qu'il n'y a que UNE ligne vide
   Dim sAdr As String, kR As Long, k As Long
   sAdr = ListObjects(sTableau).Range.Address
   kR = InStrRev(ListObjects(sTableau).Range.Address, "$")
   kR = Mid(sAdr, kR + 1)
   Debug.Print "Worksheet_Deactivate", sAdr, kR
   If Cells(kR, 3) = "" Then
      k = kR - ListObjects(sTableau).HeaderRowRange.Row
      Cells(kR, 3).ListObject.ListRows(k).Delete
   End If
End Sub

Pour le (2), plutôt que de créer une nouvelle feuille EUROCAST recopiant une partie de la feuille BOM, je proposerais de placer dans cette feuille BOM un bouton qui masque ou affiche simplement les colonnes selon les présentations souhaitées. Exemple: un bouton basculant de l'affichage 'BOM' à l'affichage 'EUROCAST' appelant cette macro:

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 Eurocast_BOM()
   Dim btn As Shape
   Set btn = Sheets("BOM").Shapes("btnEurocast")
   With btn
      If .TextFrame.Characters.Text = "EUROCAST > BOM" Then
         .TextFrame.Characters.Text = "BOM > EUROCAST"
         '--- afficher toutes les colonnes
         Sheets("BOM").Cells.EntireColumn.Hidden = False
      Else
         .TextFrame.Characters.Text = "EUROCAST > BOM"
         '--- masquer les colonnes A à C, I, P à AB
         Sheets("BOM").Range("A:C,I:I,P:AB").EntireColumn.Hidden = True
      End If
   End With
   Set btn = Nothing
End Sub

(note: par défaut, les boutons ne sont pas imprimés)

Pour le (3), il suffit d'indiquer dans les modèles pour les cellules concernées =BOM!xx (xx cellule de la feuille BOM qui contient la valeur souhaitée). Ensuite il faut supprimer tous les onglets "types de pièces" et "sous-traitance", pour les recréer ensuite en tapant la touche Entrée dans chacun des cellules en colonne 1 des listes Type_de_Piece et Sous_Traitance.

Pour le (4), il serait préférable d'ouvrir un nouvelle discussion, en ajoutant une image du résultat attendu.

Bonne continuation.

[Benzoid69]

Bonjour,

EricDgn , j'ai essayé la nomenclature avec les modifs que tu as effectué dessus.
Je remarque que si je modifie la colonne Sous-Traitance dans "BOM", les pièces actuellement pas rangé dans les onglets prévus.

De plus, j'aurai quelques améliorations supplémentaire à mettre en place.

1) Je souhaiterais que l'image "logo Eurocast" dans BOM soit caché si j’appuie sur le bouton "Eurocast-BOM" et affiché si j'appuie sur "BOM-Eurocast".

2)Comme énoncé précédent dans le problème 4, je souhaiterai avec les mises en formes conditionnels dans la colonne Etat dans "BOM" soit en lien avec celle qui sont sur le tableau "Type d'état" dans la notice.
De plus, je souhaiterai avoir la possibilité d'ajouter des états.
En conséquence, le fait d'avoir les correspondances de chaque état (en fond noir) sur chaque onglet, je souhaiterai que les écritures soit en fonction de la couleur du tableau de la notice.

3) Je souhaiterai avoir un bouton Reset afin de supprimer tous les types de pièces et les sous-traitances.

Merci

Je crois qu'après cela, la nomenclature sera terminé.

Cordialement

[EricDgn]

Bonjour,

Pour le point (0), pour la partie Sous_Traitance il suffit de reprendre/adapter le code utilisé pour la partie Type_de_Piece.

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
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
   Dim TypeU As String, W As Range, rT As Range
   Application.ScreenUpdating = False
   TypeU = ActiveSheet.Name
   Set rT = Worksheets("Notice").ListObjects("Type_de_Piece").Range
   Set W = Sheets("BOM").Range("Nomenclature")
   W.AutoFilter
   '--- onglet catégorie Type_de_Piece
   If rT.Find(what:=TypeU, LookAt:=xlWhole) Is Nothing Then
      '--- TypeU non trouvé dans le tableau Type_de_Piece --- continuer
   Else
      W.AutoFilter Field:=2, Criteria1:=TypeU   '--- Field 2 = 2e champ du tableau "Nomenclature" = Localisation
      Sheets(TypeU).Range("A4:Z1000").ClearContents
      Sheets("BOM").Range("C4:AB1000").Copy Destination:=Sheets(TypeU).Range("A4")
      W.AutoFilter
   End If
   '--- onglet catégorie Sous_Traitance
   Set rT = Worksheets("Notice").ListObjects("Sous_Traitance").Range
   If rT.Find(what:=TypeU, LookAt:=xlWhole) Is Nothing Then
      '--- TypeU non trouvé dans le tableau Sous_Traitance --- continuer
   Else
      W.AutoFilter Field:=1, Criteria1:=TypeU   '--- Field 1 = 1er champ du tableau "Nomenclature" = Sous Traitance
      Sheets(TypeU).Range("A4:Z1000").ClearContents
      Sheets("BOM").Range("C4:AB1000").Copy Destination:=Sheets(TypeU).Range("A4")
      W.AutoFilter
   End If
   Application.ScreenUpdating = True
End Sub

Pour le (1), il suffit de gérer la propriété Visible de l'image:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Sheets("BOM").Shapes("Logo_Eurocast").Visible = False   '--- ou True

Pour le (2), cela reste à faire !

Pour le (3), les reset pourraient se faire de la façon suivante:

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
Private Sub ResetListe(sListe As String)
   Dim kRows As Long, kR As Long, oListe As Object, sOnglet As String
   Set oListe = ActiveSheet.ListObjects(sListe).ListRows
   kRows = oListe.Count
   For kR = kRows To 2 Step -1               '--- la ligne 1 est conservée (une liste avec 0 ligne n'est plus une liste)
      sOnglet = oListe(kR).Range.Cells(1, 1)
      Debug.Print sOnglet
      Application.DisplayAlerts = False
      On Error Resume Next
      ActiveWorkbook.Sheets(sOnglet).Delete  '--- erreur si onglet déjà supprimé
      On Error GoTo 0
      Application.DisplayAlerts = True
      Application.EnableEvents = False       '=== no event
      oListe(kR).Delete
      Application.EnableEvents = True        '=== event
   Next kR
End Sub
 
Public Sub Reset_TypePiece()
   ResetListe "Type_de_Piece"
End Sub
 
Public Sub Reset_SousTraitance()
   ResetListe "Sous_Traitance"
End Sub

Bonne continuation.

[Benzoid69]

Bonjour,

Je n'ai pas réussis à faire fonctionner les RESET sans bug.
Je te renvoie donc ma nomenclature à modifier.

De plus, avant de réparer la macro, je te propose de réaliser 3 différents boutons.
-Un bouton Reset type de pièce
-Un bouton Reset Sous traitance
-un bouton Reset de la liste de pièce dans l'onglet BOM

Comme énoncé précédemment, je souhaiterai que les mises en forme conditionnel se trouvant dans l'onglet BOM soit en lien avec le tableau Etat dans la notice. (Est-ce possible?)
Je souhaiterai qu'on puisse régler les mises en formes conditionnel (couleur et valeur des cellules) en fonctions du tableau.

Par ailleurs, je souhaiterai que les onglets créer avec le tableau "type de pièce" soit de couleur Bleu et les onglets créer avec le tableau "Sous Traitance" soit de couleur Orange. (Comme je les ai mise manuellement sur la nomenclature)

Merci

[Menhir]

Oulalalala, j'en demande beaucoup.

C'est le moins qu'on puisse dire...
Eric, j'admire ta patience.

[EricDgn]

Bonjour,

Nouvelle version à tester.

Le bouton Reset pour BOM vide la partie données de la feuille :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub ViderBOM()
   Dim kRows As Long
   Sheets("BOM").ListObjects("Nomenclature").DataBodyRange.Clear
   kRows = Sheets("BOM").ListObjects("Nomenclature").DataBodyRange.Rows.Count
   Debug.Print "kRows: "; kRows
   If kRows > 1 Then
      Sheets("BOM").ListObjects("Nomenclature").DataBodyRange.Rows("2:" & kRows).Delete
   End If
   MsgBox "Données initiales effacées." & vbCrLf & _
          "Attention: bien sauvegarder sous un NOUVEAU NOM !", _
          vbInformation, "Recommandation"
End Sub

Ajouté aussi des boutons pour recréer des onglets (code reprenant une partie du code utilisé dans GererOnglet)

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
33
34
35
36
37
38
Sub ResetOnglets_TypePiece()
   ResetOnglets "Type_de_Piece"
End Sub
 
Sub ResetOnglets_SousTraitance()
   ResetOnglets "Sous_Traitance"
End Sub
 
Private Sub ResetOnglets(sTableau As String)
   Dim kR As Long, nR As Long, kC As Long, k As Long, sNomOnglet As String, sModele As String
   Sheets("Notice").Select
   With Sheets("Notice").ListObjects(sTableau).DataBodyRange
      nR = .Rows.Count
      kR = .Cells(1, 1).Row
      kC = .Cells(1, 1).Column
   End With
   For k = kR To kR + nR - 1
      sNomOnglet = Cells(k, kC)
      If sNomOnglet = "" Then
         '--- celule vide, ne rien faire
      Else
         Debug.Print sNomOnglet
         On Error Resume Next                   '--- erreur si Sheets(sNomOnglet) n'existe pas
         Sheets(sNomOnglet).Name = sNomOnglet   '--- renomme l'onglet
         If Err.Number = 9 Then
            '--- feuille(sNomOnglet) n'existe pas, créer la feuille
            On Error GoTo 0
            If sTableau = "Type_de_Piece" Then sModele = "TypePiece"
            If sTableau = "Sous_Traitance" Then sModele = "SousTraitance"
            Sheets(sModele).Copy After:=Sheets("BOM")
            Sheets(sModele & " (2)").Visible = True
            Sheets(sModele & " (2)").Name = sNomOnglet
            Sheets("Notice").Select
         End If
         On Error GoTo 0
      End If
   Next k
End Sub

Pour la mise en forme des cellules de la colonne Etat de la feuille BOM, une solution est d'aller repiquer les formats qui se trouvent dans le tableau Type_etat pour les appliquer aux cellules de cette colonne Etat.

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
Private Sub Worksheet_Change(ByVal target As Range)
   FormatEtat target
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal target As Range)
   FormatEtat target
End Sub
 
Private Sub FormatEtat(target As Range)
   Dim Rng As Range, sCell As String, v As Variant
   If target.Column <> 3 Then Exit Sub             '=== EXIT SUB === si cellule hors colonne 3
   On Error GoTo fin                               '--- erreur si plusieurs cellules simultanément
   If target.Offset(0, -1) = "" Then Exit Sub      '=== EXIT SUB === si cellule vide en colonne 2
   v = target.Value
   sCell = target.Address
   With Sheets("Notice").ListObjects("Type_etat").Range
      Set Rng = .Find(What:=v, LookAt:=xlWhole)
      If Rng Is Nothing Then
         'Debug.Print "Hors tableau Type_etat"
      Else
         'Debug.Print "Dans tableau Type_etat", Rng.Address, v
         Range(sCell).Interior.Color = Rng.Interior.Color
      End If
   End With
fin:
End Sub

La solution proposée ne fonctionne cependant que si dans le tableau Type_Etat, les couleurs des cellules sont "standard", càd fixées en passant par "Format de cellule ..." (clic droit dans la cellule) --> onglet Remplissage --> bouton Autres couleurs ---> ...



et autre point: si une couleur est changée dans le tableau Type_etat, cela ne se répercute pas sur la feuille BOM. Pour cela il faut retourner sur la feuille BOM et parcourir toute la colonne Etat (en utilisant la flèche bas) ... mais une macro pourrait le faire aussi.

Bonne continuation.

[Benzoid69]

Bonjour,

Je reviens vers vous à propos de ma nomenclature.
J'ai améliorer quelques petites chose (ajout de boutons pour les zones d'impression, réorganisation des onglets,...)

Tout d'abord, je n'ai pas réussis à faire ce que tu as dis, EricDng:

et autre point: si une couleur est changée dans le tableau Type_etat, cela ne se répercute pas sur la feuille BOM. Pour cela il faut retourner sur la feuille BOM et parcourir toute la colonne Etat (en utilisant la flèche bas) ... mais une macro pourrait le faire aussi.

De plus je souhaiterais copier le tableau des Etat qui se trouve dans l'onglet BOM et de coller seulement la mise en forme dans les modèles d'onglets: TypePiece et Soustraitance; sous forme de bouton ou macro en continue.

Pour finir, si j'utilise le RESET de la feuille BOM, la mise en forme conditionnel ne fonctionne plus. Je ne comprend pas pourquoi.

Merci bien

Cordialement

[EricDgn]

Bonjour,

De plus je souhaiterais copier le tableau des Etat qui se trouve dans l'onglet BOM et de coller seulement la mise en forme dans les modèles d'onglets: TypePiece et Soustraitance; sous forme de bouton ou macro en continue.

Pour ce point, je propose de mettre à jour les couleurs données aux "états" quand on quitte la feuille BOM:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Private Sub Worksheet_Deactivate()
   '--- actualise es couleurs des types d'état dans les 2 modèles
   '--- mais ne le fait pas dans les autres onglets --- passer par Reset onglets
   Dim kR As Long, kColor As Long
   For kR = 2 To 10
      kColor = Cells(kR, 1).Interior.Color
      Worksheets("TypePiece").Cells(kR, 12).Interior.Color = kColor
      Worksheets("SousTraitance").Cells(kR, 14).Interior.Color = kColor
   Next kR
End Sub

Pour le Reset onglets, je propose de les supprimer tous, sauf ceux de base, puis de recréer selon les listes. Cela donne:

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Sub ResetTousOnglets()
   SupprimerOnglets
   ResetOnglets "Type_de_Piece"
   ResetOnglets "Sous_Traitance"
End Sub
 
Private Sub ResetOnglets(sTableau As String)
   Dim kR As Long, nR As Long, kC As Long, k As Long, sNomOnglet As String, sModele As String
   Sheets("Notice").Select
   With Sheets("Notice").ListObjects(sTableau).DataBodyRange
      nR = .Rows.Count
      kR = .Cells(1, 1).Row
      kC = .Cells(1, 1).Column
   End With
   For k = kR To kR + nR - 1
      sNomOnglet = Cells(k, kC)
      If sNomOnglet = "" Then
         '--- celule vide, ne rien faire
      Else
         Debug.Print sNomOnglet
         '--- crée nouvelle feuille (onglet) vide
         If sTableau = "Type_de_Piece" Then sModele = "TypePiece"
         If sTableau = "Sous_Traitance" Then sModele = "SousTraitance"
         Application.DisplayAlerts = False         '--- sinon alerte recopie lien tableau Type_etat
         Sheets(sModele).Copy After:=Sheets("BOM")
         Application.DisplayAlerts = True
         Sheets(sModele & " (2)").Visible = True
         Sheets(sModele & " (2)").Name = sNomOnglet
         Sheets("Notice").Select
      End If
   Next k
End Sub
 
Private Sub SupprimerOnglets()
   Dim k As Long, sOnglet(99) As String
   If MsgBox("Supprimer tous les onglets (sauf Notice, BOM) et recréer" & vbCrLf & _
             "les onglets indiqués en Type de pièces et Sous traitance?", _
             vbYesNo, "A confirmer") = vbYes Then
      For k = 1 To ActiveWorkbook.Sheets.Count
         sOnglet(k) = Worksheets(k).Name
      Next k
      For k = 1 To ActiveWorkbook.Sheets.Count
         Select Case sOnglet(k)
            Case "Notice", "TypePiece", "SousTraitance", "BOM", "param" '--- attention: ne pas en oublier
               '--- ne rien faire, conserver
            Case Else
               Application.DisplayAlerts = False
               Worksheets(sOnglet(k)).Delete
               Application.DisplayAlerts = True
         End Select
      Next k
   End If
End Sub

Bonne continuation.

[Benzoid69]

Bonjour,

Je viens de regarder que maintenant ce que tu as réalisé. Je t'en félicite.
J'ai un peu de mal à comprendre comment sa marche par moment, mais c'est normal.

Ce qui est légèrement dommage, c'est le temps de mise à jour de la colonne Etat dans l'onglet BOM à chaque fois.
Vu que les couleurs des états ne sont modifiés que très rarement (personnalisation), la mise à jour devrait se faire que si on modifié une couleur des états.

Au lieu de créer un bouton "Reset onglets", cela serait bien d'avoir un bouton qui:
-Met à jour la colonne état
-

mettre à jour les couleurs données aux "états" quand on quitte la feuille BOM

-

supprimer tous, sauf ceux de base, puis de recréer selon les listes

Je ne vois plus rien à améliorer appart cela.

Encore un grand merci.

[EricDgn]

Une façon de faire est de modifier la procédure Worksheet_SelectionChange dans la feuille BOM:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Private Sub Worksheet_SelectionChange(ByVal target As Range)
   If target.Column = 1 And target.Row < 11 Then
      FormatEtatColonne 3
   Else
      FormatEtat target, 3
   End If
End Sub

et d'y supprimer la procédure Worksheet_Activate

Bonne continuation