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

 

[Yongle]

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

Q Did it work for you?

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.

It is a different topic - so start a new thread
It needs a better explanation - how is the correct picture identified?
message me the link if you want

 

[aayaanmayank]

Q Did it work for you?


It is a different topic - so start a new thread
It needs a better explanation - how is the correct picture identified?
message me the link if you want

Sure Thanks

 

[Yongle]

Did the solution in post#8 achieve what you wanted?

 

[aayaanmayank]

Did the solution in post#8 achieve what you wanted?

Honestly No because there are too many things which needs to be done like formula in Cell A1. Which will disrupt other things in my worksheet.

 

[Yongle]

I am amazed by your reply
- I explained in earlier post that =TODAY() can be placed in any cell
- there are only 4 things to be done
- perhaps I have confused you somewhere

SIMPLE INSTRUCTIONS
Choose a cell to the right of your data (in code below it is BB1 )
In that cell enter formula =TODAY()
Name that cell MyRange
Add the code and replace BB1 with the correct cell reference for the named range

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