Nom d'une cellule nommée qui disparaît

criscris11 · 17 Septembre 2007

Bonsoir à tous,
Voici un code qui me pose un petit problème :

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > Range("Debut").Row And Target.Row < Range("Fin2").Row Then
    If Range("Fin2").Row > 170 Then
        ActiveSheet.Range("A175").EntireRow.Delete
    End If
    ActiveWorkbook.Names.Add Name:="Fin2", RefersToR1C1:="=Liste batterie!R170C1"
End If
If Target.Row > Range("Debut").Row And Target.Row < Range("Fin1").Row Then
    If Range("Fin1").Row < 165 Then
        ActiveSheet.Range("A175").EntireRow.Insert Shift:=xlDown
    End If
    ActiveWorkbook.Names.Add Name:="Fin1", RefersToR1C1:="=Liste batterie!R165C1"
End If
End Sub

Cet événement se déclenche à chaque fois que j'insère ou supprime une lignes dans 3 onglets de mon fichier dont voici les codes :

Code:

Sub insert_lig()
    Application.ScreenUpdating = False
    Sheets(Array("Liste batterie", "Absences", "Feuille des présents")).Select
    Sheets("Liste batterie").Activate
    Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
    Selection.Insert Shift:=xlDown
    Application.ScreenUpdating = True
End Sub
Sub suppr_lig()
    Application.ScreenUpdating = False
    Sheets(Array("Liste batterie", "Absences", "Feuille des présents")).Select
    Sheets("Liste batterie").Activate
    Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
    Selection.Delete
    Sheets("Liste batterie").Select
    Application.ScreenUpdating = True
End Sub

Hors les deux cellules nommées (Fin1 et Fin2) ne sont pas renommées comme le prévoit la macro pourtant elles figurent bien et en bonne place dans Insertion, Nom, Définir.
Dernière chose, ces codes sont issus d'un fichier qui avait réalisé sur le forum et ce fichier fonctionne très bien : je poste les deux fichiers afin de voir une différence éventuelle.

Si quelqu'un a une autre proposition, pas de problème : le but est que si l'une des 2 macros Sub insert_lig() ou Sub suppr_lig() est lancée, on insère ou supprime une ligne de façon à ce que toutes lignes à partir de 180 ne bougent jamais (filtre élaboré par macro).

Spitnolan08 · 20 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Re,

Avec ce code c'est presque bon mais je n'efface pas toutes les données

Code:

Sub insert2()
Dim Cel As Range
    Application.ScreenUpdating = False
    Set Cel = ActiveCell
    Sheets(Array("Liste batterie", "Absences", "Feuille des présents")).Select
    Sheets("Liste batterie").Activate
    Cel.EntireRow.Select
     If Selection.Row > 3 Then
          Selection.EntireRow.insert
          Selection.FillDown
     ElseIf Selection.Row = 3 Then
          Selection.EntireRow.insert
          Selection.FillUp
     End If
     With Cel.Offset(-1, 0).EntireRow
        .SpecialCells(xlCellTypeConstants, xlTextValues).ClearContents
     End With
Application.ScreenUpdating = True
End Sub

Cordialement

JCGL · 20 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Bonsoir à tous,

Juste un coucou à mon ami Spit que je n'ai croisé sur un fil depuis longtemps

Bonne soirée à tous

criscris11 · 20 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Re,
Spit j'ai toujours le même problème dans les 2 onglets cités dans mon dernier post à savoir que je perds les données de la ligne au dessus de l'insertion : 1 personnel en mois avec toutes les données qui s'y réfèrent.
Merci en attendant et bonne soirée.

Spitnolan08 · 20 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Re,
Salut JCGL

,

Pas trop là en ce moment donc c'est normal...

@CrisCris : effectivement je viens de m'en rendre compte! En essayant de résoudre l'effacement des données autres que les formules... Pourtant ça a marché tout à l'heure

Comprends pas...

Cordialement

criscris11 · 20 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Re,
En attendant, merci pour ta persévérance : elle vas finir par payer.
Bonne soirée.

Spitnolan08 · 20 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Re,

Avec ce code, ça fonctionne. Je te laisse regarder en enlevant les enableevents...
Il faut bien que tu bosses un peu...

Code:

Sub insert3()
Dim Cel As Range, Plage As Range, Cel2 As Range
Dim Lig As Integer, Col As Integer
Application.EnableEvents = False
Application.ScreenUpdating = True
Set Cel = ActiveCell
Lig = Cel.Row
    For Each sh In Sheets(Array("Liste batterie", "Absences", "Feuille des présents"))
        With sh
            If .Cells(Lig, 1) > 3 Then
                  .Cells(Lig, 1).EntireRow.insert
                  .Cells(Lig, 1).EntireRow.FillDown
            ElseIf .Cells(Lig, 1) = 3 Then
                  .Cells(Lig, 1).EntireRow.insert
                  .Cells(Lig, 1).EntireRow.FillUp
            End If
             
            Col = .Range("IV2").End(xlToLeft).Column
            Set Plage = .Range(.Cells(Lig, "A"), .Cells(Lig, Col))
            For Each Cel2 In Plage
               If Cel2.HasFormula = False Then Cel2.ClearContents
            Next
        End With
    Next
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

La seule chose que je ne comprends pas c'est que je suis obligé de sélectionner la feuille pour définir Plage

(Edit2 : Problème corrigé suite au post de bhbh)

Cordialement

Cousinhub · 20 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Bonsoir spit

Set Plage = .Range(Cells(Lig, "A"), Cells(Lig, Col))

sans tester :

Set Plage = .Range(.Cells(Lig, "A"), .Cells(Lig, Col))

ne serait pas mieux?

Spitnolan08 · 20 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Re,
Bonsoir bhbh,

Bien vu, merci bhbh.
Je n'y vois plus rien....

Je modifie en conséquence le code du post précédent

Cordialement

criscris11 · 21 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Re,
Merci Spit, ton dernier code fonctionne mais la macro est lente dans mon fichier original qui contient beaucoup de données et des formules matricielles dans d'autres feuilles : il va falloir que je désactive le clacul automatique avant l'éxécution de ton code et le réactiver en fin de code.
Mais avant j'attends la modif suite à la remarque de bh².
Merci et bonne soirée.
@bh² : bien reçu le mail ?

Vu l'édit de 23h59 de Spit à propos de la modif.

Spitnolan08 · 21 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Re,

Mais avant j'attends la modif suite à la remarque de bh².

Euhhhhh! C'est déjà fait depuis un moment.... voir le post concerné...

Ah ben, tu viens de le voir

Cordialement

criscris11 · 21 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Oui, bien vu ta modif. Merci je teste cà en grandeur nature dans mon fichier.
Bonne soirée.

criscris11 · 21 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Re,
Je viens de tester cà fonctionne mais il reste 2 petits détails à régler :
le premier pas de problème : en fin de code sélectionner les 3 onglets pour y saisir le garde et le nom,
par contre le deuxième : cà va être plus dur pour moi

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > Range("Debut").Row And Target.Row < Range("Fin2").Row Then
    If Range("Fin2").Row > 170 Then
        ActiveSheet.Range("A175").EntireRow.Delete
    End If
    ActiveWorkbook.Names.Add Name:="Fin2", RefersTo:="='Liste batterie'!$A$170"
End If
If Target.Row > Range("Debut").Row And Target.Row < Range("Fin1").Row Then
    If Range("Fin1").Row < 165 Then
        ActiveSheet.Range("A175").EntireRow.Insert Shift:=xlDown
    End If
    ActiveWorkbook.Names.Add Name:="Fin1", RefersTo:="='Liste batterie'!$A$165"
End If
End Sub

cette procédure événementielle ne fonctionne plus (voir première demande du fil).
Voici l'ensemble du code :

Code:

Sub insert_lig() 'raccourci CTRL+i
    Dim Cel As Range, Plage As Range, Cel2 As Range
    Dim Lig As Integer, Col As Integer
    Application.EnableEvents = False
    Application.ScreenUpdating = True
    With Application
        .Calculation = xlManual
        .MaxChange = 0.001
    End With
    ActiveWorkbook.PrecisionAsDisplayed = False
Set Cel = ActiveCell
Lig = Cel.Row
    For Each sh In Sheets(Array("Liste batterie", "Absences", "Feuille des présents"))
        With sh
            If .Cells(Lig, 1) > 3 Then
                  .Cells(Lig, 1).EntireRow.Insert
                  .Cells(Lig, 1).EntireRow.FillDown
            ElseIf .Cells(Lig, 1) = 3 Then
                  .Cells(Lig, 1).EntireRow.Insert
                  .Cells(Lig, 1).EntireRow.FillUp
            End If
 
            Col = .Range("IV2").End(xlToLeft).Column
            Set Plage = .Range(.Cells(Lig, "A"), .Cells(Lig, Col))
            For Each Cel2 In Plage
               If Cel2.HasFormula = False Then Cel2.ClearContents
            Next
        End With
    Next
Application.ScreenUpdating = True
Application.EnableEvents = True
With Application
        .Calculation = xlAutomatic
        .MaxChange = 0.001
    End With
    ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

Voyez-vous d'où cà vient ?
Merci encore et bonne soirée.

En fait les cellules nommées Fin1 et Fin2 sont décalées en A166 au lieu de A165 pour Fin1 et en A171 au lieu de A170 pour Fin2.

Spitnolan08 · 21 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Re,

Question:

Voyez-vous d'où cà vient ?

Réponse dans mon post de 23h46:

Avec ce code, ça fonctionne. Je te laisse regarder en enlevant les enableevents...

Ce sont eux qui désactivent tes procédures évènementielles...
Ca me fatiguait pour les tests...

Cordialement

Spitnolan08 · 21 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Re,

J'ai oublié :

En fait les cellules nommées Fin1 et Fin2 sont décalées en A166 au lieu de A165 pour Fin1 et en A171 au lieu de A170 pour Fin2.

C'est normal puisqu'on ajoute une ligne mais que ta macro évènementielle qui décale la référence du nom de plage ne fonctionne pas pour la raison indiquée dans mon post précédent.

Bonne nuit et bons tests...
Cordialement

criscris11 · 21 Septembre 2007

Re : Nom d'une cellule nommée qui disparaît

Re,
Merci Spit. Je ne savais pas que

Code:

Application.EnableEvents = False

désactivait les procédures événementielles (c'est bon a savoir

).
Je n'avais, en plus, pas fait le rapprochement quand tu en as parlé dans ton post de 23h46.
Merci pour le coup de main et pour l'instruction (j'irais me coucher moins bête ce soir

).
Bonne journée et au plaisir de te croiser sur le forum de nouveau.

Effectuez une recherche sur Excel Downloads...

Nom d'une cellule nommée qui disparaît

criscris11

XLDnaute Accro

Pièces jointes

Spitnolan08

XLDnaute Barbatruc

JCGL

XLDnaute Barbatruc

criscris11

XLDnaute Accro

Spitnolan08

XLDnaute Barbatruc

criscris11

XLDnaute Accro

Spitnolan08

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Spitnolan08

XLDnaute Barbatruc

criscris11

XLDnaute Accro

Spitnolan08

XLDnaute Barbatruc

criscris11

XLDnaute Accro

criscris11

XLDnaute Accro

Spitnolan08

XLDnaute Barbatruc

Spitnolan08

XLDnaute Barbatruc

criscris11

XLDnaute Accro

Discussions similaires

Membres actuellement en ligne

Partager cette page

Tutos vidéos

Statistiques des forums