Disable Insert & Delete Column

aayaanmayank

Board Regular
Joined
Jul 20, 2018
Messages
156
Hi Can any one help me on this. i need a code which can stop the user to delete or Insert the column from all the ways including Ribbon.

I am using below code but its not working on EXCEL RIBBON.
VBA Code:
Sub disControls()
Application.CommandBars("Ply").FindControl(ID:=847).Enabled = False
On Error Resume Next
With Application
With .CommandBars("Worksheet Menu Bar")
.Controls("Insert").Controls("Columns").Enabled = False
End With

With .CommandBars("Ply")
.Controls("Delete").Enabled = False
.Controls("Rename").Enabled = False
End With

With .CommandBars("Standard")
.Controls("Columns").Enabled = False
End With
End With

End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,286
Office Version
  1. 2010
Platform
  1. Windows
Why not protect the sheet with only allow column insert and delete prohibited
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Test exactly as instructed below (in the SAME sequence!!) in a new workbook so that you can see how it works

1 In cell A1 enter (volatile) formula ...
=TODAY()
2 Create named range MyRange with RefersToRange A1:M10
3 Place the code in sheet1's code window (not in a module)
VBA Code:
Option Explicit
Private Sub Worksheet_Calculate()
     Dim Ref As String
     Const Monitored As String = "A1:M10"
     Ref = Replace(Split(ThisWorkbook.Names("myRange").RefersTo, "!")(1), "$", "")

     If Not Monitored = Ref Then Application.Undo
End Sub
4 Enter any value in ALL cells A2:A30 and in B1:Z1
5 Try inserting columns or rows inside A1:M20 and then ouside that range
- the former is undone, the latter is allowed to stand

Explanations
1 The sequence in which the structure is built matters because the code will fail if the other items are not in place first!
2 A volatile formula recalculates when we need it to here, and that is what triggers Worksheet_Calculate when row\column is inserted\deleted
3 The formula could be placed in any cell in the worksheet without affecting the solution
3 The named range CHANGES automatically when complete rows or columns are inserted - so can test current range against original range
4 Replace(Split(xxx,"!")(1),"$","") may appear unnecessary when the full string could be used - it is used to prevent a problem if sheet name is amended

Adapting to your needs
Ask if you require help BEFORE you complicate anything
The code appears simple enough but there are hidden dangers!
 

aayaanmayank

Board Regular
Joined
Jul 20, 2018
Messages
156

ADVERTISEMENT

Thanks for your reply but it is not working.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Tested just now, following above instructions 1 to 5
- the instructions are good
- the code works

So it is difficult to understand why it does not work for you :unsure:

Q What is on your screen in place of ?????

In VBA editor open up the window where you pasted the code and look at the TOP of the screen


top left.jpg
 

aayaanmayank

Board Regular
Joined
Jul 20, 2018
Messages
156

ADVERTISEMENT

Sorry i have pasted code in Module. but it is also working for ROW, i just need for columns.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
i just need for columns

Same method as before, but with named range containing only one cell which must be to the right of the columns you are protecting

To avoid any issues test in a new workbook

1 In cell A1 enter (volatile) formula ...
=TODAY()
2 Create named range MyRange with RefersToRange AA1
3 Place the code in sheet1's code window (not in a module ;))
4 Enter any value in ALL cells in B1:AZ1
5 Try inserting columns in A to Z and then in AB:AZ
- the former is undone, the latter is allowed to stand

VBA Code:
Private Sub Worksheet_Calculate()
    Dim Ref As String
    Const Monitored As String = "AA1"
    Ref = Replace(Split(ThisWorkbook.Names("myRange").RefersTo, "!")(1), "$", "")
    If Not Monitored = Ref Then Application.Undo
End Sub
 

aayaanmayank

Board Regular
Joined
Jul 20, 2018
Messages
156
Same method as before, but with named range containing only one cell which must be to the right of the columns you are protecting

To avoid any issues test in a new workbook

1 In cell A1 enter (volatile) formula ...
=TODAY()
2 Create named range MyRange with RefersToRange AA1
3 Place the code in sheet1's code window (not in a module ;))
4 Enter any value in ALL cells in B1:AZ1
5 Try inserting columns in A to Z and then in AB:AZ
- the former is undone, the latter is allowed to stand

VBA Code:
Private Sub Worksheet_Calculate()
    Dim Ref As String
    Const Monitored As String = "AA1"
    Ref = Replace(Split(ThisWorkbook.Names("myRange").RefersTo, "!")(1), "$", "")
    If Not Monitored = Ref Then Application.Undo
End Sub
Same method as before, but with named range containing only one cell which must be to the right of the columns you are protecting

To avoid any issues test in a new workbook

1 In cell A1 enter (volatile) formula ...
=TODAY()
2 Create named range MyRange with RefersToRange AA1
3 Place the code in sheet1's code window (not in a module ;))
4 Enter any value in ALL cells in B1:AZ1
5 Try inserting columns in A to Z and then in AB:AZ
- the former is undone, the latter is allowed to stand

VBA Code:
Private Sub Worksheet_Calculate()
    Dim Ref As String
    Const Monitored As String = "AA1"
    Ref = Replace(Split(ThisWorkbook.Names("myRange").RefersTo, "!")(1), "$", "")
    If Not Monitored = Ref Then Application.Undo
End Sub
Sure, i will try this way. Thanks alot for taking out time and help me on this.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,126
Messages
5,768,263
Members
425,460
Latest member
Astros1243

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