Prevent inserting cells

[jamieb]

I have a large spreadsheet of part numbers, part names, date..... I want it to be easy to enter new information, but some users have used insert...cells and have messed up the lines. Is there a way to only allow rows to be inserted and not just a few cells?

Jamie

 

[Oaktree]

Welcome to the board!

In Excel 2002 or later, if you protect the sheet (tools --> protection --> protect sheet), you will have several protection options. If you check "insert rows" and "insert columns", you should have the situation you described.

Note that you will have to unlock all the cells you would like users to be able to modify before you protect the sheet (press CTRL+1, go to protection, and uncheck "locked")

 

[jamieb]

Unfortunately, we only have Excel 2000 and won't be upgrading for another year.

Any other ideas?

 

[jamieb]

Ok, I've now figured out how to add and remove commands from the right click menu and the menu bar and for the most part my problem is solved. However, if I select the entire row (row header) and right-click, I get a run-time error - is there anyway to code in if a user selects a cell vs. the row header? I'm thinking the error is due to the difference in right-click menus - if you choose a cell, the menu brings up "insert...", but if you select the row header, the menu includes "insert".

Any ideas?

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)


Application.CommandBars("Cell").Reset
With Application.CommandBars("cell").Controls _
.Add(Type:=msoControlButton, before:=7, _
temporary:=True)
.Caption = "Insert Row"
.OnAction = "InsertRow"
.Tag = "brccm"
End With
Application.CommandBars("Cell").Controls.Add _
Type:=msoControlButton, _
ID:=293, _
before:=8

Application.CommandBars("cell").Controls("Insert...").Enabled = False

Application.CommandBars("cell").Controls("Delete...").Enabled = False

End Sub