PDA

View Full Version : Solved: Prevent Row Insert - (Above Row 1)



Philcjr
01-07-2010, 08:38 AM
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
01-08-2010, 07:45 AM
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
01-08-2010, 07:53 AM
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
01-08-2010, 08:05 AM
Mike,
Thank you so much, this works perfectly.
Phil

mikerickson
01-08-2010, 08:16 AM
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
01-08-2010, 01:42 PM
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
01-08-2010, 01:50 PM
Phil, I can't seem to be able to insert any rows.....could you post your file?

Philcjr
01-08-2010, 01:53 PM
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
01-08-2010, 01:56 PM
Here is the file.... still a work in progress :)

lucas
01-08-2010, 01:56 PM
With the code you provided, I can't insert any rows, anywhere. I'm missing something.

Your attachment didn't come through

Philcjr
01-08-2010, 02:00 PM
just posted

lucas
01-08-2010, 02:07 PM
Phil, I can't insert a row anywhere on sheet Weekly DOS of your file. Row one or anywhere else for that matter....

Philcjr
01-08-2010, 02:14 PM
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
01-08-2010, 02:20 PM
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
01-08-2010, 02:21 PM
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
01-08-2010, 02:24 PM
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
01-09-2010, 03:08 AM
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
01-09-2010, 09:31 AM
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
01-09-2010, 10:25 AM
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.