A toggling border macro

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

On selecting an entire row, how to create a macro (which is to be attached
to a button) that colours the top and bottom borders light red. Upon
reclicking (toggling) the button the coloured borders disappear. This
process is to repeat itself. Thanks for any help.

TIA
Tom
 
Attach the macro given below to a button and test...
to test select any row and press once to get borders and again to clear...
it will toggle the top and bottom borders for the current selection

Sub toggleBorders()
If Selection.Borders(xlEdgeTop).LineStyle = xlNone Then
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 3
End With
ElseIf Selection.Borders(xlEdgeTop).LineStyle = xlContinuous Then
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Else
'do nothing
End If
End Sub
 
Attach the macro given below to a button and test...
to test select any row and press once to get borders and again to clear...
it will toggle the top and bottom borders for the current selection

Sub toggleBorders()
If Selection.Borders(xlEdgeTop).LineStyle = xlNone Then
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 3
End With
ElseIf Selection.Borders(xlEdgeTop).LineStyle = xlContinuous Then
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Else
'do nothing
End If
End Sub
 
A bit of a variation.

Sub toggleborders()
With Selection.EntireRow
If .Borders.LineStyle = xlNone Then
myBorders = Array(, xlEdgeTop, xlEdgeBottom)
For i = 1 To UBound(myBorders)
With .Borders(myBorders(i))
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 3
End With
Next
Else
..Borders.LineStyle = xlNone
End If
End With
End Sub
 
A bit of a variation.

Sub toggleborders()
With Selection.EntireRow
If .Borders.LineStyle = xlNone Then
myBorders = Array(, xlEdgeTop, xlEdgeBottom)
For i = 1 To UBound(myBorders)
With .Borders(myBorders(i))
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 3
End With
Next
Else
..Borders.LineStyle = xlNone
End If
End With
End Sub
 
It works beautifully. Thanks Don. One question, lets say if I wanted to
limit the length of the row to only 40 cells, how do you modify the codes?
Much appreciate for your help.

Tom
 
It works beautifully. Thanks Don. One question, lets say if I wanted to
limit the length of the row to only 40 cells, how do you modify the codes?
Much appreciate for your help.

Tom
 
That is after selecting the entire row, then limit the top and bottom
borders
to 40 column cells along that row.
 
That is after selecting the entire row, then limit the top and bottom
borders
to 40 column cells along that row.
 
Change to
Sub toggleborders()
'With Selection.EntireRow
With Cells(ActiveCell.Row, 1).Resize(, 40)
If .Borders.LineStyle = xlNone Then
myBorders = Array(, xlEdgeTop, xlEdgeBottom)
For i = 1 To UBound(myBorders)
With .Borders(myBorders(i))
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 3
End With
Next
Else
..Borders.LineStyle = xlNone
End If
End With
End Sub
 
Change to
Sub toggleborders()
'With Selection.EntireRow
With Cells(ActiveCell.Row, 1).Resize(, 40)
If .Borders.LineStyle = xlNone Then
myBorders = Array(, xlEdgeTop, xlEdgeBottom)
For i = 1 To UBound(myBorders)
With .Borders(myBorders(i))
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 3
End With
Next
Else
..Borders.LineStyle = xlNone
End If
End With
End Sub
 
Yes, it is very versatile and also clears up as long as the the active cell
is anywhere along the original row. Thanks once again for your help, Don.

Tom
 
Yes, it is very versatile and also clears up as long as the the active cell
is anywhere along the original row. Thanks once again for your help, Don.

Tom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Toggle Borders 4
borders don't appear in print preview? 3
toggle borders 4
Borders Rotating 90 Degrees 1
Border disappears in Print Preview & Print 1
Create new border style? 6
COLUMN BORDER ISSUE 2
Bottom Double Border in Toolbar- Excel 2003 2

Back
Top