VBA Excel - création onglet + Worksheet_Chang [Résolu/Fermé]

Signaler
Messages postés
56
Date d'inscription
jeudi 15 février 2007
Statut
Membre
Dernière intervention
2 décembre 2013
-
Messages postés
56
Date d'inscription
jeudi 15 février 2007
Statut
Membre
Dernière intervention
2 décembre 2013
-
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.

11 réponses

Messages postés
16369
Date d'inscription
mardi 12 juin 2007
Statut
Contributeur
Dernière intervention
8 août 2020
9 608
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


Messages postés
56
Date d'inscription
jeudi 15 février 2007
Statut
Membre
Dernière intervention
2 décembre 2013

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

;-)
Messages postés
23419
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
8 août 2020
6 282
Bonjour,

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

eric
Messages postés
56
Date d'inscription
jeudi 15 février 2007
Statut
Membre
Dernière intervention
2 décembre 2013

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
Messages postés
23419
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
8 août 2020
6 282
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
Messages postés
56
Date d'inscription
jeudi 15 février 2007
Statut
Membre
Dernière intervention
2 décembre 2013

ok, développe un peu plus alors STP, je débute moi en VBA...
Messages postés
23419
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
8 août 2020
6 282
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.
Messages postés
56
Date d'inscription
jeudi 15 février 2007
Statut
Membre
Dernière intervention
2 décembre 2013

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
Messages postés
23419
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
8 août 2020
6 282
Bonjour,

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

eric
Messages postés
56
Date d'inscription
jeudi 15 février 2007
Statut
Membre
Dernière intervention
2 décembre 2013

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...
Messages postés
56
Date d'inscription
jeudi 15 février 2007
Statut
Membre
Dernière intervention
2 décembre 2013

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
Messages postés
23419
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
8 août 2020
6 282
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...
Messages postés
56
Date d'inscription
jeudi 15 février 2007
Statut
Membre
Dernière intervention
2 décembre 2013

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
Messages postés
23419
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
8 août 2020
6 282
code que tu avais mis dans l'évènement Change et qui aurait été appelé à chaque modif !!!!
Messages postés
56
Date d'inscription
jeudi 15 février 2007
Statut
Membre
Dernière intervention
2 décembre 2013

forcément puisque le but c'est un formattage conditionnel, en fct de la valeur de la cellule!
Messages postés
23419
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
8 août 2020
6 282
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
Messages postés
56
Date d'inscription
jeudi 15 février 2007
Statut
Membre
Dernière intervention
2 décembre 2013

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!