macro_man
09-23-2009, 09:21 AM
Hi
I currently have been using a Ctrl+T to toggle between two macros that either add or delete a dividing line (offet is 27 columns from active cell) between groups of data in a spreadsheet. I would like to change this to one, form-button (instead of Ctr+T). In other words ... click the button to add the dividing line, click the same button to delete the line. I did a test but am finding it does not work when changing to the next macro name. Any help would be greatly appreciated. Below is my code:
Sub Macro_ADD_LINE()
Range(ActiveCell, ActiveCell.Offset(0, 27)).Select
'add bottom border:
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
'add bold, right border:
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'change button command to delete line:
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = "Macro_DELETE_LINE"
Sheets("Sheet1").Select
Range(ActiveCell, ActiveCell.Offset(0, 0)).Select
End Sub
Sub Macro_DELETE_LINE()
'delete line:
Range(ActiveCell, ActiveCell.Offset(0, 27)).Select
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'change button command to add line:
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = "Macro_ADD_LINE"
Sheets("Sheet1").Select
Range(ActiveCell, ActiveCell.Offset(0, 0)).Select
End Sub
I currently have been using a Ctrl+T to toggle between two macros that either add or delete a dividing line (offet is 27 columns from active cell) between groups of data in a spreadsheet. I would like to change this to one, form-button (instead of Ctr+T). In other words ... click the button to add the dividing line, click the same button to delete the line. I did a test but am finding it does not work when changing to the next macro name. Any help would be greatly appreciated. Below is my code:
Sub Macro_ADD_LINE()
Range(ActiveCell, ActiveCell.Offset(0, 27)).Select
'add bottom border:
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
'add bold, right border:
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'change button command to delete line:
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = "Macro_DELETE_LINE"
Sheets("Sheet1").Select
Range(ActiveCell, ActiveCell.Offset(0, 0)).Select
End Sub
Sub Macro_DELETE_LINE()
'delete line:
Range(ActiveCell, ActiveCell.Offset(0, 27)).Select
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'change button command to add line:
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = "Macro_ADD_LINE"
Sheets("Sheet1").Select
Range(ActiveCell, ActiveCell.Offset(0, 0)).Select
End Sub