VBA Excel - création onglet + Worksheet_Chang

Résolu/Fermé

dry007 Messages postés 56 Date d'inscription jeudi 15 février 2007 Statut Membre Dernière intervention 2 décembre 2013 - 25 avril 2012 à 15:42
dry007 Messages postés 56 Date d'inscription jeudi 15 février 2007 Statut Membre Dernière intervention 2 décembre 2013 - 26 avril 2012 à 11:50

salut, salut!

encore une fois je viens faire appel aux spécialistes parce que j'en aimarre de m'arracher les cheveux!

plantons le décors:

-> une procédure Excel qui doit me créer dynamiquement un onglet et générer la Worksheet_Change associée

-> le code:

Sub creerOnglet(ByVal nom As String, ByVal couleur As Integer)

Sheets.Add Worksheets(1)
Sheets(1).Name = nom
ActiveWorkbook.Sheets(nom).Tab.ColorIndex = couleur

Code = "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf
Code = Code & "Dim c As Range" & vbCrLf
Code = Code & "For Each c In Target" & vbCrLf
Code = Code & " Sheets(" & nom & ").Range(""H"" & c.Row).Select" & vbCrLf
Code = Code & " Selection.FormatConditions.Delete" & vbCrLf
Code = Code & " Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=Sheets(""DATA"").Range(""L16"").Value" & vbCrLf
Code = Code & " Selection.FormatConditions(1).Interior.ColorIndex = 3" & vbCrLf
Code = Code & "Next c" & vbCrLf
Code = Code & "End Sub"
With ActiveWorkbook.VBProject.VBComponents(Sheets(nom).CodeName).CodeModule
.InsertLines .CountOfLines + 1, Code
End With

End Sub

-> l'erreur: erreur d'exécution 9 - l'indice n'appartient pas à la sélection

-> ce qui voudrait donc dire que Sheets(nom) n'existe pas en somme

or, l'onglet en question vient d'être crée par le code 10 lignes plus haut, et il est bien visible dans mon classeur(????).........

une idée peut être?

merci.

A voir également:

VBA Excel - création onglet + Worksheet_Chang
Liste déroulante excel - Guide
Formule excel - Guide
Creation compte gmail - Guide
Media creation tool - Télécharger - Systèmes d'exploitation
Création compte google - Guide

11 réponses

Réponse 1 / 11

m@rina Messages postés 20033 Date d'inscription mardi 12 juin 2007 Statut Contributeur Dernière intervention 19 avril 2024 11 269
25 avril 2012 à 18:45

Bonsoir,

Je n'ai pas bien suivi... Ta feuille s'appelle "nom" ???

Si c'est vraiment ça le nom de ta feuille (et non pas une variable), il faut le mettre entre guillemets :
Sheets(1).Name = "nom"
ActiveWorkbook.Sheets("nom").Tab.ColorIndex = couleur

S'il s'agit d'une variable, il faut la déclarer et attribuer une valeur à la variable :
Dim nom
nom="mafeuille"

m@rina

Réponse 2 / 11

dry007 Messages postés 56 Date d'inscription jeudi 15 février 2007 Statut Membre Dernière intervention 2 décembre 2013
25 avril 2012 à 18:47

Sub creerOnglet(ByVal nom As String, ByVal couleur As Integer)

;-)

Réponse 3 / 11

eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
25 avril 2012 à 19:17

Bonjour,

et pourquoi ne pas mettre le code en permanence dans Thisworkbook et utiliser Workbook_SheetChange ?

eric

Réponse 4 / 11

dry007 Messages postés 56 Date d'inscription jeudi 15 février 2007 Statut Membre Dernière intervention 2 décembre 2013
25 avril 2012 à 19:24

simplement parce que c'est de cette façon dont j'en ai besoin...
-> JE DOIS pouvoir créer dynamiquement un onglet et son Worksheet_Change associé
-> le tout par du code VBA

eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
Modifié par eriiic le 25/04/2012 à 19:44

Dommage, c'est plus simple.
Tu peux créer ton onglet et le gérer par cet évènement, le nom de l'onglet est reçu en paramètre.
Mais si c'est un exercice. ..
eric

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 11

dry007 Messages postés 56 Date d'inscription jeudi 15 février 2007 Statut Membre Dernière intervention 2 décembre 2013
25 avril 2012 à 19:50

ok, développe un peu plus alors STP, je débute moi en VBA...

eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
Modifié par eriiic le 25/04/2012 à 20:13

Sélectionne ThisWorkbook et saisi ton code dans l''évènement Workbook_SheetChange.
Il faut juste ajouter le test du nom de la feuille .

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)  
  if sh.name = "tonNomd'onglet" then  
    'ton code  
  endif  
End Sub

Eventuellement utiliser une variable publique à déclarer hors procédure dans un module pour passer le nom.

Pour info j'ai tester ton code, j'ai un autre code d'erreur même ligne.
1004 : L'acces par programme au projet visual basic n'est pas fiable.
Peut-être des droits à activer (?)

eric

EDIT : Si tu tiens à ta solution : Menu Outils d'Excel -> Macro -> Sécurité -> Onglets Sources fiables -> Cocher "Faire confiance au projet Visual Basic" et ça passe.
Enfin ça passe... excel crash violemment mais le code est présent dans l'onglet lorsqu'on rouvre.

Réponse 6 / 11

dry007 Messages postés 56 Date d'inscription jeudi 15 février 2007 Statut Membre Dernière intervention 2 décembre 2013
26 avril 2012 à 08:56

ok, je vais voir ce que ça donne...

pour le problème que tu as eu:

1.) oui si tu ne modifies pas les param de sécurité des macros ça plante, je sais, j'ai modifié chez moi dès le départ

2.) visiblement, pour l'avoir fait tester à un pote, il y a aussi des erreurs liées à la version (2003 chez moi)

précisions (bis):
pour moi c'était clair, mais desfois que, l'erreur que je signale est générée par la ligne de code en gras.

ce n'est pas le montage du code qui pose problème, c'est l'association à l'onglet

eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
26 avril 2012 à 09:11

Bonjour,

C'est sur 2003 aussi chez moi, et c'est passé.

eric

Réponse 7 / 11

dry007 Messages postés 56 Date d'inscription jeudi 15 février 2007 Statut Membre Dernière intervention 2 décembre 2013
26 avril 2012 à 09:32

1 différence déjà donc: chez moi après l'erreur, pas de code dans le module de la feuille(???).................. strictement NADA!

bon allez, je vais tester ta solution...

Réponse 8 / 11

dry007 Messages postés 56 Date d'inscription jeudi 15 février 2007 Statut Membre Dernière intervention 2 décembre 2013
26 avril 2012 à 09:55

OK, alors voilà le code dans ThisWorkBook comme tu disais:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.Name = "calendrier" Then
'ton code

'macro Worksheet_Change pour formattage rouge si heures >= heures max jour
Code = "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf
Code = Code & "Dim c As Range" & vbCrLf
Code = Code & "For Each c In Target" & vbCrLf
Code = Code & " Sheets(""calendrier"").Range(""H"" & c.Row).Select" & vbCrLf
Code = Code & " Selection.FormatConditions.Delete" & vbCrLf
Code = Code & " Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=Sheets(""DATA"").Range(""L16"").Value" & vbCrLf
Code = Code & " Selection.FormatConditions(1).Interior.ColorIndex = 3" & vbCrLf
Code = Code & "Next c" & vbCrLf
Code = Code & "End Sub"
With ActiveWorkbook.VBProject.VBComponents(Sheets("calendrier").CodeName).CodeModule
.InsertLines .CountOfLines + 1, Code
End With
End If
End Sub

problème: il me génère autant de Worksheet_Change() que je rempli de cellules de mon onglet!

donc si je vire manuellement les 50 occurrences superflues, il me fait bien mon formatage conditionnel oui.

mais il faudrait donc qu'il n'exécute qu'une seul fois le code

eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
Modifié par eriiic le 26/04/2012 à 10:05

et bien il ne faut pas le mettre dans l'évènement Change qui est appelé à chaque modif de la feuille.
Si tu ne veux qu'une exécution met le tout simplement dans un module et appelle-le à la suite de la création de la feuille.
Ou même directement à la suite de la création sans créer de sub pour lui (tout dépend si tu as plusieurs appels à faire)

eric

Edit: mais il va falloir que tu fixes la plage où s'applique la mise en forme car tu utilisais Target...

Réponse 9 / 11

dry007 Messages postés 56 Date d'inscription jeudi 15 février 2007 Statut Membre Dernière intervention 2 décembre 2013
Modifié par dry007 le 26/04/2012 à 10:10

c'est bien ce que je faisais au départ avec ma Sub creerOnglet(ByVal nom As String, ByVal couleur As Integer) et qui pose problème (code dans le 1er post)

-> création de l'onglet à la demande
-> ET affectation du code à cet onglet

eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
26 avril 2012 à 10:11

code que tu avais mis dans l'évènement Change et qui aurait été appelé à chaque modif !!!!

Réponse 10 / 11

dry007 Messages postés 56 Date d'inscription jeudi 15 février 2007 Statut Membre Dernière intervention 2 décembre 2013
26 avril 2012 à 10:41

forcément puisque le but c'est un formattage conditionnel, en fct de la valeur de la cellule!

eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
26 avril 2012 à 11:07

Rien à voir avec le fait d'utiliser l'évènement Change que tu as choisi...
Il sera appelé à modif de cellule et toi veux que le code ne soit exécuté qu'une fois.
Soit cohérent dans tes choix

Réponse 11 / 11

dry007 Messages postés 56 Date d'inscription jeudi 15 février 2007 Statut Membre Dernière intervention 2 décembre 2013
26 avril 2012 à 11:50

moi je suis cohérent, c'est toi qui ne comprends pas mon besoin.

mais problème réglé, j'ai trouvé une alternative.
-> peut être pas 100% dans les normes, mais ça marche!

Newsletters