View Full Version : Solved: disabling insert / delete command in excel
CCkfm2000
06-28-2006, 08:15 AM
hi all...
i need to disable the insert / delete command in excel using vba.
please help
compariniaa
06-28-2006, 09:38 AM
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
06-28-2006, 11:50 AM
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
06-28-2006, 06:40 PM
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
06-28-2006, 08:33 PM
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
06-28-2006, 08:46 PM
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
06-30-2006, 12:13 AM
thanks for all the post.
sorted.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.