Prevent Insert Row and Delete Row from Menu

[Sherry]

I have a list of numbers in rows. I have macros for "Insert Number" and
"Delete Number". These find the correct row and either insert or delete. I
need to control this to protect sums, formulas, etc. Is there a way for me
to prevent the user from inserting or deleting from the Tool Bar?

Thanks for the help!
Sherry

 

[Jim Thomlinson]

How about using sheet protection...

 

[Sherry]

Thank you Jim! I recorded macros to Protect Sheet and checked Insert Row
and Delete Row and one to unprotect. Then I added the macros to my "Insert"
and "Delete" macros. Essentially, I protect when the workbook opens.
Unprotect to insert with macro and then protect back. Same with delete.

The Delete Row macros work great, but when I protect sheet with 'insert row"
selected, I am still able to insert a row by using the Tool Bar. If I delete
a row, I get a message stating that I cannot delete a protected cell (which
is what I want).

I'm using Excel 2003. Do you have an idea why the "insert row" part is not
taking effect?

Sherry

 

[Sherry]

Never mind the other email - I got it! Thank you so much!!!!!

 

[Gord Dibben]

Don't checkmark "Insert Row" when you protect the sheet.

Just code your macro to unprotect, insert the row(s) then reprotect.

Users will only be able to insert rows when hitting your macro button.

Sub InsertRow()
With ActiveSheet
.Unprotect Password:="secret"
With Selection
.EntireRow.Insert
End With
.Protect Password:="secret"
End With
End Sub


Gord Dibben MS Excel MVP