MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Still adding rows..but different situation..


Posted by Malaun Koko on August 01, 2001 6:52 AM

How do I adding new rows with border..i.e. i have
my work at a1:c1 (a1 is item, b1 is name and c1
is sex ) if i have filled all this range , a new row
automatically with border will appear..is it possible?
thanks..so if i have filled a2:c2, a new rows wil appear
again and etc..


Posted by Cory on August 01, 2001 7:37 AM

Try this:
This works with a commandbutton on a form or a sheet... It checks if there's something in the first cell, then the cell next to it, and finally the cell two over. If all three have data in them, then it adds the row with borders...:

Private Sub commandbutton1_Click()
If ActiveCell.Value > "" Then
If ActiveCell.Offset(0, 1).Value > "" Then
If ActiveCell.Offset(0, 2).Value > "" Then
Application.ScreenUpdating = False
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(0, 2).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveCell.Select
Application.ScreenUpdating = True
Else
Exit Sub
End If
Else
Exit Sub
End If
Else
Exit Sub
End If

End Sub

Hope this helps,

Cory

Posted by Malaun Koko on August 01, 2001 8:38 AM

Thanks a bunch Cory..curious about one thing..could you
make it add the row automatically when the 3 cell
filled..thanks again