Solved: Prevent Row Insert - (Above Row 1)

[Philcjr]

How can I prevent a user from Inserting a Row above Row 1?

This would be for any sheet in an Excel 2003 workbook

Any help would be appreciated

Thanks,
Phil

[Philcjr]

While continuing to research a solution, I came across the posting from Justinlabenne.... http://www.vbaexpress.com/kb/getarticle.php?kb_id=660

This gave me some ideas and with some changes I kind of have this working.... can anyone else help me complete this.

Hopefully, this will give you some idea of what I am after...
[vba]
With Target
If .Address = .EntireRow.Address And .Row = 1 Then
With Application
.OnKey "{F4}", ""
.OnKey "^{Y}", ""
.EnableEvents = False
.Undo
.EnableEvents = True
MsgBox "No Inserting Rows above 1 or Deleting Row 1", vbInformation
Range("A1").Select
End With
End If
End With

With Application
.OnKey "{F4}"
.OnKey "^{Y}"
End With
[/vba]

[mikerickson]

Define a Name TopRow RefersTo: Sheet1!$1:$1

Put the formula =ROW(A:A) in a cell. This will cause the Calculate event to trigger when rows/columns are inserted/deleted. Then this code in the sheet's module should do what you want

[VBA]Private Sub Worksheet_Calculate()
If Range("TopRow").Row <> 1 Then Application.Undo
End Sub[/VBA]

[Philcjr]

Mike,
Thank you so much, this works perfectly.
Phil

[mikerickson]

I just saw a problem. If the user deletes row 1, Range("TopRow") will return an error. Try this.

[VBA]On Error Resume Next
If Range("TopRow") <> 1 Then
Application.Undo
End If
On Error Goto 0[/VBA]

[Philcjr]

Mike,
I too found this issue later... I went back in to post, but then I got kicked off the site and was not able to log back in until now.

Here is what I have for the final code and it seems to be working:

Thanks again for your help,
Phil

[vba]
On Error Resume Next
If Range("TopRow") <> 1 Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
MsgBox "No Inserting Rows above 1 or Deleting Row 1", vbInformation
End If
On Error GoTo 0

[/vba]

[lucas]

Phil, I can't seem to be able to insert any rows.....could you post your file?

[Philcjr]

Lucas, that is the point... I do not want the user to be able to insert a Row above Row 1 or delete Row 1

[Philcjr]

Here is the file.... still a work in progress

[lucas]

With the code you provided, I can't insert any rows, anywhere. I'm missing something.

Your attachment didn't come through

[Philcjr]

just posted

[lucas]

Phil, I can't insert a row anywhere on sheet Weekly DOS of your file. Row one or anywhere else for that matter....

[Philcjr]

Lucus, that is VERY interesting. I am inserting and deleting rows with no issues. Not that this should matter, but, I am using Excel 2003.

Anyone else finding the same result as Lucas?

[lucas]

Phil, I'm using 2003 also.

I just re-checked to be sure it wasn't a fluke but I still have the same problem currently.....

[Philcjr]

I closed out the file and went back into it, and now I can't Insert or Delete rows myself...GGGrrrrrrr

I guess I need to work on this further..... any ideas

[lucas]

For one thing, and I'm not sure how it comes into play. You don't have a defined named range called TopRow as Mike suggested......

When I add it, it doesn't seem to matter but you call it out in your code:

[VBA]If Range("TopRow") <> 1 Then[/VBA]

[mikerickson]

If there is no named range TopRow, the routine thinks that the user has deleted TopRow and therefore UnDo's.
Also, the test should be against the range's Row.

If Range("TopRow").Row <> 1 Then

This version makes sure that there is a named range TopRow
[VBA]Private Sub Worksheet_Calculate()

On Error Resume Next
If Range("TopRow").Row <> 1 Then
On Error Goto 0
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
MsgBox "No Inserting Rows above 1 or Deleting Row 1", vbInformation
End If
On Error GoTo 0

Rows(1).Name = "TopRow"
ThisWorkbook.Names("TopRow").Visible = False

End Sub[/VBA]

[lucas]

I see Mike has posted. I tried to post this yesterday but the forum was down. It is code for an idividual sheet and it seems to work as required too.

No named range..

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then
With Application
.EnableEvents = False
.Undo
MsgBox "No deleting row 1", 16
.EnableEvents = True
End With
Else
Exit Sub
End If
End Sub
[/vba]

[Philcjr]

Lucas and Mike thank you so much for your help and input.... I too was having difficulty logging back in.

The wife is out now, and I am watching my daughter so hopefully later tonight I will be able to play with this and complete the file.