Problème sur actualisation d'un plage nommée

[GeoTrouvePas]

Bonjour,

Avec l'aide d'Hasco, j'ai réalisé une petite procédure qui me permet d'actualiser une plage nommée en intégrant les lignes qui y ont été rajoutées.

Au niveau du code, ça donne ça :

Sub Actualiser_Plage(Plage_A_Actualiser As Range)
    Dim Cptr As Long
    Dim NomPlage As Name
    With Plage_A_Actualiser
        Set NomPlage = .Name
        .Cells.Interior.Color = RGB(102, 102, 53)
        .Borders.LineStyle = xlNone
        Cptr = 1
        While .Item(Cptr + 1, 1) <> ""
            Cptr = Cptr + 1
        Wend
        With .Resize(Cptr, .Columns.Count)
            NomPlage.RefersTo = "=" & .Parent.Name & "!" & .Address
            .Interior.Color = RGB(255, 255, 255)
            .Borders.LineStyle = xlNone
            .Borders.LineStyle = xlContinuous
            .Borders.Color = RGB(0, 0, 0)
            .Borders(xlEdgeLeft).Weight = xlThick
            .Borders(xlEdgeTop).Weight = xlThick
            .Borders(xlEdgeBottom).Weight = xlThick
            .Borders(xlEdgeRight).Weight = xlThick
            If .Rows.Count > 1 Then .Borders(xlInsideHorizontal).LineStyle = xlNone
            If .Columns.Count > 1 Then .Borders(xlInsideVertical).Weight = xlMedium
        End With
        With .Resize(1, .Columns.Count)
            .Borders.LineStyle = xlNone
            .Borders.LineStyle = xlContinuous
            .Borders.Color = RGB(0, 0, 0)
            .Borders(xlEdgeLeft).Weight = xlThick
            .Borders(xlEdgeTop).Weight = xlThick
            .Borders(xlEdgeBottom).Weight = xlThick
            .Borders(xlEdgeRight).Weight = xlThick
            If .Rows.Count > 1 Then .Borders(xlInsideHorizontal).LineStyle = xlNone
            If .Columns.Count > 1 Then .Borders(xlInsideVertical).Weight = xlMedium
        End With
    End With
End Sub

Lors de mes test, cela semblait fonctionner parfaitement mais, après intégration dans mon fichier, je me retrouve face à un bug que je n'arrive pas à résoudre.

J'utilise cette macro à partir de mon classeur principal en procédant de la sorte :
- L'utilisateur saisi des info dans un userform et le valide
- J'ouvre une classeur annexe dans une nouvelle instance Excel invisible
- Je rajoute les données saisies dans le première ligne située en dessous de la plage nommée "Base_PJ"
- Je lance ma macro pour actualiser cette plage
- J'enregistre mon classeur annexe et je le ferme.

Tout se passe sans problème sauf que, en le réouvrant le fichier annexe, la plage Base_PJ n'est plus "valide".

En faisant Insertion / Nom / Définir, je constate que les coordonnées de la plage sont bizarrement données comme ceci : =PJ!'L2C2':'L4C4'

Les apostrophes n'ont rien à faire là dedans et provoque une erreur.

J'ai beau chercher depuis 24h, impossible de résoudre ce problème.

Auriez vous une idée ?

 

[pierrejean]

Re : Problème sur actualisation d'un plage nommée

Bonjour GeoTrouvePas

Teste modifier

NomPlage.RefersTo = "=" & .Parent.Name & "!" & .Address
 
en 
 
NomPlage.RefersTo = "=" & .Parent.Name & "!" & .Address()

 

[kjin]

Re : Problème sur actualisation d'un plage nommée

Bonjour,
Je ne comprends pas bien dans la mesure où tu connais le nom de la plage à modifier, pourquoi ne pas n'utilises tu pas l'argument Name plutôt que Range dans la procédure ?

Sub ActuPlage()
Actualiser_Plage ThisWorkbook.Names("Maplage") 'adapter le classeur cible et le nom
End Sub

Sub Actualiser_Plage(ByRef Plage_A_Actualiser As Name)
Dim Cptr&, NomPlage As Range
Set NomPlage = Plage_A_Actualiser.RefersToRange
With NomPlage
    .Cells.Interior.Color = RGB(102, 102, 53)
    .Borders.LineStyle = xlNone
    Cptr = 1
    While .Item(Cptr + 1, 1) <> ""
        Cptr = Cptr + 1
    Wend
    With .Resize(Cptr, .Columns.Count)
        Plage_A_Actualiser.RefersTo = "=" & .Parent.Name & "!" & .Address
        .Interior.Color = RGB(255, 255, 255)
        .Borders.LineStyle = xlNone
        .Borders.LineStyle = xlContinuous
        .Borders.Color = RGB(0, 0, 0)
        .Borders(xlEdgeLeft).Weight = xlThick
        .Borders(xlEdgeTop).Weight = xlThick
        .Borders(xlEdgeBottom).Weight = xlThick
        .Borders(xlEdgeRight).Weight = xlThick
        If .Rows.Count > 1 Then .Borders(xlInsideHorizontal).LineStyle = xlNone
        If .Columns.Count > 1 Then .Borders(xlInsideVertical).Weight = xlMedium
    End With
    With .Resize(1, .Columns.Count)
        .Borders.LineStyle = xlNone
        .Borders.LineStyle = xlContinuous
        .Borders.Color = RGB(0, 0, 0)
        .Borders(xlEdgeLeft).Weight = xlThick
        .Borders(xlEdgeTop).Weight = xlThick
        .Borders(xlEdgeBottom).Weight = xlThick
        .Borders(xlEdgeRight).Weight = xlThick
        If .Rows.Count > 1 Then .Borders(xlInsideHorizontal).LineStyle = xlNone
        If .Columns.Count > 1 Then .Borders(xlInsideVertical).Weight = xlMedium
    End With
End With
End Sub

A+
kjin

 

[Jam]

Re : Problème sur actualisation d'un plage nommée

Bonjour GeoTrouvrePas, Pierrejean, kjin,

Si tu connais le nom alors il suffit juste de sélectionner la plage et de la renommer avec ce même nom. La référence sera actualisée automatiquement. Pour faire simple, cela peut donner quelque chose comme ça:

Range("A1").CurrentRegion.Name = "BasePJ"

Bon courage,

 

[GeoTrouvePas]

Re : Problème sur actualisation d'un plage nommée

Merci à vous trois d'avoir dégainé aussi rapidement.

@Pierrejean : Merci pour la soluce mais je retrouve encore le même problème. Les références de ma plage nommée sont toujours "foireuses"

@Kjin : Je n'ai absolument aucun argument pour justifier ce choix ^^ . Je n'ai tout simplement pas du voir d'avantage particulier à utiliser le Name au lieu du Range. Bizarrement, je retrouve exactement le même problème avec la macro que tu proposes.

@Jam : j'avais fait déjà fait appel à XLD car je procédais de la même façon que celle tu proposes. Je m'étais alors rendu compte que lorsque le nom que l'on attribue à la nouvelle plage, est déjà affecté à une autre plage, le résultat foire une fois sur deux.

 

[Jam]

Re : Problème sur actualisation d'un plage nommée

@Jam : j'avais fait déjà fait appel à XLD car je procédais de la même façon que celle tu proposes. Je m'étais alors rendu compte que lorsque le nom que l'on attribue à la nouvelle plage, est déjà affecté à une autre plage, le résultat foire une fois sur deux.

Bizarre, bizarre ce que tu me dis là, car je n'ai jamais rencontré cette erreur et pourtant j'utilise cette méthode depuis fort longtemps (XL2K à XL2K7), notamment pour renommer en automatique des plages de bases de données que j'importe d'Access (très) fréquement.

 

[kjin]

Re : Problème sur actualisation d'un plage nommée

Bonjour à tous,
Je ne rencontre aucun pb...
Juste une question...

- J'ouvre une classeur annexe dans une nouvelle instance Excel invisible

...veut dire quoi ?
A+
kjin

 

[GeoTrouvePas]

Re : Problème sur actualisation d'un plage nommée

Bonjour,

@Jam : J'en étais arrivé à la constatation suivante :
- Si je transfère des données vers une plage "vierge" et que je la nomme : aucun problème
- Si je rajoute des données à la suite d'une plage nommée et que je la redéfinie : la modif n'est pas prise en compte
- Si je rajoute des données à la suite d'une plage nommée, que je supprime le nom de la plage et que je lui réaffecte ce nom : ça marche !

@kjin : J'utilise le code suivant pour que, visuellement, l'utilisateur ne se rende pas compte qu'un fichier annexe a été ouvert :

Set InstanceBase = CreateObject("Excel.Application")
InstanceBase.Visible = False
Set ClasseurBase = InstanceBase.Workbooks.Open(Fichier_Base)

 

[MJ13]

Re : Problème sur actualisation d'un plage nommée

Bonjour à tous

Et si tu nommes ta plage avec une de ces 2 solutions, est-ce que la nouvelle plage est bien prise en compte.

Sub a()
ActiveWorkbook.Names.Add Name:="Plage2", RefersToR1C1:=Selection
End Sub
Sub b()
ActiveWorkbook.Names.Add Name:="Plage2", RefersTo:=Range("C1:D5")
End Sub

 

[GeoTrouvePas]

Re : Problème sur actualisation d'un plage nommée

Bonjour MJ et merci pour ton intervention.

Ta solution ne devrait pas fonctionner non plus. Je viens d'identifier ce qui pose problème grâce à la remarque de Kjin.

Le fait d'ouvrir mon fichier annexe dans une autre instance Excel a pour conséquence de faire "buger" la redéfinition de la plage nommée. Pour démonter cela, je vous ai fait un petit fichier test que vous trouverez en pièce jointe (il faut lancer le fichier interface.xls)

J'ai fait deux procédures visant à injecter des données dans le fichier Base.xls.

La première ouvre base.xls de façon classique dans la même instance et la seconde l'ouvre dans une nouvelle instance.

Vous verrez qu'avec la première méthode, tout se passe bien et avec la deuxième, la plage nommée du fichier base.xls déconne.

 

[klin89]

Re : Problème sur actualisation d'un plage nommée

Bonjour à tous,

Ne faut-il pas redéfinir ta plage nommée en la préfixant avec le nom du classeur concerné ?

Klin89

 

[GeoTrouvePas]

Re : Problème sur actualisation d'un plage nommée

Non, ça ne marche pas non plus.

J'ai peut être trouvé une solution mais il reste une difficulté :

Comment récupérer le nom de plage "dans un string" à partir de la variable "Plage_A_Actualiser" (définie comme Range) ?

 

[GeoTrouvePas]

Re : Problème sur actualisation d'un plage nommée

Biiinnngoooo ! J'ai trouvé !

Si une instruction du style :

 Les références de la plage "NomPlage" sont A1:B4

ne fonctionne pas correctement lorsqu'on l'applique sur un classeur ouvert dans une instance différente, une fonction du style :

 La plage A1:B4 a pour nom "NomPlage"

fonctionne très bien (mais par sécurité, il vaut mieux faire un ".Name.Delete" avant)

Donc au final, la procédure suivante fonctionne parfaitement (jusqu'à preuve du contraire ) :

Sub Actualiser_Plage(Plage_A_Actualiser As Range)
    Dim Cptr As Long
    Dim NomPlage As String
    With Plage_A_Actualiser
        NomPlage = .Name.Name
        .Cells.Interior.Color = RGB(102, 102, 53)
        .Borders.LineStyle = xlNone
        Cptr = 1
        While .Item(Cptr + 1, 1) <> ""
            Cptr = Cptr + 1
        Wend
        .Name.Delete
        With .Resize(Cptr, .Columns.Count)
            .Name = NomPlage
            .Interior.Color = RGB(255, 255, 255)
            .Borders.LineStyle = xlNone
            .Borders.LineStyle = xlContinuous
            .Borders.Color = RGB(0, 0, 0)
            .Borders(xlEdgeLeft).Weight = xlThick
            .Borders(xlEdgeTop).Weight = xlThick
            .Borders(xlEdgeBottom).Weight = xlThick
            .Borders(xlEdgeRight).Weight = xlThick
            If .Rows.Count > 1 Then .Borders(xlInsideHorizontal).LineStyle = xlNone
            If .Columns.Count > 1 Then .Borders(xlInsideVertical).Weight = xlMedium
        End With
        With .Resize(1, .Columns.Count)
            .Borders.LineStyle = xlNone
            .Borders.LineStyle = xlContinuous
            .Borders.Color = RGB(0, 0, 0)
            .Borders(xlEdgeLeft).Weight = xlThick
            .Borders(xlEdgeTop).Weight = xlThick
            .Borders(xlEdgeBottom).Weight = xlThick
            .Borders(xlEdgeRight).Weight = xlThick
            If .Rows.Count > 1 Then .Borders(xlInsideHorizontal).LineStyle = xlNone
            If .Columns.Count > 1 Then .Borders(xlInsideVertical).Weight = xlMedium
        End With
    End With
End Sub

Je vous remercie tous pour votre précieuse aide et vous souhaite un excellent week end !