Discussion :

[clairon37]

Bonjour à tous,

Je me tourne vers vous pour un problème assez spécial que je n'arrive pas à résoudre bien qu'ayant fait moult recherches sur les forums...Je suis tombé sur un problème tout fait similaire à celui guilty_AL (http://www.developpez.net/forums/d13...oles-active-x/).

Je ne suis pas pro dans le codage VBA Excel. Mais le code que j’ai utilisé pour dupliquer une feuille modèle EXCEL contenant environ 50 contrôles activeX et qui initialise les contrôles. La nouvelle feuille prend alors le nom "FS_Lot_N°" & I ; I étant chiffre qui change en fonction du nombre de feuille (première feuille créée = FS_Lot_N°1, 2e feuille crée = FS_Lot_N°2,...).
Ce code fonctionne très bien pour les premières feuilles créées. Pour ce qui est de la 3e, le début du script fonctionne bien mais bug avec comme message d'erreur :
Erreur d'exécution '438' :
Propriété ou méthode d'exécution non gérée par cet objet

En visualisant la 3ème feuille, tous les contrôles y sont. Par contre, je constate qu’un bon nombre de contrôles ActiveX ont été changé en contrôle de formulaire (celui où il faut affecter une macro). On peut le voir en cliquant sur propriétés de ce boutons ou plus simplement via le projet VB où la plupart des boutons de commande ne se trouve plus sur la liste des objets, mais plutôt dans la liste des évènements.
Et plus j’augmente le nombre de feuilles à dupliquer, moins j’ai de contrôles ActiveX voir aucun car tous ont été transformés en contrôles de formulaires et pourtant les 2 premières feuilles dupliquées fonctionnent à merveille.
Voici mon code
Sub DuplicateSheetFicheSuiveuseGood()
Dim i As Integer
Dim numSheetFicheSuiveuse As Integer
Dim nbSheetFicheSuiveuse As Integer
Dim nbEndos As Integer
Dim nbEndosDansLot As Integer

Dim wb As Workbook
Dim ws As Worksheet
Dim NewSheet As Worksheet

Dim startNumFS As Integer
Dim aBaseColorIndex() As Variant
Dim oOle As OLEObject
Dim iMethode as integer

On Error Resume Next

bCreationFicheSuiveuseEnCours = True

‘Coloris disponible pour l’onglet et une partie de la feuille à appliquer
aBaseColorIndex = Array(5, 6, 38, 44, 32, 17, 10, 46)


'Récupère le nombre d'endoscopes générés
nbEndos = Worksheets("1-Saisie").Cells(5, 12).Value

‘Calcule le nombre de fiche suiveuse à créer
nbEndosDansLot = 10
nbSheetFicheSuiveuse = nbEndos \ nbEndosDansLot

‘Rend visible la fiche suiveuse modèle
Worksheets("3-FicheSuiveuse").Visible = True

‘Index de la feuille de fiche suiveuse modèle
numSheetFicheSuiveuse = Worksheets("3-FicheSuiveuse").Index

‘Vide le presse-papier
Application.CutCopyMode = False

‘Autres moyens pour vider le presse-papier
‘VidePressePapier
'Application.CommandBars("Clipboard").Controls("&Vider le Presse-Papiers").Execute
‘Application.CommandBars("clipboard").Controls(4).Execute


‘Copie les objets avec les cellules
Application.CopyObjectsWithCells = True

‘Désactive tous les élements
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

‘Deux methodes pour la duplication de la feuille
iMethode = 1
For p = 1 To nbSheetFicheSuiveuse
If iMethode = 0 Then
Set ws = ThisWorkbook.Worksheets.Add
With ws
.Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
.Name = "3-FS_Lot_N" & p
ThisWorkbook.Worksheets("3-FicheSuiveuse").Cells.Copy Destination:=ThisWorkbook.Worksheets("3-FS_Lot_N" & p).Range("A1")
End With
Set ws = Nothing

With ThisWorkbook.Worksheets("3-FS_Lot_N" & p) 'IN=nitialisation
.Unprotect Password:="xxx" ‘Dévérouille les cellules
.DTPicker1.Value = Date
.Range("J1").Value = Format(Now, "YY") & Format(Format(Now, "ww", vbMonday, vbFirstFourDays), "00") & Format(Weekday(Now, vbMonday), "00")
.ComboBox_NumeroLot.Value = Format(i + 1, "000")
.ComboBox_QuantiteDansLot.Value = 10
.Range("A14").Interior.ColorIndex = aBaseColorIndex(i)
.Tab.color = ThisWorkbook.Colors(aBaseColorIndex(i))
End With

Else

ThisWorkbook.Worksheets("3-FicheSuiveuse").Copy Before:=ThisWorkbook.Worksheets("4-FC_ENVOI")
Application.CutCopyMode = False ' VidePressePapier

Set NewSheet = ThisWorkbook.Worksheets(Worksheets("4-FC_ENVOI").Index - 1)
NewSheet.Name = "3-FS_Lot_N" & p

With ThisWorkbook.Worksheets("3-FS_Lot_N" & p) 'Initialisation
.Unprotect Password:="xxx"
.DTPicker1.Value = Date
.Range("J1").Value = Format(Now, "YY") & Format(Format(Now, "ww", vbMonday, vbFirstFourDays), "00") & Format(Weekday(Now, vbMonday), "00")
.ComboBox_NumeroLot.Value = Format(p + 1, "000")
.ComboBox_QuantiteDansLot.Value = 10
.Range("A14").Interior.ColorIndex = aBaseColorIndex(p) 'colorLot(i)
.Tab.color = ThisWorkbook.Colors(aBaseColorIndex(p))
End With
End If
Set NewSheet = Nothing
Next

‘Masque la fiche suiveuse- modèle
Worksheets("3-FicheSuiveuse").Visible = False

‘Réactive tous les évènements
bCreationFicheSuiveuseEnCours = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

MsgBox nbSheetFicheSuiveuse & " fiches suiveuses ont été créees !"

End Sub

Au début, j’ai pensé à un problème de mémoire, mais rien n’y fait.
J'ai utilisé plusieurs moyens pour vider le presse-papier
Pour vider le presse-papier :
Application.CutCopyMode = False

Ou encore

Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Sub VidePressePapier()
OpenClipboard 0
EmptyClipboard
CloseClipboard
End Sub

J’ai fait attention à nommer les feuilles et d’éviter tant que possible « les ActiveSheet ».

J’ai même essayé de créer un modèle xltm pour cette fiche, de manière à l’insèrer dans mon classeur autant de fois que je voudrais, mais le même problème.
Et pour couronner le tout, cela bug sans passer par les macros càd que je copie manuellement la feuille en cliquant sur l’onglet, puis « Déplacer ou Copier », Cocher la case « Créer une copie ». J’ai exactement le même problème que celui que j’ai en exécutant la macro à partir de la 3 voire même 4 feuille.
Je m'arrache les cheveux. S'agit-il d'un bug Excel ?
Pourquoi les controles ActiveX ont été changés en contrôles de formulaires

Ce problème me perturbe fortement car il est à la base du formulaire que je veux créer...
Cela me parait incroyable de ne pas pouvoir le faire !

Je vous remercie d'avance pour votre aide, quelle qu’elle soit!

[Kimy_Ire]

Bonjour clairon37,

La première chose que je te conseille quand ça plante, c'est d'enlever le On Error Resume Next afin de voir où plante exactement la macro.
Idem pour tous ces éléments :

Code :

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

1
2
3
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Fais passer la macro en mode "Pas à pas" pour voir où se situe le problème.

Ensuite, je suppute au travers de tes lignes, relativement propres, une erreur potentielle dans la récupération des feuilles : le Set NewSheet = ThisWorkbook.Worksheets(Worksheets("4-FC_ENVOI").Index - 1).
Pars de quelque chose de simple, comme ceci :

Code :

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

1
2
3
4
5
6
7
8
Sub test()
Worksheets("Feuil1").Copy After:=Worksheets(Worksheets.Count)
 
With Worksheets(Worksheets.Count)
    .Name = "test"
    'je fais ce que je veux
End With
End Sub

Là, tu es sûr de travailler sur la bonne feuille.
On la replacera plus tard !

N'hésite pas à revenir vers moi !

Cordialement,
Kimy

[clairon37]

Merci pour votre aide. J’ai suivi vos conseils et même simplifié le code en enlevant le « On Resume Next », et laissant les EnableEvents et DisplayAlerts à True. Et en désactivant l’initialisation du formulaire .

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
Sub DuplicateSheetFicheSuiveuseTest()
 Dim i, p As Integer
 Dim numSheetFicheSuiveuse As Integer
 Dim nbSheetFicheSuiveuse As Integer
 Dim nbEndos As Integer
 Dim nbEndosDansLot As Integer
 
 Dim wb As Workbook
 Dim ws As Worksheet
 Dim NewSheet As Worksheet
 
 Dim startNumFS As Integer
 Dim aBaseColorIndex() As Variant
 Dim oOle As OLEObject
 
'Coloris disponible pour l’onglet et une partie de la feuille à appliquer
 aBaseColorIndex = Array(5, 6, 38, 44, 32, 17, 10, 46)
 
 'Récupère le nombre d'endoscopes générés
 nbEndos = Worksheets("1-Saisie").Cells(5, 12).Value
 
'Calcule le nombre de fiche suiveuse à créer
 nbEndosDansLot = 10
 nbSheetFicheSuiveuse = nbEndos \ nbEndosDansLot
 
'Rend visible la fiche suiveuse modèle
 Worksheets("3-FicheSuiveuse").Visible = True
 
'Vide le presse-papier
 Application.CutCopyMode = False
 
'Copie les objets avec les cellules
 Application.CopyObjectsWithCells = True
 
'Duplication de la feuille (ICI nbSheetFicheSuiveuse = 5, donc on copie 5 fois la feuille)
 For p = 1 To nbSheetFicheSuiveuse
         ThisWorkbook.Worksheets("3-FicheSuiveuse").Copy After:=Worksheets(Worksheets.Count) 
         Application.CutCopyMode = False ' VidePressePapier
 Next
 
MsgBox nbSheetFicheSuiveuse & " fiches suiveuses ont été créees !"
 
 End Sub

ICI, le nombre de feuilles à copier est 5.
Le code a bien créé les 5 feuilles, mais en regardant de plus près dans le projet Visual Basic et en regardant spécifiquement dans le contenu de la zone « Objets et dans celle de « Procédures/Evènements », rien ne va plus (voir les 3 images ci-attachées).

Les 3 premières feuilles semblent etre bien copiées avec les 17 ComboBox (COmbox_1 à ComboBox_17) et les 27 CommandButtons (CmdBtn_1 à CmdBtn_27) + 9 boutons de commandes diverses.
Dans la 4ème feuille, je n’ai plus que les premiers 3 ComboBox et 17 CommandButtons et 5 boutons de commandes divers.
Dans la 5ème, je n’ai plus que le Worksheet dans la zone « Objet » mais je vois les autres dans la zone « Procedures/Evenements ».
En conclusion, tous les boutons de contrôle ActiveX ont été transformées en contrôles de formulaires.

Pourquoi y-a-t-il eu ce changement de type de contrôles ?

[Kimy_Ire]

Bonjour clairon37,

Je n'ai pas la réponse à ton problème spécifique.
En revanche, quelque chose me semble évident :

• Tu as dix milliards de Contrôle ActiveX identiques sur plusieurs feuilles différentes.
• Tu as dix milliards de feuilles de ton classeur
• Tu as des modules, des modules de classe
• Bref... tu as plein d'éléments différents.

Je ne connais pas le fonctionnel de ton projet mais tu marches de travers !
Pour ma part, je ne comprends pas pourquoi tu ne passes pas par un UserForm (identique quelque soit la feuille qui l'appelle) et qui contiendrais les éléments souhaités, une seule fois.

Je pense que tu dois faire un effort de réflexion et repenser ta solution.

Quant à ton problème spécifique, je laisse les plus experts que moi te répondre. Surement un problème de mémoire, de compatibilité, de vitesse d'exécution, etc...
Tu bourres ton pauvre classeur.

Je reste disponible tout de même.

Cordialement,
Kimy

[clairon37]

Bonjour Kimy,

Certes, j'ai peut-être un milliard de controles dans mon classeur, mais j'avoue que je ne vois pas comment faire simple et surtout opérationnelle.

Sur chaque feuille, j'ai 30 items à gérer. A chacun des items, j'associe une liste déroulante contenant la liste de noms de personnes, et un bouton de commande permettant d'appeler un formulaire Word spécifique de cet item..
Pour le moment j'ai besoin à chaque création de créeer 5 fois la même feuille (Sheet_1, Sheet_2,...)

Une des raisons pour laquelle je n'ai pas utiliser le UserForm est que je ne peux appeler le même UserForm plusieurs fois en même temps sauf dans Microsoft Access (si j'ai bien compris d'après les forums), est-ce réellement le cas ?

J'ai comme l'impression qu'Excel doit gérer au maximum un certain nombre de controles ActiveX (environ 200 controles dans un classeur entier), est-ce le cas, ou est-ce un bug dans ma manière de copier les feuilles ...

J'ai simplifié au maximum le fichier Excel, avec un bouton d'action pour dupliquer 5 fois la feuille ("Sheet_Model"), et une fonction commune à tous les boutons de commande "Action_CommandButton" dans le module.

J'espère que le fichier Excel macro sera bien attaché à ce message.

Si vous avez des suggestions à me recommander, je suis toute ouïe.
Merci d'avance pour votre aide !

[clairon37]

Avec la pièce attachéTEST_BUG.xlsm