Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for a Border | Excel Discussion (Misc queries) | |||
I need a macro for inserting a border line every 20 rows | Excel Discussion (Misc queries) | |||
Snap an object to a cell border via macro | Excel Discussion (Misc queries) | |||
Place a Bottom Border in a Table via a Macro | Excel Discussion (Misc queries) | |||
toggling | Excel Worksheet Functions |