VBA Code to stop insert and delete

Malcolm torishi

Board Regular
Joined
Apr 26, 2013
Messages
219
Hi
can any one help please. Is there a way of stopping a user to insert or delete rows and columns by not protecting the sheets and keeping All other icons and functions still available on all ribbons
thank you
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

hotabae

Board Regular
Joined
Apr 18, 2018
Messages
167
On the sheet you want to protect, you should go to the review tab on the ribbon and click "Protect Sheet".

In my version (I'm not really familiar with others), there is a list of checkboxes including:

"Format Columns"
"Format Rows"
"Insert Columns"
"Insert Rows"
"Delete Columns"
"Delete Rows"

If you check those boxes but leave the others blank, that should accomplish what you're looking for.

Edit: This is the Normal Way to accomplish this, but the same thing can be done via VBA if you need that
 
Last edited:

Malcolm torishi

Board Regular
Joined
Apr 26, 2013
Messages
219
Hi thank you for coming back. One question will this still allow a user to have all icons and functions available on all ribbons
 

hotabae

Board Regular
Joined
Apr 18, 2018
Messages
167
Yes, If you use the insert and delete options, then everything outside of Inserting/Deleting should function normally.

Though an important note:

I said this before
If you check those boxes but leave the others blank, that should accomplish what you're looking for.

But Really, its the other way around. You want to check all the boxes EXCEPT the rows/columns options.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,585
Messages
5,597,029
Members
414,116
Latest member
sfullnet

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