VBA : bordures sur plage de cellules

[Eloid]

Bonjour a tous

Je cherche à fignoler un tableau qui contient notamment des MFC. Le problème est que je suis sous Excel 2000, qui n'autorise que 3 MFC par cellule.
Pour répondre a mes attentes, je voudrais résoudre au moins une MFC en ajoutant un code VBA

Dans mon exemple ci-joint, sur la plage A1:H20, la colonne A se remplit automatiquement par un "1" si la ligne contient quelque chose.
Exemple, sur la ligne 1, de ce que je voudrais : si A1 n'est pas vide --> mettre des bordures autour de toutes les cellules de A1 à H1

J'ai un début de piste avec le code suivant :

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cel As Range

For Each Cel In Range("A1:H20")
   If Cel <> "" Then
ActiveCell.EntireRow.Borders.Weight = xlThin

End If

Next

End Sub

Mais ce n'est pas bon car :
1- ça encadre toute la ligne : je ne sais pas limiter la selection à la ligne active ET colonnes de A à H
2- ça encadre la ligne QUI SUIT celle où j'ai fait ma saisie. En effet, quand je fais "entrée" pour valider la cellule, j'atteris à la ligne suivante ! Et quid si l'utilisateur utilise sa souris pour changer de cellule ?!
3- j'aimerais aussi que au cas où la valeur "1" dans la colonne A disparaisse, alors l'encadrement disparaisse aussi
---> il faudrait que l'encadrement ne découle pas de la cellule active mais plutôt de la présence ou non d'un caractère dans la colonne A

J'avoue que je ne suis pas une surdouée de VBA et mon code doit être très loin du compte !

Merci pour votre aide

 

[job75]

Re : VBA : bordures sur plage de cellules

Bonjour Eloid,

Par exemple ceci :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
For Each cel In Range("A1:A20")
  With cel.Resize(, 8).Borders
    If cel <> "" Then
      .Weight = xlThin
    Else
      .LineStyle = xlNone
    End If
  End With
Next
End Sub

C'est simple car il n'y a que 20 lignes à traiter.

S'il y en avait des milliers, il faudrait voir autre chose...

A+

 

[Eloid]

Re : VBA : bordures sur plage de cellules

Excellent !! C'est exactement ce qu'il me fallait !

C'est simple car il n'y a que 20 lignes à traiter.

S'il y en avait des milliers, il faudrait voir autre chose...

En réalité, il y a 324 lignes à traiter mais l'original étant confidentiel, j'ai mis le fichier joint comme exemple. Par ccontre, il n'est pas exclu que ce tableau soit amené à évoluer au delà de 1000 lignes... quel serait le problème avec ce code ? Trop long à traiter la plage de cellule ?

Quoi qu'il en soit, appliqué à ma plage de 324 lignes X 20 colonnes, ca marche impec !!
Donc merci beaucoup

 

[job75]

Re : VBA : bordures sur plage de cellules

Bonsoir Eloid, le forum,

Je vous devais cette solution qui n'utilise pas de boucle.

Quel que soit le nombre de lignes, c'est de loin la plus rapide :

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Intersect(Target, [A:A])
If Target Is Nothing Then Exit Sub
On Error Resume Next
Intersect(Target.SpecialCells(xlCellTypeBlanks).EntireRow, [A:H]).Borders.LineStyle = xlNone
Intersect(Target.SpecialCells(xlCellTypeConstants).EntireRow, [A:H]).Borders.Weight = xlThin
End Sub

A+

 

[Staple1600]

Re : VBA : bordures sur plage de cellules

Bonsoir le fil, job75

Bravo job
Je n'arrivais pas à supprimer la boucle

Sub Macro1()
Dim i As Byte
For i = 1 To 7
With Range("A1:A10").SpecialCells(xlCellTypeConstants, 1)
.Borders.LineStyle = 1
.Offset(, i).Borders.LineStyle = 1
End With
Next i
End Sub

En effet le code ci-dessous ne fonctionne pas

Sub pasok()
Range("A1:A10").SpecialCells(xlCellTypeConstants, 1).Resize(, 7).Borders.LineStyle = 1
End Sub

 

[job75]

Re : VBA : bordures sur plage de cellules

Bonjour Eloid, bonjour Staple

Le test If est inutile puisqu'on utilise On Error Resume Next :

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Intersect(Target, [A:A])
On Error Resume Next
Intersect(Target.SpecialCells(xlCellTypeBlanks).EntireRow, [A:H]).Borders.LineStyle = xlNone
Intersect(Target.SpecialCells(xlCellTypeConstants).EntireRow, [A:H]).Borders.Weight = xlThin
End Sub

Noter que si l'on modifie manuellement (par la commande Format de cellule) les bordures d'une plage, elles ne seront ensuite modifiées que si l'on modifie les valeurs en colonne A.

On peut donc préférer cette macro :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
On Error Resume Next
Intersect([A:A].SpecialCells(xlCellTypeBlanks).EntireRow, [A:H]).Borders.LineStyle = xlNone
Intersect([A:A].SpecialCells(xlCellTypeConstants).EntireRow, [A:H]).Borders.Weight = xlThin
End Sub

A+

 

[Eloid]

Re : VBA : bordures sur plage de cellules

Merci beaucoup a tous les deux de vous être penchés sur la question.
C'est vrai que j'ai adapté ce code à un 2ème onglet et cette fois je comprends ce que représentent les inconvénients de la boucle (même si le temps de traitement reste raisonnable).

Par contre, un petit souci : ça ne fonctionne pas... Et là je ne suis pas assez douée pour détecter ce qui cloche (j'ai vu qu'il n'y avait pas de "End If" mais vu que ça ne me fait pas de message d'erreur...???)

Pas de message d'erreur mais pas de bordure non plus

 

[Pierrot93]

Re : VBA : bordures sur plage de cellules

Bonjour à tous,

Par contre, un petit souci : ça ne fonctionne pas... Et là je ne suis pas assez douée pour détecter ce qui cloche (j'ai vu qu'il n'y avait pas de "End If" mais vu que ça ne me fait pas de message d'erreur...???)

petite info au passage, pas besoin de "end if" si les instructions sont sur la même ligne.
bonne journée
@+

 

[job75]

Re : VBA : bordures sur plage de cellules

Re, salut Pierrot

Par contre, un petit souci : ça ne fonctionne pas...

Vous croyez vraiment que je donne des codes sans les avoir testés ???

Ma boule de cristal dort encore, joignez le fichier pour la réveiller.

Edit : votre fichier étant confidentiel, vous pouvez ne garder que quelques lignes en mettant des données "bidons".

A+

 

[Eloid]

Re : VBA : bordures sur plage de cellules

Bonjour a tous,

Je vous demande pardon pour mon ignorance en VBA... J'essaye de me soigner, et je note dès aujourd'hui qu'il n'y pas besoin de "End If" si les instructions sont sur la même ligne ou si l'on utilise "On Error Resume Next"

Vous croyez vraiment que je donne des codes sans les avoir testés ???

Non, non, ce n'est pas du tout ce que j'ai voulu dire et je ne voulais surtout pas vous vexer, je vous suis déjà très reconnaissante pour votre aide précieuse !!

De plus, vous avez raison, je viens de comprendre que mon problème est inhérent à mon fichier : ma colonne A contient des formules pour qu'apparaisse "1" si un champ de la ligne est rempli. Dans le code qui faisait une boucle, on se servait de la présence ou non d'un caractère dans la ligne, ce qui n'est plus le cas ici.
Je pourrais résoudre le problème en modifiant le champ défini dans "Target" mais il se trouve que certaines de mes cellules contiendront des formules, je préfèrerais donc conserver la condition de présence d'un caractère.

Je vais chercher de mon côté, et désolée pour le "ça ne fonctionne pas"

EDIT : je remet le fichier, sur lequel j'ai appliqué le code, mais qui ne fonctionne pas à cause de cette colonne A

 

[Si...]

Re : VBA : bordures sur plage de cellules

Salut

dans le fichier joint et avec les premières explications, les lignes sont indépendantes ;donc point besoin d'une boucle. Je me contenterais de

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [B:H]) Is Nothing Then Exit Sub
    Dim rP As Range
    Set rP = Range(Cells(Target.Row, 2), Cells(Target.Row, 8))
    rP.Borders.LineStyle = IIf(Application.CountBlank(rP) < 7, xlContinuous, xlNone)
End Sub

on pourrait intégrer dans le code les formules de la colonne A

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [B:H]) Is Nothing Then Exit Sub
    Dim rP As Range, lLi As Long, bN As Byte
    lLi = Target.Row
    Set rP = Range(Cells(lLi, 2), Cells(lLi, 8))
    bN = Application.CountBlank(rP)
    rP.Borders.LineStyle = IIf(bN < 7, xlContinuous, xlNone)
    Cells(lLi, 1) = IIf(bN < 7, 1, "")
End Sub

 

[job75]

Re : VBA : bordures sur plage de cellules

Re,

Si ce sont des formules en colonne A, ce n'est pas l'évènement Change qu'il faut utiliser.

Utilisez alors l'évènement Calculate :

Private Sub Worksheet_Calculate()
On Error Resume Next
Intersect([A:A].SpecialCells(xlCellTypeBlanks).EntireRow, [A:H]).Borders.LineStyle = xlNone
Intersect([A:A].SpecialCells(xlCellTypeConstants).EntireRow, [A:H]).Borders.Weight = xlThin
End Sub

A+

 

[job75]

Re : VBA : bordures sur plage de cellules

Re,

Aïe, grossière erreur, utiliser :

Private Sub Worksheet_Calculate()
On Error Resume Next
Intersect([A:A].SpecialCells(xlCellTypeFormulas, 2).EntireRow, [A:H]).Borders.LineStyle = xlNone
Intersect([A:A].SpecialCells(xlCellTypeFormulas, 1).EntireRow, [A:H]).Borders.Weight = xlThin
End Sub

A+

 

[Eloid]

Re : VBA : bordures sur plage de cellules

Merci beaucoup !
En bonne novice, je n'avais pas réalisé la complexité de ma demande (oui oui, pour moi c'est compliqué)

Ce dernier code marche à merveille pour l'onglet sur lequel je travaille actuellement.
J'espère que je n'aurai pas de surprise pour les suivants, mais avec tous les codes que vous m'avez donnés, je devrai bien trouver mon bonheur !

En tout cas merci a tous car grace à ce post, j'aurais fait des progrès en VBA (je n'ai pas encore tout compris mais je m'y applique !)