Prevent inserting cells

jamieb

New Member
Joined
Jan 18, 2005
Messages
3
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
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

New Member
Joined
Jan 18, 2005
Messages
3
Unfortunately, we only have Excel 2000 and won't be upgrading for another year.

Any other ideas? :confused:
 

jamieb

New Member
Joined
Jan 18, 2005
Messages
3
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
 

Forum statistics

Threads
1,147,666
Messages
5,742,510
Members
423,734
Latest member
123hmMission

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top