Erreur code VBA pour bordures sur plusieurs feuilles

Bonjour à tous,

J'essaie d'écrire un code me permettant de rétablir un style de bordure sur chacune de mes feuilles suite aux mauvaises manipulations des autres utilisateurs du fichier (fichier partagé avec une trentaine de collaborateurs).

Voici le code :

Sub Bordures()

 Dim ws As Worksheet
    For Each ws In Worksheets
           If ws.Name Like "Semaine*" Then
           ws.Unprotect "mdp"

    Range("C15:G29,C31:G33,C35:G40,C42:G44,H15:I29,H31:I33,H35:I40,H42:I44").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlDashDotDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("C12").Select
    End If
    ws.Protect "mdp"
    Next ws
End Sub

Malheureusement, à chaque lancement de la macro, j'ai une erreur (cf. fichier joint).

Auriez-vous une idée ?

Un tout grand merci d'avance.

Nico.

Bonjour,

Déprotège ton fichier et ça doit marcher.

Bonjour,

Oui parce que de mon côté il n'y a pas d'erreur quand je le lance

Re,

Merci pour la réponse, c'est effectivement un problème avec la protection de la feuille en fin de code tout simplement !

Bonne journée à vous

Nico.

Bonjour,

C'est à croire que tu as commis ce code pour me permettre ma diatribe habituelle sur les effets nocifs du code enregistré ou issu d'enregistrement ou imitant le code enregistré...

Voilà une procédure qui, écrite correctement en VBA, ne rencontrera aucune difficulté à s'exécuter. Ecrite ainsi, elle reste à la merci de l'environnement...

Je préconise donc de supprimer tous les Select et Selection figurant dans ce code, de doter les expressions des qualificateurs d'objet qui conviennent. Si à la suite, il avait encore des difficultés, ce qui ne devrait pas sauf erreur de syntaxe, je m'engage à apporter mon aide pour réduire les erreurs éventuelles

Mais quand on cherche des bâtons pour se faire battre, il est logique d'y parvenir à un moment ou un autre .

Cordialement.

c'est effectivement un problème avec la protection de la feuille en fin de code tout simplement !

Justement non ! Le problème n'est pas là du tout !

Ce n'est pas parce qu'en supprimant la reprotection on fait disparaître l'erreur d'exécution que la macro fonctionnera correctement.

La protection permet dans ce cas de faire apparaître le problème qui sans elle ne serait pas apparu, et que l'on n'aurait pu que constater ultérieurement en découvrant que la macro ne faisait pas ce que l'on souhaite !

Je vois que tu n'as pas encore eu le loisir de constater que ta macro ne faisait pas ce qu'il était souhaité qu'elle fasse !

Je crois que tu devrais approfondir les tests pour pouvoir parvenir à le constater.

Cordialement. En attendant ton retour sur le sujet.

Bonjour MFerrand,

Effectivement, pour me faire battre, je me fais battre !

C'est effectivement un code enregistré en partie via l'enregistreur macro, et pour être honnête, je ne me sens pas capable de faire mieux que lui

J'ai pourtant testé de l'écrire différemment, mais mes compétences me font défaut.

J'ai cependant tout à fait conscience que cela puisse piquer les yeux des initiés, et ne demande qu'à apprendre et m'améliorer !

Si tu veux me proposer un code similaire bien écrit avec tes explications, j'en serais plus que ravi

Justement non ! Le problème n'est pas là du tout !

Ce n'est pas parce qu'en supprimant la reprotection on fait disparaître l'erreur d'exécution que la macro fonctionnera correctement.

Je t'avoue que cela me semble effectivement très bizarre étant donné que j'utilise cette technique sur la plupart de mes codes sur différents fichiers sans rencontrer de problèmes, donc tes explications seraient également les bienvenues !

Merci de ton implication

Mais s'il te plait, pas taper pas taper !

Cordialement,

Nico.

Je trouve ton message maintenant. D'accord pour une explication détaillée... mais cela attendra bien que j'ai mangé ! (décalage horaire...).

Par ailleurs, pour modifier du code enregistré, ce n'est pas très compliqué, il faut surtout comprendre ce que fait l'enregistreur pour ne pas faire comme lui... A tout à l'heure.

Qu'est-ce qui se passe quand tu enregistres ? L'enregistreur reproduit tes mouvements dans le tableurs et les actions que tu accomplis à la souris ou au clavier. Exemple d'un copier-coller simple :

Range("C15:E21").Select

ActiveSheet.Paste

Action exécuté sans faux mouvement, sinon ils seraient également enregistrés. Là c'est déjà que tu te trouvais sur la feuille où copier, sinon il y aurait une 6e ligne avant pour s'y rendre.

En effet, tu sélectionnes à la souris, puis tu agis sur la sélection. Pour revoir les codes dans presque tous les cas tu peux contracter cette opération :

Range("C15:E21").Copy

Pour le 2e volet, c'est la même contraction qui intervient mais concernant la feuille :

Sheets("Feuil2").Paste car la méthode Paste utilisée réfère à l'objet Worksheet, alors que la méthode Copy précédente référait à un objet Range. L'indication de la destination du collage devra intervenir sur la même ligne comme argument pour la spécifier.

Ce que tu peux déjà voir, c'est qu'une ligne de commande VBA correspond à une action, le fait de sélectionner est une action, nécessaire à la main pour désigner la source où la cible, mais non nécessaire si tu indiques la source et la cible par leur nom comme on le fait en VBA.

On réduit donc à deux actions : copier et coller à ce stade. Mais la méthode Copy permet d'indiquer la destination, soit où coller, on peut donc se passer entièrement du 2e volet pour programmer une seule action de copier-coller :

Range("C15:E21").Copy Sheets("Feuil2").Range("A2")

On a donc réduit les 5 ou 6 lignes à une seule, gain en volume de code et commande plus facile à lire, mais surtout tu ne demandes plus à VBA de sélectionner, donc plus rapide.

L'objet Range copié n'est pas qualifié, c'est à dire qu'on ne sait à quel objet parent il appartient. Pour une commande plus correcte et sans ambiguïté :

Sheets("Feuil1").Range("C15:E21").Copy Sheets("Feuil2").Range("A2")

A noter car le problème de ta macro repose sur un défaut de qualification.

Une autre commande que tu trouveras souvent dans le code enregistré :

Application.CutCopyMode = False Si elle figure c'est qu'il s'agit d'une action que tu as accompli (sans doute sans le savoir) ! Soit après une copie, action sur une commande quelconque ou un appui sur la touche Escape, ce qui a pour effet de vider le presse-papier (ce que signifie cette commande). On peut donc l'ignorer dans le code qu'on conserve (elle n'a d'utilité que si l'on ferme le classeur juste après une copie : provoquerait un message te demandant si tu veux conserver le contenu du presse-papier ; et éventuellement pour éliminer le scintillement de la zone copiée si retour dessus avant d'avoir fait autre chose qui l'aurait éliminée).

Je laisse ici de côté le fait qu'il existe d'autres façons que copier et coller de transférer des données d'un endroit à un autre, que là seul VBA permet d'utiliser, qui n'apparaîtront donc jamais dans un enregistrement.

Autre problème du code enregistré est que lorsque tu enregistres une commande en utilisant un paramètre, parfois deux, tu retrouves l'énumération de tous les paramètres, les autres étant à leur valeur par défaut qui n'auraient pas changée en les omettant. Il y a donc toujours un nettoyage plus ou moins important à faire.

TintAndShade est un bon exemple : la plupart des utilisateurs ne sauront même pas de quoi il s'agit, seront sinon bien en peine pour le régler ou trouver l'endroit où on peut le régler. Pourtant on le trouve systématiquement dans tout code enregistré qui touche aux couleurs...

Je reviens.

Venons-en à ton code :

Sub Bordures()
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name Like "Semaine*" Then
           ws.Unprotect "mdp"

        (...)

        End If
        ws.Protect "mdp"
    Next ws
End Sub

Rien à dire sur la structure, sinon que le reprotection se trouve hors instruction conditionnelle, donc s'il y a d'autres feuilles dans le classeur que les feuilles Semaine x, elles seront également protégées. Si cela doit être, c'est donc logique.

Que se passe-t-il après la reconnaissance et la déprotection de la feuille :

Range("C15:G29,C31:G33,C35:G40,C42:G44,H15:I29,H31:I33,H35:I40,H42:I44").Select

On sélectionne une plage (multizones) dont on va ensuite définir les bordures...

Supposons que l'on soit positionné sur Semaine 1 au lancement de la macro. Elle va identifier Semaine 1, déprotéger, sélectionner la plage, rétablir les bordures, reprotéger et passer à la feuille suivante. Elle la reconnait, la déprotège, sélectionne la plage indiquée et... erreur ! Car elle a bien sélectionné la plage mais sur Semaine 1, qui n'est plus déprotégée, d'où l'erreur.

Range n'étant pas qualifiée réfère à la feuille active, soit Semaine 1 dans l'exemple.

Evidemment en éliminant la protection, on élimine l'erreur, mais l'action se déroulera à chaque tour de boucle sur la même feuille, et les autres ne seront pas touchées !

Je ne vais évidemment pas proposer de sélectionner ou activer chaque fois la feuille avant de sélectionner la plage... Je préconise de ne pas sélectionner et d'utiliser : With ws.Range...

Et ce genre de code de mise en forme étant souvent long mais assez répétitif, on peut essayer de le raccourcir, cela ne changera rien à l'exécution mais peut faciliter la lecture et réduit en tout cas le volume.

Sub Bordures()
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name Like "Semaine*" Then
            ws.Unprotect "mdp"
            With ws.Range("C15:G29,C31:G33,C35:G40,C42:G44,H15:I29,H31:I33,H35:I40,H42:I44")
                .Borders(xlDiagonalDown).LineStyle = xlNone
                .Borders(xlDiagonalUp).LineStyle = xlNone
                With .Borders
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .Weight = xlMedium
                End With
                .Borders(xlEdgeTop).Weight = xlThick
                .Borders(xlEdgeBottom).Weight = xlThick
                With .Borders(xlInsideVertical)
                    .LineStyle = xlDashDotDot
                    .Weight = xlThin
                End With
                .Borders(xlInsideHorizontal).Weight = xlThin
            End With
        End If
        ws.Protect "mdp"
    Next ws
End Sub

Cordialement.

Bonjour MFerrand,

Waow !

Pour un cours magistral, c'est un cours magistral !

Je te remercie infiniment pour tes explications très détaillées, j'ai compris plusieurs choses !

Ton code est effectivement redoutable d'efficacité et de rapidité

J'aimerais avoir un Wikipédia de l'excel comme toi au quotidien à mon travail, je pense que je gagnerais énormément de temps et j'apprendrais bien plus rapidement !

Merci encore du temps que tu as pris pour m'expliquer tout ça, c'est très généreux de ta part

Si je passe par la Réunion un jour, je t'offre volontiers une petite Dodo

Excellente journée à toi.

Nico.

D'accord pour la dodo lé toujours la !