Prevent user from inserting / deleting rows

[Corleone]

i have used the code below which works fine in that greys out the insert / delete options from the menu,,however, the user would still be able to insert/delete by right clicking and diong it that way, can any one offer a soloution for this (i would like to do this without using the protection option)
Thanks




Private Sub Worksheet_Activate()
Dim objCmdBrPp As CommandBarPopup

Set objCmdBrPp = Application.CommandBars("Worksheet Menu Bar").Controls("Insert")
objCmdBrPp.Controls("Rows").Enabled = False
End Sub

'=================================================

Private Sub Worksheet_Deactivate()
Dim objCmdBrPp As CommandBarPopup

Set objCmdBrPp = Application.CommandBars("Worksheet Menu Bar").Controls("Insert")

objCmdBrPp.Controls("Rows").Enabled = True
End Sub

 

[Damon Ostrander]

Hi Corleone,

I believe you can disable menu options on the Cell shortcut menu like this:

CommandBars("Cell").Controls("Insert...").Enabled = False

and of course the same for the Delete option.

Keep Excelling.

Damon

 

[Corleone]

Hi Corleone,

I believe you can disable menu options on the Cell shortcut menu like this:

CommandBars("Cell").Controls("Insert...").Enabled = False

and of course the same for the Delete option.

Keep Excelling.

Damon

thanks for your help

I Finally opted for creating a "disable macro" to be run on files to be sent out
Sub EnableMenu()
CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = True
CommandBars("Worksheet Menu Bar").Controls("Format").Enabled = True
End Sub

but with an enable macro included so i can get the menu options back again.


it would be easier to protect the sheets ut it means having to go through all the existing code which toggles on and off the protected worksheets in numerous areas