VBA Excel - création onglet + Worksheet_Chang

Résolu
dry007 Messages postés 62 Statut Membre -  
dry007 Messages postés 62 Statut Membre -
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.

--
si tous les gens du monde voulaient bien se donner la main... mais si tous les **** pouvaient aussi lâcher la mienne! [lol]

11 réponses

  1.
    m@rina Messages postés 27542 Date d'inscription   Statut Modérateur Dernière intervention   11 562
     
    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

    0
  2. dry007 Messages postés 62 Statut Membre
     
    Sub creerOnglet(ByVal nom As String, ByVal couleur As Integer)

    ;-)
    0
  3. eriiic Messages postés 24581 Date d'inscription   Statut Contributeur Dernière intervention   7 281
     
    Bonjour,

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

    eric
    0
  4. dry007 Messages postés 62 Statut Membre
     
    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
    0
    1. eriiic Messages postés 24581 Date d'inscription   Statut Contributeur Dernière intervention   7 281
       
      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
      0

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

    Posez votre question
  6. dry007 Messages postés 62 Statut Membre
     
    ok, développe un peu plus alors STP, je débute moi en VBA...
    0
    1. eriiic Messages postés 24581 Date d'inscription   Statut Contributeur Dernière intervention   7 281
       
      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.
      0
  7. dry007 Messages postés 62 Statut Membre
     
    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
    0
    1. eriiic Messages postés 24581 Date d'inscription   Statut Contributeur Dernière intervention   7 281
       
      Bonjour,

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

      eric
      0
  8. dry007 Messages postés 62 Statut Membre
     
    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...
    0
  9. dry007 Messages postés 62 Statut Membre
     
    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
    0
    1. eriiic Messages postés 24581 Date d'inscription   Statut Contributeur Dernière intervention   7 281
       
      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...
      0
  10. dry007 Messages postés 62 Statut Membre
     
    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
    0
    1. eriiic Messages postés 24581 Date d'inscription   Statut Contributeur Dernière intervention   7 281
       
      code que tu avais mis dans l'évènement Change et qui aurait été appelé à chaque modif !!!!
      0
  11. dry007 Messages postés 62 Statut Membre
     
    forcément puisque le but c'est un formattage conditionnel, en fct de la valeur de la cellule!
    0
    1. eriiic Messages postés 24581 Date d'inscription   Statut Contributeur Dernière intervention   7 281
       
      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
      0
  12. dry007 Messages postés 62 Statut Membre
     
    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!
    0