Solved: Insert Row Macro/VBA

[johnske]

Hey Steve and John
If the cells have no protection you can select any cell anywhere, including areas off the sheet. The sheet then loose ist containment. I want it to only have data entry ranges to be selectable.

(sigh...) Larry, I think you have a misapprehension of what protection is or does - even though you can select them, the cells ARE completely protected.

If you only want the data entry ranges to be selectable it's not quite as simple to trigger an event to insert your new row, you need to have the 1st formula column selectable to trigger the event, as shown below (Note that although the 1st formula column (K) can be selected simply to trigger the event, the contents of the cells in that column cannot be changed without unlocking the sheet. However, apart from the 1st formula column, only the data entry ranges are now selectable...)
[vba]'<< EG for sheet "vehicle data"
Option Explicit
Private Changed As Boolean
'
Private Sub Worksheet_Activate()
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Select
End Sub
'
Private Sub Worksheet_Change(ByVal Target As Range)
Changed = True
End Sub
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
Dim Cell As Range
'
'**************************
'enter the last FORMULA column below
Const LastFormulaCol As String = "M"
'**************************
'
If Target.Row = 1 Then Exit Sub
'
'//if past the last manual entry in this row
If Changed = True _
And Target.Row = Range("B" & Rows.Count).End(xlUp).Row _
And Target.HasFormula _
And Range(LastFormulaCol & Target.Row) <> 0 Then
'
On Error GoTo Finish
ActiveSheet.Unprotect password:=""
'
Application.EnableEvents = False
'
'//insert a new row below
Rows(Target.Row + 1).Insert shift:=xlDown
'
'//copy the row
Rows(Target.Row).Copy
'
'//paste the formats in the new row
With Rows(Target.Row + 1)
.PasteSpecial xlPasteFormats
'
'//get rid of the copied unwanted heavy line
.Borders(xlEdgeTop).LineStyle = xlNone
End With
'
'//restore the thin border in the "M" column
With Range(LastFormulaCol & Target.Row + 1).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'
'//copy the formulas to the new row
For Each Cell In Range(Target.Address, LastFormulaCol & Target.Row)
Cell.Offset(1, 0) = Cell.FormulaR1C1
Next
'
'//select column B in the new row for next entry
Range("B" & Target.Row + 1).Select
ScrollArea = "B9:K" & Selection.Row
Changed = False
End If
'
Finish:
Application.EnableEvents = True
ActiveSheet.Protect password:=""
End Sub[/vba]

[LarryLaser]

Hey John
Thanks for the post. A couple of Questions.
Does [VBA]ScrollArea = "B9:K" & Selection.Row [/VBA] make cell "K" selectable or do I need to change the Protection properties for "col K"??

[johnske]

Yes.

No.

This cat has been well and truly skinned, if you have some new questions please start a new thread.