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...

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

[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

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

[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.

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

[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


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

[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:

If Range("TopRow") <> 1 Then

[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
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

[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..

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

[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.