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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.