A toggling border macro
 

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

A toggling border macro
 

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

"Tom" wrote:

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

A toggling border macro
 

It works perfectly. Thanks very much Sheeloo.

Tom
"Sheeloo" wrote in message
...
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

"Tom" wrote:

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

A toggling border macro
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

A toggling border macro
 

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

"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

A toggling border macro
 

Which 40? Rows/columns, from where.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

A toggling border macro
 

That is after selecting the entire row, then limit the top and bottom
borders
to 40 column cells along that row.

"Don Guillett" wrote in message
...
Which 40? Rows/columns, from where.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

A toggling border macro
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
That is after selecting the entire row, then limit the top and bottom
borders
to 40 column cells along that row.

"Don Guillett" wrote in message
...
Which 40? Rows/columns, from where.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

A toggling border macro
 

BTW, this works the same if you select the row number or any cell in the
row.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
That is after selecting the entire row, then limit the top and bottom
borders
to 40 column cells along that row.

"Don Guillett" wrote in message
...
Which 40? Rows/columns, from where.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

A toggling border macro
 

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

"Don Guillett" wrote in message
...

BTW, this works the same if you select the row number or any cell in the
row.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
That is after selecting the entire row, then limit the top and bottom
borders
to 40 column cells along that row.

"Don Guillett" wrote in message
...
Which 40? Rows/columns, from where.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

A toggling border macro
 

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

"Don Guillett" wrote in message
...

BTW, this works the same if you select the row number or any cell in the
row.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
That is after selecting the entire row, then limit the top and bottom
borders
to 40 column cells along that row.

"Don Guillett" wrote in message
...
Which 40? Rows/columns, from where.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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