Disable Insert & Delete Column

[aayaanmayank]

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.

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

 

[offthelip]

Why not protect the sheet with only allow column insert and delete prohibited

 

[aayaanmayank]

By protecting it will disable all the other Command bar icon like shapes chart etc.

 

[Yongle]

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)

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]

Thanks for your reply but it is not working.

 

[Yongle]

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

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

 

[aayaanmayank]

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

 

[Yongle]

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

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]

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

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

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.

 

[aayaanmayank]

Sure, i will try this way. Thanks alot for taking out time and help me on this.

Can you help me on how to select a picture which is in excel ?
every time that picture gets change so i have to select picture with dynamic name.