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

Question

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.

m@rina · Answer

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

dry007 · Answer

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

eriiic · Answer

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

dry007 · Answer

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

dry007 · Answer

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

dry007 · Answer

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

dry007 · Answer

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...

dry007 · Answer

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

dry007 · Answer

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

dry007 · Answer

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

dry007 · Answer

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!