Solved: disabling insert / delete command in excel

[CCkfm2000]

hi all...

i need to disable the insert / delete command in excel using vba.

please help

[compariniaa]

One way would be to write a macro that selects all the cells, unlocks them, then protects the sheet. It would look like this:

sub noInsertDelete()
Cells.Locked = False
Cells.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Unfortunately, this also keeps you from formatting cells and doing other common tasks, but you can still change the cell values, so it's not TOTALLY worthless :)
Other than that, I can't think of anything

Edited 29-Jun-06 by geekgirlau. Reason: insert vba tags

[compariniaa]

If i had just tried a little harder the first time i would have seen this. :) i think this may solve your problem:
Sub noInsertDelete()
Dim SheetNum As Integer
Dim i As Integer
Dim Shts As Integer

i = Worksheets.Count
Shts = i
SheetNum = 1

For i = 1 To Shts
On error goto Unlocked
Sheets(SheetNum).Select
Cells.Locked = False
Cells.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingHyperlinks:=True, AllowSorting:= _
True, AllowFiltering:=True, AllowUsingPivotTables:=True
SheetNum = SheetNum + 1
Next i
Sheets(1).Select
Exit Sub

Unlocked:
End Sub

[geekgirlau]

I think you'll find that most of these protection options are only available in Excel 2003 (just in case you have an earlier version)

[malik641]

Why not just disable the commandbars?

Do you need to FULLY disable all types of Inserts? Like the whole Insert menu and the Insert... in the right click? Even Insert Hyperlinks?

[malik641]

I think this covers any inserts and deletes...

Run this code once to disable inserts and deletes, and again to re-enable them
Option Explicit

Public blnSwitch As Boolean

Sub HideDeleteInsert()
Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Visible = blnSwitch
Application.CommandBars("Standard").Controls("Hyperlink...").Visible = blnSwitch
Application.CommandBars("Cell").Controls("Insert...").Visible = blnSwitch
Application.CommandBars("Cell").Controls("Insert Comment").Visible = blnSwitch
Application.CommandBars("Cell").Controls("Hyperlink...").Visible = blnSwitch
Application.CommandBars("Cell").Controls("Delete...").Visible = blnSwitch

If blnSwitch = False Then blnSwitch = True Else blnSwitch = False
End Sub

Yay? Nay?

[CCkfm2000]

thanks for all the post.

sorted.