Preventing Insertion of Rows...

wotonka

Board Regular
Joined
Aug 6, 2002
Messages
142
Thanks so much in advance for any help you may offer:
I have searched this forum, and can't seem to solve my problem...I would like to prevent the user from inserting rows. I know that ater versions of Excel have built in "Protection" features which would easily accomplish this. Unfortunately, our company uses Excel 2000.

I have keyed in a constant value in cell B100 ("bottom" of the practical range). I have tried ...
Private Sub Worksheet _Change (ByVal Target As Excel.Range)
MsgBox "Range" & Target.Address & "Was Changed."
If Range ("B100").Value = "" Then
Application.Undo
MsgBox ("Do Not Insert or Delete Rows")
EndIf
End Sub.

The problem with the above is: The "Change" macro does not seem to detect the insertion of rows. It does, however, detect the deletion of rows.

Could I please ask for help to prevent insertion of rows, either by modifying the above code, or by using some other method? Again, thanks to all of you "gurus" for the kind help you provide.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
You could put a formual hidden somwhere referring to Cell B100.

if a row is inserted, that should cause the CALCULATE event to trigger...
 
Upvote 0
Thanks, Jonmo1.
I am worried that by using the "Calculate" event to detect the insertion of a row, that the error message would become a nuisance alarm, since a calculation could be performed upon change of "normal" data (when a row is not inserted). Maybe I am not understanding your suggestion completely, though.
 
Upvote 0
well, just like in your change event, you would use an IF statement, only give the message if your formula DOES NOT equal what you expect it to equal..

Also, your formula would have to be written in a way that it does not update cell reference automatically on inserting/deleting rows..

Put this formula somewhere way off the side of your data, like cell AZ1 or something..
and the formula would be
=INDEX("B:B",100)

That formula will always refer to B100, even after inserting/deleting rows, it will not change.

then in B100 put a code word or something.. "Don't Delete Me"

Then in your Calculate Event

IF Range("AZ1").Value <> "Don't Delete Me" Then
msgbox ...
Application.Undo
End If

This way, the message box will only appear if "Don't Delete Me" No longer appears in B100 (if a row was inserted or deleted)...

that should work..
 
Last edited:
Upvote 0
Thanks. Please forgive this ignorant question, but I cannot figure out the syntax of the "Calculate" event. I tried ...
Private Sub Worksheet_Calculate (ByVal Target As Excel.Range) ... but my "guessed" syntax isn't correct. Also, according to "Excel 2002 Power Programming" by Walkenbach, the "Calculate" event is a chart object. I assume it will still work in my "non-chart" worksheet, but again - the syntax is still troubling me. Could you please offer help?
Thanks !!

BTW: I couldn't get the syntax of INDEX("B:B", 100) to work, but INDEX(B:B,100,1,1) seems to work. Again, I appreciate your help.
 
Upvote 0
Why can't you use Tools>Protection...?:eek:

I use Excel 2000 and when I protected a worksheet I can't insert rows.:)
 
Upvote 0
The calcualte event doesn't have a target...so
Private Sub Worksheet_Calculate ()
 
Upvote 0
Jonmo1: That worked !!! Please accept my gratitude !!
Norie: I can't use the built in "Protection" features which examine "Add Rows" or "Delete Rows", because my company is using Excel 2000. When we upgrade, I may remove this code and use the built in cell Protection features Until then, the detection as proposed by Jonmo1 will work nicely. Thanks for helping.
 
Upvote 0
Sorry you've lost me.:confused:

Like I said I'm using Excel 2000 and when I protected a sheet I can't insert rows.
 
Upvote 0
Norie,
I guess I had a "mental momentum" that made me think I HAD TO write VBA code. Now that you reiterate, I think your suggestion of protecting the worksheet may also be a viable alternative. When I tried protecting the sheet, I could not insert or delete rows either. So, assuming that I properly select the proper "alterable" cells before I protect the sheet, that may work too.

I have learned much from this exercise, so thanks for all of the help.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top